Rosey's Roses is contemplating several alternative means of financing their acquisition 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 $150,000 were earned in the prior year and are expected to increase each year thereafter by 15 percent. Purchases are based on an expected cost of sales of 60 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 $1,000, and utilities expense of $3,300. All expenses except depreciation and interest expense are paid in the year in which they are incurred and are expected to increase 10 percent each year. Interest expense is paid in the year incurred and is expected to remain constant at $4,000 each year for years 1?3. Collections in the year of sale are expected to be 85 percent, with the remaining 15 percent collected in the next year. Payments in the year of purchase are expected to be 90 percent, with the remaining 10 percent paid in the next year. Proceeds from the $80,000 loan are expected at the beginning of year 1, and $100,000 of equipment will be purchased during year 1. In subsequent years, equipment purchases are expected to be $2,000 each year. Proceeds from projected equipment sales each year are expected to amount to $500. Annual payments of $10,360 on the loan occur at the end of each year.

Using the ch6?07 file to start your work, create a cash budget (as you did in the chapter) based on the assumptions just provided. Use Excel's grouping feature to group operating cash receipts, operating cash payment, cash from (to) operating activities, cash from (to) investing activities, and cash from (to) financing activities. Define names as appropriate.

Save your file as ch6-07_student_name (replacing student_name with your name).

• a.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 only columns A through E of the cash budget, no 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 only columns A through E of the cash budget, no assumptions.
• c.Collapse rows to level 2, 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 only columns A through E of the cash budget, no assumptions.
• d.Undo the what-if analysis performed in part d. Collapse rows to level 2, and then use goal seek to determine 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 only columns A through E of the cash budget, no assumptions.

Rosey's Roses Cash Budget Operating activities Operating cash receipts Product sales revenue Collections in the year of sale Collections in the year following sale Operating cash receipts Operating cash payments Purchases Cost of expected sales Required ending inventory Beginning inventory Purchases Payments in the year of purchase Payments in the year following purchase Cash payments for purchases Expenses Advertising expense Interest expense Wages expense Supplies expense Utilities expense Expenses Operating cash payments Cash from (to) operating activities Investing activities Equipment purchases Equipment sales Cash from (to) investing activities Financing activities Loan proceeds Loan payments Cash from (to) financing activities Change in cash Beginning cash Ending cash Assumptions Sales growth each year Expense growth each year Collections in the year of sale Collections in the year following sale Payments in the year of purchase Payments in the year following purchase Cost of expected sales Required ending inventory Beginning inventory Interest expense Loan proceeds Annual loan payment Annual equipment purchases Annual equipment sales Year 1 equipment purchase Prior Year 150,000 127,500 16,000 143,500 90,000 20,700 (18,000) 92,700 83,430 6,000 89,430 15,000 20,000 1,000 3,300 39,300 128,730 14,770 14,770 5,430 20,200 1 2 3