Question

1. In the file MajorSalary.xlsx, data have been collected from 111 College of Business graduates on their monthly starting salaries. The graduates include students majoring in management, finance, accounting, info systems, and marketing. Create a PivotTable in Excel to display the number of graduates in each major and the average monthly starting salary for students in each major.
a. Which major has the most graduates?
b. Which major has the highest average starting monthly salary?
c. Use the PivotTable to determine the major of the student with the lowest overall starting salary?
2. In the file Jensen.xlsx, Jensen auto is deciding whether to purchase a new maintenance contract for its new computer wheel alignment and balancing machine. The data in the file shows the relationship between usage and annual expense.
a. Develop a scatter chart with usage as the independent variable.
b. Use the data to develop a regression equation that could be used to predict the annual maintenance expense for a given numbers of hours of weekly usage. What is the model?
c. How much of the variation in the sample values of annual maintenance does the model you estimate in part b explain (Hint: Use r squared to explain).
Weekly Usage (hours) Annual Maintenance Expense
13 17.0
10 22.0
20 30.0
28 37.0
32 47.0
17 30.5
24 32.5
31 39.0
40 51.5
38 40.0

Answer 1

Solution :

a.The table below shows the different distribution of the students in differnt majors :

Subjects        No. of graduates

Accounting          28

Finance               21

Management       24

Info System         16

Marketing            22

Therefore, "accounting" have the maximum number of graduates.

b. The average monthly salaries of the graduates from some of the different majors are :

Subjects                    Average salary                Overall salary

Finance                          699                                 77595

Accounting                    1014                               112560

Info Sys                          577                                 64000

Marketing                        663                                 73590

Management                  688                                  76320

Therefore, the "Accounting" group have the highest average salary.

c. The "info System" has the lowest overall salary.

2.b. The data showing is more or less the linear relationship between the "Annual Expenses" and the "Weekly usage", therefore initially, we fit the linear regression equation of the form.

$AE = a+b \times(WU)$

The ordinary least square estimates are $\hat{a} = 10.528 \text{ and} \ \hat{b} = 0.9534$

So, $AE = 10.528+0.9534 \times(WU)$

c. The amount of the variation the estimated model explains for the entire variation is given by the measure of

$R^2 = \frac{\text{variance of fitted observation}}{\text{variance in original data}}$

    = 0.856

It shows the linear regression that explains the good amount of entire variability of the annual expenses. It also supports the assumption of the "Linear Model".

a. The Scatter plot is attached below.