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	70,000	110,000	75,000	90,000	95,000
Selling price per unit	$7	per unit

	A	B	C	D	E	F	F
1	Chapter 7: Applying Excel
2
3	Data	Year 2 Quarter				Year 3 Quarter
4		1	2	3	4	1	2
5	Budgeted unit sales	45,000	70,000	110,000	75,000	90,000	95,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

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

      

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

      

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

      

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

      

e.	After seeing this revised budget, the production manager cautioned that due to the current production constraint, a complex milling machine, the plant can produce no more than 80,000 units in any one quarter. Is this a potential problem?
	Yes No

Answer 1

a) Calculation of Expected Cash Collections for year 2 (Amounts in $)

Particulars	Qtr 1	Qtr 2	Qtr 3	Qtr 4	Year 2
Accounts Receivable, beg bal	65,000	0	0	0	65,000
Quarter 1 (45,000 units*$7 (75%:25%)	236,250	78,750	0	0	315,000
Quarter 2 (70,000 units*$7)	0	367,500	122,500	0	490,000
Quarter 3 (110,000 units*$7)	0	0	577,500	192,500	770,000
Quarter 4 (75,000 units*$7)	0	0	0	393,750	393,750
Total	301,250	446,250	700,000	586,250	2,033,750

Therefore total cash collections for the year is $2,033,750.

b) Calculation of Required Production for the Year (in units)

Particulars	Qtr 1	Qtr 2	Qtr 3	Qtr 4	Year 2
Budgeted units sales	45,000	70,000	110,000	75,000	300,000
Add: Desired Ending Inventory (30% of next Qtr)	21,000	33,000	22,500	27,000	27,000
Less: Beginning Inventory	(12,000)	(21,000)	(33,000)	(22,500)	(12,000)
Units required to be produced	54,000	82,000	99,500	79,500	315,000

Therefore total required production for the year is 315,000 units.

c) Desired ending raw materials for year 2 = Year 3 Qtr 1 production needs*10%

Year 3 Qtr 1 finished goods required = 90,000+(95,000*30%)-27,000 = 91,500 units

Desired ending raw materials for year 2 = 91,500 units*5 pounds*10% = 45,750 pounds

Total material production needs for the year = 315,000 units*5 pounds = 1,575,000 pounds

Raw material required to purchased = Production needs+Desired ending materials-Beg. materials

= 1,575,000 pounds+45,750 pounds-23,000 pounds = 1,597,750 pounds

Cost of raw material purchased for the year = 1,597,750 pounds*$0.80 per pound

= $1,278,200

d) The Accounts payable in the beginning will be paid in this year, purchases made in quarter 1, quarter 2 and quarter 3 will be wholly paid in this year (i.e. year 2) but 40% of quarter 4 purchase will be outstanding at year end.

Raw material to be purchased in Qtr 4 = Production needs+Desired ending materials-Beg. materials

= (units produced*5 pounds)+Year Ending material-(10% of production needs of current Qtr)

= (79,500 units*5 pounds)+45,750 pounds-(10%*79,500 units*5 pounds)

= 397,500 pounds+45,750 pounds-39,750 pounds = 403,500 pounds

Cost of raw materials purchased in Qtr 4 = 403,500 pounds*$0.80 = $322,800

Total expected cash disbursements for the year

= Beg. Accounts Payable+Purchases-Ending Accounts Payable

= $81,500+$1,278,200-($322,800*40%) = $1,230,580

Therefore total cash disbursements for the year is $1,230,580.