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 Total Total Period Overhead Machine (Month) Costs Hours July $695,000 3,410 August 700,000 3,454 September 665,000 2,453 October 725,000 3,740 November 655,000 022 2,442 December ecca 672,500 2,695 January 687,500 2,937 ancang February 715,000 3,652 wy March 625,000 2,400 2,200 April 632.500 032,00 2,244 May 650.000 JU, 2,255 June 702.500 U2,00 3,520 July 730,000 3,542 August 735,000 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. 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)
