Question

+++ Show excel formulas please +++

Marvel Company has the following budgeted sales for the selected four-month period:

Month                                  Unit Sales

October                                40,000

November                          70,000

December                           50,000

January                                 60,000

There were 14,000 units of finished goods in inventory at the beginning of October. Plans are to have an inventory of finished product equal to 25 percent of the unit sales for the next month. Five pounds of a single raw material are required for each unit produced. Each pound of material costs $10. Plans are to have inventory levels for materials equal to 30 percent of the amount of materials needed to satisfy next month's production and 84,000 units of raw material on hand at the end of December. Materials inventory on October 1 was 60,000 pounds.

Required:

a.   Prepare a production budget in units for October, November, and December.

b.   Prepare a purchase budget in pounds and dollars for October, November, and December.

+++ Show excel formulas please +++

Answer 1

Solution:

Part 1 – Production Budget

Production Budget
	October	November	December
Budgeted bats to be sold	40,000	70,000	50,000
Plus: Desired Ending Inventory (25% of next month's Unit sales)	17500 (70,000*25%)	12500 (50,000*25%)	15000 (Jan 60,000*25%)
Total Needs	57,500	82,500	65,000
Less: Estimated Beginning Inventory (Ending Inventory of last month)	14,000 (given)	17500 (Ending inventory of Oct)	12500 (Ending inventory of Nov)
Budgeted Units to be produced	43,500	65,000	52,500

Part 2 -- Purchase budget in pounds and dollars for October, November, and December

Direct Materials Budget
	October	November	December
Budgeted Units to be produced	43,500	65,000	52,500
Direct materials per unit in pounds	5	5	5
Direct materials needed for production	217500	325000	262500
Plus: Desired Raw Material Ending Inventory (30% of next month's production needs)	97500 (325,000*30%)	78750 (262500*30%)	84000 (Given)
Total direct materials needed	315000	403750	346500
Less: Estimated Beginning Inventory	60000 (given)	97500 (Oct ending inventory)	78750 (Nov ending inventory)
Budgeted purchases of direct materials	255000	306250	267750
Direct materials cost per pound	$10	$10	$10
Budgeted cost of direct materials	$2,550,000	$3,062,500	$2,677,500

Hope the above calculations, working and explanations are clear to you and help you in understanding the concept of question.... please rate my answer...in case any doubt, post a comment and I will try to resolve the doubt ASAP…thank you