Financial Modeling Patterns

Model architecture

Every financial model follows the same three-layer architecture:

[Assumptions Sheet]  -->  [Calculations Sheet]  -->  [Output Sheet]
     (inputs)               (formulas only)          (dashboard/summary)

Assumptions sheet

Contains every editable input in the model. Organize into sections:

Formatting convention:

• Blue font = hardcoded input (editable)
• Black font = formula (do not edit)
• Yellow background = key assumption that drives the model

Calculations sheet

Pure formulas. No hardcoded values. Every cell references either the Assumptions sheet or other cells on the Calculations sheet.

Output sheet

Summary tables, charts, and KPIs. Typically contains:

• Income statement summary
• Key metrics (revenue growth, margins, EBITDA)
• Scenario comparison table
• Valuation summary

Three-statement model

Links the Income Statement, Balance Sheet, and Cash Flow Statement.

Income Statement structure

Revenue
  - COGS
= Gross Profit
  - Operating Expenses (SG&A, R&D)
= EBITDA
  - Depreciation & Amortization
= EBIT (Operating Income)
  - Interest Expense
= EBT (Earnings Before Tax)
  - Taxes
= Net Income

Formula pattern for revenue line:

=Prior_Period_Revenue * (1 + Revenue_Growth_Rate)

Formula pattern for expense lines:

=Revenue * Expense_As_Pct_Of_Revenue

Balance Sheet structure

Assets:
  Cash
  Accounts Receivable
  Inventory
  PP&E (net of depreciation)
  Other Assets

Liabilities:
  Accounts Payable
  Short-term Debt
  Long-term Debt

Equity:
  Common Stock
  Retained Earnings (prior + Net Income - Dividends)

Balance check formula (must always equal zero):

=Total_Assets - Total_Liabilities - Total_Equity

If the balance check is not zero, there is an error in the model. Use conditional formatting to turn the check cell red if non-zero.

Cash Flow Statement structure

Operating Activities:
  Net Income
  + Depreciation & Amortization
  - Change in Working Capital
    (Change in AR, Inventory, AP)

Investing Activities:
  - Capital Expenditures
  + Asset Sales

Financing Activities:
  + Debt Issuance
  - Debt Repayment
  - Dividends
  + Equity Issuance

= Net Change in Cash
  + Beginning Cash
= Ending Cash (must tie to Balance Sheet)

Working capital change formula:

=-(Current_AR - Prior_AR) - (Current_Inventory - Prior_Inventory) + (Current_AP - Prior_AP)

DCF valuation

Free Cash Flow (FCF) projection

EBIT
  * (1 - Tax Rate)
= NOPAT
  + Depreciation & Amortization
  - Capital Expenditures
  - Change in Working Capital
= Free Cash Flow

Terminal value (perpetuity growth method)

=FCF_Last_Year * (1 + Terminal_Growth_Rate) / (WACC - Terminal_Growth_Rate)

Discount FCF to present value

=FCF_Year_N / (1 + WACC) ^ N

Full DCF formula in a row:

Year:              1        2        3        4        5       Terminal
FCF:              =calc    =calc    =calc    =calc    =calc
Terminal Value:                                               =TV formula
Discount Factor:  =1/(1+WACC)^1  ...
PV of FCF:        =FCF*DF  =FCF*DF  ...

Enterprise Value = SUM(PV of FCFs) + PV of Terminal Value
Equity Value = Enterprise Value - Net Debt
Value per Share = Equity Value / Shares Outstanding

WACC calculation

=Equity_Weight * Cost_Of_Equity + Debt_Weight * Cost_Of_Debt * (1 - Tax_Rate)

Cost of Equity (CAPM):

=Risk_Free_Rate + Beta * Equity_Risk_Premium

Sensitivity analysis

Two-variable data table for DCF

Set up a matrix with WACC values across the top and terminal growth rates down the side, with the implied share price formula in the top-left corner.

              WACC
              8%     9%     10%    11%    12%
Growth  1%   $XX    $XX    $XX    $XX    $XX
        2%   $XX    $XX    $XX    $XX    $XX
        3%   $XX    $XX    $XX    $XX    $XX

Use Excel's Data Table feature (Data > What-If > Data Table) to fill the matrix automatically.

Scenario toggle pattern

Scenario_Selector: [dropdown: 1=Base, 2=Bull, 3=Bear]

Revenue_Growth: =CHOOSE(Scenario_Selector, 0.05, 0.10, 0.02)
EBITDA_Margin:  =CHOOSE(Scenario_Selector, 0.20, 0.25, 0.15)
CapEx_Pct:      =CHOOSE(Scenario_Selector, 0.05, 0.04, 0.06)

This lets the entire model recalculate by changing a single cell.

Depreciation schedules

Straight-line

=Asset_Cost / Useful_Life

