just fill out the excel spreadsheet by using the scenario.

Chapter 6 Case Problem 4: ROSEY'S ROSES Rosey's Roses is contemplating several alternative means of financing their quisition of $100.000 in new equipment in year 1. One option is to borrow $80,000 from a local bank. The bank has asked them to produce a 3-year cash budget broken down by year (Year 1, 2, and 3). Sales of $250,000 were eamed in the prior year and are expected to increase each year thereafter by 15 percent Prior year purchases were $195,500. Future purchases are based on an expected cost of sales of 75 percent and a required ending inventory of 20 percent of next year's sales. Prior year expenses included advertising expense of $15,000 depreciation expense of $1,000, wages expense of $20,000, supplies expense of Cash Baletine $1.000, and utiliti interest expense ane to increase 5 percen is expected to rem the year of sale a 90 percent, with the the $80.000 loan equipment will be chases are expected to sales each year are on the loan occur at of year I were $20,20 Using the ch6-07 file to the chapter) based on feature to group (10) operating activiti utilities expense of $3.30. All expenses e depreciation and crise are paid in the year in which they are incurred and are expected encent each year. Interest expense is paid in the year incurred and remain constant at $4,000 each year for years 1-3. Collections in sale are expected to be 85 percent, with the remaining 15 percent the next year. Payments in the year of purchase are expected to be flected in the new remaining 10 percent with the remaining 10 percent paid in the next year. Proceeds from loan are expected at the beginning of year 1, and $100.000 of will be purchased during year 1. In subsequent years, equipment pur- xpected to be $2,000 each year. Proceeds from projected equipment year are expected to amount to $500. Annual payments of $10,360 occur at the end of each year. Cash and Inventory at the beginning were $20,200 and $58.000 respectively. the ch6 07 file to start your work, create a cash budget (as you did in ter) based on the assumptions just provided. Use Excel's grouping to group operating cash receipts, operating cash payment, cash from rating activities, cash from (to) investing activities, and cash from (to) cing activities. Define names as appropriate. ve your file as ch6-07_student_name (replacing student_name with your name). Print the newly completed worksheet in Value view, with your name and date printed in the lower left footer and the file name in the lower right footer. Print the resulting worksheet in Value view, with your name and date printed in the lower left footer and the file name in the lower right footer. Print cash budget and assumptions. b. Collapse rows to level 2, and then print the worksheet in Value view. with your name and date printed in the lower left footer and the file name in the lower right footer. Print the cash budget, no assumptions. c. Expand all rows and then use what-if analysis to calculate end-of-year cash if the sales growth each year were 0 percent. Print the resulting worksheet in Value view, with your name and date printed in the lower left footer and the file name in the lower right footer. Print cash budget and assumptions. Undo the what-if analysis performed in part c. Use goal seek to deter- mine what annual sales growth would be needed to produce an ending cash balance of $50,000 in year 3. Print the resulting worksheet in Value view, with your name and date printed in the lower left footer and the file name in the lower right footer. Print cash budget and assumptions. AutoSave Of H C B ch6-07 (1) - Exc File Home Insert Draw Page Layout Formulas Data Review View Help Sear - A == Calibri . 11 BIU - I - General A A . E 2 Wrap Text Merge & Center - $ -% 68 Clipboard 6 Font Alignment Number B40 C D E F 1 Rosey's Roses 2 Cash Budget Prior Year 1 2 3 250.000 4 Operating activities 5 Operating cash receipts 6 Product sales revenue 7 Collections in the year of sale 8 Collections in the year following sale 9 Operating cash receipts 10 Operating cash payments 11 Purchases 12 Cost of expected sales 13 Required ending inventory 14 Beginning inventory 15 Purchases 16 Payments in the year of purchase 17 Payments in the year following purchase 18 Cash payments for purchases 19 Expenses 20 Advertising expense 21 interest expense 22 Wages expense 23 Supplies expense 24 Utilities expense 25 Expenses Cash Budget Sheet2 195,500E 200,000 15,000 20,000 1,000 3.300 39,300 Sheet3 Type here to search O IMO | AutoSave coh) H 2 O + ch6-07 (1) - Excel 1 File Home Insert Draw Page Layout Formulas Data Review View Help Search Calibri = == > 11 . A A .A. Wrap Text Merge & Center - BIU General $ % & Clipboard Font Alignment Number B40 20.000 1.000 3,300 39 300 239.300 14.300 21 Interest expense 22 Wages expense 23 Supplies expense 24 Utilities expense 25 Expenses 26 Operating cash payments 27 Cash from (to) operating activities 28 Investing activities 29 Equipment purchases 30 Equipment sales 31 Cash from (to) investing activities 32 Financing activities 33 loan proceeds 34 Loan payments 35 Cash from (lo) financing activities 36 Change in cash 37 Beginning cash 38 Ending cash 4.300) 24 500 3 40 Assumptions 41 Prior year sales 42 Sales growth each year 43 Expense growth each year 46 Collections in the year of sale 45 Collections in the year following sale Cash Budget Sheet2 Sheet3 Type here to search AutoSave om File Home Insert Calibri BIU. 8 : Draw Page Layout 11 - A A = . . A. Formulas ch6-07 (1) - SE Help Sea Data Review View = 2. 4. 23 Wrap Text General Merge & Center - $ .% Clipboard Font Alignment Number A40 f e Assumptions 40 Assumptions 41 Prior year sales 42 Sales growth each year 43 Expense growth each year 44 Collections in the year of sale 45 Collections in the year following sale 46 Prior year purchases 47 Payments in the year of purchase 48 Payments in the year following purchase 49 Cost of expected sales 50 Required ending inventory 51 Beginning inventory 52 Interest expense 53 Loan proceeds 54 Annual loan payment 55 Annual equipment purchases 56 Annual equipment sales 57 Year 1 equipment purchase 58 Beginning cash for year 1 Cash Budget Sheet2 Sheets Type here to search