Discussion:
Response the following using Excel in a 3 page and refer to datasheet.
1. Calculate the descriptive statistics (summary statistics) for total sales (in K units), and report the mean, median, and standard deviation.
2. Using the unit data by product line, compile a breakdown of sales by product. Also, show the expected sales by product for 100,000 total units.
3. ThermoStar tests thermostats so they are calibrated with a mean of 70 degrees, with an associated standard deviation of 2 degrees. Given this mean and standard deviation what percentage of thermostats will test below the inspector's cutoff value and thus need to be recalibrated? (See the data sheet for the cutoff and assume that the test results are normally distributed.)
4. A sample of 40 employees showed the average time spent in training per year to be 40 hours, with the associated standard deviation as noted in the datasheet. Construct a 95 percent confidence interval for the training program.
5. Do a simple regression for the total costs. Report the regression equation, as well as the R2 and t-statistic. Is this model suitable for business decision making? Explain why. (Hint: copy and paste special, transpose the data into columns, then run the regression tool.)
| Final Project Spreadsheet -- Master Worksheet |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
| Sales and Cost Data |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
| Quarter |
1 |
2 |
3 |
4 |
5 |
6 |
7 |
8 |
9 |
10 |
11 |
12 |
13 |
14 |
15 |
16 |
17 |
18 |
19 |
20 |
Total |
| Product Line (K Units) |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
| Alpha |
5 |
5 |
8 |
10 |
11 |
11 |
12 |
11 |
12 |
9 |
12 |
11 |
11 |
11 |
13 |
12 |
13 |
14 |
15 |
12 |
218 |
| Beta |
11 |
14 |
3 |
12 |
12 |
12 |
8 |
10 |
12 |
11 |
11 |
9 |
9 |
8 |
9 |
9 |
13 |
9 |
10 |
11 |
203 |
| Gamma |
4 |
8 |
7 |
4 |
4 |
6 |
9 |
3 |
5 |
6 |
8 |
3 |
8 |
9 |
3 |
3 |
4 |
5 |
5 |
3 |
107 |
| Total Sales (K Units) |
20 |
27 |
18 |
26 |
27 |
29 |
29 |
24 |
29 |
26 |
31 |
23 |
28 |
28 |
25 |
24 |
30 |
28 |
30 |
26 |
528 |
| |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
| Total Sales $M |
2.00 |
2.70 |
1.80 |
2.60 |
2.70 |
2.90 |
2.90 |
2.40 |
2.90 |
2.60 |
3.10 |
2.30 |
2.80 |
2.80 |
2.50 |
2.40 |
3.00 |
2.80 |
3.00 |
2.60 |
52.8 |
| |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
| Total Costs $M |
1.00 |
1.35 |
0.90 |
1.30 |
1.35 |
1.45 |
1.45 |
1.20 |
1.45 |
1.30 |
1.55 |
1.15 |
1.40 |
1.40 |
1.13 |
1.08 |
1.35 |
1.26 |
1.35 |
1.17 |
25.585 |
| Quality Data |
|
| Mean |
70 |
| Standard Deviation |
2 |
| Cuttoff |
65 |
| |
|
| Training Data |
|
| Mean Hours |
40 |
| Standard Deviation |
5 |
| Employees in Sample |
40 |