Question

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

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 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
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 90,000 units in any one quarter. Is this a potential problem?
	Yes/No

Answer 1

A

Sales Budget
	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	$7	$7	$7	$7	$7
Total sales	$350,000	$455,000	$805,000	$490,000	$560,000	$630,000

Schedule of expected cash collections
	Year 2 Quarter
	1	2	3	4	Year
Beginning balance accounts receivable	$     65,000				$     65,000
First-quarter sales	262,500	$     87,500			350,000
Second-quarter sales		341,250	$ 113,750		455,000
Third-quarter sales			603,750	$ 201,250	805,000
Fourth-quarter sales				367,500	367,500
Total cash collections	$   327,500	$   428,750	$ 717,500	$ 568,750	$ 2,042,500

The total expected cash collections for the year under this revised budget are $2,042,500

_______________________________________________________________

B

Construct the production budget	Year 2 Quarter
	1	2	3	4	Year
Budgeted unit sales	50,000	65,000	115,000	70,000	300,000
Add desired ending finished goods inventory	19,500	34,500	21,000	24,000	24,000
Total needs	69,500	99,500	136,000	94,000	324,000
Less beginning finished goods inventory	12,000	19,500	34,500	21,000	12,000
Required production in units	57,500	80,000	101,500	73,000	312,000

The total required production for the year under this revised budget is 312,000 units

_________________________________________________________

C

the raw materials purchases budget	Year 2 Quarter
	1	2	3	4	Year
Required production (units)	57,500	80,000	101,500	73,000	312,000
Raw materials required to produce one unit (pounds)	5	5	5	5	5
Production needs (pounds)	287,500	400,000	507,500	365,000	1,560,000
Add desired ending inventory of raw materials (pounds)	40,000	50,750	36,500	41,500	41,500
Total needs (pounds)	327,500	450,750	544,000	406,500	1,601,500
Less beginning inventory of raw materials (pounds)	23,000	40,000	50,750	36,500	23,000
Raw materials to be purchased (pounds)	304,500	410,750	493,250	370,000	1,578,500
Cost of raw materials per pound	$0.80	$0.80	$0.80	$0.80	$0.80
Cost of raw materials to be purchased	$243,600	$328,600	$394,600	$296,000	$1,262,800

The total cost of raw materials to be purchased for the year under this revised budget is $1,262,800

______________________________________________________

D)

the schedule of expected cash payments	Year 2 Quarter
	1	2	3	4	Year
Beginning balance accounts payable	$     81,500				$     81,500
First-quarter purchases	146,160	$     97,440			243,600
Second-quarter purchases		197,160	$ 131,440		328,600
Third-quarter purchases			236,760	$ 157,840	394,600
Fourth-quarter purchases				177,600	177,600
Total cash disbursements	$   227,660	$   294,600	$ 368,200	$ 335,440	$ 1,225,900

The total expected cash disbursements for raw materials for the year under this revised budget are $1,225,900

_________________________________________________

E)

The production constraint of 90,000 units per quarter is a problem in the third quarter of Year 2 and may be a problem later in Year 3. This problem can be approached in a variety of ways. First, the excess capacity in the first and second quarters could be used to build up finished goods inventories beyond the usual levels. Second, management could investigate acquiring another of the milling machines. Third, improvement efforts can be focused on the milling machine; if these efforts are successful, the capacity of the milling machine can be increased and consequently the capacity of the entire plant can be increased. Fourth, management could investigate hiring another company with such a milling machine to do some of the work.