Summary of "Learn Financial Modelling - Step by Step - Session 2"

High-level summary

This session is a practical, Excel-first walkthrough for building professional financial models. The instructor emphasizes industry conventions and repeatable processes that maximize control, auditability, and workbook hygiene: minimal hard-coding, consistent formatting, driver-led forecasts, and clear validation checks so you control the model — not the other way around.

Key frameworks and playbooks

Excel setup & baseline conventions (one-time workbook settings)

• Default font: Calibri 11; set preferred default view and default number of sheets.
• Calculation mode: Automatic. Keep iterative calculation OFF by default to avoid accidental circular references.
• AutoSave: set frequency (example: every 1 minute).
• Enable Analysis ToolPak (Add-ins).
• Use Go To Special (Ctrl+G → Special) to locate Constants vs Formulas during audits.

Workbook architecture / modeling playbook

• Central assumptions/drivers sheet: keep inputs in one place to avoid hard-coding throughout the workbook.
• Separate actuals vs forecast: clearly mark actual years vs forecast years (example: actuals through 2021; forecast 2022–2026).
• Color/format convention: bright blue = hard-coded inputs; black = formulas. Enforce consistently.
• Layout conventions:
  • First column reserved for row numbers/IDs (thin).
  • Description column left-aligned; numeric columns right-aligned.
  • Freeze header row(s) and use grouping (Data → Group) so sections can be collapsed/expanded.
  • Remove gridlines for presentation; use single/double borders for subtotals/totals.

Forecasting & driver-led modeling

• Link forecast cells to assumption drivers; avoid re-hardcoding numbers across sheets.
• Use percentage drivers for common items (e.g., revenue growth, COGS as % of sales).
• Build common-size statements (each income-statement line as % of revenue) to analyze margins and drivers.
• Run change/sensitivity analysis by applying uniform shocks (e.g., +10%) and observing impacts.

Auditing & validation

• Use Go To Special to find leftover constants or formulas.
• Check for circular references; keep iterative calculation disabled unless intentionally used and documented.
• Include simple reconciliation checks and visible conventions to help reviewers spot anomalies.

Date & period handling

• Use date functions and end-of-month logic so period increments (monthly/quarterly/annual) are dynamic — anchor a start date and increment with month offsets rather than hard-coded dates.
• Use logical checks (IF) for partial vs full periods (e.g., treat partial-year months separately).

Efficiency / keyboard-driven workflow

• Use keyboard shortcuts heavily (Alt sequences, F4 to toggle anchors, Ctrl+Alt+V for Paste Special).
• Paste Special → Values when importing data to avoid bringing formulas or unwanted formats.
• Use Format Painter or Paste Special → Formats to preserve consistent formatting.

Concrete Excel rules, techniques and playbook items

One-time Excel settings

• File → Options: set default font to Calibri 11, default view, and default number of sheets.
• Formulas tab: set Calculation Options → Automatic; ensure iterative calculation is disabled.
• Save options: AutoSave every 1 minute (or preferred interval).
• Enable Analysis ToolPak via Add-ins.

Formatting & layout

• Description column: left-aligned. Numeric columns: right-aligned.
• Use custom number formats: thousands separators, bracket negatives, fixed decimals for numbers and two decimals for percentages.
• Row/column sizing: first column narrow; year/data columns around width 10–12 for clarity.

Inputs vs formulas

• Keep inputs in the assumptions sheet and color them bright blue; formulas remain black.
• When pasting external data: use Ctrl+Alt+V → Values.

Anchoring / cell locking

• Use F4 to cycle absolute ($A$1), row-locked, column-locked, and none.
• Use partial anchoring appropriately when copying formulas (e.g., lock a revenue column when calculating ratios).

Grouping and presentation

• Group rows for major sections (Income Statement, COGS, Opex) for collapse/expand functionality.
• Single border for subtotals; double border for totals.
• Turn off gridlines for final models.

Paste special & formatting shortcuts

• Ctrl+Alt+V opens Paste Special. Use Values or Formats (T) as needed.
• Use Format Painter or Ctrl+Alt+V → Formats to copy formatting rapidly.

Conditional formatting

• Use conditional rules for KPI thresholds (e.g., <10% gray italic; >15% green) to highlight ranges.

Audit checks

