Chapter 7 Applying Excel eer2 are Data 5 Budgeted unit saies Seling price per unit 00006 100,000 50.000 80.000 58 per unit Accounts rocesable baginning balance 9Sales colected in the quarter sales are made Sales collected in the quarter after sales are made $65,000 75% 25% 30% of the tudgeted unt sales of the next quarter Desired ending fnighed goods inentory is 12 . Finished goods inventory begm 12000 units Ran matienials required to produce one uni 14 . Desired ending inventory of raw materd3 5 peunds 10% of the rent quarters production needs Ram matenials inventery,beginning 23 000 peunds Raw matarial eosts 00 per pound 60% n the cater the puchases are made 40% is the quarter following purchase Raw maconale purchases are pad and i, . Accounts payable for ra matesals, begning balance S81500 21 Enter a fomula into each of he cel marked wn a 7 Delour 4 Cosmct the sales budge 22 Review Problem: Budget Schedules 23 Year 2 Quarte Budgeted unit sales Selling pice per uni Total sales o Construct the schedale of expected cash cellections Year 2 Quate Year 2 Accounts roceable. beginneg balance 33 First-quaner sale 4 Second-quarter sales s Thid-quanter saes 6 Fourch-quarter sales 7 Total cash collections Consuct the production budgor Year 2 Quarter Yoar3 Quater Year Budgeted unit sales 42 Aad degired Anished gods inenory Total needs 44 Less beginning inventary 45 Required production 7 Construct the raw matorials purchases budget Year 2 Quarter Year 3 Quater Year 9 Required production (units 0 Raau macenals tequired to produce one unt Production needs (pounds) 2 Add desired ending imventory of raw materials (pounes) 33 Total needs (pounds) S4 Less beginning inentary ofaw matarials (pounde 5 Raw matorals to bo purchased Cost of an materials per pound 7 Cost of aw materials to be purchased Construet the schederle af expected cash payments Year 2 Quarter 61 Accounts payablo, beginning balanco 2 First-quaner purchase 3 Second-guater purchases 64 Thid quarter purchases 5 Fouch-quater purcheses 66 Tetai cash disbursements 57 1. Check your worksheet by changing the budgeted unit sales in Quarter 2 of Year 2 in cell C5 to 75,000 units. The total expected cash collections for the year should now be $2,085,000. If you do not get this answer, find the errors in your worksheet and correct them. Have the total cash disbursements for the year changed? Why or why not? 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 2 4 2 Budgeted unit sales 50,000 70,000 120,000 80,000 90,000 100,000 Selling price per unit $7 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 limited availability of a complex milling machine, the plant can produce no more than 90,000 units in any one quarter. Is this a potential problem? If so, what can be done about it