I don't under stand ho to get the answer to this excel question can anyone help?

and email me back
skylerbloo at netscape . net

The assignment is in three parts:

1. An analytic section that you will submit as an Excel spread sheet
2. A PowerPoint presentation of 3 to 5 slides.
3. A narrative section that you will submit as a two to three page Word document
First, read the business case and identify the business decisions that need to be made. Then, using the case datasheet, perform a statistical analysis of the data and obtain the required statistics. Submit your analysis as an Excel spreadsheet. Your spreadsheet will contain numerical data. You will then submit a PowerPoint file to showcase graphical information. Finally, in a narrative of about two to three pages, describe the business situation and the decisions that the business must make. Explain, based on your findings, a recommended course of action for the business. Refer to the statistics you calculated and the graphs you created in the spreadsheet. Submit your narrative as a Word document.

The ThermoStar Case

ThermoStar has been producing residential thermostats for over 50 years.

Looking over the past 20 quarters (5 years) of the different product lines, the general manager has been trying to get a handle on sales, costs, and production, and has asked for your help.

Answer the following using Excel and refer to your 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

Sorry here is the only information I have to go on.

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

Since we Jiskha tutors have no connection with any school, we have no access to "Final Project Spreadsheet -- Master Worksheet."

I don't think any of us has a background in business analytical statistics.

Please note that we do not email answers.

Hi

Is there a way for me to post the master worksheet? For you

To get the answer to the Excel question in the ThermoStar Case, you will need to follow these steps:

1. Open Excel and create a new spreadsheet.
2. Import or enter the datasheet provided in the assignment into Excel.
3. For question 1, you need to calculate the descriptive statistics for total sales. To do this, select the column with the total sales data and use the Excel function AVERAGE, MEDIAN, and STDEV respectively to calculate the mean, median, and standard deviation.
4. For question 2, you need to compile a breakdown of sales by product line. Use the unit data by product line and create a new table or graph to display the sales breakdown. To show the expected sales by product for 100,000 total units, you can use simple algebra to calculate the expected sales for each product line based on the proportion of total units.
5. For question 3, you need to calculate the percentage of thermostats that will test below the inspector's cutoff value. Given the mean and standard deviation, you can use the Excel function NORM.DIST to calculate the cumulative probability of the cutoff value. Subtract this probability from 1 to get the percentage value.
6. For question 4, you need to construct a confidence interval for the training program. Use the sample mean, standard deviation, and sample size to calculate the margin of error using the Excel function CONFIDENCE. Then, use the mean plus and minus the margin of error to create the confidence interval.
7. For question 5, you need to perform a simple regression for total costs. Select the data for total costs and the corresponding independent variable. Use the Excel function LINEST to calculate the regression equation, R^2, and t-statistic. Determine if the model is suitable for business decision making based on the R^2 value and the t-statistic.

Once you have completed these steps, you will have the answers to the Excel questions in the ThermoStar Case. Remember to organize the results neatly and present them in the required formats (e.g., tables, graphs, equations) as specified in the assignment.