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:      Data Year 2 Quarter Year 3 Quarter 1 2 3 4 1 2   Budgeted unit sales 50,000 65,000 115,000 70,000 80,000 90,000   Selling price per unit $7 per unit             Chapter 7: Applying Excel
2
3	Data	Year 2 Quarter				Year 3 Quarter
4		1	2	3	4	1	2
5	Budgeted unit sales	50,000	65,000	115,000	70,000	80,000	90,000
6
7	� Selling price per unit	$8	per unit
8	� Accounts receivable, beginning balance	$65,000
9	� Sales collected in the quarter sales are made	75%
10	� Sales collected in the quarter after sales are made	25%
11	� Desired ending finished goods inventory is	30%	of the budgeted unit sales of the next quarter
12	� Finished goods inventory, beginning	12,000	units
13	� Raw materials required to produce one unit	5	pounds
14	� Desired ending inventory of raw materials is	10%	of the next quarter's production needs
15	� Raw materials inventory, beginning	23,000	pounds
16	� Raw material costs	$0.80	per pound
17	� Raw materials purchases are paid	60%	in the quarter the purchases are made
18	and	40%	in the quarter following purchase
19	� Accounts payable for raw materials, beginning balance	$81,500
20

Answer 1

		Year 2 Quarter				Year 3 Quarter
		1	2	3	4	1	2
	Budgeted unit sales	50000	65000	115000	70000	80000	90000
	Selling price per unit	7	7	7	7	7	7
	Sales ( In USD)	350000	455000	805000	490000	560000	630000
		75%
1	Sales Collection ( 75%) Same Quarter	262500	341250	603750	367500	420000	472500
		25%
2	Sales Collection ( 25%) Next Quarter		87500	113750	201250	122500	140000
i)	Total Expected Cash Collection	262500	428750	717500	568750	542500	612500
iv)	Total Net Production Under Revised Budget
A	Opening Finished Goods	12,000	19,500	34500	21000	24000	27000
B	Budgeted Sales	50,000	65000	115000	70000	80000	90000
C	Ending Inventory 30% of Next Quarter	19,500	34500	21000	24000	27000
	Net Production ( Units) ( A-B+C)	57,500	80,000	1,01,500	73,000	83,000	63,000
	Total Cost of Raw Material ( Under Revised Budget)
	Ram Material Opening	23,000	40,000	50,750	36500	41500	31500
	Unit Produced	2,87,500	400000	507500	365000	415000	315000
	Ending inventory ( 10%)	40,000	50750	36500	41500	31500
	Raw Material Purchased ( QTY)	3,04,500	4,10,750	4,93,250	3,70,000	4,05,000	283500
	0.8
	Raw Material ( In USD)	2,43,600	3,28,600	3,94,600	2,96,000	3,24,000	2,26,800
	Cash Disbursement of Raw Material
A	Raw Material Payment ( 60 % Same Quarter)	1,46,160	1,97,160	2,36,760	1,77,600	1,94,400	1,36,080
	40%
B	Raw Material Payment ( 40 % Same Quarter)		97440	131440	157840	118400	129600
	Total ( A+B)	1,46,160	2,94,600	3,68,200	3,35,440	3,12,800	2,65,680