AutoSave OFF Checkin #9 Cash Budget FULL Home Insert Draw Page Layout Formulas Data Review View Tell me X Calibri (Body) 11 A v ale a Wrap Text General Tv Paste B I av Av 0 Conditiona Formatting H L M M N 0 4 8 9 Open recovered workbooks? Your recent changes were saved. Do you want to continue working where you left off? A1 XV fx Weekly Check-in Assignment #9 A B C D E F G 1 J K 1 Weekly Check-in Assignment #9 Name Due by 11:59 p.m. on Sunday, October 24, 2021 3 The Tracey Company is a retailer that is preparing its budget for the upcoming fiscal year. Management has prepared the following 5 summary of its budgeted cash flows: 6 1st Qtr. 2nd Qtr. 3rd Qtr. 4th Qtr. 7 Total cash receipts $340,000 $670,000 $410,000 $470,000 Total cash disbursements $530,000 $450,000 $430,000 $480,000 9 10 Beginning cash balance, 1st quarter $50,000 11 Minimum cash balance required $30,000 12 Quarterly interest rate on borrowings 3% 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 Required: PLEASE, PLEASE, PLEASE read all of the stuff on this page. 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.) 22 Look at the formulas that I did in the pinkish cells. You do not need to change those. Look at my hints for A, B, C starting in row 38. 23 Formulas and/or cell references must be used for the yellow cells. Repayments and Interest in the 2nd Quarter should be NEGATIVE. 24 1st Quarter 2nd Quarter 3rd Quarter 4th Quarter 25 Cash balance, beginning $50,000 $0 $0 $ $0 26 Total cash receipts 27 Total cash available 28 Total cash disbursements 29 Excess (deficiency) of cash 30 available over cash disbursements 31 Financing: 32 Borrowings (at beginning of qtr.) (A) o 0 o 0 0 33 (Repayments) (at end of qtr.) 0 (B) 0 0 0 34 (Interest) 0 (C) 0 0 35 Total financing 0 0 0 0 19 0 0 $0 $0 $0 $0 (A) This formula will be =B11-B30 36 Cash balance, ending 37 38 HINTS: 39 40 41 42 (B) This formula will be-B32 (yes, that's a minus sign and not an equal sign) (= (C) This formula will be =E33*12*2