Declining balance (double declining)

Year 1: =Asset_Cost * (2 / Useful_Life)
Year N: =MAX(0, Prior_Book_Value * (2 / Useful_Life))

Switch to straight-line when straight-line depreciation exceeds declining balance.

Sum-of-years-digits

=Asset_Cost * (Remaining_Life / Sum_Of_Years)
where Sum_Of_Years = Useful_Life * (Useful_Life + 1) / 2

Debt schedule

Beginning Balance
  + New Borrowings
  - Scheduled Repayments
  - Optional Prepayments (from excess cash)
= Ending Balance

Interest Expense = Beginning_Balance * Interest_Rate

For revolving credit facilities, model the draw as:

=MAX(0, -Cash_Before_Revolver)

This creates a circular reference (interest affects cash, cash affects revolver draw). Resolve by enabling iterative calculation or using a prior-period approximation.

Common financial modeling mistakes

Advanced Formula Patterns

LET - Name intermediate calculations

LET eliminates repeated sub-expressions and makes complex formulas readable.

=LET(
  revenue, SUMIFS(Sales[Amount], Sales[Region], A1),
  cost, SUMIFS(Sales[Cost], Sales[Region], A1),
  margin, revenue - cost,
  margin_pct, IF(revenue=0, 0, margin / revenue),
  TEXT(margin_pct, "0.0%")
)

Use LET whenever a sub-expression appears more than once, or when naming an intermediate step improves readability.

LAMBDA - Reusable custom functions (no VBA)

LAMBDA lets you define custom functions in the Name Manager without VBA.

Define in Name Manager (Formulas > Name Manager > New):

Name: TAX
Refers to: =LAMBDA(amount, rate, amount * rate)

Use in cells:

=TAX(B5, Tax_Rate)

LAMBDA with MAP for row-by-row processing:

=MAP(Sales[Amount], LAMBDA(amt, IF(amt > 1000, amt * 0.9, amt)))

LAMBDA with REDUCE for accumulation:

=REDUCE(0, Sales[Amount], LAMBDA(acc, amt, acc + amt))

Dynamic array functions (Excel 365+ / Google Sheets)

These functions return arrays that "spill" across multiple cells.

FILTER - Extract rows matching criteria

=FILTER(Data, (Data[Region]="West") * (Data[Revenue]>10000), "No results")

Multiple criteria use * for AND, + for OR:

=FILTER(Data, (Data[Status]="Active") + (Data[Status]="Pending"))

SORT and SORTBY

=SORT(FILTER(Data, Data[Region]="West"), 3, -1)

SORTBY sorts by a column that may not be in the output:

=SORTBY(Data[Name], Data[Revenue], -1)

UNIQUE - Deduplicate

=UNIQUE(Data[Region])

Unique rows (all columns must match):

=UNIQUE(A2:C100)

SEQUENCE - Generate number series

=SEQUENCE(12, 1, 1, 1)          -- 1 to 12 in a column
=SEQUENCE(1, 10, 0, 0.1)        -- 0.0 to 0.9 in a row
=DATE(2025, SEQUENCE(12), 1)     -- First of each month in 2025

CHOOSECOLS / CHOOSEROWS - Select specific columns or rows

=CHOOSECOLS(Data, 1, 3, 5)      -- Return columns 1, 3, 5 only
=CHOOSEROWS(Data, 1, -1)         -- First and last row

Text manipulation formulas

TEXTJOIN - Concatenate with delimiter

=TEXTJOIN(", ", TRUE, FILTER(Data[Name], Data[Region]="West"))

TEXTSPLIT (Excel 365) - Split delimited text

=TEXTSPLIT(A1, ",")              -- Split by comma into columns
=TEXTSPLIT(A1, , CHAR(10))       -- Split by newline into rows

REGEXMATCH / REGEXEXTRACT / REGEXREPLACE (Google Sheets only)

=REGEXMATCH(A1, "^\d{3}-\d{4}$")
=REGEXEXTRACT(A1, "(\d+\.?\d*)")
=REGEXREPLACE(A1, "\s+", " ")

Excel has no native regex. Use LAMBDA + MID + SEQUENCE for pattern matching, or VBA's RegExp object for complex patterns.

Date and time patterns

EOMONTH - End of month arithmetic

=EOMONTH(TODAY(), 0)             -- Last day of current month
=EOMONTH(TODAY(), -1) + 1        -- First day of current month
=EOMONTH(A1, 0) - EOMONTH(A1, -1)  -- Days in the month of A1

NETWORKDAYS - Business day calculations

=NETWORKDAYS(start_date, end_date, holidays_range)
=WORKDAY(start_date, 10, holidays_range)    -- 10 business days from start

Fiscal year / quarter mapping

