D E G H The accountant for Nelly's Dress Shop prepared the fourth quarter 2017 cash budget below. Nelly's has a policy to maintain a minimum cash balance of $14,000 before the interest payment at the end of each month. The shop borrows and repays funds on the first day of the month. The interest rate is 2 percent per month. $ 14,000 2% Required Complete the spreadsheet below. Be sure to use formulas where possible so that any changes to the estimates will be 5 automatically reflected in the spreadsheet. Note: Negative amounts or amounts to be deducted should be input and displayed as negative values. All 6 other answers should be input and displayed as positive values. Spreadsheet Tips 9 1. Rows 21, 25, 27, 28, 30 to 32, and 34 should be based on formulas. 10 2. Cells C16, D16, C19, and D19 should be based on formulas also. For example, cell C16 should be =B34. 3. Explore the power of formulas by copying cells B13:D34 into the scratchpad area below. There you will be able to modify the Desired cash cushion and interest rate per month (currently in cells B13:B14) and 1 1 see the resulting changes in the cash budget. 12 13 Desired cash cushion 14,000 14 Interest rate per month 2% 15 15 Beginning loan balance $ 72,806 $ 103,152 18 October November December 19 Beginning cash balance $ 12,454 $ 11,937 20 Add cash receipts 164,000 192,000 230,000 21 Cash available before current financing activity (a) 176,454 22 Less disbursements 23 For inventory purchases 150,800 139,000 162,800 24 For selling & administrative expenses 42,000 56,400 58,400 25 Total budgeted disbursements (b) 192,800File Home Insert Draw Formulas Data Review View Help Editing v al Wrze Text General LG Copy Format Painter Merge 3. Center $( 95 9 08 98 Conditional Format As Styles Formatting Table Clipboard A1 Accessibility tab summary: The financial information of Nelly's Dress Shop is presented in row 2. A statement of requirement is pres B Spreadsheet Tips 1. Rows 21, 25, 27, 28, 30 to 32, and 34 should be based on formulas. 10 2. Cells C16, D16, C19, and D19 should be based on formulas also. For example, cell C16 should be =B34. 3. Explore the power of formulas by copying cells B13:D34 into the scratchpad area below. There you will be able to modify the Desired cash cushion and Interest rate per month (currently in cells B13:B14) and 17see the resulting changes in the cash budget. 12 13 Desired cash cushion 14,000 14 Interest rate per month 2% 15 16 Beginning loan balance S 72,806 5 103,152 13 October November December + 19 Beginning cash balance $ 12,454 5 11,937 20 Add cash receipts 164,000 192,000 230,000 21 Cash available before current financing activity (a) 176,454 22 Less disbursements 23 For inventory purchases 150,800 139,000 162,800 24 For selling & administrative expenses 42,000 56,400 58,400 25 Total budgeted disbursements (b) 192,800 26 Payments minus receipts 27 Suplus (shortage) (16,346) 28 Needed cash cushion 14,000 14,000 14,000 29 Financing activity 30 Borrowing (repayment) (c) 30.346 31 Interest expense (d) 2,063 32 Ending cash balance (a-b+c-d) $ 11,937 33 34 Loan balance $ 103,152 35 Graded Worksheet