I just started work for a company and have been asked to review how corporate policy relates to business strategy implemented at the business unit level. I am a political science major with some statistics but don't have a good grasp of how to approach this.
The attached Excel file displays the results of a survey completed by selected management at the corporate, regional, division, and department levels and reflects their view of how six elements at each level relate to the other levels (except for Division which has seven elements).
So far I have added in a dummy variable to account for the additional set of data at the division level, determined the standard deviation, color coded the data to disply up to three standard deviations above the mean, and correlated the data in each panel. The standard deviation seemed to work all right but the correlations told me very little.
I need to discern the stronger and weaker relationships among the data elements and develop an easily understandable display. I also need to do some cluster analysis.
1. Is the standard deviation a good measure for this and is it used correctly?
2. How would you suggest I analyze the data?
3. How would you graphically display the results?
I will probably be limited to Excel with the Analysis Toolpak and Powerpoint. Any analysis or examples you could provide would be appreciated.
POLICY TO STRATEGY ANALYSIS
Since you have analyzed the standard deviation I directly move onto the correlations.
Please see the cluster analysis on the excel sheet.
CLUSTER ANALYSIS allows you to place variables or objects into subgroups or clusters. These clusters are not defined a priori by you bit are formed by the cluster analysis procedure itself. The important points are"
1. They form sub groupings and assigning variables or objects to these groups.
2. They take as input a matrix of associations between variables or objects; a correlation matrix is an example of one such matrix (this is what you already have in hand!). We can take any clustering algorithm that can take nominal, ordinal, interval or ratio measures in this matrix of associations as input..
3. They assume that natural clusters exist within the data.
In case of your data we use cluster analysis to any of the variables or objects (corporate, regional, division, and department). And your major task like in the placement of objects into clusters, based upon the value these objects have on a set of variables.
In case of your data we have used cluster analysis by using different algorithms to 'cluster' the variables, which are the departments, the regions and divisions. ...