Cash Flow Forecast

About this template

A 13-week rolling view

Three sheets: Weekly cash plan, Forecast by line and Scenarios & notes. Everything hangs off the four assumptions at the top of the first sheet – opening cash on hand KRW 184m, minimum cash buffer KRW 120m, accounts receivable days 45, accounts payable terms 30 – and the weekly rows 14 to 26.

Filling it in

1. Put the actual bank balance on day one into the Opening cash on hand cell (C7). Week 1 takes it directly with =$C$7; every later week inherits the closing balance above it.
2. Set the Minimum cash buffer (C8). Note ④ on the Scenarios & notes sheet suggests two months of payroll. That number is the threshold for every weekly alert.
3. Type only Forecast inflow and Forecast outflow on rows 14-26. Opening balance, net for week, closing balance and the shortfall alert are formulas.
4. On the Forecast by line sheet, spread three inflow lines (Accounts receivable collected, Advances · deposits, Other income) and five outflow lines (Payroll · bonuses, Subcontracting · purchases, Rent · service charges, Taxes · national insurance (KR), Equipment · asset purchases) across Jul to Dec. That sheet is in KRW millions, unlike the weekly plan.
5. After 13 weeks, drop the first week and add one at the end.

Worked example

• Week 8 with KRW 36m in and KRW 152m out nets -116m and pushes the closing balance under the buffer, so the alert =IF(H21<0,”Cash shortfall”,IF(H21<$C$8,”Below buffer”,”Ample”)) switches to Below buffer.
• The alert summary finds the lowest closing balance and its week with =INDEX(B14:B26,MATCH(MIN(H14:H26),H14:H26,0)) and sizes the funding gap with =MAX(0,$C$8-MIN(H14:H26)). Collection or borrowing has to land before that week.

Two things to repair

• On the scenario table, the “Vs. buffer” column (F7:F9) is not a formula but fixed text – Ample, Monitor, Shortfall risk. It keeps saying Ample even when the closing balance is below the buffer. Replace F7 with =IF(E7<0,”Cash shortfall”,IF(E7<‘Weekly cash plan’!$C$8,”Below buffer”,”Ample”)) and fill it down, so the verdicts match the weekly alert word for word.
• Cumulative balance on the Forecast by line sheet starts as =184+D23, with the opening balance hard-coded. Point it at the assumption instead: =’Weekly cash plan’!$C$7/1000000+D23 – the division converts won to the millions that sheet works in.

Common mistakes

• Booking uncertain receipts at full value. The point of the sheet is the worst plausible balance, so discount them.
• Entering the invoice date instead of the week the money actually arrives.
• Typing over a closing balance or an alert, which breaks the chain from that week onward.
• Forgetting that the weekly plan is in won while the Forecast by line sheet is in millions.