Hello,

I have started on my "Chapter 6 Case Problem 1: Kelly's Boutique". I'm getting a little confused as to where to place the numbers now, because in the homework it says expected #, but it actually should be placed in the assumption area within the template I am doing. Could you please help me figure out what to do? I did as much as I could do (that I thought was correct placement). I would appreciate your help!

Kelly's Boutique Cash Budget Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec Year Operating activities $ - Operating cash receipts $ - Product sales revenue $ - Collections in the month of sale Collections in the 1st month following a sale $ $ - Collections in the 2nd month following a sale Operating cash receipts Operating cash payments Purchases Cost of expected sales $ $ $ $ $ - Required ending inventory Beginning inventory $ $ $ 11,000 11,000 Purchases $ - Payments in the month of purchase Payments in the 1st month following a purchase Payments in the 2nd month following a purchase Cash payments for purchases $ $ $ $ - Expenses Advertising expense Interest expense Payroll expense Supplies expense Utilities expense Expenses Operating cash payments Cash from (to) operating activities Investing activities Equipment purchases Other Cash from (to) investing activities $ $ $ $ 900 1,000 8,000 500 $ $ $ $ 900 1,000 8,000 500 $ 600 $ 600 $ 900 $ 900 $ 900 $ 900 $ $ 1,000 $ 1,000 $ 1,000 $ 1,000 $ $ 8,000 $ 8,000 $ 8,000 $ 8,000 $ $ 500 $ 500 $ 500 $ 500 $ $ 600 $ 600 $ 600 $ 600 $ 900 1,000 8,000 500 $ 900 $ 900 $ 900 $ 900 $ 1,000 $ 1,000 $ 1,000 $ 1,000 $ 8,000 $ 8,000 $ 8,000 $ 8,000 $ 500 $ 500 $ 500 $ 500 600 $ 600 $ 600 $ 600 $ 600 $ - $ $ Beginning cash $ 15,000 Ending cash $ 15,000 - Assumptions January sales 55% 70% Beginning inventory Payments in the month of purchase Payments in the 1st month following a purchase Payments in the 2nd month following a purchase Previous December purchases Interest expense Payroll expense Supplies expense Utilities expense Loan proceeds Loan payment Equipment purchase 600 $ - $ - $ - $ - $ - $ (200,000) $ - $ - $ - $ - $ - $ $ $ $ $ 10,800 12,000 96,000 6,000 $ $ $ $ 7,200 - $ $ $ $ $ $ Loan payments Cash from (to) financing activities Change in cash Previous November purchases Beginning cash Next January forecasted sales Advertising expense Depreciation expense $ $ (200,000) Financing activities Loan proceeds Sales growth each month Collections in the month of sale Collections in the 1st month following a sale Collections in the 2nd month following a sale Previous December sales Previous November sales Cost of expected sales Required ending inventory $ 900 $ 1,000 $ 8,000 $ 500 $15,000.0 $ $ - $ $ - $ $ - $ $ - $ $ - $ 1,400 $ 1,400 $ 1,400 $ 1,400 $ 1,400 $ 1,400 $ 1,400 $ 1,400 $ 1,400 $ 1,400 $ (198,600) $ 1,400 $ 1,400 $ 1,400 $ 1,400 $ 1,400 $ $ 1,400 $ $ 1,400 $ $ - $ - $ - $ - $ - $ (198,600) $ 1,400 $ 1,400 $ 1,400 $ 1,400 $ 1,400 $ $ (200,000) (200,000) 8,400 8,400 (191,600) 15,000 (176,600) Chapter 6 Case Problem 1: KELLY'S BOUTIQUE Kelly's Boutique is contemplating several means of financing their acquisition of $200,000 in special equipment. One alternative is to borrow $200,000 from a local bank for 10 years at 12 percent per annum. The bank has asked them to produce a 1-year cash budget broken down by months (January through December). Sales of $40,000 are expected in the first month, with each month thereafter increasing 2 percent. Purchases are based on an expected cost of sales of 55 percent and a required ending inventory of 70 percent of next month's cost of sales. Beginning inventory was $11,000. Sales for January next year are expected to be $50,000. Sales in the previous November and December were $29,000 and $28,000, respectively. Expenses include advertising expense of $900, depreciation expense of $800, interest expense of $1,000, payroll expense of $8,000, supplies expense of $500, and utilities expense of $600 per month throughout the year. All expenses except depreciation are paid in the month during which they are incurred. Collections in the month of sale are expected to be 60 percent, collections in the first month following a sale 30 percent, and in the second month 10 percent. Payments in the month of purchase are expected to be 75 percent, payments in the first month following a purchase 15 percent, and payments in the second month to be 10 percent. Purchases in the previous November and December were $16,000 and $17,000, respectively. Proceeds from the $200,000 loan are expected in June, and $200,000 of equipment will be purchased in July. Monthly payments of $1,400 on the loan also begin in July. The beginning cash balance in January was $15,000. Using the ch6-04 file to start your work, create a cash budget that is based on the assumptions listed in the previous paragraph. 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 and also to group the twelve monthly columns together. (Use equations in all cells, do not just put in numbers within the cell) 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. b. Print the worksheet from part a, above, in Formula 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 and B of the cash budget, no assumptions. c. Collapse rows to level 2 and columns to level 1, 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 cash budget only, no assumptions. d. Expand rows to level 3 and columns to level 2, and then use what-if analysis to calculate end-of-year cash if the sales growth each month were 4 percent and payroll expense were $18,000 per month. 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 only, no assumptions. e. Undo the what-if analysis performed in part d. Use goal seek to determine what sales growth would be needed to produce an ending cash balance of $150,000. 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