Can any one help me with this?
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.)
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
We are not privy to your data sheet. DO the best you can first.
Sra
To answer the questions using Excel, you will need to input the provided data into an Excel spreadsheet. Here's a step-by-step guide on how to perform the calculations using Excel:
1. Calculate the descriptive statistics for total sales:
a. Input the total sales data (in K units) into a column in Excel.
b. Use the AVERAGE function to calculate the mean of the data. For example, enter "=AVERAGE(A2:A21)" in a different cell to calculate the mean.
c. Use the MEDIAN function to calculate the median of the data. For example, enter "=MEDIAN(A2:A21)" in a different cell to calculate the median.
d. Use the STDEV.S function to calculate the standard deviation of the data. For example, enter "=STDEV.S(A2:A21)" in a different cell to calculate the standard deviation.
2. Compile a breakdown of sales by product:
a. Input the product line data (in K units) for each quarter into separate columns in Excel.
b. Sum the values for each product line to calculate the total sales for each product. For example, enter "=SUM(B2:B21)" in a different cell to calculate the total sales for the Alpha product line.
c. Divide the total sales for each product by the sum of the total sales to calculate the percentage contribution of each product. For example, enter "=B22/$B$27" in a different cell to calculate the percentage contribution of the Alpha product line.
d. Multiply the percentage contribution of each product by 100,000 to calculate the expected sales by product for 100,000 total units.
3. Calculate the percentage of thermostats that will test below the inspector's cutoff value:
a. Input the mean and standard deviation for thermostat calibration into separate cells in Excel.
b. Use the NORM.DIST function to calculate the cumulative probability of values below the cutoff value. For example, enter "=NORM.DIST(65,$B$32,$B$31,TRUE)" in a different cell to calculate the percentage of thermostats that will test below the cutoff value.
4. Construct a 95 percent confidence interval for the training program:
a. Input the mean, standard deviation, and sample size for training hours into separate cells in Excel.
b. Use the CONFIDENCE function to calculate the margin of error for the confidence interval. For example, enter "=CONFIDENCE(0.05, $B$36, $B$37)" in a different cell to calculate the margin of error for the 95 percent confidence interval.
c. Subtract the margin of error from the mean to calculate the lower bound of the confidence interval, and add the margin of error to the mean to calculate the upper bound of the confidence interval.
5. Perform a simple regression for total costs:
a. Copy and paste the total sales and total costs data into adjacent columns in Excel.
b. Select the data range for total sales and total costs.
c. Choose the "Data" tab in Excel's ribbon, then click on "Data Analysis" in the "Analysis" group.
d. Select "Regression" from the list of analysis tools and click "OK".
e. In the regression analysis dialog, input the total costs range as the dependent variable and the total sales range as the independent variable.
f. Check the "Labels" box if your data range includes headers.
g. Click "OK" to run the regression analysis.
h. The regression equation, R-squared (R2), and t-statistic will be displayed in the output.
Based on the results obtained, you can then answer the specific questions posed:
- Report the mean, median, and standard deviation for total sales.
- Present the breakdown of sales by product and the expected sales by product for 100,000 total units.
- Determine the percentage of thermostats that will test below the cutoff value.
- Construct the 95 percent confidence interval for the training program.
- Report the regression equation, R2, and t-statistic for the total costs regression model, and explain whether the model is suitable for business decision making.
Remember to refer to the datasheet provided in the question for accurate calculation inputs. Good luck with your analysis!