Production Budget (all in Units) Sales (Units) January February March 15,800 April 16,500 16,600 48,900 17,800 QTR May 17,700 Ending Inventory (with Constraint) Beginning Inventory Production 1,650 1,600 15,850 1,660 1,780 5,090 1,650 16,510 1,660 4,910 16,720 49,080 Sales Budget Sales (units) Selling Price Sales Revenue ($) January February March QTR April May 15,800 16,500 16,600 88 88 88 48,900 88 17,800 17,700 88 88 1,390,400 1,452,000 1,460,800 4,303,200 1,566,400 1,557,600
As part of his cost-reduction efforts, Fletch is interested in reducing labor costs. He asks you to evaluate the financial impact of a proposal involving an alternative labor contract for manual finishing labor (MAN-5). Under the proposed contract, the company would guarantee a 40-hour workweek for these employees. The hourly wage under the guaranteed 40-hour workweek would be $14 instead of $15. Based on the number of MAN-5 employees that the company plans to employ, this means that the company is committed to paying its MAN-5 employees for 1,600 hours each month, even if there is not enough work to keep them busy. If the number of MAN-5 labor hours per month is higher than 1,600 hours, MAN-5 employees will receive time-and-a-half overtime pay. MACH-2 labor hours will not be impacted. To fulfil Fletch’s request, first, insert a new worksheet at the end of the Excel template and set up a new direct labor budget (only for MAN-5). Then, answer the following questions:
a. Calculate the MAN-5 cost for each month in the first quarter and the total cost for the first quarter under the proposed labor contract.
b. If Fletch’s primary objective is cost reduction, would you recommend the proposed labor contract? What other factors besides cost minimization should be considered in evaluating each option?
c. What other factors besides cost minimization should be considered in evaluating each option?
d. Assume that Fletch decides to adopt option 2. But he is convinced that increase in labor MAN-5 consumption from six minutes per unit to 15 minutes per unit is excessive. At what level of MAN-5 consumption (MAN-5 hours per unit), will the company be able to achieve the total direct labor cost that was originally budgeted, if the batch size is changed to 100? Use the GOAL SEEK function in Excel to answer this question.
Step by step
Solved in 2 steps
