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

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 *(WU)$`

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

So,
`$AE = 10.528+0.9534 *(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.