Excel for Financial Modeling: Build Professional Models

Financial modeling in Excel is essential for analysts, accountants, and finance professionals. This guide covers best practices, essential techniques, and common financial models you can build in Excel.

What is Financial Modeling?

Financial modeling is the process of creating a summary of a company's expenses and earnings in the form of a spreadsheet that can be used to calculate the impact of a future event or decision.

Common Uses:

• 💼 Company valuation
• 📊 Budgeting and forecasting
• 📈 Investment analysis
• 💰 Project finance
• 🏦 Credit analysis
• 📋 Merger and acquisition (M&A) analysis

Financial Modeling Best Practices

1. Structure and Organization

Standard Model Structure:

1. Assumptions/Inputs: All variable inputs
2. Supporting Schedules: Calculations
3. Core Model: Main financial statements
4. Outputs/Summary: Key results and metrics

Worksheet Organization:

• Color code: Blue for inputs, black for formulas
• Group related worksheets
• Use consistent naming conventions
• Add navigation sheet with links

2. Formula Best Practices

Use One Formula per Row:

// Good: Same formula across row
=Assumptions!B2*Revenue_Growth

// Bad: Different formulas
=Assumptions!B2*1.1
=Assumptions!B3*1.12
=Assumptions!B4*1.15

Use Named Ranges:

// Instead of: =B2*1.1
// Use: =Revenue*Growth_Rate

Avoid Hard-Coded Values:

// Bad
=Revenue*1.1

// Good
=Revenue*(1+Growth_Rate)

3. Error Prevention

Checksums:

• Add check totals
• Verify balance sheet balances
• Cross-check calculations

Sensitivity Analysis:

• Test with extreme values
• Validate assumptions make sense
• Check for circular references

Essential Financial Formulas

Time Value of Money

Present Value (PV):

=PV(rate, nper, pmt, [fv], [type])
=PV(0.1, 5, 0, -1000)  // PV of $1000 in 5 years at 10%

Future Value (FV):

=FV(rate, nper, pmt, [pv], [type])
=FV(0.08, 10, -1000, 0)  // FV of $1000/year for 10 years at 8%

Net Present Value (NPV):

=NPV(rate, value1, value2, ...)
=NPV(0.1, -1000, 200, 300, 400, 500)

Internal Rate of Return (IRR):

=IRR(values, [guess])
=IRR({-1000, 200, 300, 400, 500})

Financial Ratios

Profitability Ratios:

Gross Margin = (Revenue - COGS) / Revenue
Operating Margin = Operating Income / Revenue
Net Margin = Net Income / Revenue
ROE = Net Income / Equity
ROA = Net Income / Total Assets

Liquidity Ratios:

Current Ratio = Current Assets / Current Liabilities
Quick Ratio = (Current Assets - Inventory) / Current Liabilities

Leverage Ratios:

Debt to Equity = Total Debt / Total Equity
Debt to Assets = Total Debt / Total Assets
Interest Coverage = EBIT / Interest Expense

Efficiency Ratios:

Asset Turnover = Revenue / Total Assets
Inventory Turnover = COGS / Average Inventory
Days Sales Outstanding = (Accounts Receivable / Revenue) * 365

Building the Three-Statement Model

Income Statement

Structure:

Revenue
- Cost of Goods Sold (COGS)
= Gross Profit
- Operating Expenses
= Operating Income (EBIT)
+ Other Income
- Interest Expense
= Earnings Before Tax (EBT)
- Taxes
= Net Income

Key Formulas:

Revenue = Prior_Year_Revenue * (1 + Growth_Rate)
COGS = Revenue * COGS_Percentage
Operating Expenses = Revenue * OpEx_Percentage
Taxes = EBT * Tax_Rate

Balance Sheet

Structure:

ASSETS
Current Assets
  Cash
  Accounts Receivable
  Inventory
  Other Current Assets
Non-Current Assets
  Property, Plant & Equipment (PP&E)
  Intangible Assets
  Other Assets
= Total Assets

LIABILITIES & EQUITY
Current Liabilities
  Accounts Payable
  Short-term Debt
  Other Current Liabilities
Non-Current Liabilities
  Long-term Debt
  Other Liabilities
= Total Liabilities
Equity
  Common Stock
  Retained Earnings
= Total Equity
= Total Liabilities & Equity

Balancing:

// Cash is usually the plug
Cash = Total Assets - (Other Assets + Total Liabilities + Equity)

// Or use a circular reference solver

Cash Flow Statement

Structure:

Operating Activities
  Net Income
  + Depreciation
  - Changes in Working Capital
= Operating Cash Flow

Investing Activities
  CapEx
  Acquisitions
= Investing Cash Flow

Financing Activities
  Debt Issuance/Repayment
  Equity Issuance
  Dividends
= Financing Cash Flow

= Net Change in Cash
+ Beginning Cash
= Ending Cash

