Question

The company has just hired a new marketing manager who insists that unit sales can be dramatically increased by dropping the selling price from $8 to $7. The marketing manager would like to use the following projections in the budget:

	Year 2 Quarter					Year 3 Quarter
Data	1	2	3	4		1	2
Budgeted unit sales	50,000	70,000	120,000	60,000		80,000	95,000
Selling price per unit	$7

Chapter 8: Applying Excel
Data	Year 2 Quarter				Year 3 Quarter
	1	2	3	4	1	2
Budgeted unit sales	50,000	70,000	120,000	60,000	80,000	95,000
• Selling price per unit	$7	per unit
• Accounts receivable, beginning balance	$65,000
• Sales collected in the quarter sales are made	75%
• Sales collected in the quarter after sales are made	25%
• Desired ending finished goods inventory is	30%	of the budgeted unit sales of the next quarter
• Finished goods inventory, beginning	12,000	units
• Raw materials required to produce one unit	5	pounds
• Desired ending inventory of raw materials is	10%	of the next quarter's production needs
• Raw materials inventory, beginning	23,000	pounds
• Raw material costs	$0.80	per pound
• Raw materials purchases are paid	60%	in the quarter the purchases are made
and	40%	in the quarter following purchase
• Accounts payable for raw materials, beginning balance	$81,500

Questions:

1. What are the total expected cash collections for the year under this revised budget?

2. What is the total required production for the year under this revised budget?

3. What is the total cost of raw materials to be purchased for the year under this revised budget?

4. What are the total expected cash disbursements for raw materials for the year under this revised budget?

Answer 1

1.

	Quarter	Total year
	1	2	3	4
Accounts receivable, beginning balance	$65,000				$65,000
Quarter 1 sales	262,500 (50,000*$7*75%)	87,500 (50,000*$7*25%)			350,000
Quarter 2 sales		367,500 (70,000*$7*75%)	122,500 (70,000*$7*25%)		490,000
Quarter 3 sales			630,000 (120,000*$7*75%)	210,000 (120,000*$7*25%)	840,000
Quarter 4 sales				315,000 (60,000*$7*75%)	315,000
Total	$327,500	$455,000	$752,500	$525,000	$2,060,000

2.

	Quarter	Total year
	1	2	3	4
Budgeted sales	50,000	70,000	120,000	60,000	300,000
Add: Desired ending inventory	21,000 (70,000*30%)	36,000 (120,000*30%)	18,000 (60,000*30%)	24,000 (80,000*30%)	24,000
Total needs	71,000	106,000	138,000	84,000	324,000
Less: Beginning inventory	(12,000)	(21,000)	(36,000)	(18,000)	(12,000)
Budgeted production	59,000	85,000	102,000	66,000	312,000

3.

	Quarter	Total year
	1	2	3	4
Budgeted production	59,000	85,000	102,000	66,000	312,000
Required raw material per unit (In pounds)	5	5	5	5	5
Total raw material requirements	295,000	425,000	510,000	330,000	1,560,000
Add: Desired ending inventory	42,500 (425,000*10%)	51,000 (510,000*10%)	33,000 (330,000*10%)	42,250 (84,500*5*10%)	42,250
Total available	337,500	476,000	543,000	372,250	1,602,250
Less: Beginning inventory	(23,000)	(42,500)	(51,000)	(33,000)	(23,000)
Budgeted raw material purchases	314,500	433,500	492,000	339,250	1,579,250
Cost of raw material per pound	$0.80	$0.80	$0.80	$0.80	$0.80
Budgeted cost of raw material purchases	$251,600	$346,800	$393,600	$271,400	$1,263,400

Budgeted production for Year 3, Quarter 1 :

Budgeted sales	80,000
Add: Desired ending inventory (95,000*30%)	28,500
Total needs	108,500
Less: Beginning inventory	(24,000)
Budgeted production	84,500

4.

	Quarter	Total year
	1	2	3	4
Accounts payable, beginning balance	$81,500				$81,500
Quarter 1 purchases	150,960 (251,600*60%)	100,640 (251,600*40%)			251,600
Quarter 2 purchases		208,080 (346,800*60%)	138,720 (346,800*40%)		346,800
Quarter 3 purchases			236,160 (393,600*60%)	157,440 (393,600*40%)	393,600
Quarter 4 purchases				162,840 (271,400*60%)	162,840
Total	$232,460	$308,720	$374,880	$320,280	$1,236,340