Skill Tracer
Skills › Financial Services › initiating-coverage › task2-financial-modeling.md

task2-financial-modeling.md

665 lines · plugins/vertical-plugins/equity-research/skills/initiating-coverage/references/task2-financial-modeling.md at 6056506

Open on GitHub
1# Task 2: Financial Modeling - Detailed Workflow
2
3This document provides step-by-step instructions for executing Task 2 (Financial Modeling) of the initiating-coverage skill.
4

Task 2: Financial Modeling - Detailed Workflow

This document provides step-by-step instructions for executing Task 2 (Financial Modeling) of the initiating-coverage skill.

Raw lines 1 to 4
5## Task Overview
6
7**Purpose**: Extract historical financials and build comprehensive Excel financial model with projections and scenarios.
8
9**Prerequisites**: ⚠️ Verify before starting
10- **Required**: Access to company financial data
11 - For public companies: Latest 10-K and recent 10-Qs from SEC EDGAR
12 - For private companies: Financial statements or estimates from available sources
13 - OR: Pre-extracted historical financials provided by user
14- **Optional**: Company research (Task 1) for business context
16**Output**: Excel Financial Model (.xlsx) with 6 essential tabs:
171. Revenue Model
182. Income Statement
193. Cash Flow Statement
204. Balance Sheet
215. Scenarios
226. DCF Inputs
24---

Task Overview

Purpose: Extract historical financials and build comprehensive Excel financial model with projections and scenarios.

Prerequisites: ⚠️ Verify before starting

  • Required: Access to company financial data
  • For public companies: Latest 10-K and recent 10-Qs from SEC EDGAR
  • For private companies: Financial statements or estimates from available sources
  • OR: Pre-extracted historical financials provided by user
  • Optional: Company research (Task 1) for business context

Output: Excel Financial Model (.xlsx) with 6 essential tabs:

  1. Revenue Model
  2. Income Statement
  3. Cash Flow Statement
  4. Balance Sheet
  5. Scenarios
  6. DCF Inputs
Raw lines 5 to 25
26## Input Verification
28**BEFORE STARTING - CHECK:**
30**Option A: Extract financials directly (most common)**
31- [ ] Have access to 10-K filings (public company)?
32- [ ] OR have access to financial statements (private company)?
33- [ ] Ready to create Excel file for historical extraction?
35**Option B: User has pre-extracted financials**
36- [ ] Historical financials file provided? (.xlsx or other format)
37- [ ] Contains 3-5 years of income statement, cash flow, balance sheet?
38- [ ] Data is clean and ready to use?
40**Optional Context:**
41- [ ] Company research (Task 1) complete for business understanding?
43**IF VERIFICATION FAILS**: Stop and obtain access to financial statements (10-K or equivalent) before proceeding.
45---

Input Verification

BEFORE STARTING - CHECK:

Option A: Extract financials directly (most common)

  • [ ] Have access to 10-K filings (public company)?
  • [ ] OR have access to financial statements (private company)?
  • [ ] Ready to create Excel file for historical extraction?

Option B: User has pre-extracted financials

  • [ ] Historical financials file provided? (.xlsx or other format)
  • [ ] Contains 3-5 years of income statement, cash flow, balance sheet?
  • [ ] Data is clean and ready to use?

Optional Context:

  • [ ] Company research (Task 1) complete for business understanding?

IF VERIFICATION FAILS: Stop and obtain access to financial statements (10-K or equivalent) before proceeding.

Raw lines 26 to 46
47## Model Structure and Formatting

Model Structure and Formatting

Raw lines 47 to 48
49### Color Coding (Industry Standard)
50- **Blue text**: Hardcoded inputs (user can change)
51- **Black text**: Formulas and calculations
52- **Green text**: Links to other sheets
53- **Red text**: Errors or flags (should be resolved)

Color Coding (Industry Standard)

  • Blue text: Hardcoded inputs (user can change)
  • Black text: Formulas and calculations
  • Green text: Links to other sheets
  • Red text: Errors or flags (should be resolved)
Raw lines 49 to 54
55### Formatting Standards
56- Professional borders and shading
57- Clear section headers
58- Grouped rows for collapsibility
59- Named ranges for key inputs/outputs
60- No hardcoded numbers in formulas (except constants like 12 months)
61- Clear units ($ thousands, $ millions, etc.)

Formatting Standards

  • Professional borders and shading
  • Clear section headers
  • Grouped rows for collapsibility
  • Named ranges for key inputs/outputs
  • No hardcoded numbers in formulas (except constants like 12 months)
  • Clear units ($ thousands, $ millions, etc.)
Raw lines 55 to 62
63### Formula Best Practices
64- All numbers should flow from assumptions
65- Change an assumption → entire model updates
66- No circular references
67- Use named ranges for key cells
68- Keep formulas simple and auditable
69- Add comments for complex calculations
71---

Formula Best Practices

  • All numbers should flow from assumptions
  • Change an assumption → entire model updates
  • No circular references
  • Use named ranges for key cells
  • Keep formulas simple and auditable
  • Add comments for complex calculations
Raw lines 63 to 72
73## Step-by-Step Modeling Workflow

Step-by-Step Modeling Workflow

