📈 1. Revenue and Expense Forecasting Techniques

🔹 A. Revenue Forecasting Techniques

Method Description
Top-Down Approach Start with market size → estimate market share → derive revenue
Bottom-Up Approach Start with units sold × price → build from operational metrics
Historical Growth Rate Use past revenue growth rates to estimate future values
Driver-Based Forecasting Link revenue to key drivers (e.g., number of stores, customer base, conversion rate)

🔸 Example:

If a company had:

  • 10 stores

  • Avg. sales per store: $500,000
    Forecast for 1-year growth to 12 stores:

Revenue=12×500,000=6,000,000text{Revenue} = 12 times 500,000 = 6,000,000

🔹 B. Expense Forecasting Techniques

Expense Type Forecasting Method
COGS As a % of revenue (e.g., COGS = 60% of sales)
Operating Expenses (SG&A) Fixed + variable model, or as % of sales
Depreciation Based on asset schedule or straight-line depreciation
Interest Expense Based on average debt and projected interest rates
Taxes Apply effective tax rate to pre-tax income

🧾 2. Projecting the Income Statement, Balance Sheet, and Cash Flow Statement

🔹 A. Projected Income Statement

Forecast over 3–5 years:

  • Revenue (from top-down or bottom-up)

  • COGS (as % of revenue or cost-driver based)

  • Gross profit

  • Operating expenses (fixed + variable)

  • EBIT (Operating Profit)

  • Interest

  • EBT (Earnings Before Tax)

  • Taxes (apply tax rate)

  • Net Income

🔹 B. Projected Balance Sheet

  1. Assets

    • Cash: From ending balance in Cash Flow Statement

    • Accounts Receivable: % of sales (e.g., DSO)

    • Inventory: % of COGS or based on Inventory Days

    • Fixed Assets: From CapEx less Depreciation

  2. Liabilities

    • Accounts Payable: % of COGS or based on Payables Days

    • Short/Long-Term Debt: Update based on financing needs

  3. Equity

    • Retained Earnings: Prior retained earnings + Net Income – Dividends

🔹 C. Projected Cash Flow Statement

Use Indirect Method:

  1. Start with Net Income

  2. Add back non-cash expenses (e.g., depreciation)

  3. Adjust for changes in working capital

  4. Add/subtract investing cash flows (CapEx, asset sales)

  5. Add/subtract financing cash flows (debt/equity issued, dividends)

  6. Result: Change in cash balance

🔗 3. Linking Financial Statements

A strong financial model must link all statements so that changes flow through accurately.

🔹 Key Linkages:

Link Description
Net Income → Balance Sheet Affects Retained Earnings and Cash (via CFS)
Depreciation → BS & CFS Reduces PP&E (BS), added back in CFS
CapEx → BS & CFS Reduces cash (CFS), increases PP&E (BS)
Working Capital Changes → CFS Affects operating cash flow
Debt → BS & CFS Affects interest (IS), balances (BS), and cash inflow/outflow (CFS)

🔍 4. Scenario and Sensitivity Analysis

🔹 Scenario Analysis

  • Evaluate outcomes under different business environments (e.g., optimistic, base, pessimistic).

  • Change multiple variables at once (e.g., sales growth, cost inflation).

🔹 Sensitivity Analysis

  • Tests how one input affects outputs like Net Income or Cash Flow.

  • Helps identify which variables most impact the model.

Example:

How does Net Income change if:

  • Sales growth = 10% → Net Income = $1.5M

  • Sales growth = 5% → Net Income = $1.2M

🔹 Excel Tools for Analysis

  • Data Tables: For sensitivity analysis

  • Scenario Manager: Save and switch between scenarios

  • Goal Seek: Find required input to reach a specific output

  • Solver: Optimize based on constraints

✅ Summary

Step Description
Revenue Forecasting Based on market, units sold, price, growth
Expense Forecasting Use % of sales, drivers, or fixed/variable split
Project Financial Statements Income Statement, Balance Sheet, Cash Flow
Link Statements Use formulas to connect net income, cash, and equity
Perform Analysis Scenario and sensitivity testing using Excel
PreviousNext