Lesson 10 of 10

Build your first DCF end to end

Step 3 of 7

Worked example: the forecast

Step 1: the base year

Open a blank sheet. From the 10-K:

Line FY2026
Total revenue (sales + membership fees) 303,154
Operating income 11,685
Operating margin 11,685 ÷ 303,154 = 3.85%
Tax rate (taxes 3,025 ÷ pre-tax income 12,251) 24.7%

Revenue grew 10.1% in FY2026 and 9.1% a year over five years (FY2021: 195,929).

Step 2: last year's FCFF (lesson 04)

after-tax operating profit = 11,685 × (1 − 0.247)                  = 8,799
reinvestment = (capex 6,435 − depreciation 2,674) − working capital released 2,320 = 1,441
FCFF = 8,799 − 1,441                                                 = 7,358

Working capital fell: payables rose 2,755 while inventories rose 1,153, and other items released 718. Suppliers fund much of Costco's stock.

Step 3: the story and its inputs

These are my illustrative choices; change them once the build works.

  • Growth: 9% in FY2027, fading 1 point a year to 5% in FY2031, near Costco's own pace; Damodaran's Retail (General) industry expects about 4% a year.
  • Margin: held at 3.85% (3.65% in FY2024, 3.77% in FY2025).
  • Sales-to-capital 6.5, Costco's own: revenue rose 60,864 on 9,306 of net capex from FY2023 to FY2026, leaving out the working-capital help. Damodaran's industry average is 3.51.
  • Long-run growth 4%, lesson 06's base case.

Step 4: the cost of capital (lesson 05)

cost of equity = 5.07% + 0.81 × 4.41% = 8.64%

Inputs: the 10-year Treasury (18 Nov 2026), Damodaran's Retail (General) industry beta, his premium (1 Nov 2026).

Debt: with no new bond to read a rate from, use Damodaran's ratings table. It turns interest coverage (operating income ÷ interest) into a rating and a default spread, the extra yield lenders charge over Treasuries. Costco's 80.6 (11,685 ÷ 145) rates AAA, spread 0.40%: pre-tax 5.47%, after tax 4.12%.

Weights at market value, with shares outstanding as in lesson 05: equity 443.129M × $1,093.83 = $484.7B; debt $6.2B (2,248 + 3,914), or 1.3%.

cost of capital = 98.7% × 8.64% + 1.3% × 4.12% ≈ 8.59%  →  rounded to one decimal, as in lesson 05: 8.6%

Step 5: set up the inputs

Type these in A1:B9 (labels in A, values in B). Type 8.6% in B5, not 8.59%: every check value uses it.

Cell Input Value
B1 Base revenue 303154
B2 Operating margin 3.85%
B3 Tax rate 24.7%
B4 Sales-to-capital 6.5
B5 Cost of capital 8.6%
B6 Long-run growth 4%
B7 Cash + short-term investments 21301
B8 Debt 6162
B9 Diluted shares (millions) 445.129

B9 is diluted, not Step 4's 443.129M: shares outstanding (29 Sep 2026, 10-K cover) plus 2.000M unvested restricted stock units, as in lesson 07. B7 is cash 20,207 + short-term investments 1,094.

Step 6: the forecast and the final year

Row 13 is the base year: A13 0, C13 =B1. Rows 14–18 are years 1–5: year number in A, growth in B (9%, 8%, 7%, 6%, 5%). Row 14, then fill down to row 18:

C14 revenue          =ROUND(C13*(1+B14),0)
D14 operating income =ROUND(C14*$B$2,0)
E14 after-tax profit =ROUND(D14*(1-$B$3),0)
F14 reinvestment     =ROUND((C14-C13)/$B$4,0)
G14 FCFF             =E14-F14
H14 discount factor  =1/(1+$B$5)^A14
I14 present value    =G14*H14

Row 19 is the final year (lesson 06's recipe): type =$B$6 in B19, then fill C18:G18 down one row. Leave H19 and I19 empty.

Year Revenue Op. income FCFF
FY2027 330,438 12,722 5,382
FY2028 356,873 13,740 6,279
FY2029 381,854 14,701 7,227
FY2030 404,765 15,583 8,209
FY2031 425,003 16,363 9,207
Final year 442,003 17,017 10,199
Year After-tax profit Reinvestment Present value
FY2027 9,580 4,198 4,955.8
FY2031 12,321 3,114 6,094.9
Final year 12,814 2,615 —