"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. Your response to the general manager will be in three parts: An Excel spreadsheet that details your analysis, a 3 slide PowerPoint presentation that shows some key graphs, and a two to three page report on the situation that addresses the company’s operations, cites your analysis, and provides recommendations. You need to use the data to describe the business, plus give your personal recommendations on what the business seems to be doing right, what needs to change, and/or what the manager might need to be concerned about. Write your report with the intention that the general manager will view your complete product as your deliverable, that is, your report should use your Excel and PowerPoint analysis as “attachments” that provide evidence to your narrative. Answer the following five questions using Excel and refer to your datasheet. Make sure your answers include all the data required and are easily accessible for the professor to locate and grade.

1. Calculate the descriptive statistics (summary statistics) for total sales (in K units). Show the Descriptive Statistics as your output on the Excel sheet.

2. Using the unit data by product line, compile a breakdown of sales by product line.
What percentage of the total sales is for each product line?
Also, currently the sales total equals 528,000 units.
If the sales total was changed to 100,000 total units, how many units will Alpha, Beta, and Gamma have?

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 shows the mean 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 around the mean presenting both the upper and lower limits

5. We want to predict total costs, and we know the number of units produced. Do a simple linear regression to predict the total costs. What is your dependent variable? What is your independent variable? Write the regression equation showing the relationship between the independent and dependent variables. Also, use the R2 and t-statistic to tell me if this model is suitable for business decision making? If so, why? If not, why not? (Hint: copy and paste special, transpose the data into columns, then run the regression tool.)
"

Tonya took out a loan to help pay for her house. She borrowed $70,000 for 15 years at a yearly simple interest rate of 5%. How much interest will she end up paying the bank?

Simple Interest(I) = PRT

Principal(P)- $70,000
Rate of Interest(R)- 5%= 5/100= 0.05
Time(T)- 15 years

I=P*R*T
I= 70000*0.05*15
I= $52500

She will pay interest of $52500

To answer these questions, you will need to use Microsoft Excel to perform calculations and analyze the data. Here's a step-by-step guide on how to approach each question:

1. Calculate descriptive statistics for total sales:
- Open Excel and create a new spreadsheet.
- Enter the total sales data for each quarter in a column.
- Use the "Descriptive Statistics" function in Excel to calculate the summary statistics for the total sales data. To do this, follow these steps:
- Select an empty cell where you want the summary statistics to appear.
- Go to the "Formulas" tab in the Excel ribbon.
- Click on "More Functions" and select "Statistical."
- Choose "DEVSQ" from the list of functions.
- Enter the range of cells containing the total sales data as the argument.
- Press Enter to calculate the sum of squares of deviations.
- Repeat the steps above for calculating mean, standard deviation, minimum, maximum, and other relevant statistics.

2. Breakdown of sales by product line:
- Create a new sheet in Excel and enter the unit data by product line.
- Calculate the total sales for each product line by summing up the units sold.
- Divide the total sales for each product line by the overall total sales to get the percentage of total sales for each product line.
- To calculate the number of units for each product line if the sales total was changed to 100,000 units, you can use proportionality:
- Calculate the proportion of each product line's sales to the total sales.
- Multiply the proportion by 100,000 to find the number of units for each product line.

3. Calculating the percentage of thermostats needing recalibration:
- Use the given mean (70 degrees) and standard deviation (2 degrees) for the calibration data.
- Determine the inspector's cutoff value from the data sheet.
- Use the z-score formula to find the percentage of thermostats that will test below the cutoff value. The formula is:
- Z = (X - μ) / σ, where X is the cutoff value, μ is the mean, and σ is the standard deviation.
- Use the standardized normal distribution table or Excel's "NORM.S.DIST" function to find the percentage.

4. Constructing a confidence interval for employee training time:
- Use the provided sample mean (40 hours) and standard deviation from the datasheet.
- Use the t-distribution table or Excel's "T.INV.2T" function to find the critical t-value for a 95% confidence interval with the given sample size (40).
- Use the formula for the confidence interval mean ± margin of error, where the margin of error is the critical t-value multiplied by the standard deviation divided by the square root of the sample size.
- Calculate both the upper and lower limits of the confidence interval using the formula.

5. Performing a simple linear regression for predicting total costs:
- Create a new worksheet in Excel and enter the number of units produced and the corresponding total costs.
- Select the data and go to the "Data" tab in the Excel ribbon.
- Click on "Data Analysis" in the "Analysis" group and choose "Regression."
- In the regression dialog box, choose the number of units produced as the independent variable (x) and the total costs as the dependent variable (y).
- Check the "Labels" box if you have labeled your data.
- Click "OK" to perform the regression analysis.
- The regression output will provide the regression equation, R-squared value, and t-statistic.
- The dependent variable is the total costs, and the independent variable is the number of units produced.
- Evaluate whether the model is suitable for business decision making based on the R-squared value, which indicates the proportion of the variation in the dependent variable that can be explained by the independent variable. A higher R-squared value suggests a stronger relationship between the variables. The t-statistic helps determine if the independent variable is statistically significant in predicting the dependent variable.