B C D E F G H I J K L M N O 5 The Tracey Company is a retailer that is preparing its budget for the upcoming fiscal year. Management has prepared the following summary of its budgeted cash flows: 1st Qtr. 2nd Qtr. 3rd Qtr. 4th Qtr. Total cash receipts $340,000 $670,000 $410,000 $470,000 Total cash disbursements $530,000 $450,000 $430,000 $480,000 7 8 3% 10 Beginning cash balance $50,000 11 Minimum cash balance required $30,000 12 Quarterly interest rate on borrowings 13 If nothing is req'd in a financing cell $0 14 15 The company requires a minimum cash balance of $30,000. The company may borrow any amount at the BEGINNING of any quarter 16 and may repay its loans, or any part of its loans, and the END of any quarter. Interest payments are due on any principal at the time 17 it is repaid. Repayments of principal and interest should be shown as negative numbers. 18 19 Required: 20 Prepare the company's cash budget for the upcoming fiscal year in the spaces below. EVERY cell should be a formula or cell 21 reference. (Hint: We need to borrow in the first quarter and we have enough to pay it back, with the interest, in the second quarter.) 2nd Quarter 3rd Quarter 4th Quarter 23 Formulas and/or cell references must be used for the yellow cells. 1st Quarter 25 Cash balance, beginning 26 Add total cash receipts 27 Total cash available 28 Subtract total cash disbursements 29 Excess (deficiency) of cash 30 available over disbursements 31 Financing: 32 Borrowings (at beginning of qtr.) 33 Repayments (at end of qtr.) 34 Interest 35 Total financing 36 Cash balance, ending