The accountant for Nelly€™s Dress Shop prepared the fourth quarter 2011 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 5 D24.

Transcribed Image Text:

$ 14,000 2% Desired cash cushion Interest rate per month $ 72,806 Beginning loan balance $103,162 $108,616 October November December S 12454 164,000 $ 11.937 $ 11.828 Beginning cash balance Add cash rocoipts Cash available before curent financing activity (a) 176.454 Less disbursements 192,000 230,000 203.937 241.828 For inventory purchases For S&A exponse0 Total budgeted disbursements (b) Payments minus receipte Suplus (shortage) Needed cash cushion 139.000 150.800 162,800 56,400 195,400 42,000 192,800 58,400 221,200 (16,346) 14,000 8,537 14,000 20,628 14,000 Financing activity Borrowing (repayment) (c) Interest expense (d) Ending cash balance (a-btc-d) (6,628) 2,040 $ 11.960 30,346 2,063 S 11.937 5.463 2,172 $ 11.828 Loan balance $103,152 $108,615 $101.987