I need help with the excel formulas for this part of my assignment.
Task # Points Task Description 1 3 Calculate the total cash inflows for the six months. The total cash inflows are sum of the cash sales and customer payments. 2 3 Calculate the total expenses for the six months. The total expenses are sum of all of the expenses incurred for each the month in which they are incurred. 3 1.5 Calculate the net cash flows for the six months. The net cash flows are calculated as the difference between the total cash inflows and the total expenses for each month. 4 3 Calculate the Shortfall/Surplus Cash for the six months. The shortfall/surplus cash is the beginning cash balance plus the net cash flows for each month. 5 6 Calculate the additional cash needed for the six months. 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. Therefore, additional cash needed is equal to any cash shortfall. It is zero if there is a cash surplus. The additional cash needed should be a positive number even though a shortfall will be a negative number in the model (row 26). 6 6 Calculate the cash available to retire debt for each of the six months. There is cash available to retire debt if there is a cash surplus at the end of a month (shortfall/surplus cash > 0). Therefore cash available to retire debt is equal to any cash surplus at the end of a month. If there is not a surplus, cash available to retire debt is 0. 7 3 Calculate the cash used to retire debt for each of the six months. Since the company can't pay down more debt than there is cash available to retire the debt, the cash used to retire debt cannot exceed the cash available to retire debt. Likewise, the company won't pay more money to retire debt than it has debt from the prior month to retire. Therefore, the cash used to retire debt will be equal to the cash available to retire debt if that total is less than the loan balance from the prior month. Otherwise, the cash used to retire debt will be the loan total from the prior month. 8 3 Calculate the loan balance for each of the six months. The loan balance is calculated as the additional cash needed for a given month minus any cash used to retire debt for that month plus the loan balance carried from the prior month. 9 3 Calculate the ending cash balance for each month. The ending cash balance is equal to the cash available to retire debt minus any cash used to retire debt. 10 2.5 Calculate the beginning cash balance for February through June. The beginning cash balance is equal to the ending cash balance from the prior month. 11 3 Calculate the loan interest for each month. The loan interest is equal to the loan balance for that month times the monthly interest rate in cell C3. This will create a circular reference. Configure Excel to appropriately handle the circular reference. 12 3 Calculate the line of credit needed. The line of credit needed is the credit limit the company needs to negotiate with the bank. It is calculated as the maximum loan balance that the company expects to carry over the course of the six month period. Total: 40 A B D E F G H 1 J K 1 2 Cash Flow Budget Worksheet 1.5% 3 Monthly Interest Rate 4 Line of Credit Needed 5 6 7 December February March April May June January $5,000.00 8 Beginning Cash Balance 9 10 11 Cash Inflows (Income): Customer Payments Cash Sales Total Cash Inflows $35,000.00 $45,000.00 $53,000.00 $64,680.00 $76,540.00 $90,540.00 $52,000.00 $62,000.00 $73,500.00 $86,000.00 $100,600.00 $117,700.00 12 13 14 15 16 $20,000.00 $30,000.00 $35,000.00 $40,000.00 $50,000.00 $60,000.00 17 18 19 Cash Outflows (Expenses): Advertising Loan Interest Insurance Inventory Purchases Office Supplies Payroll Rent or Lease Utilities & Telephone Total Expenses 20 21 22 23 $4,500.00 $4,500.00 $4,500.00 $4,500.00 $4,500.00 $4,500.00 $46,000.00 $53,000.00 $54,500.00 $59,800.00 $71,900.00 $83,300.00 $3,200.00 $3,200.00 $3,200.00 $3,500.00 $4,000.00 $4,000.00 $11,000.00 $12,000.00 $13,500.00 $18,100.00 $17,700.00 $23,200.00 $12,000.00 $12,000.00 $12,000.00 $12,000.00 $12,000.00 $12,000.00 $2,700.00 $3,000.00 $3,100.00 $3,200.00 $3,500.00 $5,000.00 24 25. CashFlow Summary CatastropheCoverageLowActivity CatastropheCoverage WithSurgery Pr ... + A1 A B D E F G H J 25 26 Cash Flows (Net): 27 28 Shortfall/Surplus Cash $6000.00* 29 30 31 32 33 34 35 Loan Calculatations Additional Cash Needed Cash Available to Retire Debt Cash Used to Retire Debt Loan Balance** $0.00 $6,000.00 $1,000.00 $0.00 36 Ending Cash Balance $5,000.00 37 38 39 * Note: there was a loan balance of $1000 in November which was paid to $0 with the cash surplus in December. This is why the beginning cash value in January is $1000 less than the ending cash in December. ** A loan taken out in any given month cannot be paid off in that month. The earliest a loan will be paid off will be the next month. 40 41 42 43 44 45 46 Task # Points Task Description 1 3 Calculate the total cash inflows for the six months. The total cash inflows are sum of the cash sales and customer payments. 2 3 Calculate the total expenses for the six months. The total expenses are sum of all of the expenses incurred for each the month in which they are incurred. 3 1.5 Calculate the net cash flows for the six months. The net cash flows are calculated as the difference between the total cash inflows and the total expenses for each month. 4 3 Calculate the Shortfall/Surplus Cash for the six months. The shortfall/surplus cash is the beginning cash balance plus the net cash flows for each month. 5 6 Calculate the additional cash needed for the six months. 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. Therefore, additional cash needed is equal to any cash shortfall. It is zero if there is a cash surplus. The additional cash needed should be a positive number even though a shortfall will be a negative number in the model (row 26). 6 6 Calculate the cash available to retire debt for each of the six months. There is cash available to retire debt if there is a cash surplus at the end of a month (shortfall/surplus cash > 0). Therefore cash available to retire debt is equal to any cash surplus at the end of a month. If there is not a surplus, cash available to retire debt is 0. 7 3 Calculate the cash used to retire debt for each of the six months. Since the company can't pay down more debt than there is cash available to retire the debt, the cash used to retire debt cannot exceed the cash available to retire debt. Likewise, the company won't pay more money to retire debt than it has debt from the prior month to retire. Therefore, the cash used to retire debt will be equal to the cash available to retire debt if that total is less than the loan balance from the prior month. Otherwise, the cash used to retire debt will be the loan total from the prior month. 8 3 Calculate the loan balance for each of the six months. The loan balance is calculated as the additional cash needed for a given month minus any cash used to retire debt for that month plus the loan balance carried from the prior month. 9 3 Calculate the ending cash balance for each month. The ending cash balance is equal to the cash available to retire debt minus any cash used to retire debt. 10 2.5 Calculate the beginning cash balance for February through June. The beginning cash balance is equal to the ending cash balance from the prior month. 11 3 Calculate the loan interest for each month. The loan interest is equal to the loan balance for that month times the monthly interest rate in cell C3. This will create a circular reference. Configure Excel to appropriately handle the circular reference. 12 3 Calculate the line of credit needed. The line of credit needed is the credit limit the company needs to negotiate with the bank. It is calculated as the maximum loan balance that the company expects to carry over the course of the six month period. Total: 40 A B D E F G H 1 J K 1 2 Cash Flow Budget Worksheet 1.5% 3 Monthly Interest Rate 4 Line of Credit Needed 5 6 7 December February March April May June January $5,000.00 8 Beginning Cash Balance 9 10 11 Cash Inflows (Income): Customer Payments Cash Sales Total Cash Inflows $35,000.00 $45,000.00 $53,000.00 $64,680.00 $76,540.00 $90,540.00 $52,000.00 $62,000.00 $73,500.00 $86,000.00 $100,600.00 $117,700.00 12 13 14 15 16 $20,000.00 $30,000.00 $35,000.00 $40,000.00 $50,000.00 $60,000.00 17 18 19 Cash Outflows (Expenses): Advertising Loan Interest Insurance Inventory Purchases Office Supplies Payroll Rent or Lease Utilities & Telephone Total Expenses 20 21 22 23 $4,500.00 $4,500.00 $4,500.00 $4,500.00 $4,500.00 $4,500.00 $46,000.00 $53,000.00 $54,500.00 $59,800.00 $71,900.00 $83,300.00 $3,200.00 $3,200.00 $3,200.00 $3,500.00 $4,000.00 $4,000.00 $11,000.00 $12,000.00 $13,500.00 $18,100.00 $17,700.00 $23,200.00 $12,000.00 $12,000.00 $12,000.00 $12,000.00 $12,000.00 $12,000.00 $2,700.00 $3,000.00 $3,100.00 $3,200.00 $3,500.00 $5,000.00 24 25. CashFlow Summary CatastropheCoverageLowActivity CatastropheCoverage WithSurgery Pr ... + A1 A B D E F G H J 25 26 Cash Flows (Net): 27 28 Shortfall/Surplus Cash $6000.00* 29 30 31 32 33 34 35 Loan Calculatations Additional Cash Needed Cash Available to Retire Debt Cash Used to Retire Debt Loan Balance** $0.00 $6,000.00 $1,000.00 $0.00 36 Ending Cash Balance $5,000.00 37 38 39 * Note: there was a loan balance of $1000 in November which was paid to $0 with the cash surplus in December. This is why the beginning cash value in January is $1000 less than the ending cash in December. ** A loan taken out in any given month cannot be paid off in that month. The earliest a loan will be paid off will be the next month. 40 41 42 43 44 45 46
