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 1 2 Budgeted unit sales 45,000 70,000 115,000 65,000 90,000 90,000 Selling price per unit ST A B C D E F G 1 Chapter 8: Applying Excel 2 3 Data 4 1 2 3 Year 3 Quarter 1 90,000 2 45,000 70,000 115,000 85,000 90,000 S S 7 per unit 65,000 5 Budgeted unit sales 6 6 7 - Selling price per unit 8 8 - Accounts receivable, beginning balance 9 - Sales collected in the quarter sales are made 10 Sales collected in the quarter after sales are made 11 Desired ending finished goods inventory is 1 Finished goods inventory beginning • Raw materials required to produce one unit 13 Desired ending inventory of raw materials is 14 Raw materials inventory, beginning 15 - Raw material costs 16 - Raw materials purchases are paid 17 and 18 - Accounts payable for raw materials, beginning balance 19 75% 25% 30% of the budgeted unit sales of the next quarter 12,000 units 5 pounds 10% lof the next quarter's production needs 23.000 pounds 0.80 per pound 60% in the quarter the purchases are made 40% in the quarter following purchase 81,500 S S a. What are the total expected cash collections for the year under this revised budget? Expected cash collections for the year b. What is the total required production for the year under this revised budget? Total required production for the year c. What is the total cost of raw materials to be purchased for the year under this revised budget? Total cost of raw materials to be purchased for the year d. What are the total expected cash disbursements for raw materials for the year under this revised budget? Tolal expected cash disbursements for raw materials for the year 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? O No Yes

Answer 1

Answer (A) $ 2,016,250

Answer (B) 310,000 Units

Answer (C) $ 1,257,600

Answer (D) $ 1,220,300

Answer (E) Yes it is an potential problem

Budget	Quarter 1	Quarter 2	Quarter 3	Quarter 4	Quarter 1	Quarter 2
Budgeted Unit Sales	45000	70000	115000	65000	90000	90000
Selling Price	7	7	7	7	7	7
Account Receivable Beginning	65000	78750	122500	201250	113750	157500
Addition	315000	490000	805000	455000	630000	630000
Cash Received	301250	446250	726250	542500	586250	630000
Account Receivable Ending	78750	122500	201250	113750	157500	157500
Finish Goods Beginning Bal	12000	21000	34500	19500	27000	27000
Manufactured UNITS	54000	83500	100000	72500	90000	90000
Sold	45000	70000	115000	65000	90000	90000
Closing Bal	21000	34500	19500	27000	27000	27000
Raw Material Beginning Bal	23000	41750	50000	36250	45000	45000
Purchsed UNITS	288750	425750	486250	371250	450000	450000
Utilised for Manufacturing	270000	417500	500000	362500	450000	450000
Closing Bal	41750	50000	36250	45000	45000	45000
Account Payable Begin	81500	92400	136240	155600	118800	144000
Purcheses	231000	340600	389000	297000	360000	360000
Paid	220100	296760	369640	333800	334800	360000
Closing Balance	92400	136240	155600	118800	144000	144000