Beginning Cash Balance Cash flown com 34 15 Cash Outowapenses) 16 D 2 Cash M MyEducator Tasks Cash Flow Budget Worksheet 116 340 34 Cash Flow Complete the statement of cash flow for January through June on the CashFlow worksheet by completing the following tasks. As part of the process you will need to use a circular reference. You will need to set Excel to handle circular references CD Calculate the total cash inflows for the six months a. The total cash inflows is the sum of the cash sales at the customer payments b Reuse your function to complete the total cash inflows for each month Calculate the total expenses Calculate the net cash flows Q) Caluate the shortfarpu C) Caluate the additional cash C Cone the cash available to MyEducator Tasks 2.1 Calculate the total cash inflows for the six months. a. The total cash inflows is the sum of the cash sales and the customer payments. b. Reuse your function to complete the total cash inflows for each month. 3 2.2 Calculate the total expenses for the six months. a. The total expenses is the sum of all the expenses incurred for the month. b. Be sure to sum all of the expenses including the "Loan Interest" that will be calculated in a later task. c. Reuse your function to complete the total expenses for each month. 3 rach flowS MyEducator Tasks 2.3 Calculate the net cash flows for the six months. a. The net cash flows is calculated as the difference between the total cash inflows and the total expenses for each month. b. Reuse your formula to complete the net cash flows for each month. (3 2.4 Calculate the shortfall/surplus cash for the six months. a. The shortfall/surplus cash is the beginning cash balance plus the net cash flows for each month. b. Reuse your formula to complete the Shortfall/Surplus for each month. Note- The February through June beginning cash balances will be calculated in a later task. 3 25 Calculate the additional cash 2.5 Calculate the additional cash needed for the six months. a. When there is a cash shortfall (the shortfall/surplus cash calculation is less than zero), additional cash must be obtained from a loan to cover all of the monthly expenses. b. Additional Cash Needed is equal to any cash shortfall. c. Additional Cash Needed is zero if there is a cash surplus. d. Additional Cash Needed should be a positive number even though a shortfall will be a negative number in the model (row 28). Use the ABS function to make the value positive. e. Reuse your function to complete the Additional Cash Needed for each month. (6 2.6 Calculate the cash available to retire debt for each of the six months. a. There is cash available to retire debt if there is a cash surplus at the end of a month (shortfall/surplus cash > 0). b. Cash Available to Retire Debt is + 100% 2 2.6 Calculate the cash available to retire debt for each of the six months. a. There is cash available to retire debt if there is a cash surplus at the end of a month (shortfall/surplus cash > 0). b. Cash Available to Retire Debt is equal to any cash surplus at the end of a month. c. If there is not a cash surplus, Cash Available to Retire Debt is zero (0). d. Reuse your function to complete the Cash Available to Retire Debt for each month. (6 2.7 Calculate the cash sed to retire debt for each of the six months. Since the company can't use more cash than is available to retire the debt, Cash Used to Retire Debt cannot exceed the Cash Available to Retire Debt. Likewise, the company can't use more money to retire debt than it has debt from the prior month to retire. a. Cash Used to Retire Debt will be equal to the Cash Available to Retire Debt if the cash available is 100% a. Cash Used to Retire Debt will be equal to the Cash Available to Retire Debt if the cash available is less than the loan balance from the prior month. b. Otherwise, the Cash Used to Retire Debt will be the loan total from the prior month. c. Reuse your function to complete the Cash Used to Retire Debt for each month. 6 2.8 Calculate the loan balance for each of the six months. a. The loan balance is equal to Additional Cash Needed minus any Cash Used to Retire Debt for that month plus the loan balance carried from the prior month. b. Reuse your formula to complete the loan balance for each month. 3 2.9 Calculate the ending cash 2.10 Calculate the beginning cash 2.11 Calculate the loan interest + 100% 3 2.9 Calculate the ending cash balance for each month. a. The ending cash balance is equal to the Cash Available to Retire Debt minus any Cash Used to Retire Debt. b. Reuse your formula to complete the ending cash balance for each month. 3 2.10 Calculate the beginning cash balance for February through June. a. The beginning cash balance is equal to the ending cash balance from the prior month. b. Reuse your formula to complete the beginning cash balance for each month. 3 2.11 Calculate the loan interest for each month. a. The loan interest is equal to the loan balance for that month times the monthly interest rate in cell 100% i 2 3 2.11 Calculate the loan interest for each month. a. The loan interest is equal to the loan balance for that month times the monthly interest rate in cell C3). b. This calculation will create a circular reference. Configure Excel to appropriately handle the circular reference. c. Reuse your formula to complete the loan interest for each month. E 3 2.12 Calculate the line of credit needed. a. The line of credit needed is the credit limit the company needs to negotiate with the bank. b. It is calculated as the maximum loan balance that the company expects to carry over the course of the six month period. 3 El 100%