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.)
For the manufacturing plant discussed in Exercise 8.10, the union president and the human resources director jointly select a simple random sample of 36 employees to engage in a discussion with regard to the company’s work rules and overtime policies. What is the probability that the average number of overtime hours last year for members of this sample would have been less than 65.0 hours? Between 55.0 and 65.0 hours?
To complete the assignment and answer the questions, you will need to follow these steps:
1. Read the business case and identify the business decisions that need to be made. This will help you understand the context and purpose of the analysis.
2. Gather the case datasheet, which contains the necessary data for analysis.
3. Open Excel and create a new spreadsheet. Save it as your working file for the assignment.
4. Calculate the descriptive statistics for total sales. To do this:
a. Locate the column with total sales data and select the range of values.
b. Apply the appropriate Excel function (e.g., AVERAGE, MEDIAN, STDEV) to calculate the mean, median, and standard deviation.
5. Create a breakdown of sales by product using the unit data provided in the datasheet:
a. Identify the columns or rows with product-specific sales data.
b. Sum up the sales for each product and create a table or chart to display the breakdown.
c. Calculate the expected sales by product for 100,000 total units using appropriate formulas. Add this information to the table or chart.
6. Calculate the percentage of thermostats that will test below the inspector's cutoff value:
a. Determine the cutoff value from the datasheet.
b. Use the mean and standard deviation provided in the datasheet to calculate the Z-score for the cutoff value.
c. Look up the corresponding percentage below the Z-score in a standard normal distribution table. Multiply by 100 to get the percentage.
7. Construct a 95% confidence interval for the training program:
a. Identify the sample size, sample mean, and associated standard deviation from the datasheet.
b. Use the appropriate Excel function (e.g., CONFIDENCE.NORM or T.INV) to calculate the margin of error.
c. Calculate the upper and lower bounds of the confidence interval by adding and subtracting the margin of error from the sample mean.
8. Perform a simple regression for the total costs:
a. Transpose the data from rows to columns, if necessary, to have the costs and corresponding variables in separate columns.
b. Highlight the data, including the dependent variable (total costs) and independent variable(s) (if any).
c. Use the Excel regression tool (e.g., Data Analysis ToolPak or built-in regression functions) to obtain the regression equation, R-squared value, and t-statistic.
9. Analyze the suitability of the regression model for business decision making:
a. Examine the regression equation's coefficients and statistical significance of the variables.
b. Evaluate the R-squared value to determine the proportion of total cost variance explained by the independent variable(s).
c. Consider the t-statistic to assess the significance of the overall regression model.
d. Explain your findings and interpretation in a concise and clear manner.
10. Create a PowerPoint presentation of 3 to 5 slides to showcase graphical information:
a. Choose appropriate charts or graphs (e.g., bar chart, line chart) to visualize the sales breakdown, expected sales, and regression results.
b. Input the data and create the charts in PowerPoint.
c. Format the slides to be visually appealing and easy to understand.
d. Add titles, labels, and a brief description of each figure.
11. Write a narrative of about two to three pages in a Word document:
a. Describe the business situation and decisions that the business must make.
b. Summarize the results of your analysis, including the descriptive statistics, sales breakdown, cutoff value, confidence interval, and regression findings.
c. Explain your recommended course of action for the business based on your analysis.
d. Reference the statistics you calculated and the graphs you created in the spreadsheet and PowerPoint.
12. Save your Excel spreadsheet, PowerPoint presentation, and Word document with appropriate filenames.
13. Submit all three parts of the assignment (Excel spreadsheet, PowerPoint presentation, and Word document) based on the submission method specified by your instructor or the assignment instructions.
Remember to double-check your calculations, proofread your work, and ensure that your analysis and explanations are clear and supported by the data.