QSe CH 8 Cash Budget MAC Version (1) Insert Page Layout Formulas Data Review View Home Conditional Formatting * A A Calibri (Body) General 11 Format as Table $ 0 .00 OC Paste I U .0 Cell Styles fx C17 A B C D F G K M Tracey Excel 2 Cash Budget 1 Name 4 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: 5 3rd Qtr 4th Qtr 1st Qtr 2nd Qtr. 6 7 Total cash receipts Total cash disbursements $340,000 $670,000 $410,000 $470,000 $530,000 $450,000 $430,000 $480,000 9 10 Beginning cash balance 11 Minimum cash balance required 12 Quarterly interest rate on borrowings 13 If nothing is req'd in a financing cell $50,000 $30,000 3% $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 s own as negativenumbers. 18 19 Required: Prepare the company's cash budget for the upcoming fiscal year in the spaces below. EVERY cell should be a formula or cell 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.) 20 21 22 Formulas and/or cell references must be used for the yellow cells. 23 2nd Quarter 3rd Quarter 4th Quarter 24 1st Quarter 25 Cash balance, beginning 26 Add total cash receipts Total cash available 27 Subtract total cash disbursements 28 Excess (deficiency) of cash 29 available over disbursements 30 31 Financing: Borrowings (at beginning of qtr.) Repayments (at end of qtr.) 32 0 33 0 0 34 Interest C 35 Total financing 0 36 Cash balance, ending 37 38 39 40 41 Sheet1 El Ready LT olo Nn nD