Question

Requirement 2:

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	65,000	115,000	70,000		85,000	100,000
Selling price per unit	$7
A B C D E F G 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 Chapter 8: Applying Excel Data Year 3 Quarter 1 2 3 4 1 2 Budgeted unit sales 50,000 65,000 115,000 70,000 85,000 100,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

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?

No

Yes

Answer 1

SALES BUDGET
			Q1	Q2	Q3	Q4	Year	Q1	Q2
Budgeted Sales units			50,000	65,000	115,000	70,000	300,000	85,000	100,000
Selling price per unit			7	7	7	7	7	7	7
Total Sales			350,000	455,000	805,000	490,000	2,100,000	595,000	700,000
EXPECTED CASH COLLECTIONS
			Q1	Q2	Q3	Q4	Year
Accounts receivable			65,000				65000
Q1 Sales			262,500	87500			350000
Q2 Sales				341250	113750		455000
Q3 Sales					603,750	201,250	805000
Q4 Sales						367,500	367500
Total Cash Collections			327,500	428,750	717,500	568,750	2042500
PRODUCTION BUDGET
			Q1	Q2	Q3	Q4	Year	Q1	Q2
Budgeted Sales Units			50,000	65,000	115,000	70,000	300,000	85,000	100,000
Add: Desired Ending Finished inventory			19,500	34,500	21,000	25,500	25,500	30,000
Total Needs			69,500	99,500	136,000	95,500	325,500	115,000
Less: Beginning Finished Inventory			12,000	19,500	34,500	21,000	12,000	25,500
Required Production in units			57,500	80,000	101,500	74,500	313,500	89,500
RAW MATERIAL PURCHASE BUDGET
			Q1	Q2	Q3	Q4	Year	Q1
Budgeted Production units			57,500	80,000	101,500	74,500	313,500	89,500
Raw material required per unit			5	5	5	5	5	5
Total Raw material needs			287,500	400,000	507,500	372,500	1,567,500	447,500
Add: Desired Ending Inventory			40,000	50,750	37,250	44,750	44,750
Total needs			327,500	450,750	544,750	417,250	1,612,250
Less: Beginning Inventory			23,000	40,000	50,750	37,250	23,000
Purchase Units			304,500	410,750	494,000	380,000	1,589,250
Cost price per unit			0.80	0.80	0.80	0.80	0.80
Budgeted Purchase in $			243,600	328,600	395,200	304,000	1,271,400
EXPECTED CASH PAYMENTS
			Q1	Q2	Q3	Q4	Year
Beginning Accounts payable			81,500				81500
Q1 Purchases			146,160	97440			243600
Q2 Purchases				197160	131440		328600
Q3 Purchases					237120	158,080	395200
Q4 Purchases						182,400	182400
Total Cash disbursement			227,660	294,600	368,560	340,480	1,231,300
Req e: Yes, its is a bottleneck