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,000 3,875

February 700,000 3,925

March 665,000 2,788

April 725,000 4,250

May 655,000 2,775

June 672,500 3,063

July 687,500 3,338

August 715,000 4,150

September 625,000 2,500

October 632,500 2,550

November 650,000 2,563

December 702,500 4,000

January 730,000 4,025

February 735,000 4,088

March 697,500 2,900

April 762,500 4,425

May 687,500 2,888

June 705,000 3,188

1. Use Excel to perform regression analysis. Provide a printout of the results.
2. Use the regression output given to develop the cost equation Y =f+vX by filling in the dollar amounts forfandv.
3. Use the results of the regression analysis to estimate costs for 3,750 machine hours.
4. 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?