Objectives

Section Learning Objectives

5.1

The Balance Sheet Balancing Equation

πŸ“–Fundamental Rule

The Balance Sheet must always balance: Total Assets = Total Liabilities + Total Equity. In a three-statement model, if the BS doesn't balance, there is an error in your linking. Every dollar of assets must be funded by either debt (liabilities) or equity.

BALANCE CHECK (every period):

Total Assets = Cash + AR + Inventory + PP&E + Other Assets
Total L&E = AP + Debt + Other Liabilities + Retained Earnings + Other Equity

Check Row: = Total Assets βˆ’ Total L&E
Must = $0 in every single period

Excel: =IF(ABS(Check)<0.01,"βœ“ Balanced","βœ— ERROR: "&TEXT(Check,"#,##0"))
βœ…

Balanced

Assets = L&E
Check = 0

❌

Assets > L&E

Check > 0
Missing liability or excess asset

❌

Assets < L&E

Check < 0
Missing asset or excess liability

5.2

Cash as the Natural Plug

πŸ’‘ The Concept of a "Plug"

In financial modeling, a "plug" is the item that automatically adjusts to make the balance sheet balance. In a well-built model, cash is the natural plug.

How Cash Balances Automatically:

1. All BS items are calculated independently (AR, Inv, PP&E, AP, Debt, RE)
2. Cash is derived from the Cash Flow Statement
3. Total Assets = Cash + All Other Assets
4. If all links are correct, Cash adjusts to fill the gap

Cash = Total Liabilities + Total Equity βˆ’ All Other Assets

This should match your CF-calculated ending cash exactly.
βœ…Best Practice

Include a "Cash Check" row in your model: = CF_Ending_Cash βˆ’ BS_Cash. This should equal zero. If it doesn't, there's a linking error somewhere. This is separate from the BS balance check and catches errors that might net out to zero by coincidence.

5.3

Revolver as a Balancing Mechanism

πŸ”„ The Revolver: Automatic Cash Management

When the model projects negative cash, the revolver automatically draws to cover the shortfall.

Minimum Cash Balance = Assumption (e.g., $10M)

Cash Before Revolver = Beg Cash + CF from Operations + CF from Investing + CF from Financing

If Cash Before Revolver < Minimum Cash:
β†’ Revolver Draw = Minimum Cash βˆ’ Cash Before Revolver

If Cash Before Revolver > Minimum Cash AND Revolver has balance:
β†’ Revolver Paydown = MIN(Excess Cash, Revolver Balance)

Excel:
Revolver Draw = MAX(0, Min_Cash βˆ’ Cash_Before_Revolver)
Revolver Paydown = MIN(MAX(0, Cash_Before_Revolver βˆ’ Min_Cash), Beg_Revolver)
⚠️Circular Reference Warning

The revolver creates a circular reference: Revolver draw β†’ Interest expense β†’ Net Income β†’ Cash Flow β†’ Revolver draw. Handle this using:
1. Simple method: Use beginning balance for interest (avoids circularity)
2. Excel iteration: Enable iterative calculation (File β†’ Options β†’ Formulas)
3. Copy-paste macro: VBA that copies values to break the loop

5.4

Debugging Imbalances: A Step-by-Step Guide

Step 1: Check the BS Balance Row

Find which period(s) don't balance. Is it all periods or just one?

If imbalance = same amount every period β†’ likely a missing starting balance
If imbalance grows over time β†’ likely a compounding error in roll-forward
If imbalance appears suddenly β†’ likely a one-time entry error

Step 2: Verify Net Income Flows Correctly

Check that NI flows to both the BS and CF.

βœ“ Retained Earnings = Prior RE + NI βˆ’ Dividends
βœ“ CF Operating starts with the same Net Income
βœ“ Both reference the SAME cell (not duplicate calculations)

Step 3: Check Working Capital Signs

The #1 cause of imbalances β€” wrong signs on WC changes.