Forecasting Techniques

Historical Growth Method

Revenue_Forecast = Prior_Year_Revenue * (1 + Historical_Growth_Rate)

Linear Regression

// Using FORECAST function
=FORECAST(period, known_y, known_x)

// Or manually
Slope = SLOPE(known_y, known_x)
Intercept = INTERCEPT(known_y, known_x)
Forecast = Intercept + Slope * New_Period

Seasonal Forecasting

// Calculate seasonal index
Seasonal_Index = Historical_Month_Avg / Overall_Avg

// Apply to base forecast
Seasonal_Forecast = Base_Forecast * Seasonal_Index

Percentage of Sales Method

// Many items vary with sales
COGS = Revenue * COGS_Percentage
Operating_Expenses = Revenue * OpEx_Percentage
Working_Capital = Revenue * WC_Percentage

Scenario Analysis

Creating Scenarios

Base Case:

Growth_Rate = 5%
Margin = 15%

Best Case:

Growth_Rate = 10%
Margin = 20%

Worst Case:

Growth_Rate = 0%
Margin = 10%

Data Tables for Sensitivity

1. Create input variables
2. Create output formula
3. Data → What-If Analysis → Data Table
4. Set row/column inputs
5. View all scenarios

Valuation Models

Discounted Cash Flow (DCF)

Steps:

1. Forecast free cash flows
2. Calculate terminal value
3. Discount to present value
4. Sum to get enterprise value

Free Cash Flow:

FCF = Operating_Cash_Flow - CapEx

Terminal Value (Gordon Growth):

Terminal_Value = Final_FCF * (1 + Growth) / (WACC - Growth)

Present Value:

PV = FCF / (1 + WACC)^period

Comparable Company Analysis

Methodology:

1. Identify comparable companies
2. Calculate valuation multiples (P/E, EV/Revenue, etc.)
3. Apply multiples to target company metrics
4. Calculate valuation range

Common Multiples:

P/E = Market_Cap / Net_Income
EV/Revenue = Enterprise_Value / Revenue
EV/EBITDA = Enterprise_Value / EBITDA

Model Validation

Balancing Checks

Balance Sheet:

Check: Total_Assets = Total_Liabilities + Equity

Cash Flow:

Check: Ending_Cash = Beginning_Cash + Net_Cash_Flow

Reconciliation:

Check: Retained_Earnings = Prior_RE + Net_Income - Dividends

Error Checks Section

Create an error check section:

IF(ABS(Assets - Liabilities_Equity) > 0.01, "ERROR", "OK")
IF(Cash_Flow_Check <> 0, "ERROR", "OK")

Professional Model Features

Scenario Manager

1. Define scenarios
2. Data → What-If Analysis → Scenario Manager
3. Create scenarios with different inputs
4. Generate summary report

Goal Seek

Find input for desired output:

1. Data → What-If Analysis → Goal Seek
2. Set cell (output)
3. To value (target)
4. By changing (input)

Data Validation

Protect input cells:

1. Select input cells
2. Data → Data Validation
3. Set allowed values/ranges
4. Add input messages

Protection

Protect Structure:

1. Review → Protect Workbook
2. Prevent sheet deletion/insertion

Protect Formulas:

1. Unlock input cells
2. Lock formula cells
3. Protect worksheet
4. Allow only input cells to be edited

Presentation and Formatting

Professional Formatting

Color Coding:

• Blue: Inputs/assumptions
• Black: Formulas/calculations
• Green: Links from other sheets
• Red: Error checks or negatives

Number Formatting:

• Currency: $#,##0
• Percentages: 0.0%
• Thousands: #,##0,"k"
• Millions: #,##0.0,,"m"

Headers and Labels:

• Clear, descriptive headers
• Consistent formatting
• Use bold for section headers
• Add borders for clarity

Common Financial Models

1. Budget Model

• Revenue and expense forecasts
• Departmental budgets
• Variance analysis

2. Forecast Model

• Multi-year projections
• Seasonality adjustments
• Growth assumptions

3. Valuation Model

• DCF analysis
• Comparable company analysis
• Valuation range

4. M&A Model

• Accretion/dilution analysis
• Synergy estimates
• Combined entity financials

5. Project Finance Model

• Project cash flows
• Debt service coverage
• Return calculations

Practice Exercise

Build a simple three-statement model:

1. Create assumptions sheet
2. Build 5-year income statement
3. Build balance sheet with plugs
4. Create cash flow statement
5. Add valuation outputs
6. Include error checks

Conclusion

Financial modeling in Excel is both art and science. Master the fundamentals, follow best practices, and build models that are accurate, flexible, and easy to understand. Good financial models tell a story and support decision-making.

Remember: A model is only as good as its assumptions. Spend time validating inputs!

Resources

• Explore Financial Functions
• Try Model Templates
• Learn Advanced Analysis

Build models that drive decisions!