Three-Statement Modeling
Income Statement, Balance Sheet and Cash Flow Statement are linked together.
Three-Statement Financial Modeling | Forecasting | DCF Valuation
This is an independent financial modeling project that analyzes Microsoft Corporation. It uses Microsoft's reported financials for FY2019–FY2024 as the historical foundation and projects FY2025–FY2029 under Bull, Base and Bear scenarios.
Developed and adapted an integrated three-statement financial model for Microsoft Corporation using FY2019–FY2024 historical financials and FY2025–FY2029 forecast scenarios.
The model links the Income Statement, Balance Sheet, and Cash Flow Statement and incorporates revenue and segment analysis, operating assumptions, PP&E and capital expenditure schedules, Bull/Base/Bear scenario analysis, and DCF valuation.
Key outputs include projected revenue, EBITDA, free cash flow, enterprise value, equity value, implied share value, and valuation sensitivity to WACC and terminal growth assumptions.
Every forecast output is formula-driven. A single scenario selector flows through the statements, the PP&E schedule and the DCF, and a balance sheet check confirms the three statements tie out.
Microsoft's reported FY2019–FY2024 income statement, balance sheet, cash flow and segment revenue.
The three statements are linked; ending cash ties to the balance sheet and the balance check equals zero.
Segment growth, margins, working capital, capex and D&A drive FY2025–FY2029.
An active-scenario selector applies different growth and margin cases across the model.
Unlevered FCF: NOPAT plus D&A, less capex and change in net working capital.
Cash flows discounted at WACC, with a Gordon-growth terminal value.
Present value of forecast cash flows plus present value of terminal value.
Enterprise value less total debt, plus cash and short-term investments.
Equity value divided by diluted shares outstanding.
Income Statement, Balance Sheet and Cash Flow Statement are linked together.
Five-year forecasts based on operating assumptions.
Bull, Base and Bear cases to evaluate different operating outcomes.
Discounted cash flow analysis using WACC and terminal growth.
Tests how valuation changes under different WACC and terminal-growth assumptions.
Values are as documented in the Excel model (Base scenario) and are not read live from the file.
| Base case · $ in millions | FY24A | FY25E | FY26E | FY27E | FY28E | FY29E |
|---|---|---|---|---|---|---|
| Revenue | 245,122 | 279,795 | 320,378 | 367,968 | 423,870 | 489,647 |
| Revenue growth % | 15.7% | 14.1% | 14.5% | 14.9% | 15.2% | 15.5% |
| EBITDA | 131,720 | 150,352 | 172,160 | 197,733 | 227,773 | 263,119 |
| EBITDA margin % | 53.7% | 53.7% | 53.7% | 53.7% | 53.7% | 53.7% |
| Free cash flow (CFO − capex) | 74,071 | 88,760 | 101,856 | 117,215 | 135,259 | 156,494 |
Free cash flow shown here is cash from operations less capital expenditures, as reported on the model's Executive Summary. The DCF uses unlevered free cash flow (see the DCF Valuation view). FY2025E–FY2029E are forecasts; FY2024A and earlier are Microsoft's reported results.
Type a question about this project, or tap one below. It answers only from this model's own figures and assumptions.
Each view is rebuilt from the values in the Excel file, sheet by sheet, so the numbers stay legible on any screen.
| $ in millions | FY19A | FY20A | FY21A | FY22A | FY23A | FY24A | FY25E | FY26E | FY27E | FY28E | FY29E |
|---|---|---|---|---|---|---|---|---|---|---|---|
| Revenue | 125,843 | 143,015 | 168,088 | 198,270 | 211,915 | 245,122 | 279,795 | 320,378 | 367,968 | 423,870 | 489,647 |
| Revenue growth % | — | 13.6% | 17.5% | 18.0% | 6.9% | 15.7% | 14.1% | 14.5% | 14.9% | 15.2% | 15.5% |
| Gross margin % | 65.9% | 67.8% | 68.9% | 68.4% | 68.9% | 69.8% | 69.8% | 69.8% | 69.8% | 69.8% | 69.8% |
| Operating margin % | 34.1% | 37.0% | 41.6% | 42.1% | 41.8% | 44.6% | 44.6% | 44.6% | 44.6% | 44.6% | 44.6% |
| EBITDA | 54,641 | 65,755 | 81,602 | 97,843 | 102,384 | 131,720 | 150,352 | 172,160 | 197,733 | 227,773 | 263,119 |
| EBITDA margin % | 43.4% | 46.0% | 48.5% | 49.3% | 48.3% | 53.7% | 53.7% | 53.7% | 53.7% | 53.7% | 53.7% |
| Net income | 39,240 | 44,281 | 61,271 | 72,738 | 72,361 | 88,136 | 100,793 | 115,608 | 132,981 | 153,388 | 177,400 |
| Free cash flow (CFO − capex) | 38,260 | 45,234 | 56,118 | 65,149 | 59,475 | 74,071 | 88,760 | 101,856 | 117,215 | 135,259 | 156,494 |
| Capital expenditures | 13,925 | 15,441 | 20,622 | 23,886 | 28,107 | 44,477 | 50,768 | 58,132 | 66,767 | 76,911 | 88,846 |
| Cash & short-term investments | 133,819 | 136,527 | 130,334 | 104,757 | 111,262 | 75,543 | 121,958 | 175,241 | 236,580 | 307,384 | 389,328 |
| Total debt | 72,178 | 63,327 | 58,146 | 49,781 | 47,237 | 51,630 | 51,630 | 51,630 | 51,630 | 51,630 | 51,630 |
Shaded columns are forecast years (Base case). The four charts mirror those on the workbook's Executive Summary sheet: revenue growth, EBITDA margin, free cash flow and revenue by segment.
| $ in millions | FY23A | FY24A | FY25E | FY26E | FY27E | FY28E | FY29E |
|---|---|---|---|---|---|---|---|
| Revenue by segment | |||||||
| Productivity & Business Processes | 69,274 | 77,728 | 87,808 | 99,195 | 112,059 | 126,591 | 143,007 |
| Intelligent Cloud | 87,907 | 105,362 | 127,058 | 153,222 | 184,773 | 222,821 | 268,704 |
| More Personal Computing | 54,734 | 62,032 | 64,929 | 67,962 | 71,136 | 74,459 | 77,936 |
| Total revenue | 211,915 | 245,122 | 279,795 | 320,378 | 367,968 | 423,870 | 489,647 |
| Revenue growth % | 6.9% | 15.7% | 14.1% | 14.5% | 14.9% | 15.2% | 15.5% |
| Gross profit | 146,052 | 171,008 | 195,198 | 223,510 | 256,711 | 295,711 | 341,600 |
| Gross margin % | 68.9% | 69.8% | 69.8% | 69.8% | 69.8% | 69.8% | 69.8% |
| Total operating expenses | 57,529 | 61,575 | 70,285 | 80,480 | 92,434 | 106,477 | 123,000 |
| Operating income (EBIT) | 88,523 | 109,433 | 124,913 | 143,031 | 164,277 | 189,234 | 218,600 |
| Operating margin % | 41.8% | 44.6% | 44.6% | 44.6% | 44.6% | 44.6% | 44.6% |
| Net income | 72,361 | 88,136 | 100,793 | 115,608 | 132,981 | 153,388 | 177,400 |
| Diluted EPS ($) | $9.68 | $11.80 | $13.49 | $15.48 | $17.80 | $20.54 | $23.75 |
| D&A | 13,861 | 22,287 | 25,440 | 29,129 | 33,456 | 38,539 | 44,520 |
| EBITDA | 102,384 | 131,720 | 150,352 | 172,160 | 197,733 | 227,773 | 263,119 |
| EBITDA margin % | 48.3% | 53.7% | 53.7% | 53.7% | 53.7% | 53.7% | 53.7% |
Forecast revenue is built by segment; costs are driven by the assumptions on the Scenario Analysis sheet.
| Statement links · $M | FY23A | FY24A | FY25E | FY26E | FY27E | FY28E | FY29E |
|---|---|---|---|---|---|---|---|
| Total assets (Balance Sheet) | 411,976 | 512,163 | 605,721 | 713,539 | 838,127 | 982,472 | 1,150,124 |
| Total liabilities & equity (Balance Sheet) | 411,976 | 512,163 | 605,721 | 713,539 | 838,127 | 982,472 | 1,150,124 |
| Balance sheet check (must = 0) | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
| Ending cash (Cash Flow Statement) | 34,704 | 18,315 | 56,635 | 100,444 | 150,672 | 208,424 | 275,011 |
| Cash & equivalents (Balance Sheet) | 34,704 | 18,315 | 56,635 | 100,444 | 150,672 | 208,424 | 275,011 |
The balance sheet check equals zero in every year, and ending cash on the Cash Flow Statement ties to cash on the Balance Sheet.
| Forecast driver (FY2025–FY2029, flat) | Bear | Base | Bull | Bear / Bull vs. Base |
|---|---|---|---|---|
| Productivity & Business Proc. revenue growth % | 9.0% | 13.0% | 17.0% | −4.0 / +4.0 pts |
| Intelligent Cloud revenue growth % | 14.6% | 20.6% | 26.6% | −6.0 / +6.0 pts |
| More Personal Computing revenue growth % | 0.7% | 4.7% | 8.7% | −4.0 / +4.0 pts |
| Total Cost of Revenue % of Revenue | 32.2% | 30.2% | 28.2% | +2.0 / −2.0 pts |
| R&D % of Revenue | 13.0% | 12.0% | 11.0% | +1.0 / −1.0 pts |
| S&M % of Revenue | 11.0% | 10.0% | 9.0% | +1.0 / −1.0 pts |
| G&A % of Revenue | 3.6% | 3.1% | 2.6% | +0.5 / −0.5 pts |
| Effective Tax Rate % | 20.2% | 18.2% | 16.2% | +2.0 / −2.0 pts |
| Capital Expenditures % of Revenue | 20.1% | 18.1% | 16.1% | +2.0 / −2.0 pts |
| Depreciation & Amortization % of Revenue | 10.1% | 9.1% | 8.1% | +1.0 / −1.0 pts |
| Stock-Based Compensation % of Revenue | 4.9% | 4.4% | 3.9% | +0.5 / −0.5 pts |
| Dividend Payout Ratio (% of Net Income) | 24.7% | 24.7% | 24.7% | held equal |
| Share Repurchases (% of Net Income) | 14.6% | 19.6% | 24.6% | −5.0 / +5.0 pts |
| Common Stock Issued (% of Revenue) | 0.8% | 0.8% | 0.8% | held equal |
| Other Income (Expense), $M | ($2,646M) | ($1,646M) | ($1,146M) | −$1,000M / +$500M |
Base-case revenue drivers are computed from the FY2021–FY2024 segment growth history; Bear and Bull apply the documented spreads shown at right. Changing the selector on the Scenario Analysis sheet flows a different case through every statement and the DCF.
| Unlevered FCF · $M | FY25E | FY26E | FY27E | FY28E | FY29E |
|---|---|---|---|---|---|
| Revenue | 279,795 | 320,378 | 367,968 | 423,870 | 489,647 |
| EBITDA | 150,352 | 172,160 | 197,733 | 227,773 | 263,119 |
| Operating income (EBIT) | 124,913 | 143,031 | 164,277 | 189,234 | 218,600 |
| Less: taxes on EBIT | (22,773) | (26,076) | (29,950) | (34,500) | (39,854) |
| NOPAT | 102,139 | 116,954 | 134,327 | 154,734 | 178,746 |
| Plus: D&A | 25,440 | 29,129 | 33,456 | 38,539 | 44,520 |
| Less: capital expenditures | (50,768) | (58,132) | (66,767) | (76,911) | (88,846) |
| Change in net working capital | 3,838 | 4,493 | 5,268 | 6,189 | 7,282 |
| Unlevered free cash flow | 80,649 | 92,444 | 106,284 | 122,551 | 141,702 |
| Discount factor | 0.908 | 0.824 | 0.748 | 0.678 | 0.616 |
| PV of unlevered FCF | 73,194 | 76,144 | 79,452 | 83,144 | 87,250 |
| Risk-free rate (10-yr U.S. Treasury) | 5.00% |
| Equity risk premium | 4.50% |
| Levered beta | 1.17 |
| Cost of equity (CAPM) | 10.27% |
| Pre-tax cost of debt | 5.50% |
| Effective tax rate | 18.23% |
| After-tax cost of debt | 4.50% |
| Weight of equity / debt | 98.6% / 1.4% |
| WACC | 10.18% |
| Terminal growth rate | 3.0% |
| Sum of PV of forecast FCF ($M) | 399,183 |
| PV of terminal value ($M) | 1,250,768 |
| Enterprise value ($M) | 1,649,951 |
| Less: total debt (FY2024A) | (51,630) |
| Plus: cash & short-term investments (FY2024A) | 75,543 |
| Equity value ($M) | 1,673,864 |
| Diluted shares (FY2024A, millions) | 7,469 |
| Implied value per share | $224.11 |
| Reference share price — cross-check only, not an input | $493.78 |
| Implied upside / (downside) vs. reference | (54.6%) |
The reference share price appears on the DCF sheet only as a cross-check and is not a model input. Net debt uses FY2024 actuals. Market inputs (risk-free rate, equity risk premium, beta, reference price) are the as-of values recorded in the workbook.
Terminal growth rate →
| WACC ↓ | 2.0% | 2.5% | 3.0% | 3.5% | 4.0% |
|---|---|---|---|---|---|
| 9.18% | $231.75 | $245.64 | $261.79 | $280.77 | $303.42 |
| 9.68% | $216.02 | $227.89 | $241.53 | $257.38 | $276.02 |
| 10.18% | $202.22 | $212.45 | $224.11 | $237.51 | $253.07 |
| 10.68% | $190.03 | $198.92 | $208.97 | $220.41 | $233.57 |
| 11.18% | $179.18 | $186.96 | $195.69 | $205.56 | $216.80 |
Across the grid, implied value per share ranges from $179.18 to $303.42. The highlighted cell is the Base case (WACC 10.18%, terminal growth 3.0%).
Reported FY2019–FY2024 income statement, balance sheet, cash flow and segment revenue anchor the model.
The statements feed one another through the PP&E schedule, retained earnings and cash, with a balance check that equals zero.
Segment growth, cost ratios, working capital, capex and D&A are applied to FY2025–FY2029.
Three documented sets of drivers test different growth and margin outcomes.
NOPAT plus D&A, less capital expenditures and the increase in net working capital.
Cash flows are discounted at WACC with a Gordon-growth terminal value, then bridged to equity value and value per share.
A five-by-five grid shows how implied value per share moves with both assumptions.
Adapted and extended from an existing three-statement financial modeling framework. Microsoft historical financial information was sourced from Microsoft's reported financial statements and SEC filings. The model structure, Microsoft-specific adaptation, forecasting assumptions, scenario analysis, DCF analysis, and portfolio presentation were developed for this independent project.
Microsoft historical data: Microsoft Investor Relations. Microsoft is a trademark of Microsoft Corporation; its use here identifies the company analyzed and does not imply affiliation, sponsorship or endorsement.
Open the workbook to see the formulas, switch between Bear, Base and Bull, and trace every forecast line back to its assumption.
Microsoft_Integrated_Financial_Model.xlsx · Excel workbook · 60 KB