Start with the tab layout. Column B holds labels, and columns C:G are FY1 to FY5. The FY0 year, the one that just ended, goes in column B where you need it for deltas. Inputs are marked ⟦blue⟧.
How do you build the FCFF block in Excel?
Block 1: the FCFF build (rows 5–10). Pull, don't retype. Every line comes from the Model tab.
| Row | Label | FY1 formula (fill right) | Sundar FY1 |
|---|---|---|---|
| 5 | EBIT | =Model!C38 | 403.2 |
| 6 | × (1 − tax rate) | =C5*(1-$C$3) where C3 ⟦25%⟧ | 302.4 |
| 7 | + D&A | =Model!C52 | 100.8 |
| 8 | − Capex | =-Model!C64 | (145.6) |
| 9 | − Δ Net working capital | =-(Model!C58-Model!B58) | (36.0) |
| 10 | FCFF | =SUM(C6:C9) | 221.6 |
Row 6 is NOPAT, which is EBIT after tax. Rows 7 to 9 are stored as signed adjustments, so FCFF is a clean SUM(C6:C9). Watch the signs. Capex and NWC increases enter as negatives. A sign flip in row 9 is the classic self-inflicted wound. If working capital is a use of cash and your FCFF went up, you flipped it. Add a sanity row, FCFF check = Model CFO − Model capex + after-tax interest. It works because FCFF is pre-financing and CFO is post-interest, the same reconciliation you met when FCFF was first defined.
If the two disagree by more than rounding, find out why before you go any further.
Advertisement
Worked example: forecast FCFF and discount it
Sundar Foods' full forecast goes like this. Revenue growth fades from 12% to 11%, 10%, 9% and then 8%. EBIT margin is 18%, D&A is 4.5% of revenue, and capex fades from 6.5% to 5.0% of revenue. ΔNWC is 15% of incremental revenue, and tax is 25%.
| ₹ cr | FY1 | FY2 | FY3 | FY4 | FY5 |
|---|---|---|---|---|---|
| Revenue | 2,240.0 | 2,486.4 | 2,735.0 | 2,981.2 | 3,219.7 |
| EBIT (18%) | 403.2 | 447.6 | 492.3 | 536.6 | 579.5 |
| NOPAT | 302.4 | 335.7 | 369.2 | 402.5 | 434.7 |
| + D&A | 100.8 | 111.9 | 123.1 | 134.2 | 144.9 |
| − Capex | (145.6) | (161.6) | (164.1) | (164.0) | (161.0) |
| − ΔNWC | (36.0) | (37.0) | (37.3) | (36.9) | (35.8) |
| FCFF | 221.6 | 249.0 | 290.9 | 335.7 | 382.8 |
Before you touch a discount rate, run the consistency test from the forecasting work. Does the reinvestment support the growth? Total reinvestment in FY5 = (161.0 − 144.9) + 35.8 = ₹51.9 cr, against NOPAT of 434.7. A reinvestment rate of ~12% funding 8% growth implies incremental returns of ~67%. That's aggressive, but it isn't absurd for an asset-light snacks franchise scaling up plants it already has. Note it, and remember that the terminal year won't be allowed to keep that assumption.
Block 2: discounting (rows 12–15).
| Row | Label | FY1 formula | Values |
|---|---|---|---|
| 12 | Period | =COLUMN()-COLUMN($B$12) → 1, 2, … 5 | 1–5 |
| 13 | Discount factor | =1/(1+$C$4)^C12 where C4 = =WACC!C12 | 0.8929 … 0.5674 |
| 14 | PV of FCFF | =C10*C13 | 197.9, 198.5, 207.1, 213.4, 217.2 |
| 15 | Σ PV explicit window | =SUM(C14:G14) | 1,034.0 |
Two mechanical notes. First, =NPV($C$4, C10:G10) gives the same 1,034.0, but only because NPV assumes the first cell is one full year away and that every flow lands at year-end. Know that assumption. When valuation dates and fiscal year-ends drift apart, switch to XNPV with real dates, as you did when IRR and NPV first came up.
Should you use mid-year discounting?
Second, the mid-year convention. Cash arrives throughout the year, and it doesn't all turn up on 31 March at 11:59 pm. So many practitioners discount at t − 0.5 instead, by changing row 13's exponent to (C12-0.5). That's the same as multiplying year-end EV by (1+WACC)^0.5 = 1.0583 here, an uplift of ~5.8%. By default, the course uses the mid-year convention, set back in the terminal-value lessons and applied to the terminal value as well as the explicit flows.
So why does this tab print year-end figures? On purpose. While you're learning the layout, every cell should reconcile by hand to the arithmetic above. Flipping row 13's exponent is the whole change, and a live valuation makes it. Year-end is the stated exception. You take it where cash really does bunch, where a mandate wants the conservative read, or where you have to line up against someone else's year-end model. Whichever you use, put the convention on the output sheet, and never let a 5.8% convention choice masquerade as 5.8% of insight.