Question

PROBLEM1. The Football Bowl Subdivision (FBS) level of the National Collegiate Athletic Association (NCAA) consists of over 100 schools. Most of these schools belong to one of several conferences, or collections of schools, that compete with each other on a regular basis in collegiate sports. Suppose the NCAA has commissioned a study that will propose the formation of conferences based on the similarities of the constituent schools. The file FBS contains data on schools belong to the Football Bowl Subdivision (FBS). Each row in this file contains information on a school. The variables include football stadium capacity, latitude, longitude, athletic department revenue, endowment, and undergraduate enrollment.

10. Refer to the clustering problem involving the file FBS described in Problem 1. The NCAA has a preference for conferences consisting of similar schools with respect to their endowment, enrollment, and football stadium capacity, but these conferences must be in the same geographic region to reduce traveling costs. Follow the following steps to address this desire. Apply k-means clustering using latitude and longitude as variables with k 5 3. Be sure to Normalize Input Data and specify 50 iterations and 10 random starts in Step 2 of the XLMiner k-Means Clustering procedure. Using the cluster assignments, separate the original data in the Data worksheet into three separate data sets – one data set for each of the three “regional” clusters.

a. For Region 1 data set, apply hierarchical clustering with Ward’s method to form four clusters using football stadium capacity, endowment, and enrollment as variables. Be sure to Normalize Input Data in Step 2 of the XLMiner Hierarchical Clustering procedure. Using a PivotTable on the data in the corresponding HC_Clusters worksheet, report the characteristics of each cluster.

b. For the Region 2 data set, apply hierarchical clustering with Ward’s method to form three clusters using football stadium capacity, endowment, and enrollment as variables. Be sure to Normalize Input Data in Step 2 of the XLMiner Hierarchical Clustering procedure. Using a PivotTable on the data in the corresponding HC_ Clusters worksheet, report the characteristics of each cluster.

c. For the Region 3 data set, apply hierarchical clustering with Ward’s method to form two clusters using football stadium capacity, endowment, and enrollment as variables. Be sure to Normalize Input Data in Step 2 of the XLMiner Hierarchical Clustering procedure. Using a PivotTable on the data in the corresponding HC_ Clusters worksheet, report the characteristics of each cluster.

Answer 1

SOLUTION 1;

2. A dialog box opens. in it, put the required variables from 'variables in input data' column to 'selected variables' column as shown in below.

3.click next>" to get another dialog box in which update the "clusters", "iterations" "random starts" and tick "normalize" as shown in the below screenshot;

4. Click on Next>" to click the required output options as shown below.