=LET(
  month, MONTH(A1),
  fiscal_month, MOD(month - fiscal_start_month, 12) + 1,
  fiscal_quarter, ROUNDUP(fiscal_month / 3, 0),
  "Q" & fiscal_quarter
)

Array formula patterns (legacy CSE)

For older Excel versions without dynamic arrays, use Ctrl+Shift+Enter (CSE):

Multi-criteria SUMPRODUCT (works everywhere):

=SUMPRODUCT((Data[Region]="West") * (Data[Year]=2025) * Data[Revenue])

Conditional array count of unique values:

=SUMPRODUCT((Data[Region]="West") / COUNTIF(
  IF(Data[Region]="West", Data[Product]),
  IF(Data[Region]="West", Data[Product])
))

SUMPRODUCT does not need CSE entry and works in all Excel versions. Prefer it over CSE array formulas for maximum compatibility.

Error handling patterns

Nested IFERROR for fallback chains

=IFERROR(XLOOKUP(A1, Primary[ID], Primary[Value]),
  IFERROR(XLOOKUP(A1, Secondary[ID], Secondary[Value]),
    "Not found in any source"))

IFNA vs IFERROR

Use IFNA when you only want to catch #N/A (lookup miss). Use IFERROR when any error type should be handled. IFNA is safer because it does not mask unexpected errors like #REF! or #VALUE! that indicate real problems.

=IFNA(XLOOKUP(A1, range, range), 0)     -- Only catches #N/A
=IFERROR(complex_formula, "Error")       -- Catches everything (use carefully)

ISERROR / ISNA for conditional logic

=IF(ISNA(MATCH(A1, range, 0)), "New item", "Existing")

VBA and Apps Script Patterns

VBA fundamentals

Loop through rows efficiently

Sub ProcessRows()
    Dim ws As Worksheet
    Set ws = ThisWorkbook.Sheets("Data")

    Dim lastRow As Long
    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row

    Dim i As Long
    For i = 2 To lastRow
        ' Read values
        Dim name As String
        name = ws.Cells(i, 1).Value

        Dim amount As Double
        amount = ws.Cells(i, 2).Value

        ' Write result
        ws.Cells(i, 3).Value = amount * 1.1
    Next i
End Sub

Use arrays for speed (avoid cell-by-cell reads)

Sub FastProcess()
    Dim ws As Worksheet
    Set ws = ThisWorkbook.Sheets("Data")

    Dim lastRow As Long
    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row

    ' Read entire range into array (much faster than cell-by-cell)
    Dim data As Variant
    data = ws.Range("A2:D" & lastRow).Value

    Dim results() As Variant
    ReDim results(1 To UBound(data, 1), 1 To 1)

    Dim i As Long
    For i = 1 To UBound(data, 1)
        results(i, 1) = data(i, 2) * data(i, 3)  ' Price * Quantity
    Next i

    ' Write results back in one operation
    ws.Range("E2:E" & lastRow).Value = results
End Sub

Reading/writing arrays is 10-100x faster than cell-by-cell operations. Always use this pattern for datasets larger than ~100 rows.

Error handling

Sub SafeOperation()
    On Error GoTo ErrorHandler

    ' ... risky operations ...

    Exit Sub

ErrorHandler:
    MsgBox "Error " & Err.Number & ": " & Err.Description, vbCritical
    ' Optionally log to a sheet
    Dim logWs As Worksheet
    Set logWs = ThisWorkbook.Sheets("Log")
    Dim logRow As Long
    logRow = logWs.Cells(logWs.Rows.Count, "A").End(xlUp).Row + 1
    logWs.Cells(logRow, 1).Value = Now()
    logWs.Cells(logRow, 2).Value = Err.Description
End Sub

Screen updating and calculation toggles

Sub OptimizedMacro()
    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual
    Application.EnableEvents = False

    ' ... bulk operations ...

    Application.EnableEvents = True
    Application.Calculation = xlCalculationAutomatic
    Application.ScreenUpdating = True
End Sub

Always re-enable these in a Finally-style pattern. If an error occurs before re-enabling, Excel stays in manual calc mode until the user notices.

VBA UserForms

Basic input form pattern

' In UserForm code module
Private Sub btnSubmit_Click()
    Dim ws As Worksheet
    Set ws = ThisWorkbook.Sheets("Data")

    Dim nextRow As Long
    nextRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row + 1

    ws.Cells(nextRow, 1).Value = txtName.Value
    ws.Cells(nextRow, 2).Value = CDbl(txtAmount.Value)
    ws.Cells(nextRow, 3).Value = cboCategory.Value
    ws.Cells(nextRow, 4).Value = Now()

    MsgBox "Record added.", vbInformation
    Unload Me
End Sub

Private Sub UserForm_Initialize()
    ' Populate combo box
    cboCategory.AddItem "Revenue"
    cboCategory.AddItem "Expense"
    cboCategory.AddItem "Transfer"
