Question

Please use Excel to complete the following problem:

Joyce Corporation prepares monthly operating and financial budgets. The operating budgets for June and July are based on the following data:

  Units produced  Units sold  

June: 400,000 360,000

July: 360,000 400,000

All sales are at $30 per unit.
Direct materials, direct labor, and variable manufacturing overhead are estimated at $3, $6, and $3 per unit, respectively.
  
Total fixed manufacturing overhead is budgeted at $1,080,000 per month.

Selling and administrative expenses are budgeted at $1,200,000 plus 10% of sales, while federal income taxes are budgeted at 40% of income before federal income taxes.

The inventory at June 1 consists of 200,000 units with a cost of $17.10 each.

a. Prepare monthly budget estimates of cost of goods sold assuming that FIFO inventory procedure is used.
b. Prepare planned operating budgets for June and July

Answer 1

a.

Cost of Goods Sold
	June	July
Beginning Inventory	$     34,20,000	$        35,28,000
Direct Material	$     12,00,000	$        10,80,000
Direct Labor	$     24,00,000	$        21,60,000
Variable Manufacturing Overhead	$     12,00,000	$        10,80,000
Fixed Manufacturing Overhead	$     10,80,000	$        10,80,000
Cost of Goods Available for sale	$     93,00,000	$        89,28,000
Less : Ending Inventory	$     35,28,000	$        30,00,000

b.

Sales Budget
	June	July
Units sold	360000	400000
Sales price per unit	$                   30	$                     30
Sales Revenue	$ 1,08,00,000	$     1,20,00,000
Production Budget
	June	July
Units sold	360000	400000
Add : Ending Inventory	240000	200000
	600000	600000
Less : Beginning Inventory	200000	240000
Units Produced	400000	360000
Direct Material Budget
	June	July
Units Produced	400000	360000
Direct material per unit	$                     3	$                       3
Direct Material Purchases	$     12,00,000	$        10,80,000
Direct Labor Budget
	June	July
Units Produced	400000	360000
Direct Labor per unit	$                     6	$                       6
Direct Labor cost	$     24,00,000	$        21,60,000
Manufacturing Overhead Budget
	June	July
Units Produced	400000	360000
Variable Overhead cost per unit	$                     6	$                       6
Variable Overhead	$     24,00,000	$        21,60,000
Fixed Overhead	$     10,80,000	$        10,80,000
Total Overhead	$     34,80,000	$        32,40,000