The accountant for Nelly's Dress Shop prepared the fourth quarter 2017 cash budget that appears on the following spreadsheet. 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

a. Construct a spreadsheet to model the cash budget as in the following screen capture. Be sure to use formulas where possible so that any changes to the estimates will be automatically reflected in the spreadsheet.

Spreadsheet Tips
(1) Rows 11, 15, 17, 18, 20 to 22, and 24 should be based on formulas.
(2) Cells F6, H6, F9, and H9 should be based on formulas also. For example, cell F6 should be = D24.

Transcribed Image Text:

1 Chapter 7- Working with Excel Name Desired cash cushion Interest rate per month $ 14,000 2% 6 Beginning loan balance 72,806 $103,152 $108,615 October November December 10 11 12 13 Beginning cash balance Add cash receipts Cash available before current financing activity (a) Less disbursements 12,454 11,937 $ 11,828 104.00192,000 230,000 176,454 203,937 241,828 139,000 56,400 192.800195,400 221.200 For inventory purchases 150,800 42,000 162,800 58.400 For S&A expenses Total budgeted disbursements (b) Payments minus receipts 15 16 17 18 19 Surplus (shortage) Needed cash cushion Financing activity (16,346) 14,000 8,537 20,628 14,000 14,000 (6,628) 11,960 $101,987 Borrowing (repayment) (c) Interest expense (d) 30,346 2,063 5,463 2.002,172 2,040 $ 11,828 21 22 23 24 Ending cash balance (a-b+c-d) Loan balance $103,152 $108,615