βœ“ Asset increase (AR↑, Inv↑) β†’ subtract from CF (cash outflow)
βœ“ Asset decrease (AR↓, Inv↓) β†’ add to CF (cash inflow)
βœ“ Liability increase (AP↑) β†’ add to CF (cash inflow)
βœ“ Liability decrease (AP↓) β†’ subtract from CF (cash outflow)

Quick check: Ξ”NWC = Current NWC βˆ’ Prior NWC
CFO = NI + D&A βˆ’ Ξ”NWC (not + Ξ”NWC!)

Step 4: Verify Depreciation Triple Link

Ensure depreciation appears correctly in all three places.

βœ“ IS: Depreciation expense reduces EBIT
βœ“ BS: PP&E = Beg + CapEx βˆ’ Depreciation (same D&A amount)
βœ“ CF: Depreciation added back to NI in Operating CF
βœ“ All three reference the SAME depreciation cell

Step 5: Trace Debt and Interest

Verify the debt schedule links to all three statements.

βœ“ IS: Interest expense from debt schedule
βœ“ BS: Ending debt balance = Beg + Drawings βˆ’ Repayments
βœ“ CF: Drawings (inflow) and Repayments (outflow) in Financing
βœ“ Revolver draws/repays correctly balance cash
5.5

Quality Checks & Model Auditing

βœ… Essential Model Checks

Check Formula Should Equal
BS Balance = Total Assets βˆ’ Total L&E 0
Cash Check = CF_End_Cash βˆ’ BS_Cash 0
NI Consistency = IS_NI βˆ’ CF_NI 0
RE Roll-Forward = BS_RE βˆ’ (Prior_RE + NI βˆ’ Div) 0
PP&E Roll-Forward = BS_PPE βˆ’ (Prior_PPE + CapEx βˆ’ Dep) 0
Debt Roll-Forward = BS_Debt βˆ’ (Prior_Debt + Draw βˆ’ Repay) 0
πŸ’‘Professional Tip

Create a dedicated "Checks" tab in your Excel model that aggregates all these checks. Use conditional formatting: green for 0 (balanced), red for anything else. Add a master check: =IF(AND(all_checks=0),"ALL CHECKS PASS","ERRORS FOUND"). This is standard practice at investment banks and PE firms.

Practice

Hands-on Exercise: Checks & Revolver Logic

Implement balance checks and a simple revolver draw/paydown logic. Download the dataset and follow the condensed instructions below. Full step‑by‑step solutions are hidden and can be revealed with the Show / Hide Solution Steps button.

Download Balancing & Revolver CSV Download Full Guide (includes solutions)

  1. Open the CSV and compute Cash_Before_Revolver = Beginning_Cash + CFO + CFI + CFF for each year.
  2. Implement Revolver Draw = MAX(0, Min_Cash βˆ’ Cash_Before_Revolver) and Revolver Paydown = MIN(MAX(0, Cash_Before_Revolver βˆ’ Min_Cash), Revolver_Beg).
  3. Compute Ending Revolver = Revolver_Beg + Draw βˆ’ Paydown and Ending Cash = Cash_Before_Revolver + Draw βˆ’ Paydown.
  4. Add a Balance Check row and a Cash Check row to your model and verify both equal zero in each period.
Summary

Key Takeaways

  • BS must balance every period β€” Assets = Liabilities + Equity
  • Cash is the natural plug β€” it adjusts to make the BS balance
  • Revolver acts as the balancing mechanism when cash is short
  • Debug systematically: BS check β†’ NI flow β†’ WC signs β†’ D&A links β†’ Debt
  • Always build a Checks tab with conditional formatting for professional models
πŸ“šNext Session

Session 9: Scenario & Sensitivity Analysis
Now that your model is fully integrated and balanced, learn how to stress-test it with different scenarios. Read: Excel 2019 Bible, Ch. 12

← Previous Section Back to Lecture Overview β†’