Raw lines 73 to 74
75### Step 1: Extract Historical Financials
77**If historical financials are already extracted, skip to Step 2.**
79**For Public Companies:**
811. **Download 10-K Filing**
82 - Go to SEC EDGAR (https://www.sec.gov/edgar/searchedgar/companysearch.html)
83 - Search for company name or ticker
84 - Download latest 10-K (annual report)
85 - Navigate to Item 8: Financial Statements and Supplementary Data
872. **Create Historical Financials Excel File**
88 - File name: `[Company]_Historical_Financials_[Date].xlsx`
89 - This file will be the foundation for the model
913. **Extract Income Statement (3-5 years)**
92 - Create Sheet 1: "Historical Income Statement"
93 - Extract ALL line items for 3-5 years:
94 - Revenue (total and by segment if disclosed)
95 - Cost of revenue / COGS
96 - Gross profit
97 - Operating expenses (R&D, Sales & Marketing, G&A broken out)
98 - EBITDA (calculate if not disclosed: EBIT + D&A)
99 - EBIT / Operating income
100 - Interest expense/income
101 - Other income/expense
102 - Pre-tax income
103 - Income tax and tax rate
104 - Net income
105 - EPS (basic and diluted)
106 - Shares outstanding (basic and diluted)
1084. **Extract Cash Flow Statement (3-5 years)**
109 - Create Sheet 2: "Historical Cash Flow"
110 - Extract ALL line items:
111 - Operating activities (starting from net income)
112 - Depreciation & amortization
113 - Stock-based compensation
114 - Changes in working capital (receivables, inventory, payables)
115 - Cash from operations
116 - Investing activities (CapEx, acquisitions)
117 - Financing activities (debt issuance/repayment, equity, dividends)
118 - Net change in cash
119 - Beginning and ending cash
1215. **Extract Balance Sheet (3-5 years)**
122 - Create Sheet 3: "Historical Balance Sheet"
123 - Extract ALL line items:
124 - Current assets (cash, receivables, inventory, other)
125 - Non-current assets (PP&E, intangibles, goodwill)
126 - Total assets
127 - Current liabilities (payables, accrued expenses, current debt)
128 - Non-current liabilities (long-term debt, deferred taxes)
129 - Total liabilities
130 - Shareholders' equity (common stock, retained earnings)
131 - Total liabilities + equity
1336. **Calculate Historical Metrics**
134 - Create Sheet 4: "Historical Metrics"
135 - Calculate from statements:
136 - Revenue growth % (YoY)
137 - Gross margin %
138 - EBITDA margin %
139 - Operating margin %
140 - Net margin %
141 - Free cash flow (CFO - CapEx)
142 - FCF margin %
143 - ROIC (approximate: NOPAT / Invested Capital)
144 - Debt/Equity ratio
145 - Current ratio (Current Assets / Current Liabilities)
1477. **Document Sources and Notes**
148 - Create Sheet 5: "Notes"
149 - Document:
150 - 10-K filing date and fiscal year end
151 - Any one-time items or adjustments noted
152 - Non-GAAP vs GAAP differences
153 - Segment breakdown (if revenue split by product/geography)
154 - Data quality notes and limitations
156**For Private Companies:**
1581. **Gather Available Data**
159 - Financial statements (if available)
160 - Press releases with revenue figures
161 - Funding announcements
162 - Industry estimates or comparable company data
1642. **Create Simplified Historical File**
165 - Estimated revenue (if available)
166 - Estimated margins (from comparables if needed)
167 - Key ratios and metrics
168 - Document all assumptions and sources
170**Verification:**
171- [ ] All 3 financial statements extracted (3-5 years)
172- [ ] Numbers reconcile across statements (net income ties)
173- [ ] Key metrics calculated correctly
174- [ ] Excel file saved and can be opened
175- [ ] Data sources documented (10-K dates, page numbers)
177**Foundation for projection model is now complete. Proceed to Step 2.**
178 - Capital expenditures
179 - Working capital items
180 - Debt and interest expense
181 - Share count (basic and diluted)
1833. **Organize historical data for entry**
184 - Prepare 3-5 years of actuals
185 - Will be entered directly into Income Statement, Cash Flow Statement, and Balance Sheet tabs
186 - Historical years in columns, projected years following
1884. **Calculate historical trends**
189 - Revenue CAGR
190 - Margin progression
191 - OpEx leverage
192 - Working capital patterns
193 - CapEx as % of revenue
194 - These trends will inform projection assumptions
196**Note**: Assumptions will be documented directly in each tab as blue text inputs, not in a separate tab.

Step 1: Extract Historical Financials

If historical financials are already extracted, skip to Step 2.

For Public Companies:

  1. Download 10-K Filing
  2. Go to SEC EDGAR (https://www.sec.gov/edgar/searchedgar/companysearch.html)
  3. Search for company name or ticker
  4. Download latest 10-K (annual report)
  5. Navigate to Item 8: Financial Statements and Supplementary Data
  1. Create Historical Financials Excel File
  2. File name: [Company]_Historical_Financials_[Date].xlsx
  3. This file will be the foundation for the model
  1. Extract Income Statement (3-5 years)
  2. Create Sheet 1: "Historical Income Statement"
  3. Extract ALL line items for 3-5 years:
  4. Revenue (total and by segment if disclosed)
  5. Cost of revenue / COGS
  6. Gross profit
  7. Operating expenses (R&D, Sales & Marketing, G&A broken out)
  8. EBITDA (calculate if not disclosed: EBIT + D&A)
  9. EBIT / Operating income
  10. Interest expense/income
  11. Other income/expense
  12. Pre-tax income
  13. Income tax and tax rate
  14. Net income
  15. EPS (basic and diluted)
  16. Shares outstanding (basic and diluted)
  1. Extract Cash Flow Statement (3-5 years)
  2. Create Sheet 2: "Historical Cash Flow"
  3. Extract ALL line items:
  4. Operating activities (starting from net income)
  5. Depreciation & amortization
  6. Stock-based compensation
  7. Changes in working capital (receivables, inventory, payables)
  8. Cash from operations
  9. Investing activities (CapEx, acquisitions)
  10. Financing activities (debt issuance/repayment, equity, dividends)
  11. Net change in cash
  12. Beginning and ending cash
  1. Extract Balance Sheet (3-5 years)
  2. Create Sheet 3: "Historical Balance Sheet"
  3. Extract ALL line items:
  4. Current assets (cash, receivables, inventory, other)
  5. Non-current assets (PP&E, intangibles, goodwill)
  6. Total assets
  7. Current liabilities (payables, accrued expenses, current debt)
  8. Non-current liabilities (long-term debt, deferred taxes)
  9. Total liabilities
  10. Shareholders' equity (common stock, retained earnings)
  11. Total liabilities + equity
  1. Calculate Historical Metrics
  2. Create Sheet 4: "Historical Metrics"
  3. Calculate from statements:
  4. Revenue growth % (YoY)
  5. Gross margin %
  6. EBITDA margin %
  7. Operating margin %
  8. Net margin %
  9. Free cash flow (CFO - CapEx)
  10. FCF margin %
  11. ROIC (approximate: NOPAT / Invested Capital)
  12. Debt/Equity ratio
  13. Current ratio (Current Assets / Current Liabilities)
  1. Document Sources and Notes
  2. Create Sheet 5: "Notes"
  3. Document:
  4. 10-K filing date and fiscal year end
  5. Any one-time items or adjustments noted
  6. Non-GAAP vs GAAP differences
  7. Segment breakdown (if revenue split by product/geography)
  8. Data quality notes and limitations

For Private Companies:

  1. Gather Available Data
  2. Financial statements (if available)
  3. Press releases with revenue figures
  4. Funding announcements
  5. Industry estimates or comparable company data
  1. Create Simplified Historical File
  2. Estimated revenue (if available)
  3. Estimated margins (from comparables if needed)
  4. Key ratios and metrics
  5. Document all assumptions and sources

Verification:

  • [ ] All 3 financial statements extracted (3-5 years)
  • [ ] Numbers reconcile across statements (net income ties)
  • [ ] Key metrics calculated correctly
  • [ ] Excel file saved and can be opened
  • [ ] Data sources documented (10-K dates, page numbers)

Foundation for projection model is now complete. Proceed to Step 2.

  • Capital expenditures
  • Working capital items
  • Debt and interest expense
  • Share count (basic and diluted)
  1. Organize historical data for entry
  2. Prepare 3-5 years of actuals
  3. Will be entered directly into Income Statement, Cash Flow Statement, and Balance Sheet tabs
  4. Historical years in columns, projected years following
  1. Calculate historical trends
  2. Revenue CAGR
  3. Margin progression
  4. OpEx leverage
  5. Working capital patterns
  6. CapEx as % of revenue
  7. These trends will inform projection assumptions

Note: Assumptions will be documented directly in each tab as blue text inputs, not in a separate tab.

Raw lines 75 to 197
198### Step 2: Model Revenue
200**CRITICAL: This is the most important and detailed part of the model.**
202#### A. Revenue by Product/Category (20-30 rows)
204Create detailed table:
205```
206 2021A 2022A 2023A 2024A 2025E 2026E 2027E 2028E 2029E
207Product Category A
208 Sub-product A1 XX XX XX XX XX XX XX XX XX
209 Sub-product A2 XX XX XX XX XX XX XX XX XX
210 Sub-product A3 XX XX XX XX XX XX XX XX XX
211 Category A Total XX XX XX XX XX XX XX XX XX
212 % of Total Rev X% X% X% X% X% X% X% X% X%
213 YoY Growth % - X% X% X% X% X% X% X% X%
215Product Category B
216 [Similar structure]
218[Continue for all product categories]
220Services Revenue XX XX XX XX XX XX XX XX XX
221Other Revenue XX XX XX XX XX XX XX XX XX
223TOTAL REVENUE XX XX XX XX XX XX XX XX XX
224Total Revenue Growth % - X% X% X% X% X% X% X% X%
225```
227**Key Requirements:**
228- Show absolute revenue ($M) for each category
229- Calculate % of total revenue for each category
230- Show YoY growth % for each category
231- Must have granular sub-categories (not just 3-5 top-level categories)
232- Show mix shift over time
233- Link all projections to Assumptions tab
235#### B. Revenue by Geography (15-20 rows)
237Create detailed table:
238```
239 2021A 2022A 2023A 2024A 2025E 2026E 2027E 2028E 2029E
240North America
241 United States XX XX XX XX XX XX XX XX XX
242 Canada XX XX XX XX XX XX XX XX XX
243 Mexico XX XX XX XX XX XX XX XX XX
244 NA Total XX XX XX XX XX XX XX XX XX
245 % of Total X% X% X% X% X% X% X% X% X%
246 YoY Growth % - X% X% X% X% X% X% X% X%
248Europe
249 UK XX XX XX XX XX XX XX XX XX
250 Germany XX XX XX XX XX XX XX XX XX
251 France XX XX XX XX XX XX XX XX XX
252 Other Europe XX XX XX XX XX XX XX XX XX
253 Europe Total XX XX XX XX XX XX XX XX XX
254 % of Total X% X% X% X% X% X% X% X% X%
255 YoY Growth % - X% X% X% X% X% X% X% X%
257Asia-Pacific
258 [Similar structure]
260Rest of World
261 [Similar structure]
263TOTAL REVENUE XX XX XX XX XX XX XX XX XX
264```
266**Verification:**
267- Revenue by product total = Revenue by geography total = Total revenue
268- All percentages sum to 100%
269- Growth rates calculated correctly
271#### C. Revenue by Channel (if applicable)
273```
274 2021A 2022A 2023A 2024A 2025E 2026E 2027E 2028E 2029E
275Direct Sales XX XX XX XX XX XX XX XX XX
276E-commerce/Online XX XX XX XX XX XX XX XX XX
277Wholesale/Partner XX XX XX XX XX XX XX XX XX
278Retail Stores
279 Company-owned stores XX XX XX XX XX XX XX XX XX
280 Store count XX XX XX XX XX XX XX XX XX
281 Sales per store XX XX XX XX XX XX XX XX XX
282Other Channels XX XX XX XX XX XX XX XX XX
284TOTAL REVENUE XX XX XX XX XX XX XX XX XX
285```

Step 2: Model Revenue

CRITICAL: This is the most important and detailed part of the model.

A. Revenue by Product/Category (20-30 rows)

Create detailed table:

                        2021A   2022A   2023A   2024A   2025E   2026E   2027E   2028E   2029E
Product Category A
  Sub-product A1        XX      XX      XX      XX      XX      XX      XX      XX      XX
  Sub-product A2        XX      XX      XX      XX      XX      XX      XX      XX      XX
  Sub-product A3        XX      XX      XX      XX      XX      XX      XX      XX      XX
  Category A Total      XX      XX      XX      XX      XX      XX      XX      XX      XX
  % of Total Rev        X%      X%      X%      X%      X%      X%      X%      X%      X%
  YoY Growth %          -       X%      X%      X%      X%      X%      X%      X%      X%

Product Category B
  [Similar structure]

[Continue for all product categories]

Services Revenue        XX      XX      XX      XX      XX      XX      XX      XX      XX
Other Revenue           XX      XX      XX      XX      XX      XX      XX      XX      XX

TOTAL REVENUE           XX      XX      XX      XX      XX      XX      XX      XX      XX
Total Revenue Growth %  -       X%      X%      X%      X%      X%      X%      X%      X%

Key Requirements:

  • Show absolute revenue ($M) for each category
  • Calculate % of total revenue for each category
  • Show YoY growth % for each category
  • Must have granular sub-categories (not just 3-5 top-level categories)
  • Show mix shift over time
  • Link all projections to Assumptions tab

B. Revenue by Geography (15-20 rows)

Create detailed table:

                        2021A   2022A   2023A   2024A   2025E   2026E   2027E   2028E   2029E
North America
  United States         XX      XX      XX      XX      XX      XX      XX      XX      XX
  Canada                XX      XX      XX      XX      XX      XX      XX      XX      XX
  Mexico                XX      XX      XX      XX      XX      XX      XX      XX      XX
  NA Total              XX      XX      XX      XX      XX      XX      XX      XX      XX
  % of Total            X%      X%      X%      X%      X%      X%      X%      X%      X%
  YoY Growth %          -       X%      X%      X%      X%      X%      X%      X%      X%

Europe
  UK                    XX      XX      XX      XX      XX      XX      XX      XX      XX
  Germany               XX      XX      XX      XX      XX      XX      XX      XX      XX
  France                XX      XX      XX      XX      XX      XX      XX      XX      XX
  Other Europe          XX      XX      XX      XX      XX      XX      XX      XX      XX
  Europe Total          XX      XX      XX      XX      XX      XX      XX      XX      XX
  % of Total            X%      X%      X%      X%      X%      X%      X%      X%      X%
  YoY Growth %          -       X%      X%      X%      X%      X%      X%      X%      X%

Asia-Pacific
  [Similar structure]

Rest of World
  [Similar structure]

TOTAL REVENUE           XX      XX      XX      XX      XX      XX      XX      XX      XX

Verification:

  • Revenue by product total = Revenue by geography total = Total revenue
  • All percentages sum to 100%
  • Growth rates calculated correctly

C. Revenue by Channel (if applicable)

                        2021A   2022A   2023A   2024A   2025E   2026E   2027E   2028E   2029E
Direct Sales            XX      XX      XX      XX      XX      XX      XX      XX      XX
E-commerce/Online       XX      XX      XX      XX      XX      XX      XX      XX      XX
Wholesale/Partner       XX      XX      XX      XX      XX      XX      XX      XX      XX
Retail Stores
  Company-owned stores  XX      XX      XX      XX      XX      XX      XX      XX      XX
  Store count           XX      XX      XX      XX      XX      XX      XX      XX      XX
  Sales per store       XX      XX      XX      XX      XX      XX      XX      XX      XX
Other Channels          XX      XX      XX      XX      XX      XX      XX      XX      XX

TOTAL REVENUE           XX      XX      XX      XX      XX      XX      XX      XX      XX
Raw lines 198 to 286
287### Step 3: Model Operating Expenses
289#### A. Cost of Revenue
2901. **Break down COGS components**
291 - Product costs (materials, manufacturing)
292 - Shipping and logistics
293 - Service delivery costs
294 - Other direct costs
2962. **Link to revenue**
297 - Calculate COGS as % of revenue
298 - Model gross margin by year
299 - Link to Assumptions tab
301#### B. R&D Expenses
302```
303Research & Development 2021A 2022A 2023A 2024A 2025E 2026E 2027E 2028E 2029E
304R&D Headcount XX XX XX XX XX XX XX XX XX
305R&D Comp per head XX XX XX XX XX XX XX XX XX
306R&D Personnel Costs XX XX XX XX XX XX XX XX XX
307R&D Other Costs XX XX XX XX XX XX XX XX XX
308Total R&D XX XX XX XX XX XX XX XX XX
309% of Revenue X% X% X% X% X% X% X% X% X%
310```
312#### C. Sales & Marketing Expenses
313```
314Sales & Marketing 2021A 2022A 2023A 2024A 2025E 2026E 2027E 2028E 2029E
315S&M Headcount XX XX XX XX XX XX XX XX XX
316S&M Comp per head XX XX XX XX XX XX XX XX XX
317S&M Personnel Costs XX XX XX XX XX XX XX XX XX
318Marketing Spend XX XX XX XX XX XX XX XX XX
319S&M Other Costs XX XX XX XX XX XX XX XX XX
320Total S&M XX XX XX XX XX XX XX XX XX
321% of Revenue X% X% X% X% X% X% X% X% X%
322```
324#### D. General & Administrative
325```
326G&A 2021A 2022A 2023A 2024A 2025E 2026E 2027E 2028E 2029E
327G&A Headcount XX XX XX XX XX XX XX XX XX
328G&A Comp per head XX XX XX XX XX XX XX XX XX
329G&A Personnel Costs XX XX XX XX XX XX XX XX XX
330G&A Other Costs XX XX XX XX XX XX XX XX XX
331Total G&A XX XX XX XX XX XX XX XX XX
332% of Revenue X% X% X% X% X% X% X% X% X%
333```
335#### E. Depreciation & Amortization
336- Link to CapEx schedule
337- Apply depreciation rates from Assumptions
338- Calculate annual D&A

Step 3: Model Operating Expenses

A. Cost of Revenue

  1. Break down COGS components
  2. Product costs (materials, manufacturing)
  3. Shipping and logistics
  4. Service delivery costs
  5. Other direct costs
  1. Link to revenue
  2. Calculate COGS as % of revenue
  3. Model gross margin by year
  4. Link to Assumptions tab

B. R&D Expenses

Research & Development  2021A   2022A   2023A   2024A   2025E   2026E   2027E   2028E   2029E
R&D Headcount           XX      XX      XX      XX      XX      XX      XX      XX      XX
R&D Comp per head       XX      XX      XX      XX      XX      XX      XX      XX      XX
R&D Personnel Costs     XX      XX      XX      XX      XX      XX      XX      XX      XX
R&D Other Costs         XX      XX      XX      XX      XX      XX      XX      XX      XX
Total R&D               XX      XX      XX      XX      XX      XX      XX      XX      XX
% of Revenue            X%      X%      X%      X%      X%      X%      X%      X%      X%

C. Sales & Marketing Expenses

Sales & Marketing       2021A   2022A   2023A   2024A   2025E   2026E   2027E   2028E   2029E
S&M Headcount           XX      XX      XX      XX      XX      XX      XX      XX      XX
S&M Comp per head       XX      XX      XX      XX      XX      XX      XX      XX      XX
S&M Personnel Costs     XX      XX      XX      XX      XX      XX      XX      XX      XX
Marketing Spend         XX      XX      XX      XX      XX      XX      XX      XX      XX
S&M Other Costs         XX      XX      XX      XX      XX      XX      XX      XX      XX
Total S&M               XX      XX      XX      XX      XX      XX      XX      XX      XX
% of Revenue            X%      X%      X%      X%      X%      X%      X%      X%      X%

D. General & Administrative

G&A                     2021A   2022A   2023A   2024A   2025E   2026E   2027E   2028E   2029E
G&A Headcount           XX      XX      XX      XX      XX      XX      XX      XX      XX
G&A Comp per head       XX      XX      XX      XX      XX      XX      XX      XX      XX
G&A Personnel Costs     XX      XX      XX      XX      XX      XX      XX      XX      XX
G&A Other Costs         XX      XX      XX      XX      XX      XX      XX      XX      XX
Total G&A               XX      XX      XX      XX      XX      XX      XX      XX      XX
% of Revenue            X%      X%      X%      X%      X%      X%      X%      X%      X%

E. Depreciation & Amortization

  • Link to CapEx schedule
  • Apply depreciation rates from Assumptions
  • Calculate annual D&A
Raw lines 287 to 339
340### Step 4: Build Income Statement
342**Create full P&L with 40-50 line items:**
344```
345INCOME STATEMENT 2021A 2022A 2023A 2024A 2025E 2026E 2027E 2028E 2029E
347REVENUE
348[Link to Revenue Model tab]
349Total Revenue XX XX XX XX XX XX XX XX XX
350 YoY Growth % - X% X% X% X% X% X% X% X%
352COST OF REVENUE
353[Link to COGS breakdown]
354Total COGS XX XX XX XX XX XX XX XX XX
356GROSS PROFIT XX XX XX XX XX XX XX XX XX
357 Gross Margin % X% X% X% X% X% X% X% X% X%
359OPERATING EXPENSES
360Total R&D XX XX XX XX XX XX XX XX XX
361 % of Revenue X% X% X% X% X% X% X% X% X%
362Total S&M XX XX XX XX XX XX XX XX XX
363 % of Revenue X% X% X% X% X% X% X% X% X%
364Total G&A XX XX XX XX XX XX XX XX XX
365 % of Revenue X% X% X% X% X% X% X% X% X%
366Depreciation & Amort. XX XX XX XX XX XX XX XX XX
368Total Operating Exp. XX XX XX XX XX XX XX XX XX
369 % of Revenue X% X% X% X% X% X% X% X% X%
371EBITDA XX XX XX XX XX XX XX XX XX
372 EBITDA Margin % X% X% X% X% X% X% X% X% X%
374EBIT XX XX XX XX XX XX XX XX XX
375 EBIT Margin % X% X% X% X% X% X% X% X% X%
377Interest expense (XX) (XX) (XX) (XX) (XX) (XX) (XX) (XX) (XX)
378Interest income XX XX XX XX XX XX XX XX XX
379Other income/(expense) XX XX XX XX XX XX XX XX XX
381Pre-tax income XX XX XX XX XX XX XX XX XX
383Income tax (XX) (XX) (XX) (XX) (XX) (XX) (XX) (XX) (XX)
384 Tax rate % X% X% X% X% X% X% X% X% X%
386NET INCOME XX XX XX XX XX XX XX XX XX
387 Net Margin % X% X% X% X% X% X% X% X% X%
389SHARES OUTSTANDING
390Basic shares (M) XX XX XX XX XX XX XX XX XX
391Diluted shares (M) XX XX XX XX XX XX XX XX XX
393EARNINGS PER SHARE
394Basic EPS $X.XX $X.XX $X.XX $X.XX $X.XX $X.XX $X.XX $X.XX $X.XX
395Diluted EPS $X.XX $X.XX $X.XX $X.XX $X.XX $X.XX $X.XX $X.XX $X.XX
396```

Step 4: Build Income Statement

Create full P&L with 40-50 line items:

INCOME STATEMENT        2021A   2022A   2023A   2024A   2025E   2026E   2027E   2028E   2029E

REVENUE
[Link to Revenue Model tab]
Total Revenue           XX      XX      XX      XX      XX      XX      XX      XX      XX
  YoY Growth %          -       X%      X%      X%      X%      X%      X%      X%      X%

COST OF REVENUE
[Link to COGS breakdown]
Total COGS              XX      XX      XX      XX      XX      XX      XX      XX      XX

GROSS PROFIT            XX      XX      XX      XX      XX      XX      XX      XX      XX
  Gross Margin %        X%      X%      X%      X%      X%      X%      X%      X%      X%

OPERATING EXPENSES
Total R&D               XX      XX      XX      XX      XX      XX      XX      XX      XX
  % of Revenue          X%      X%      X%      X%      X%      X%      X%      X%      X%
Total S&M               XX      XX      XX      XX      XX      XX      XX      XX      XX
  % of Revenue          X%      X%      X%      X%      X%      X%      X%      X%      X%
Total G&A               XX      XX      XX      XX      XX      XX      XX      XX      XX
  % of Revenue          X%      X%      X%      X%      X%      X%      X%      X%      X%
Depreciation & Amort.   XX      XX      XX      XX      XX      XX      XX      XX      XX

Total Operating Exp.    XX      XX      XX      XX      XX      XX      XX      XX      XX
  % of Revenue          X%      X%      X%      X%      X%      X%      X%      X%      X%

EBITDA                  XX      XX      XX      XX      XX      XX      XX      XX      XX
  EBITDA Margin %       X%      X%      X%      X%      X%      X%      X%      X%      X%

EBIT                    XX      XX      XX      XX      XX      XX      XX      XX      XX
  EBIT Margin %         X%      X%      X%      X%      X%      X%      X%      X%      X%

Interest expense        (XX)    (XX)    (XX)    (XX)    (XX)    (XX)    (XX)    (XX)    (XX)
Interest income         XX      XX      XX      XX      XX      XX      XX      XX      XX
Other income/(expense)  XX      XX      XX      XX      XX      XX      XX      XX      XX

Pre-tax income          XX      XX      XX      XX      XX      XX      XX      XX      XX

Income tax              (XX)    (XX)    (XX)    (XX)    (XX)    (XX)    (XX)    (XX)    (XX)
  Tax rate %            X%      X%      X%      X%      X%      X%      X%      X%      X%

NET INCOME              XX      XX      XX      XX      XX      XX      XX      XX      XX
  Net Margin %          X%      X%      X%      X%      X%      X%      X%      X%      X%

SHARES OUTSTANDING
Basic shares (M)        XX      XX      XX      XX      XX      XX      XX      XX      XX
Diluted shares (M)      XX      XX      XX      XX      XX      XX      XX      XX      XX

EARNINGS PER SHARE
Basic EPS               $X.XX   $X.XX   $X.XX   $X.XX   $X.XX   $X.XX   $X.XX   $X.XX   $X.XX
Diluted EPS             $X.XX   $X.XX   $X.XX   $X.XX   $X.XX   $X.XX   $X.XX   $X.XX   $X.XX
Raw lines 340 to 397
398### Step 5: Build Cash Flow Statement
400```
401CASH FLOW STATEMENT 2021A 2022A 2023A 2024A 2025E 2026E 2027E 2028E 2029E
403OPERATING ACTIVITIES
404Net Income XX XX XX XX XX XX XX XX XX
405Adjustments:
406 Depreciation & Amort. XX XX XX XX XX XX XX XX XX
407 Stock-based comp XX XX XX XX XX XX XX XX XX
408 Other non-cash XX XX XX XX XX XX XX XX XX
410Changes in WC:
411 Accounts Receivable (XX) (XX) (XX) (XX) (XX) (XX) (XX) (XX) (XX)
412 Inventory (XX) (XX) (XX) (XX) (XX) (XX) (XX) (XX) (XX)
413 Accounts Payable XX XX XX XX XX XX XX XX XX
414 Other working capital (XX) (XX) (XX) (XX) (XX) (XX) (XX) (XX) (XX)
416Cash from Operations XX XX XX XX XX XX XX XX XX
418INVESTING ACTIVITIES
419Capital Expenditures (XX) (XX) (XX) (XX) (XX) (XX) (XX) (XX) (XX)
420Acquisitions (XX) (XX) (XX) (XX) (XX) (XX) (XX) (XX) (XX)
421Other investing XX XX XX XX XX XX XX XX XX
423Cash from Investing (XX) (XX) (XX) (XX) (XX) (XX) (XX) (XX) (XX)
425FREE CASH FLOW XX XX XX XX XX XX XX XX XX
426 FCF Margin % X% X% X% X% X% X% X% X% X%
428FINANCING ACTIVITIES
429Debt issuance XX XX XX XX XX XX XX XX XX
430Debt repayment (XX) (XX) (XX) (XX) (XX) (XX) (XX) (XX) (XX)
431Equity issuance XX XX XX XX XX XX XX XX XX
432Dividends paid (XX) (XX) (XX) (XX) (XX) (XX) (XX) (XX) (XX)
433Other financing XX XX XX XX XX XX XX XX XX
435Cash from Financing XX XX XX XX XX XX XX XX XX
437NET CHANGE IN CASH XX XX XX XX XX XX XX XX XX
439Beginning Cash XX XX XX XX XX XX XX XX XX
440Ending Cash XX XX XX XX XX XX XX XX XX
441```

Step 5: Build Cash Flow Statement

CASH FLOW STATEMENT     2021A   2022A   2023A   2024A   2025E   2026E   2027E   2028E   2029E

OPERATING ACTIVITIES
Net Income              XX      XX      XX      XX      XX      XX      XX      XX      XX
Adjustments:
  Depreciation & Amort. XX      XX      XX      XX      XX      XX      XX      XX      XX
  Stock-based comp      XX      XX      XX      XX      XX      XX      XX      XX      XX
  Other non-cash        XX      XX      XX      XX      XX      XX      XX      XX      XX

Changes in WC:
  Accounts Receivable   (XX)    (XX)    (XX)    (XX)    (XX)    (XX)    (XX)    (XX)    (XX)
  Inventory             (XX)    (XX)    (XX)    (XX)    (XX)    (XX)    (XX)    (XX)    (XX)
  Accounts Payable      XX      XX      XX      XX      XX      XX      XX      XX      XX
  Other working capital (XX)    (XX)    (XX)    (XX)    (XX)    (XX)    (XX)    (XX)    (XX)

Cash from Operations    XX      XX      XX      XX      XX      XX      XX      XX      XX

INVESTING ACTIVITIES
Capital Expenditures    (XX)    (XX)    (XX)    (XX)    (XX)    (XX)    (XX)    (XX)    (XX)
Acquisitions            (XX)    (XX)    (XX)    (XX)    (XX)    (XX)    (XX)    (XX)    (XX)
Other investing         XX      XX      XX      XX      XX      XX      XX      XX      XX

Cash from Investing     (XX)    (XX)    (XX)    (XX)    (XX)    (XX)    (XX)    (XX)    (XX)

FREE CASH FLOW          XX      XX      XX      XX      XX      XX      XX      XX      XX
  FCF Margin %          X%      X%      X%      X%      X%      X%      X%      X%      X%

FINANCING ACTIVITIES
Debt issuance           XX      XX      XX      XX      XX      XX      XX      XX      XX
Debt repayment          (XX)    (XX)    (XX)    (XX)    (XX)    (XX)    (XX)    (XX)    (XX)
Equity issuance         XX      XX      XX      XX      XX      XX      XX      XX      XX
Dividends paid          (XX)    (XX)    (XX)    (XX)    (XX)    (XX)    (XX)    (XX)    (XX)
Other financing         XX      XX      XX      XX      XX      XX      XX      XX      XX

Cash from Financing     XX      XX      XX      XX      XX      XX      XX      XX      XX

NET CHANGE IN CASH      XX      XX      XX      XX      XX      XX      XX      XX      XX

Beginning Cash          XX      XX      XX      XX      XX      XX      XX      XX      XX
Ending Cash             XX      XX      XX      XX      XX      XX      XX      XX      XX
Raw lines 398 to 442
443### Step 6: Build Balance Sheet
445Create full balance sheet with 35-45 line items:
447```
448BALANCE SHEET 2021A 2022A 2023A 2024A 2025E 2026E 2027E 2028E 2029E
450ASSETS
451Current Assets:
452 Cash & Equivalents XX XX XX XX XX XX XX XX XX
453 Accounts Receivable XX XX XX XX XX XX XX XX XX
454 Inventory XX XX XX XX XX XX XX XX XX
455 Prepaid expenses XX XX XX XX XX XX XX XX XX
456 Other current assets XX XX XX XX XX XX XX XX XX
457Total Current Assets XX XX XX XX XX XX XX XX XX
459Non-Current Assets:
460 PP&E, gross XX XX XX XX XX XX XX XX XX
461 Accumulated Depr. (XX) (XX) (XX) (XX) (XX) (XX) (XX) (XX) (XX)
462 PP&E, net XX XX XX XX XX XX XX XX XX
463 Intangible assets XX XX XX XX XX XX XX XX XX
464 Goodwill XX XX XX XX XX XX XX XX XX
465 Other non-current XX XX XX XX XX XX XX XX XX
466Total Non-Current XX XX XX XX XX XX XX XX XX
468TOTAL ASSETS XX XX XX XX XX XX XX XX XX
470LIABILITIES
471Current Liabilities:
472 Accounts Payable XX XX XX XX XX XX XX XX XX
473 Accrued expenses XX XX XX XX XX XX XX XX XX
474 Deferred revenue XX XX XX XX XX XX XX XX XX
475 Current debt XX XX XX XX XX XX XX XX XX
476 Other current liab. XX XX XX XX XX XX XX XX XX
477Total Current Liab. XX XX XX XX XX XX XX XX XX
479Non-Current Liabilities:
480 Long-term debt XX XX XX XX XX XX XX XX XX
481 Deferred taxes XX XX XX XX XX XX XX XX XX
482 Other non-current XX XX XX XX XX XX XX XX XX
483Total Non-Current Liab. XX XX XX XX XX XX XX XX XX
485TOTAL LIABILITIES XX XX XX XX XX XX XX XX XX
487EQUITY
488 Common stock XX XX XX XX XX XX XX XX XX
489 Additional paid-in XX XX XX XX XX XX XX XX XX
490 Retained earnings XX XX XX XX XX XX XX XX XX
491 Treasury stock (XX) (XX) (XX) (XX) (XX) (XX) (XX) (XX) (XX)
492 Other equity XX XX XX XX XX XX XX XX XX
493TOTAL EQUITY XX XX XX XX XX XX XX XX XX
495TOTAL LIAB + EQUITY XX XX XX XX XX XX XX XX XX
497BALANCE CHECK OK OK OK OK OK OK OK OK OK
498```
500**Balance Check Formula:**
501- Total Assets must equal Total Liabilities + Equity for each year
502- Flag any imbalances in red

Step 6: Build Balance Sheet

Create full balance sheet with 35-45 line items:

BALANCE SHEET           2021A   2022A   2023A   2024A   2025E   2026E   2027E   2028E   2029E

ASSETS
Current Assets:
  Cash & Equivalents    XX      XX      XX      XX      XX      XX      XX      XX      XX
  Accounts Receivable   XX      XX      XX      XX      XX      XX      XX      XX      XX
  Inventory             XX      XX      XX      XX      XX      XX      XX      XX      XX
  Prepaid expenses      XX      XX      XX      XX      XX      XX      XX      XX      XX
  Other current assets  XX      XX      XX      XX      XX      XX      XX      XX      XX
Total Current Assets    XX      XX      XX      XX      XX      XX      XX      XX      XX

Non-Current Assets:
  PP&E, gross           XX      XX      XX      XX      XX      XX      XX      XX      XX
  Accumulated Depr.     (XX)    (XX)    (XX)    (XX)    (XX)    (XX)    (XX)    (XX)    (XX)
  PP&E, net             XX      XX      XX      XX      XX      XX      XX      XX      XX
  Intangible assets     XX      XX      XX      XX      XX      XX      XX      XX      XX
  Goodwill              XX      XX      XX      XX      XX      XX      XX      XX      XX
  Other non-current     XX      XX      XX      XX      XX      XX      XX      XX      XX
Total Non-Current       XX      XX      XX      XX      XX      XX      XX      XX      XX

TOTAL ASSETS            XX      XX      XX      XX      XX      XX      XX      XX      XX

LIABILITIES
Current Liabilities:
  Accounts Payable      XX      XX      XX      XX      XX      XX      XX      XX      XX
  Accrued expenses      XX      XX      XX      XX      XX      XX      XX      XX      XX
  Deferred revenue      XX      XX      XX      XX      XX      XX      XX      XX      XX
  Current debt          XX      XX      XX      XX      XX      XX      XX      XX      XX
  Other current liab.   XX      XX      XX      XX      XX      XX      XX      XX      XX
Total Current Liab.     XX      XX      XX      XX      XX      XX      XX      XX      XX

Non-Current Liabilities:
  Long-term debt        XX      XX      XX      XX      XX      XX      XX      XX      XX
  Deferred taxes        XX      XX      XX      XX      XX      XX      XX      XX      XX
  Other non-current     XX      XX      XX      XX      XX      XX      XX      XX      XX
Total Non-Current Liab. XX      XX      XX      XX      XX      XX      XX      XX      XX

TOTAL LIABILITIES       XX      XX      XX      XX      XX      XX      XX      XX      XX

EQUITY
  Common stock          XX      XX      XX      XX      XX      XX      XX      XX      XX
  Additional paid-in    XX      XX      XX      XX      XX      XX      XX      XX      XX
  Retained earnings     XX      XX      XX      XX      XX      XX      XX      XX      XX
  Treasury stock        (XX)    (XX)    (XX)    (XX)    (XX)    (XX)    (XX)    (XX)    (XX)
  Other equity          XX      XX      XX      XX      XX      XX      XX      XX      XX
TOTAL EQUITY            XX      XX      XX      XX      XX      XX      XX      XX      XX

TOTAL LIAB + EQUITY     XX      XX      XX      XX      XX      XX      XX      XX      XX

BALANCE CHECK           OK      OK      OK      OK      OK      OK      OK      OK      OK

Balance Check Formula:

  • Total Assets must equal Total Liabilities + Equity for each year
  • Flag any imbalances in red
Raw lines 443 to 503
504### Step 7: Build DCF Inputs Tab
506Prepare inputs for valuation (Task 3):
508```
509DCF INPUTS 2025E 2026E 2027E 2028E 2029E
511EBIT XX XX XX XX XX
512Tax Rate X% X% X% X% X%
513NOPAT XX XX XX XX XX
515Add: D&A XX XX XX XX XX
516Less: CapEx (XX) (XX) (XX) (XX) (XX)
517Less: Chg in NWC (XX) (XX) (XX) (XX) (XX)
519UNLEVERED FCF XX XX XX XX XX
521Terminal Year Metrics:
522 2029E Revenue $X,XXX
523 2029E EBITDA $XXX
524 2029E EBIT $XXX
525 2029E Unlevered FCF $XXX
526```

Step 7: Build DCF Inputs Tab

Prepare inputs for valuation (Task 3):

DCF INPUTS              2025E   2026E   2027E   2028E   2029E

EBIT                    XX      XX      XX      XX      XX
Tax Rate                X%      X%      X%      X%      X%
NOPAT                   XX      XX      XX      XX      XX

Add: D&A                XX      XX      XX      XX      XX
Less: CapEx             (XX)    (XX)    (XX)    (XX)    (XX)
Less: Chg in NWC        (XX)    (XX)    (XX)    (XX)    (XX)

UNLEVERED FCF           XX      XX      XX      XX      XX

Terminal Year Metrics:
  2029E Revenue         $X,XXX
  2029E EBITDA          $XXX
  2029E EBIT            $XXX
  2029E Unlevered FCF   $XXX
Raw lines 504 to 527
528### Step 8: Build Scenarios Tab
530Create three scenarios with different assumptions:
532#### Scenario Assumptions Table
533```
534Assumption Bull Base Bear
535Revenue CAGR (2025-2029) XX% XX% XX%
536Gross Margin 2029E XX% XX% XX%
537EBITDA Margin 2029E XX% XX% XX%
538CapEx as % of Revenue X% X% X%
539[Add other key assumptions]
540```
542#### Scenario Output Table
543```
544Metric Bull Base Bear
5452029E Revenue ($M) $X,XXX $X,XXX $X,XXX
5462029E EBITDA ($M) $XXX $XXX $XXX
5472029E EBITDA Margin XX% XX% XX%
5482029E Net Income ($M) $XXX $XXX $XXX
5492029E EPS $X.XX $X.XX $X.XX
5502029E FCF ($M) $XXX $XXX $XXX
5512029E FCF Margin XX% XX% XX%
553Cumulative FCF 2025-2029 ($M) $XXX $XXX $XXX
554```
556**Document scenario rationale:**
557- Bull case: [Describe optimistic but achievable assumptions]
558- Base case: [Describe most likely scenario]
559- Bear case: [Describe downside risks and triggers]

Step 8: Build Scenarios Tab

Create three scenarios with different assumptions:

Scenario Assumptions Table

Assumption                      Bull        Base        Bear
Revenue CAGR (2025-2029)        XX%         XX%         XX%
Gross Margin 2029E              XX%         XX%         XX%
EBITDA Margin 2029E             XX%         XX%         XX%
CapEx as % of Revenue           X%          X%          X%
[Add other key assumptions]

Scenario Output Table

Metric                          Bull        Base        Bear
2029E Revenue ($M)              $X,XXX      $X,XXX      $X,XXX
2029E EBITDA ($M)               $XXX        $XXX        $XXX
2029E EBITDA Margin             XX%         XX%         XX%
2029E Net Income ($M)           $XXX        $XXX        $XXX
2029E EPS                       $X.XX       $X.XX       $X.XX
2029E FCF ($M)                  $XXX        $XXX        $XXX
2029E FCF Margin                XX%         XX%         XX%

Cumulative FCF 2025-2029 ($M)   $XXX        $XXX        $XXX

Document scenario rationale:

  • Bull case: [Describe optimistic but achievable assumptions]
  • Base case: [Describe most likely scenario]
  • Bear case: [Describe downside risks and triggers]
Raw lines 528 to 560
561### Step 9: Quality Check
563**Verify model integrity:**
5641. [ ] Test all formulas (spot check calculations)
5652. [ ] Change assumption → verify model updates correctly
5663. [ ] Test scenario switching
5674. [ ] Verify color coding (blue/black/green)
5685. [ ] Check balance sheet balances for all years
5696. [ ] Verify no circular references (Excel will flag)
5707. [ ] Check for hardcoded numbers in projections
5718. [ ] Verify all cross-sheet links work
5729. [ ] Test that revenue totals tie across all tabs
57310. [ ] Review formatting and presentation
575---

Step 9: Quality Check

Verify model integrity:

  1. [ ] Test all formulas (spot check calculations)
  2. [ ] Change assumption → verify model updates correctly
  3. [ ] Test scenario switching
  4. [ ] Verify color coding (blue/black/green)
  5. [ ] Check balance sheet balances for all years
  6. [ ] Verify no circular references (Excel will flag)
  7. [ ] Check for hardcoded numbers in projections
  8. [ ] Verify all cross-sheet links work
  9. [ ] Test that revenue totals tie across all tabs
  10. [ ] Review formatting and presentation
Raw lines 561 to 576
577## Quality Standards

Quality Standards

Raw lines 577 to 578
579### Model Integrity
580- All formulas link properly across sheets
581- No hardcoded numbers in projections (except in Assumptions tab)
582- No circular references
583- Balance sheet balances for all years
584- Scenario switching works properly

Model Integrity

  • All formulas link properly across sheets
  • No hardcoded numbers in projections (except in Assumptions tab)
  • No circular references
  • Balance sheet balances for all years
  • Scenario switching works properly
Raw lines 579 to 585
586### Completeness
587- All 6 essential tabs: Revenue Model, Income Statement, Cash Flow Statement, Balance Sheet, Scenarios, DCF Inputs
588- 40-50 line items in Income Statement
589- 20-30 rows in Revenue Model (product breakdown)
590- 15-20 rows in Revenue Model (geography breakdown)
591- Full cash flow and balance sheet with all line items
592- Bull/Base/Bear scenarios complete

Completeness

  • All 6 essential tabs: Revenue Model, Income Statement, Cash Flow Statement, Balance Sheet, Scenarios, DCF Inputs
  • 40-50 line items in Income Statement
  • 20-30 rows in Revenue Model (product breakdown)
  • 15-20 rows in Revenue Model (geography breakdown)
  • Full cash flow and balance sheet with all line items
  • Bull/Base/Bear scenarios complete
Raw lines 586 to 593
594### Professional Formatting
595- Consistent color coding (blue/black/green)
596- Clear headers and labels
597- Proper borders and shading
598- Named ranges for key cells
599- Grouped rows for collapsibility
600- Units clearly labeled ($ thousands vs. $ millions)

Professional Formatting

  • Consistent color coding (blue/black/green)
  • Clear headers and labels
  • Proper borders and shading
  • Named ranges for key cells
  • Grouped rows for collapsibility
  • Units clearly labeled ($ thousands vs. $ millions)
Raw lines 594 to 601
602### Documentation
603- Assumptions documented with rationale (blue text cells with comments)
604- Data sources noted in cell comments or notes section within tabs
605- Complex calculations explained with comments
606- Methodology described
608---

Documentation

  • Assumptions documented with rationale (blue text cells with comments)
  • Data sources noted in cell comments or notes section within tabs
  • Complex calculations explained with comments
  • Methodology described
Raw lines 602 to 609
610## File Naming Convention
612Save the financial model as:
613`[Company]_Financial_Model_[Date].xlsx`
615Example: `Tesla_Financial_Model_2024-10-27.xlsx`
617---

File Naming Convention

Save the financial model as: [Company]_Financial_Model_[Date].xlsx

Example: Tesla_Financial_Model_2024-10-27.xlsx

Raw lines 610 to 618
619## Success Criteria
621A successful financial model should:
6221. Have all 6 essential tabs (Revenue Model, Income Statement, Cash Flow Statement, Balance Sheet, Scenarios, DCF Inputs)
6232. Be fully dynamic (change assumption → model updates)
6243. Have no hardcoded numbers in projections
6254. Include detailed revenue breakdowns (20-30 rows by product, 15-20 rows by geography)
6265. Contain 40-50 line items in Income Statement
6276. Include Bull/Base/Bear scenarios
6287. Be professionally formatted with color coding
6298. Balance properly (balance sheet, cash flows)
6309. Be auditable and easy to follow
63110. Support valuation analysis with proper FCF calculations
633---

Success Criteria

A successful financial model should:

  1. Have all 6 essential tabs (Revenue Model, Income Statement, Cash Flow Statement, Balance Sheet, Scenarios, DCF Inputs)
  2. Be fully dynamic (change assumption → model updates)
  3. Have no hardcoded numbers in projections
  4. Include detailed revenue breakdowns (20-30 rows by product, 15-20 rows by geography)
  5. Contain 40-50 line items in Income Statement
  6. Include Bull/Base/Bear scenarios
  7. Be professionally formatted with color coding
  8. Balance properly (balance sheet, cash flows)
  9. Be auditable and easy to follow
  10. Support valuation analysis with proper FCF calculations
Raw lines 619 to 634
635## Common Model Types - Special Considerations

Common Model Types - Special Considerations

Raw lines 635 to 636
637### High-Growth Tech/SaaS
638- Focus on ARR growth and net retention
639- Model by product line and geography
640- Heavy R&D and S&M spend
641- Path to profitability timeline
642- Unit economics (LTV/CAC)

High-Growth Tech/SaaS

  • Focus on ARR growth and net retention
  • Model by product line and geography
  • Heavy R&D and S&M spend
  • Path to profitability timeline
  • Unit economics (LTV/CAC)
Raw lines 637 to 643
644### E-commerce/Retail
645- Revenue by product category and channel
646- Store count and comp store growth (if applicable)
647- Inventory turns and working capital
648- Fulfillment costs
649- Customer acquisition

E-commerce/Retail

  • Revenue by product category and channel
  • Store count and comp store growth (if applicable)
  • Inventory turns and working capital
  • Fulfillment costs
  • Customer acquisition
Raw lines 644 to 650
651### Manufacturing/Industrial
652- Production capacity utilization
653- Raw material costs and pricing
654- Gross margin bridge (volume/price/mix/cost)
655- CapEx heavy models
656- Working capital cycles
658---

Manufacturing/Industrial

  • Production capacity utilization
  • Raw material costs and pricing
  • Gross margin bridge (volume/price/mix/cost)
  • CapEx heavy models
  • Working capital cycles
Raw lines 651 to 659
660## Next Steps
662After completing Task 2, the financial model will be used for:
663- **Task 3 (Valuation)**: DCF inputs, projected financials
664- **Task 4 (Charts)**: Data for revenue trends, margin charts, scenario comparisons
665- **Task 5 (Report Assembly)**: Financial data for report tables and analysis

Next Steps

After completing Task 2, the financial model will be used for:

  • Task 3 (Valuation): DCF inputs, projected financials
  • Task 4 (Charts): Data for revenue trends, margin charts, scenario comparisons
  • Task 5 (Report Assembly): Financial data for report tables and analysis
Raw lines 660 to 665