$35.$ Regression Analysis Using Excel $($Appendix$).$ Metal Products, Inc., produces metal storage sheds. The company$$s manufacturing overhead costs tend to fluctuate from one month to the next, and management would like an accurate estimate of these costs for planning and decision$-$making purposes.

The company$$s accounting staff recommends that costs be broken down into fixed and variable components. Because the production process is highly automated, most of the manufacturing overhead costs are related to machinery and equipment. The accounting staff agrees that reviewing historical data for costs and machine hours is the best starting point. Data for the past $18$ months follow.

Reporting Period $($Month$)$ Total Overhead Costs Total Machine Hours

January $$695,0003,875$

February $700,0003,925$

March $665,0002,788$

April $725,0004,250$

May $655,0002,775$

June $672,5003,063$

July $687,5003,338$

August $715,0004,150$

September $625,0002,500$

October $632,5002,550$

November $650,0002,563$

December $702,5004,000$

January $730,0004,025$

February $735,0004,088$

March $697,5002,900$

April $762,5004,425$

May $687,5002,888$

June $705,0003,188$

Use Excel to perform regression analysis. Provide a printout of the results.

Management is considering plans to expand by opening several new facilities and asks you to estimate costs for $22,000$ machine hours. Calculate your estimate, and explain why this estimate may be misleading and what can be done to improve the estimate made in part d$?$

Use the results of the regression analysis to estimate costs for $3,750$ machine hours.

Use the regression output given to develop the cost equation Y $=$ f $+$ vX by filling in the dollar amounts for f and v$.$

Management is considering plans to expand by opening several new facilities and asks you to estimate costs for $22,000$ machine hours. Calculate your estimate, and explain why this estimate may be misleading. What can be done to improve the estimate made in part d$?$