• Go To Special → Constants to find leftover hard codes.
• Go To Special → Formulas to view formulas only.
• Add reconciliation lines and IF checks to flag inconsistencies (e.g., IF(total <> expected, "ERROR", "")).

Key Excel functions & formulas emphasized

• Date & period: EOMONTH, and logic to build dynamic month/quarter/year series from an anchored start date.
• Growth/roll-forward: =previous_year*(1+growth_rate)
• Anchoring: use F4 to toggle $ anchor states.
• Go To Special (Ctrl+G → Special) to locate constants/formulas.
• Paste Special (Ctrl+Alt+V) for Values, Formats, Formulas.
• Aggregation & stats: AVERAGE, MEDIAN, MIN, MAX, SMALL, LARGE.
• Weighted metrics: SUMPRODUCT for weighted averages (recommended).
• Logicals: IF for partial/full period checks and conditional outcomes.
• Multiplication of multiple factors: PRODUCT (and SUMPRODUCT where applicable).
• Conditional formatting: threshold-based rules.
• Avoid circular references; if used, document iterative calc settings.

Example numbers, drivers, KPIs and timelines (takeaways)

• Forecast horizon: 5-year example (2022–2026) with actuals until 2021.
• Typical assumption examples:
  • Revenue growth: +10%
  • COGS = 40% of Sales
  • Interest expense: 5% or fixed 250
  • Tax rate: 30% or 35%
  • Fixed expense example: 2,500
• Date example: start date 15‑Aug‑2022 to illustrate end-of-month and partial-period logic.
• Scenario/sensitivity: apply +10% shock across revenue/line items to see impacts on gross profit/net income.
• Circular reference example: defining a company’s sales as one-third of market total — demonstrates how circularity can arise and why to avoid it.

Concrete examples & step-by-step walkthrough highlights

1. Build the model skeleton
   • Top banner: currency and header rows; immediately Freeze Panes.
   • Create income statement lines with correct indentation, subtotals, and totals (apply borders).
   • Central assumptions/drivers sheet (blue inputs).
   • Link forecasts to drivers (no hardcoding in the main model).
   • Format numbers consistently (thousands separator, % with 2 decimals, bracket negatives).
2. Importing data correctly
   • Copy from source → target cell → Ctrl+Alt+V → Values to avoid importing formulas.
3. Create a common-size statement
   • Copy the income statement → paste values → compute each line as line / revenue.
4. Build change analysis
   • Copy baseline statement → apply a multiplier (e.g., *(1+10%)) anchored to an assumption cell → paste into a scenario table.
5. Audit & finalize
   • Use Go To Special to find constants and formulas; correct any misplaced hardcoding.
   • Add IF checks to flag anomalies (e.g., IF(totals_mismatch, "ERROR", "")).
   • Group sections and turn off gridlines for a clean, client-ready delivery.

Practical recommendations / short checklist

• Keep one assumptions sheet and avoid hard-coding elsewhere.
• Color-code inputs (blue) vs formulas (black); enforce via Go To Special checks.
• Disable iterative calculation by default; enable only when intentionally building iterative models and document it.
• Lock and partially anchor references correctly; test copying formulas across rows/columns.
• Use keyboard shortcuts and Paste Special workflows to save time and reduce mouse reliance.
• Build reconciliation and error-check lines to speed up model review.
• Use grouping, Freeze Panes, proper borders, and no gridlines for a clean presentation.

Common errors and pitfalls

• Hard-coding the same input in many places — makes models brittle and error-prone.
• Accidentally replacing formulas with values when pasting — always be deliberate with Paste Special.
• Incorrect anchoring ($ usage) leading to wrong copied formulas.
• Circular references introduced unintentionally; iterative calculation should be off unless required and documented.
• Forgetting to audit constants/formulas — use Go To Special to catch these issues.

Presenter / source

• Parth Verma (instructor referenced in the video and closing).

Recommendation: download and follow along with the exercise workbook provided in the video description and practice the shortcuts, Paste‑Special workflows, anchoring patterns, and audit checks described.

Category

Business

---

Share this summary

Share on X/Twitter Share on Facebook Share on LinkedIn Share on Reddit Share on WhatsApp Share on Telegram

---

Is the summary off?

If you think the summary is inaccurate, you can reprocess it with the latest model.

Reprocess summary

Video