Comprehensive Company Earnings Forecasting Model

1. Model Structure Overview

• Historical Data Analysis
• Assumption Input (Drivers)
• Calculations (Forecast)
• Outputs (Earnings, Ratios, Summary)

All hard-coded assumptions (growth, margins, rates) are isolated in a dedicated Input section or tab for transparency.

2. Key Forecast Assumptions

Driver	Input Example
Revenue Growth %	5.0%
COGS as % of Revenue	60.0%
SG&A as % of Revenue	20.0%
R&D as % of Revenue	5.0%
Depreciation as % Sales	5.4%
Tax Rate	25.0%
Interest Rate on Debt	5.0%
Shares Outstanding	10.0M
Dividend Payout Ratio	20.0%

3. Forecasting Formulas & Methodology

A. Revenue Forecast

Revenue_t = Revenue_t-1 × (1 + Growth Rate)

or using price-volume breakdown:

Revenue = Σ (Volume_i × Price_i)

B. COGS

COGS_t = Revenue_t × COGS%

C. Gross Profit

Gross Profit = Revenue - COGS

D. Operating Expenses

SG&A = Revenue × SG&A%

Depreciation = Revenue × Depreciation%

E. EBITDA and EBIT

EBITDA = Gross Profit - SG&A - R&D

EBIT = EBITDA - Depreciation

F. Interest and Taxes

Interest = Interest Rate × Average Debt

Taxes = (EBIT - Interest) × Tax Rate

G. Net Income

Net Income = EBIT - Interest - Taxes

H. EPS (Earnings Per Share)

EPS = Net Income / Shares Outstanding

4. Working Capital & Balance Sheet Forecasts

• Working capital = % of revenue/COGS or based on days (DSO, DPO, DIO)
• Cash balances tied to minimum cash or liquidity ratios
• Debt/equity adjusted for financing or retained earnings

5. Outputs and Key Ratios

• Forecast: Revenue, EBITDA, EBIT, Net Income, EPS
• Margins: Gross Margin, EBITDA Margin, Net Margin
• Returns: ROE, ROA, Debt/Equity

6. Example Forecast Table

Metric	2024	2025	2026
Revenue	$100.0M	$105.0M	$110.25M
COGS (60%)	$60.0M	$63.0M	$66.15M
Gross Profit	$40.0M	$42.0M	$44.10M
SG&A (20%)	$20.0M	$21.0M	$22.05M
R&D (5%)	$5.0M	$5.25M	$5.51M
Depreciation	$5.4M	$5.67M	$5.96M
EBIT	$9.6M	$10.08M	$10.58M
Interest	$1.0M	$1.0M	$1.0M
Taxes (25%)	$2.15M	$2.27M	$2.39M
Net Income	$6.45M	$6.81M	$7.19M
Shares	10M	10M	10M
EPS	$0.65	$0.68	$0.72

7. Modeling Best Practices

• Use dynamic Excel formulas
• Separate inputs, calculations, and outputs
• Audit with sensitivity tables and scenario analysis

8. Advanced Forecasting Methods

• Time Series: Moving Average, Trend, Regression
• Multi-Variable Regressions: For complex businesses
• DCF Analysis: For intrinsic value estimation
• Rolling Forecasts: Update with actuals periodically