ComprehensiveCase
- Ethics: Manipulating Data to Establish a Budget(Appendix). Healthy Bar, Inc., produces energy bars forsports enthusiasts. The company’s fiscal year ends on December 31.The production manager, Jim Wallace, is establishing a cost budgetfor the production department for each month of this coming quarter(January through March). At the end of March, Jim will be evaluatedbased on his ability to meet the budget for the three months endingMarch 31. In fact, Jim will receive a significant bonus if actualcosts are below budgeted costs for the quarter.
The productionbudget is typically established based on data from the last 18months. These data are as follows:
Reporting Period (Month) | Total Overhead Costs | Total Machine Hours |
July | $695,000 | 3,410 |
August | 700,000 | 3,454 |
September | 665,000 | 2,453 |
October | 725,000 | 3,740 |
November | 655,000 | 2,442 |
December | 672,500 | 2,695 |
January | 687,500 | 2,937 |
February | 715,000 | 3,652 |
March | 625,000 | 2,200 |
April | 632,500 | 2,244 |
May | 650,000 | 2,255 |
June | 702,500 | 3,520 |
July | 730,000 | 3,542 |
August | 735,000 | 3,597 |
September | 697,500 | 2,552 |
October | 762,500 | 3,894 |
November | 687,500 | 2,541 |
December | 705,000 | 2,805 |
You are theaccountant who assists Jim in preparing an estimate of productioncosts for the next three months. You intend to use regressionanalysis to estimate costs, as was done in the past. Jim expectsthat 3,100 machine hours will be used in January, 3,650 machinehours in February, and 2,850 machine hours in March.
Jim approaches youand asks that you add $100,000 to production costs for each of thepast 18 months before running the regression analysis. As he putsit, “After all, management always takes my proposed budgets andreduces them by about 10 percent. This is my way of leveling theplaying field!”
Required:
- Use Excel to perform regression analysis using the historicaldata provided.
- Submit a printout of the results.
- Use the regression output to develop the cost equation Y =f + vX by filling in the dollar amounts forf and v.
- Calculate estimated production costs for January, February, andMarch. Also provide a total for the three months.
- Use Excel to perform regression analysis after adding $100,000to production costs for each of the past 18 months, as Jimrequested.
- Submit a printout of the results.
- Use the regression output to develop the cost equation Y = f +vX by filling in the dollar amounts for f and v.
- Calculate estimated production costs for January, February, andMarch. Also provide a total for the three months.
- Why did Jim ask you to add $100,000 to production costs foreach of the past 18 months?
- How should you handle Jim’s request? (If necessary, review thepresentation of ethics in Chapter 1 "What Is ManagerialAccounting?" for additional information.)
