Question

Data                                         1                              2                              3                              4              1                              2

Budgeted unit sales                 50,000                     65,000                     110,000                   65,000     80,000                     95,000

Selling price per unit                                                              $7 per unit

                               

                                                                                Year 2                                                     Year 3

                                                1                              2                              3                              4              1                              2

Budget Unit Sales                   50,000                     65,000                     110,000                   65,000     80,000                     95,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 good inventory is 30% of the budgeted unit sales of the next quarter

Finished goods inventory, beginning $12,000

Raw materials required to produce one unit 5 pounds

Desired ending inventory of the raw material is 10% of the next quarter’s production needs

Raw material inventory, beginning 23,000 pounds

Raw material costs 0.80 per bound

Raw materials purchases are paid 60% in the quarter the purchase are made

Accounts payable for raw material, beginning balance$ 81,500

               

1. What are the total expected cash collections for the year under this revised budget? 2. What is the total required production for the year under this revised budget? 3. What is the total cost of raw materials to be purchased for the year under this revised budget?

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

5.

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 or No ?

* Please provide answers with excel formulas

Answer 1

EXPECTED CASH COLLECTIONS
			Q-1	Q-2	Q-3	Q-4	YEAR
Accounts receivable Beginning			65,000				65000
Quartere-1 sales collected			262,500	87500			350000
Quarter-2 Sales collected				341250	113750		455000
Quarter-3 Sales collected					577,500	192,500	770000
Quarter-4 Sales collected						341,250	341250
Total Cash Collections			327,500	428,750	691,250	533,750	1981250
PRODUCTION BUDGET
			Q-1	Q-2	Q-3	Q-4	YEAR	Q-1	Q-2
Budgeted Sales Units			50,000	65,000	110,000	65,000	290,000	80,000	95,000
Add: Desired Ending Finished inventory			19,500	33,000	19,500	24,000	24,000	28,500
Total Needs			69,500	98,000	129,500	89,000	314,000	108,500
Less: Beginning Finished Inventory			12,000	19,500	33,000	19,500	12,000	24,000
Required Production in units			57,500	78,500	96,500	69,500	302,000	84,500
RAW MATERIAL PURCHASE BUDGET
			Q-1	Q-2	Q-3	Q-4	Year	Q-1
Budgeted Production units			57,500	78,500	96,500	69,500	302,000	84,500
Raw material units per unit			5	5	5	5	5	5
Total Production needs			287,500	392,500	482,500	347,500	1,510,000	422,500
Add: Desired Ending Inventory			39,250	48,250	34,750	42,250	42,250
Total needs			326,750	440,750	517,250	389,750	1,552,250
Less: Beginning Inventory			23,000	39,250	48,250	34,750	23,000
Purchase Units			303,750	401,500	469,000	355,000	1,529,250
Cost price per unit			0.80	0.80	0.80	0.80	0.80
Budgeted Purchase in $			243,000	321,200	375,200	284,000	1,223,400
EXPECTED CASH PAYMENTS
			Q-1	Q-2	Q-3	Q-4	Year
Beginning Accounts payable			81,500				81500
Q-1 Purchases paid			145800	97200			243000
Q-2 Purchases paid				192720	128480		321200
Q-3 Purchases paid					225120	150,080	375200
Q-4 Purchases paid						170,400	170400
Total Cash disbursement			227,300	289,920	353,600	320,480	1,191,300
Req5
Yes
Manager is right in seeing the potential problem. Quarter-3 production plan shows 96500 units however, the constraint is of 80000 units