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 45,000 65,000 105,000 60,000 90,000 90,000 Selling price per unit $7 per unit

	A	B	C	D	E	F	F
1	Chapter 9: Applying Excel
2
3	Data	Year 2 Quarter				Year 3 Quarter
4		1	2	3	4	1	2
5	Budgeted unit sales	45,000	65,000	105,000	60,000	90,000	90,000
6
7	• Selling price per unit	$7	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

Answer 1

Prepartion of Sales Budget
		Year-2 , Quarter				Year-3 Quarter
		1	2	3	4	1	2
	Sales Unit	45,000	65,000	105,000	60,000	90,000	90,000
	Selling Price/Unit	$7	$7	$7	$7	$7	$7
	Budgeted Sales Value	$315,000	$455,000	$735,000	$420,000	$630,000	$630,000
Schedule of Expected Cash Collection
		Year-2 , Quarter				Year
		1	2	3	4	Total
	Account Receivable, Beginning Balance	$65,000				$65,000
	First Quarter Sales	$236,250	$78,750			$315,000
	Second Quarter Sales		$341,250	$113,750		$455,000
	Third Quarter Sales			$551,250	$183,750	$735,000
	Fourth Quarter Sales				$315,000	$315,000
	Total Cash Collection	$301,250	$420,000	$665,000	$498,750	$1,885,000
Schedule of Production Budget
		Year-2 , Quarter				Year-3 Quarter
		1	2	3	4	1	2
Add:	Sales Unit	315,000	455,000	735,000	420,000	630,000	630,000
Less	Ending Inventory (30% of Next Quarter Sales)	136,500	220,500	126,000	189,000	189,000	-
	Total Needs	451,500	675,500	861,000	609,000	819,000	630,000
	Less: Beginning Inventory	12,000	136,500	220,500	126,000	189,000	189,000
	Production Unit	439,500	539,000	640,500	483,000	630,000	441,000
Schedule of Raw Material Purchase Budget
		Year-2 , Quarter				Year-3 Quarter
		1	2	3	4	1	2
	Production Unit	439,500	539,000	640,500	483,000	630,000	441,000
Add:	Raw material Rquired for Prodcution Unit @ 5 Pound/Unit	2,197,500	2,695,000	3,202,500	2,415,000	3,150,000	2,205,000
Less:	Ending Inventory ( in Pound) 10% of Next Quarter	269,500	320,250	241,500	315,000	220,500	-
	Total Needs in Pound	2,467,000	3,015,250	3,444,000	2,730,000	3,370,500	2,205,000
	Less: Beginning Inventory (in Pound)	23,000	269,500	320,250	241,500	315,000	220,500
	Total Raw Material Purchase ( Pound)	2,444,000	2,745,750	3,123,750	2,488,500	3,055,500	1,984,500
	Price/Pound	$0.80	$0.80	$0.80	$0.80	$0.80	$0.80
	Total Raw Material Purchase Cost	$1,955,200	$2,196,600	$2,499,000	$1,990,800	$2,444,400	$1,587,600
Schedule of Expected Cash Payment
		Year-2 , Quarter				Year
		1	2	3	4	Total
	Account payable, Beginning Balance	$81,500				$81,500
	First Quarter Purchase	$1,173,120	$782,080			$1,955,200
	Second Quarter Purchase		$1,317,960	$878,640		$2,196,600
	Third Quarter Purchase			$1,499,400	$999,600	$2,499,000
	Fourth Quarter Purchase				$1,194,480	$1,194,480
	Total Cash Collection	$1,254,620	$2,100,040	$2,378,040	$2,194,080	$7,926,780