I am trying to create the formula for Cash Used to Retire Debt in question 7. I know it is an if function...
Q7: 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. [6 points]
Beginning Cash Balance $5,000.00 -$7,400.00 - $18,100.00 $6,700.00 $8,520.00 $6,840.00 Cash Inflows (Income): Customer Payments Cash Sales Total Cash Inflows $35,000.00 $15,000.00 $53,000.00 $64,680.00 $76,540.00 $90,540.00 $52,000.00 $62,000.00 $73,500.00 $85,000.00 $100,600.00 $117,700.00 $87,000.00 $107,000.00 $126,500.00 $150,680.00 $177,140.00 $208,240.00 Assignment Tasks $20,000.00 $30,000.00 $35,000.00 $40,000.00 $50,000.00 $60,000.00 Cash Outflows (Expenses): Advertising Loan Interest Insurance Inventory Purchases Office Supplies Payroll Rent or Lease Utilities & Telephone Total Expenses $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 $99,400.00 $117,700.00 $125,800.00 $141,100.00 $163,600.00 $192,000.00 Cash Flow 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. (6 points) Cash Flows (Net): -$12,400.00 -$10,700.00 $700.00 $9,580.00 $13,540.00 $16,240.00 Shortfall/Surplus Cash $6000.00* $7,400.00 - $18,100.00 $6,700.00 $8,520.00 $6,840.00 $7,720.00 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 $7,400.00 $0.00 $0.00 $7,400.00 $18,100.00 $0.00 $0.00 $25,500.00 $6,700.00 $0.00 $0.00 $32,200.00 $8,520.00 $0.00 $0.00 $40,720.00 $0.00 $6,840.00 $6,840.00 $33,880.00 $0.00 $7,720.00 $7,720.00 $26,160.00 Ending Cash Balance $5,000.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 * 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
