B 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. Required $14,000 2% Complete the spreadsheet below. Be sure to use formulas where possible so that any changes to the estimates will be automatically reflected in the spreadsheet. Note: Negative amounts or amounts to be deducted should be input and displayed as negative values. All other answers should be input and displayed as positive values. 1 Spreadsheet Tips 1. Rows 21, 25, 27, 28, 30 to 32, and 34 should be based on formulas. 0 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 1 will be able to modify the Desired cash cushion and Interest rate per month (currently in cells B13:B14) and see the resulting changes in the cash budget. 3 Desired cash cushion 4 Interest rate per month 5 6 Beginning loan balance 7 $ 72,806 October November December 9 Beginning cash balance $ 12,454 0 Add cash receipts 164,000 192,000 230,000 1 Cash available before current financing activity (a) 2 Less disbursements 3 For inventory purchases 150,800 139,000 162,800 24 For selling & administrative expenses 42,000 56,400 58,400 5 Total budgeted disbursements (b) 6 Payments minus receipts 27 Suplus (shortage) 28 Needed cash cushion 19 Financing activity 0 31 Borrowing (repayment) (c) Interest expense (d) 32 Ending cash balance (a-b+c-d) 33 34 Loan balance +