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.

Calculate the beginning cash balance for February through June.

1. The beginning cash balance is equal to the ending cash balance from the prior month.
2. Reuse your formula to complete the beginning cash balance for each month.

Calculate the loan interest for each month.

1. The loan interest is equal to the loan balance for that month times the monthly interest rate in cell C3.
2. This calculation will create a circular reference. Configure Excel to appropriately handle the circular reference.
3. Reuse your formula to complete the loan interest for each month.  

Monthly Interest Rate Line of Credit Needed Beginning Cash Balance Cash Inflows (Income): Customer Payments Cash Sales Total Cash Inflows Cash Outflows (Expenses): Advertising Loan Interest Insurance Inventory Purchases Office Supplies Payroll Rent or Lease Utilities & Telephone Total Expenses Cash Flows (Net): Shortfall/Surplus Cash 1.5% December Cash Flow Budget Worksheet $0.00 $6,000.00 $1,000.00 $0.00 January February $5,000.00 $0.00 $5,000.00 $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 $87,000.00 $107,000.00 $126,500.00 $150,680.00 $177,140.00 $208,240.00 $6000.00* -$7,512.69 -$10,700.00 $700.00 March $0.00 $20,000.00 $30,000.00 $35,000.00 $40,000.00 $50,000.00 $60,000.00 $112.69 $0.00 $0.00 $0.00 $0.00 $0.00 $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,512.69 $117,700.00 $125,800.00 $141,100.00 $163,600.00 $192,000.00 -$12,512.69 -$10,700.00 $700.00 $7,512.69 $10,700.00 $0.00 $0.00 $0.00 $0.00 $7,512.69 $10,700.00 Loan Calculations Additional Cash Needed Cash Available to Retire Debt Cash Used to Retire Debt Loan Balance** Ending Cash Balance * 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. $0.00 April May June $0.00 $10,280.00 $23,120.00 $0.00 $9,580.00 $13,540.00 $16,240.00 $0.00 $9,580.00 $23,820.00 $39,360.00 $0.00 $0.00 $0.00 $0.00 $700.00 $9,580.00 $23,820.00 $39,360.00 -$700.00 $700.00 -$700.00 $700.00 -$700.00 $700.00 $700.00 -$700.00 $10,280.00 $23,120.00 $40,060.00