End Sub

Validate form inputs

Private Sub btnSubmit_Click()
    If Trim(txtName.Value) = "" Then
        MsgBox "Name is required.", vbExclamation
        txtName.SetFocus
        Exit Sub
    End If

    If Not IsNumeric(txtAmount.Value) Then
        MsgBox "Amount must be a number.", vbExclamation
        txtAmount.SetFocus
        Exit Sub
    End If

    ' ... proceed with submission ...
End Sub

VBA API calls (HTTP requests)

GET request using XMLHTTP

Function FetchJSON(url As String) As String
    Dim http As Object
    Set http = CreateObject("MSXML2.XMLHTTP")

    http.Open "GET", url, False
    http.setRequestHeader "Content-Type", "application/json"
    http.send

    If http.Status = 200 Then
        FetchJSON = http.responseText
    Else
        FetchJSON = "Error: " & http.Status & " " & http.statusText
    End If
End Function

Parse JSON response (using VBA-JSON library)

' Requires: https://github.com/VBA-tools/VBA-JSON imported as JsonConverter module
Sub ImportAPIData()
    Dim json As String
    json = FetchJSON("https://api.example.com/data")

    Dim parsed As Object
    Set parsed = JsonConverter.ParseJson(json)

    Dim ws As Worksheet
    Set ws = ThisWorkbook.Sheets("Import")
    Dim row As Long
    row = 2

    Dim item As Variant
    For Each item In parsed
        ws.Cells(row, 1).Value = item("id")
        ws.Cells(row, 2).Value = item("name")
        ws.Cells(row, 3).Value = item("value")
        row = row + 1
    Next item
End Sub

Google Apps Script patterns

Read and write ranges

function processData() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheet = ss.getSheetByName("Data");

  // Read all data at once (fast)
  const data = sheet.getDataRange().getValues();
  const headers = data[0];

  // Process rows
  const results = data.slice(1).map(row => {
    const revenue = row[1];
    const cost = row[2];
    return [revenue - cost, (revenue - cost) / revenue];
  });

  // Write results in one call (fast)
  const outputRange = sheet.getRange(2, headers.length + 1, results.length, 2);
  outputRange.setValues(results);
}

Like VBA arrays, batch getValues() / setValues() is orders of magnitude faster than cell-by-cell getValue() / setValue().

Custom menu

function onOpen() {
  SpreadsheetApp.getUi()
    .createMenu("Custom Tools")
    .addItem("Refresh Data", "refreshData")
    .addItem("Send Report", "sendWeeklyReport")
    .addSeparator()
    .addItem("Archive Old Rows", "archiveRows")
    .addToUi();
}

Fetch external API data

function importFromAPI() {
  const url = "https://api.example.com/data";
  const options = {
    method: "get",
    headers: { "Authorization": "Bearer " + getApiKey() },
    muteHttpExceptions: true
  };

  const response = UrlFetchApp.fetch(url, options);

  if (response.getResponseCode() !== 200) {
    throw new Error("API error: " + response.getResponseCode());
  }

  const data = JSON.parse(response.getContentText());
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Import");

  // Clear old data and write new
  sheet.getRange("A2:Z").clearContent();

  const rows = data.map(item => [item.id, item.name, item.value, new Date()]);
  if (rows.length > 0) {
    sheet.getRange(2, 1, rows.length, rows[0].length).setValues(rows);
  }
}

function getApiKey() {
  return PropertiesService.getScriptProperties().getProperty("API_KEY");
}

Store API keys in Script Properties (File > Project properties > Script properties), never hardcoded in the script.

Scheduled triggers

function createScheduledTriggers() {
  // Delete existing triggers first
  ScriptApp.getProjectTriggers().forEach(t => ScriptApp.deleteTrigger(t));

  // Daily data refresh at 6 AM
  ScriptApp.newTrigger("importFromAPI")
    .timeBased()
    .everyDays(1)
    .atHour(6)
    .create();

  // Weekly report every Monday at 9 AM
  ScriptApp.newTrigger("sendWeeklyReport")
    .timeBased()
    .everyWeeks(1)
    .onWeekDay(ScriptApp.WeekDay.MONDAY)
    .atHour(9)
    .create();
}

Sidebar / dialog UI

function showSidebar() {
  const html = HtmlService.createHtmlOutput(`
    <h3>Data Filter</h3>
    <label>Region:</label>
    <select id="region">
      <option>All</option>
      <option>West</option>
      <option>East</option>
    </select>
    <br><br>
    <button onclick="google.script.run.filterByRegion(
      document.getElementById('region').value)">
      Apply Filter
    </button>
  `).setTitle("Filter Panel");

  SpreadsheetApp.getUi().showSidebar(html);
}

Common gotchas