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 Budgeted unit sales 45,000 70,000 105,000 70.000 90,000 90, eee selling price per unit B D E F G 1 A Chapter 8: Applying Excel 2 3 Data 1 4 Year 3 Quarter 1 90,000 2 70.000 2 45,000 105,000 70.000 90.000 $ $ S Budgeted units 6 7 Selling price per unit Accounts receivable, beginning balance 9 Sales collected in the quarter sales are made 10 Sales collected in the quartet after sales are made 11 - Desired ending finished goods inventory is 12 Finished goodo inventory beginning 13 Raw materials required to produce one unit 14 Desired ending inventory of raw materiais 15 - Raw materials inventory beginning 16 Raw material costs 12 Raw material purchases are paid 18 and 19 Accounts payable for raw materials, beginning balance 7 per unit 65,000 75 20 30% of the budgeted unit sales of the next Quarter 12.000 units 5 pounds 10% of the next quarter's production needs 23,000 pounds 0.80 por pound 60% in the quarter the purchases are made 40% in the quarter following purchase 81.500 $ a. What are the total expected cash collections for the year under this revised budget? Part 2 of 2 Expected cash collections for the year mo Doints b. What is the total required production for the year under this revised budget? Book Total required production for the year Print References c. What is the total cost of raw materials to be purchased for the year under this revised budget? Total cost of raw material to be purchased for the year d. What are the total expected cash disbursements for raw materials for the year under this revised budget? Total expected cash disbursements for raw materials for the year e Aher seeing this revived budget, the production manager cautioned that due to the current production constraint, a complex miling machine, the plant can produce no more than 80,000 units in any one quarter Is this a potential problem? ONO Yes Part 1 of 2 10 point supped Required: To improve your understanding of all schedules included in the master budget and their interrelationships answer the following questions 1. Go to the Sales Budgettab a. How are the sales for July (cell HD) being calculated? b. How are the cash collections for August (cell 115) being calculated? 2. Go to the "Merchandise Purchases Budget" tab; a. How are the required merchandise purchases for April (ell E10) being calculated? b. How are the cash disbursements for merchandise purchases for May (cell F17) being calculated? 3. Go to the Selling & Admin Budget" tab. a. How are the total selling and administrative expenses for September (cell 16) being calculated? b. How are the cash disbursements for selling and administrative expenses for November (cell Ets) being calculated? 4. Go to the Cash Budgettab a. How is the excess (deficiency) of cash available over disbursements for March (cer D14) being calculated? b. How is the ending cash balance for June (cell G20) being calculated? 5. Go to the Budgeted Income Statements tab 0. How is the cost of goods sold for February (cell ca) being calculated? b. How is the net income for April (cell E13) being calculated? Bow PI References 6. Go to the Budgeted Balance Sheets' tab: a. How are the accounts receivable for December (cel M9) being calculated? b. How is the ending merchandise Inventory for October (cell K10) being calculated c. How is the accounts payable for January (cell 820) being calculated? d. How is the retained earnings for April (cell E26) being calculated? was como 100 MES Typh o WC The Year A 1 10 44501 300 O WE . 3.500 2011 RE HE PM CORO tie OTOM 136 HE 20 30,000 th 12 2013 SOM WS COM WY 2000 30 100 110 1744 14 RE 1 402 11 ROUTE BE 0 to search William Company Setting and Admini perlu For The Year MY 1000 2.500 1300 > 900 100 by M 100 DDO 1 0001 1. 2000 NO 000 100 1001 2001 ng 131 DON 12.000 2000 20 000 6.000 0001 000 1 000 15.000 200 9000 so 000 3.000 . 3.00 000 1000 8000 30 1000 1000 000 1900 000 www 1 DO UR 14 1200 - MO 0001 200 TO . RON HO TO 00015 10 O B Type here to search E 9 Wom Cash For The wy 30000 30.000 30.000 90000 LEO 9900 ODOB OFFEL OS 2007 22 40210 4400 1999 . 1000 000 5400 OL ST00 TI 19 13 343 2010 CO 10.10 4 TO 1651 10000 0 1 2003 112 300 30.000 0.0001 3000 300001 Type toch 3 O i PROTECTE Secawl files from the inteme can continues Unless you need to edit afer to stay in Protected View Enable doing G12 > ='Cash Budget'585164 Cash Budget'Sc516 Cash det0516: Cash Budget S$16+ Cash Budget SF516 Gush Buder'156516*0,01 Was Company Budgeted Income Sites For This Year M Ay 270 000 140 350 14250 Jury Pary 5 1 000 $ 6000 46530 34650 70450 200 10.000 OL 0.00 54450 043 Cestos Oy 00005 BOS 005 000 20.000.000 1 144.000 15400 1000 70-200 129000 14800 Art Mey 9.000 20.000 $225.000 00 300 1237 TO 200 1400 2010 30 14 C LO OD ow 004 OS OYO sao 445 40 2000 DO SP 100 20 41 11.30 OSES TO 12.2001 35250 24 SON 40 w Company Bogdan For Each This February MY $ 50001 72.000 30 000 580 112001 30.000 79200 300D 20694 10.200 640 121 0.000 2 7. 216000 516335 5000 12 13 handen RE 18.800 16 300 ORD 111615 900.00 123 90000 00000 00000 00000 600.000 90000 540000 9000 00000 000000 SOHO 1 den Arc hart 2000 TO 600 . . 5 30 ON NOS 7051 13 120 000 TIL co 1 50 110 Account 65 1 11001 70471 WP 44 13 474254 Ce 2011 10 15592 100 To 253000 CSE OCESE 23.00 253000 25000 2011 300 2330 000 2000 me 103 101 TH 1053 63971 TIL 12 142231 It To CHO 03 313 31 BOT1293 ME O D Type here to search E