Please use USE EXCEL AND show how answers were derived (formulas) for ALL three
ID: 2477755 • Letter: P
Question
Please use USE EXCEL AND show how answers were derived (formulas) for ALL three of the following problems!! In advance, thank you for your prompt attention.
1. Quincy Smith Enterprises expects the following unit sales over the next five months: Irwin's goal is to maintain an inventory equal to 20% of next month's sales requirements. March 31st inventory is projected to be 70,000 units. Required: 40 points Prepare a Production Budget (in units) for Irwin for April, May, June, and July.
April
Irwin's goal is to maintain an inventory equal to 20% of next month's sales requirements. March 31stinventory is projected to be 70,000 units.
Prepare a Production Budget (in units) for Irwin for April, May, June, and July.
2. Kindschuh Corporation is working on its direct labor budget for the next two months. Each unit of output requires 0.07 direct labor-hours. The direct labor rate is $8.50 per direct labor-hour. The production budget calls for producing 4,800 units in June and 5,300 units in July.
Construct the direct labor budget for the next two months, June and July
3. Keenan Williams, Inc., Inc., is preparing its budget for the second quarter of the calendar year. The following sales data (in units) have been forecasted:
Additional information:
Desired ending inventory each month--Finished goods: 30% of next month's sales
Desired ending inventory each month--Raw materials: 25% of next month's production needs
Number of raw material units required per finished unit: 4; Each units of materials costs $25
a. Prepare a Production Budget for the 2nd Quarter
b. Prepare a Materials Budget for the 2ndQuarter, in units and dollars
In advance, thank you for your prompt attention to this important matter!
MONTH UNIT SALESApril
400,000 May 480,000 June 540,000 July 600,000 August 560,000Explanation / Answer
1. Production Budget:
Formula: Monthly sales in units + Desired ending finished goods inventory - Beginning finished goods inventory = Units required to be produced
2. Direct labor budget:
Formula: Production in units x Direct labor hours per unit x Direct labor hour rate = Total direct labor cost
3. a. Production Budget for second quarter:
b. Materials Budget:
April May June July Expected sales in units 400,000 480,000 540,000 600,000 Desired ending finished goods inventory 96,000 108,000 120,000 112,000 Total units required 496,000 588,000 660,000 712,000 Beginning finished goods inventory 70,000 96,000 108,000 120,000 Units required to be produced 426,000 492,000 552,000 592,000