Question

Spelling Company has the following sales projection (in units) for the next six months:
Feb: 9000
Mar: 10500
Apr: 8000
May: 11500
Jun: 9500
Jul: 7000

Each unit sells for $30.

Spelling has prepared the following sales budget for the quarter of April, May and June:

Sales Budget
	April	May	June	Total
Sales in units	8000	11500	9500	29000
Selling price per unit	x $30	x $30	x $30
Sales revenue	$240000	$345000	$285000	$870000

Spelling's cost of goods sold is 60% of its sales revenue. The company has a policy that it keeps 10% of next months budgeted cost of goods sold as ending inventory. The company had exactly the budgeted amount of inventory on hand at April 1.

Prepare a purchases budget on paper or, PREFERABLY, in Excel for the quarter of April, May and June. (If you build your schedule using formulas in excel, multiple attempts will be much faster.)

1. What is the cost of inventory at April 1 (Beginning inventory)

2. What is the budgeted cost of purchases in June?

3. What is the desired cost of inventory at the end of the quarter?

Answer 1

Please hit LIKE button if this helped. For any further explanation, please put your query in comment, will get back to you.

Working
Sales Budget	Mar	April	May	June	July	Quarter
Sale Units	10500	8000	11500	9500	7000	29000
Selling Price per unit	30	30	30	30	30	30
Sales Revenue	315000	240000	345000	285000	210000	870000
Purchase Budget	Mar	April	May	June	July	Quarter
Cost of Goods Sold (60% of Sales Revenue)	189000	144000	207000	171000	126000	522000
add: Ending Inventory (10% of Next Mongh COGS)	14400	20700	17100	12600		12600
Less: Beginning Inventory (Closing of the month is beginning for next)		14400	20700	17100		14400
Purchase Cost		150300	203400	166500		520200

1. Cost of Inventory at April 1
March Ending Inventory=	10% of April's Budgeted Cost of Goods Sold
	10%*(144000)
	14400
Hence April Beginning Inventory=	March Ending Inventory=14400
2. Budgeted Cost of Purchase in June
June COGS	171000
add: June Ending Inventory (10% of July COGS)	12600
less: June Beginning Inventory (May closing)	17100
June Purchase Cost	166500
3. Desired cost of inventory at end of Quarter
10% of July Cost of goods sold	10%*126000
10% of July Cost of goods sold	12600