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:

  

Data	Year 2 Quarter				Year 3 Quarter
	1	2	3	4	1	2
Budgeted unit sales	45,000	65,000	105,000	70,000	85,000	90,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 65,000 105,000 70,000 85,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 80,000 units in any one quarter. Is this a potential problem?
	Yes No

Answer 1

SALES BUDGET
			Q1	Q2	Q3	Q4	TOTAL	Q1	Q2
Budgeted Sales units			45,000	65,000	105,000	70,000	285,000	85,000	90,000
Selling price per unit			8	8	8	8	8	8	8
Total Sales			360,000	520,000	840,000	560,000	2,280,000	680,000	720,000
EXPECTED CASH COLLECTIONS
			Q1	Q2	Q3	Q4	Total
Sales collected in the current year			270,000	390,000	630,000	420,000	1710000
Sales collected in following month			65,000	90000	130000	210,000	495000
Total Cash Collections			335,000	480,000	760,000	630,000	2205000
PRODUCTION BUDGET
			Q1	Q2	Q3	Q4	TOTAL	Q1	Q2
Budgeted Sales Units			45,000	65,000	105,000	70,000	285,000	85,000	90,000
Add: Desired Ending Finished inventory			19,500	31,500	21,000	25,500	25,500	27,000
Total Needs			64,500	96,500	126,000	95,500	310,500	112,000
Less: Beginning Finished Inventory			12,000	19,500	31,500	21,000	12,000	25,500
Required Production in units			52,500	77,000	94,500	74,500	298,500	86,500
RAW MATERIAL PURCHASE BUDGET
			Q1	Q2Q	Q3	Q4	TOTAL	Q1	Q2
Budgeted production units			52,500	77,000	94,500	74,500	298,500	86,500
Raw material required per unit			5	5	5	5	5	5
Total production needs			262,500	385,000	472,500	372,500	1,492,500	432,500
Add: Desired Ending Inventory			38,500	47,250	37,250	43,250	43,250
Total needs			301,000	432,250	509,750	415,750	1,535,750
Less: Beginning Inventory			23,000	38,500	47,250	37,250	23,000
Purchase Units			278,000	393,750	462,500	378,500	1,512,750
Cost price per unit			0.80	0.80	0.80	0.80	0.80
Budgeted Purchase in $			222,400	315,000	370,000	302,800	1,210,200
EXPECTED CASH PAYMENTS
			Q1	Q2	Q3	Q4	Total
Disbursement in current month			133,440	189000	222000	181,680	726120
Disbursement in following month			81500	88960	126000	148,000	444460
Total Cash disbursement			214,940	277,960	348,000	329,680	1170580
Req 5: Yes, As the in Q-3 Budgetd production is 94500 units and having a capacity of 80000 units only.