Part two Ricardo Inc. produces helmets. The company's fiscal year ends on December 31st. The production manager, Peter Russell, is establishing a cost budget for the production department for each month of this coming quarter (January through March). At the end of March, Peter will be evaluated based on his ability to meet the budget for the three months ending March 31st. In fact, Peter will receive a significant bonus if actual costs are below budgeted costs for the quarter. Because the production process is highly automated, most of the manufacturing overhead costs are related to machinery and equipment. The production budget is typically established based on data from the last 18 months. These data are as follows: Reporting Period (Month) July August September October November December January February March April May June July August Total Overhead Costs $695,000 700,000 665,000 725,000 655,000 672,500 687,500 715,000 625,000 632,500 650,000 702,500 730,000 735,000 Total Machine Hours 3,410 3,454 2,453 3,740 2,442 2,695 2,937 3,652 2,200 2,244 2,255 3,520 3,542 3,597 September October November December 697,500 762,500 687,500 705,000 2,552 3,894 2,541 2,805 You are the accountant who assists Peter in preparing an estimate of production costs for the next three months. You intend to use regression analysis to estimate costs, as was done in the past. Peter expects that 3,100 machine hours will be used in January, 3,650 machine hours in February, and 2,850 machine hours in March. Peter approaches you and asks that you add $100,000 to production costs for each of the past 18 months before running the regression analysis. As he puts it, After all, management always takes my proposed budgets and reduces them by about 10 percent. This is my way of leveling the playing field! 1. Use Excel to perform regression analysis using the historical data provided. a. Use the regression output to develop the cost equation y = a + bx by filling in the dollar amounts for a and b. (Hint: a is the intercept, and b is the slope. You can use Excel functions to find these two estimates directly) b. Calculate estimated production costs for January, February, and March. Provide the total amount for the quarter as well. a 2. Use Excel to perform regression analysis after adding $100,000 to production costs for each of the past 18 months, as Peter requested. a. Use the regression output to develop the cost equation y = a + bx by filling in the dollar amounts for a and b. b. Calculate estimated production costs for January, February, and March. Provide the total amount for the quarter as well. 3. Why did Peter ask you to add $100,000 to production costs for each of the past 18 months? 4. How should you handle Peter's request? (You can rely on the information that you provided in Part one)