Answered step by step
Verified Expert Solution
Question
1 Approved Answer
The Tracey company is a retailer that is preparing it's budget for the upcoming fiscal year. management has prepared the following summary of it's budgeted
The Tracey company is a retailer that is preparing it's budget for the upcoming fiscal year. management has prepared the following summary of it's budgeted cash flows. Formulas and/or cell reference must be used for the yellow cells. Formulas need to fill the yellow cells.
M N O Q R s U 6 B G H 1 K 1 Weekly Check-in Assignment #9 Name 2 Due by 11:59 p.m. on Sunday, October 24, 2021 3 4 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: 1st Qtr. 2nd Qtr. 3rd Qtr. 4th Qt 7 Total cash receipts $340,000 $670,000 $410,000 $470,000 8 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 so 14 15 The company requires a minimum cash balance of $30,000. The company may bolow 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: 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 $50,000 25 Cash balance, beginning $0 $0 SO 26 Total cash receipts 27 Total cash available 28 Total cash disbursements 29 Excess (deficiency) of cash 30 available over cash disbursements 31 Financing 0 32 (A) 0 Borrowings (at beginning of qtr.) Sheet1 FER 16096 13 If nothing is req'd in a financing cell $0 12 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: 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 SO SO 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 (A) Borrowings (at beginning of qtr.) 0 0 33 0 (Repayments) (at end of qtr.) 0 (B) 0 0 34 0 0 (C) (Interest) 0 0 0 0 tal fina SO SO SO $0 36 Cash balance, ending 37 38 HINTS: 39 40 (A) This formula will be =B11-B30 (B) This formula will be -B32 (yes, that's a minus sign and not an equal sign) 41 42 (C) This formula will be =E33"B12"2
Step by Step Solution
There are 3 Steps involved in it
Step: 1
Get Instant Access to Expert-Tailored Solutions
See step-by-step solutions with expert insights and AI powered tools for academic success
Step: 2
Step: 3
Ace Your Homework with AI
Get the answers you need in no time with our AI-driven, step-by-step assistance
Get Started