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

CALCULATION OF COST OF GOODS SOLD
	JUNE			JULY
PARTICULAR	UNIT	RATE	AMOUNT	UNIT	RATE	AMOUNT
DIRECT MATERIAL	4,00,000.00	3.00	12,00,000.00	3,60,000.00	3.00	10,80,000.00
DIRECT LABOUR		6.00	24,00,000.00		6.00	21,60,000.00
VARIABLE MANUFACTURING OVERHEAD		3.00	12,00,000.00		3.00	10,80,000.00
FIXED MANUFACTURING COST			10,80,000.00			10,80,000.00
COST OF GOODS MANUFACTURED	4,00,000.00	14.70	58,80,000.00	3,60,000.00	15.00	54,00,000.00
OPENING FINISHED GOODS
	2,00,000.00	17.10	34,20,000.00	2,40,000.00	14.70	35,28,000.00
TOTAL UNITS	6,00,000.00		93,00,000.00	6,00,000.00		89,28,000.00
CLOSING STOCK	2,40,000.00	14.70	35,28,000.00	2,00,000.00	15.00	30,00,000.00
COST OF GOODS SOLD	3,60,000.00	16.03	57,72,000.00	4,00,000.00	14.82	59,28,000.00
OPERATING BUDGET
	JUNE			JULY
PARTICULAR	UNIT		AMOUNT	UNIT		AMOUNT
SALES	3,60,000.00	30.00	1,08,00,000.00	4,00,000.00	30.00	1,20,00,000.00
A			1,08,00,000.00			1,20,00,000.00
COST OF GOODS SOLD	3,60,000.00	16.03	57,72,000.00	4,00,000.00	14.82	59,28,000.00
SELLING AND ADMINISTRATIVE COST
FIXED			12,00,000.00			12,00,000.00
VARIABLE 10% OF SALES			10,80,000.00			12,00,000.00
B			80,52,000.00			83,28,000.00
PROFIT (A-B)			27,48,000.00			36,72,000.00
TAX @40%			10,99,200.00			14,68,800.00
PROFIT AFTER TAX			16,48,800.00			22,03,200.00
NOTE -
CLOSING STOCK CALCULATED ON THE BASIS OF FIFO METHOD
JUNE - 240000 X 14.70	=35,28,000
JULY - 200000 X 15	=30,00,000
VARIABLE SELLING AND ADMINISTRATIVE COST IS 10 % SALES VALUE
JUNE = 1,08,00,000 X 10/100	=10,80,000
JULY = 1,20,00,000 X 10/100	=12,00,000