Semiconductor DCF Modeler

This is an institutional-quality research and investment skill.

Expert-level semiconductor industry valuation for institutional investment research.

Workflow Overview

1. Identify company type - Equipment, memory, foundry, or fabless
2. Select methodology - Based on company type decision tree
3. Gather data - Yahoo Finance, SEC filings, SemiAnalysis (high-confidence), other industry sources
4. Build projections - Segment-specific revenue/margin models
5. Calculate diluted shares - TSM for options, If-Converted for converts
6. Apply valuation - Appropriate method with cycle/risk adjustments
7. Adjust FCF for SBC - Subtract stock-based compensation
8. Scenario analysis - Bull/base/bear with geopolitical scenarios
9. Output Excel workbook - Multi-tab model per company type template

Valuation Method Selection

Share Dilution Analysis (Required for All Models)

Dilution Bridge

Basic Shares Outstanding              (10-K/10-Q cover page)
+ RSUs                                (1-for-1 addition)
+ Net New Shares from Options (TSM)   (Treasury Stock Method)
+ Net New Shares from Warrants (TSM)  (Treasury Stock Method)
+ Shares from Convertibles            (If-Converted Method)
+ PSUs                                (Probability-weighted)
= FULLY DILUTED SHARES OUTSTANDING

Treasury Stock Method (TSM) - Options & Warrants

Only include IN-THE-MONEY options (Strike Price < Current Price)

Net New Shares = Options × (1 - Strike Price / Current Price)

Or equivalently:

Option Proceeds = Options × Strike Price
Shares Repurchased = Option Proceeds ÷ Current Price
Net New Shares = Options - Shares Repurchased

If-Converted Method - Convertible Securities

Only include IN-THE-MONEY convertibles (Current Price > Conversion Price)

Dilutive Shares = Principal Amount ÷ Conversion Price

Important: Remove in-the-money convertibles from debt in EV calculation.

SBC-Adjusted Free Cash Flow

Adjusted UFCF = Unlevered FCF - Stock-Based Compensation Expense

Semi SBC Benchmarks:

Per-Share Value Impact

Always show valuation at both basic and fully diluted shares:

Value per Basic Share = Equity Value ÷ Basic Shares
Value per Diluted Share = Equity Value ÷ Fully Diluted Shares
Dilution Impact = (Basic Value - Diluted Value) / Basic Value

Industry Expert Sources

High-Confidence Sources

Other Industry Experts

Industry Constants (Dr. Walden Rhines)

Reference Documents

Excel Model Structure by Company Type

Equipment Companies (12 Tabs)

1. CoverSheet - Summary, price target, cycle position
2. Tool_Demand - Customer capex → tool units
3. Revenue_Model - Units × ASP × Mix
4. Income_Statement - Full P&L with margins
5. Cash_Flow - FCF calculation
6. Balance_Sheet - Key items
7. Diluted_Shares - TSM/If-Converted dilution bridge, SBC-adjusted FCF
8. DCF_Valuation - Cycle-peak EPS × Multiple
9. Cycle_Analysis - Historical P/E, indicators
10. China_Scenarios - Revenue impact by scenario
11. Sensitivity - EPS vs Multiple tables
12. Assumptions - All inputs with sources

Memory Companies (13 Tabs)

1. CoverSheet - SOTP summary
2. DRAM_Model - Commodity DDR
3. HBM_Model - High Bandwidth Memory (AI)
4. NAND_Model - Flash storage
5. Revenue_Rollup - Consolidated
6. Income_Statement - With cost/bit analysis
7. Cash_Flow - FCF with heavy capex
8. Diluted_Shares - TSM/If-Converted dilution bridge, SBC-adjusted FCF
9. SOTP_Valuation - Segment values + net debt
10. PBV_CrossCheck - LT ROE / CoE
11. Cycle_Analysis - Spot prices, inventory
12. Sensitivity - ASP vs Volume tables
13. Assumptions - Learning curve position

Foundry Companies (12 Tabs)

1. CoverSheet - RIM-based target
2. Capacity_Model - By node
3. Revenue_Model - Capacity × Utilization × ASP
4. Margin_Model - GM by node
5. Income_Statement - Customer concentration
6. Cash_Flow - Massive capex intensity
7. Diluted_Shares - TSM/If-Converted dilution bridge, SBC-adjusted FCF
8. RIM_Valuation - Residual Income Model
9. Geopolitical_Risk - Taiwan scenarios
10. Customer_Analysis - Apple, Nvidia concentration
11. Sensitivity - CoE vs Growth tables
12. Assumptions - Geopolitical probabilities

Fabless Companies (11 Tabs)

1. CoverSheet - TAM-based target
2. TAM_Analysis - By end market
3. Revenue_Model - By segment
4. Income_Statement - SBC adjustment
5. Cash_Flow - Asset-light FCF
6. Diluted_Shares - TSM/If-Converted dilution bridge, SBC-adjusted FCF
7. DCF_Valuation - Three-stage for high growth
8. Moat_Assessment - Architecture, ecosystem
9. Scenarios - Bull/Base/Bear
10. Sensitivity - Growth vs Multiple
11. Assumptions - TAM sources

Diluted_Shares Tab Structure

Section A: Basic Shares

Basic Shares Outstanding    | XX.X M | Source: 10-Q Cover [Date]

Section B: RSUs (1-for-1)

Unvested RSUs Outstanding   | X.X M  | Source: 10-K Note X

Section C: Options (TSM)

In-the-Money Options        | X.X M  |
Weighted Avg Strike Price   | $XX.XX |
Current Share Price         | $XX.XX |
Option Proceeds             | $XX.X M|
Shares Repurchased          | X.X M  |
Net New Shares from Options | X.X M  |

Section D: Warrants (TSM)

In-the-Money Warrants       | X.X M  |
Net New Shares from Warrants| X.X M  |

Section E: Convertibles (If-Converted)

Convertible Principal       | $XX.X M|
Conversion Price            | $XX.XX |
In-the-Money? (Y/N)         | Yes    |
Dilutive Shares             | X.X M  |
Treatment in EV             | Equity |

Section F: PSUs

PSUs Outstanding            | X.X M  |
Probability of Achievement  | XX%    |
Expected Shares from PSUs   | X.X M  |

Section G: Dilution Summary

Basic Shares                | XX.X M |
+ RSUs                      | X.X M  |
+ Net Options (TSM)         | X.X M  |
+ Net Warrants (TSM)        | X.X M  |
+ Convertibles              | X.X M  |
+ PSUs                      | X.X M  |
= FULLY DILUTED SHARES      | XX.X M |
Dilution %                  | XX.X%  |

Section H: SBC-Adjusted FCF

($ millions)    | FY26E | FY27E | FY28E | FY29E | FY30E
------------------------------------------------------
Unlevered FCF   | $XXX  | $XXX  | $XXX  | $XXX  | $XXX
SBC Expense     | ($XX) | ($XX) | ($XX) | ($XX) | ($XX)
Adjusted UFCF   | $XXX  | $XXX  | $XXX  | $XXX  | $XXX

Section I: Per-Share Impact

                        | Basic  | Diluted | Impact
--------------------------------------------------
Equity Value ($M)       | $X,XXX | $X,XXX  |
÷ Shares               | XX.X M | XX.X M  | +XX.X%
= Value per Share      | $XX.XX | $XX.XX  | -XX.X%
Current Price          | $XX.XX |
Upside (Diluted)       | XX.X%  |

Key Parameters Quick Reference

Equipment Multiples (at Cycle Peak)

Memory Parameters

Foundry RIM Inputs

Fabless Forward P/E

Typical Dilution by Company Type

Geopolitical Risk Framework

China Scenario Probabilities

Taiwan Risk Discount

10-K Red Flags

Output Standards

Excel Formatting

Model Checklist

Before delivery:

• Cycle position documented with indicators
• Appropriate methodology for company type
• Diluted shares calculated using TSM/If-Converted
• SBC-adjusted FCF included
• Per-share value at basic AND diluted
• China/Taiwan risk explicitly modeled
• Learning curve position for ASP assumptions
• Sensitivity tables for key drivers
• Bull/Base/Bear with explicit drivers
• All assumptions sourced