Financial Modeling for Beginners: A Step-by-Step Guide to Excel-Based Analysis
Financial modeling is the skill that separates analysts who describe what happened from analysts who predict what will happen. It is the process of building a structured, assumption-driven spreadsheet that represents a company's financial performance - past, present, and projected. Investment banks use financial models to value acquisition targets. FP&A teams use them to build annual budgets. Equity research analysts use them to set price targets. If you want to work seriously in finance, financial modeling is not optional.
The challenge for beginners is that most financial modeling guides assume you already know the material. They reference DCF as if it's common knowledge, or skip straight to sensitivity tables without explaining the 3-statement model that drives them. This guide starts from the beginning. By the time you finish reading, you'll understand what financial modeling actually is, which Excel functions matter most, how a 3-statement model works and connects, how to forecast revenue, how to build a basic DCF, and - critically - which mistakes to avoid that trip up most beginners. If you want to know where financial modeling fits in the broader finance career landscape, Board Infinity's guide on Colliers Financial Analyst real estate role shows how DCF and scenario analysis are applied in real analyst jobs day-to-day.
This guide is practical and jargon-light. Every section maps directly to what you'll do in Excel on the job.
Who This Guide Is For
This guide is for you if you:
- Are a finance student or early-career professional building your first financial model
- Have Excel basics but haven't applied them to financial analysis yet
- Are preparing for analyst interviews where modeling ability is assessed
- Want to understand how DCF, sensitivity analysis, and forecasting work before your first role
- Are curious how financial models at Goldman Sachs GIR or similar firms are structured - Board Infinity's Goldman Sachs GIR Summer Analyst interview guide covers exactly what's expected
1. Core Financial Statements: P&L, Balance Sheet, Cash Flow
Every financial model is built on three core financial statements. Understanding each one - and how they connect to each other - is the foundation of everything else.
The Income Statement (P&L) shows revenue, expenses, and profit over a period (monthly, quarterly, annually). It starts with revenue at the top, subtracts costs to get gross profit, subtracts operating expenses to get EBIT, subtracts interest and taxes to arrive at net income at the bottom. The income statement answers: "Was the company profitable this period?"
The Balance Sheet is a snapshot of what the company owns (assets) and owes (liabilities) at a single point in time, with the difference being shareholders' equity. The fundamental equation: Assets = Liabilities + Equity. The balance sheet answers: "What is the company's financial position right now?"
The Cash Flow Statement tracks actual cash moving in and out of the business across three activities: operating (cash from running the business), investing (cash from buying or selling assets), and financing (cash from debt or equity). It answers: "Where is the cash actually going?" Understanding all three together is the starting point - Board Infinity's guide on Introduction to Banking explains how banks and analysts use these statements to make credit and investment decisions. Understanding how these statements underpin personal investment decision-making is also covered in Board Infinity's Introduction to Goal Setting and Risk Profiling.
| Statement | What It Shows | Time Frame | Key Output |
|---|---|---|---|
| Income Statement | Revenue, expenses, profit | Over a period (Q or Y) | Net Income |
| Balance Sheet | Assets, liabilities, equity | At a point in time | Total Assets = Liabilities + Equity |
| Cash Flow Statement | Cash in and out by activity | Over a period (Q or Y) | Net Change in Cash |
The three statements are not independent - they are wired together. Net income from the Income Statement flows into retained earnings on the Balance Sheet. Net income is also the starting point of the Cash Flow Statement (indirect method). The ending cash balance on the Cash Flow Statement must equal the cash line on the Balance Sheet. If your model balances, these three connections hold automatically. If they don't, you have a formula error somewhere - always check these linkages first when debugging a model.
2. Excel Skills That Matter Most for Financial Analysts
Excel is the primary tool for financial modeling at most firms. Before building your first model, make sure you're confident with these specific functions and techniques - they appear in virtually every financial model. Strong Excel skills are among the most consistently assessed competencies in analyst interviews - whether at a Big Four firm or a bank. Board Infinity's KPMG Associate Consultant interview guide specifically highlights Excel modeling and financial analysis as core competencies tested in the hiring process.
// === LOOKUP FUNCTIONS === // INDEX-MATCH: more flexible than VLOOKUP - handles left-side lookups =INDEX(C2:C100, MATCH(A2, B2:B100, 0)) // Use: pull revenue figures from a data table by company name or year // XLOOKUP (Excel 365): even cleaner than INDEX-MATCH =XLOOKUP(A2, B2:B100, C2:C100, "Not Found") // === CONDITIONAL FUNCTIONS === // SUMIFS: sum values matching multiple criteria =SUMIFS(Revenue, Year, 2024, Region, "North") // Use: sum revenue only for specific year AND region // IF with nested logic =IF(Revenue>0, Revenue*GrossMargin%, 0) // Use: only calculate gross profit if revenue is positive // IFERROR: prevent #N/A or #DIV/0 errors breaking your model =IFERROR(NetIncome/Revenue, 0) // === FINANCIAL FUNCTIONS === // NPV: Net Present Value of future cash flows =NPV(DiscountRate, CashFlow_Y1:CashFlow_Y5) + InitialInvestment // Note: NPV() in Excel discounts from period 1, add Year 0 separately // IRR: Internal Rate of Return =IRR(CashFlows_Y0:CashFlows_Y5) // XNPV / XIRR: handles irregular date intervals (more accurate) =XNPV(DiscountRate, CashFlows, Dates) =XIRR(CashFlows, Dates) // === MODELING BEST PRACTICES === // Named ranges: replace cell references with readable names // Define: Formulas > Name Manager > New > Name: "RevenueGrowthRate" =Revenue_2024 * (1 + RevenueGrowthRate) // Absolute vs relative references =B5 * $B$2 // $B$2 is locked (absolute) - gross margin % assumption =B5 * B2 // B2 moves with the row (relative) - use for row-by-row calcs // Data validation for assumption inputs // Data > Data Validation > Decimal > Between 0 and 1 // Use: prevent accidental text entry in % assumption cells
Professional financial models use a consistent color convention so anyone reading the model instantly knows what they're looking at. The most common standard: blue text for hardcoded inputs (assumptions you type directly), black text for formulas that calculate from other cells, and green text for data linked from other sheets or external sources. If a blue cell contains a formula, or a black cell contains a hardcoded number, something is wrong. Apply this from your very first model - it makes reviewing, auditing, and updating models dramatically faster.
3. Building Your First 3-Statement Model
The 3-statement model is the foundation of almost every more complex model (LBO, DCF, M&A). It integrates the Income Statement, Balance Sheet, and Cash Flow Statement so that when you change one assumption - say, revenue growth rate - the effects cascade correctly through all three statements automatically. Understanding how data science is enhancing financial modelling can help you see how Python and ML are now extending these same 3-statement principles into predictive analytics - but Excel remains the starting point every analyst must master first.
The construction sequence matters: build in the right order or you'll create circular references that crash the model.
| Step | What You Build | Key Connections |
|---|---|---|
| Step 1 | Assumptions tab - all inputs in one place | Revenue growth %, margins, tax rate, capex % |
| Step 2 | Income Statement - revenue down to net income | Feeds net income to BS retained earnings and CFS |
| Step 3 | Supporting schedules (D&A, Capex, Debt) | Feed into IS (D&A expense) and BS (asset, debt balances) |
| Step 4 | Cash Flow Statement - operating, investing, financing | Ending cash flows to BS cash line |
| Step 5 | Balance Sheet - assets, liabilities, equity | Must balance: Assets = Liabilities + Equity |
| Step 6 | Check cell - confirms model balances | =IF(Assets=Liabilities+Equity,"OK","ERROR") |
// === INCOME STATEMENT (simplified) === Revenue = Prior_Year_Revenue * (1 + Revenue_Growth_Rate) COGS = Revenue * COGS_Percent Gross_Profit = Revenue - COGS Operating_Exp = Revenue * OpEx_Percent EBITDA = Gross_Profit - Operating_Exp DA_Expense = DA_Schedule!Total_DA // linked from D&A schedule EBIT = EBITDA - DA_Expense Interest_Expense = Debt_Schedule!Interest // linked from debt schedule EBT = EBIT - Interest_Expense Tax_Expense = EBT * Tax_Rate Net_Income = EBT - Tax_Expense // KEY OUTPUT - feeds CFS and BS // === CASH FLOW STATEMENT (indirect method) === // Operating Activities Net_Income = IS!Net_Income // starts with Net Income from IS Add_Back_DA = DA_Schedule!Total_DA // non-cash - add back Change_WorkingCapital = BS!Working_Capital_Change // increase in WC = cash outflow CFO = Net_Income + Add_Back_DA + Change_WorkingCapital // Investing Activities Capex = -(Revenue * Capex_Percent) // cash outflow - negative CFI = Capex // Financing Activities Debt_Repayment = Debt_Schedule!Repayment CFF = -Debt_Repayment Net_Change_Cash = CFO + CFI + CFF Ending_Cash = Beginning_Cash + Net_Change_Cash // must equal BS cash // === BALANCE SHEET CHECK === Check = IF(Total_Assets = Total_Liabilities + Total_Equity, "BALANCED", "ERROR") // If this shows ERROR, a linkage is broken - fix before proceeding
4. Revenue Forecasting Methods
Revenue is the most important assumption in any financial model because every other line item - costs, taxes, cash flow - flows from it. A small error in the revenue growth assumption compounds significantly over a 5-year forecast. Analysts use several forecasting approaches depending on the data available.
Growth rate method - apply a fixed or declining percentage growth rate to prior year revenue. Simplest approach, used when the business is relatively stable.
Driver-based forecasting - decompose revenue into its underlying drivers: units sold × average selling price, or number of customers × average revenue per user (ARPU). More accurate because you can model each driver independently. Preferred for SaaS, subscription businesses, and retail.
Top-down forecasting - start with total addressable market (TAM), apply a market share percentage, then apply an average selling price. Used in new-market or startup valuations. This approach mirrors how investment professionals think about market sizing and opportunity assessment - concepts covered in Board Infinity's Introduction to Equity Investing and A Deep Dive into Mutual Funds.
Bottom-up forecasting - build revenue from individual products, geographies, or sales team capacity. Most granular and most accurate, but requires more data
// SaaS Revenue = Starting Customers + New Customers - Churned Customers // Each customer paying Monthly Recurring Revenue (MRR) // === ASSUMPTIONS TAB (blue cells - hardcoded inputs) === Starting_Customers = 1000 New_Customers_Mo = 80 // new customers per month Churn_Rate = 2.5% // % of customers lost per month Avg_MRR_Per_Customer = $450 // average monthly revenue per customer MRR_Growth_Rate = 1.5% // MRR expansion from upgrades // === MONTHLY REVENUE SCHEDULE === // Row per month: Jan, Feb, Mar... Churned_Customers = Prior_Month_Customers * Churn_Rate End_Customers = Prior_Month_Customers + New_Customers_Mo - Churned_Customers MRR = End_Customers * Avg_MRR_Per_Customer * (1 + MRR_Growth_Rate) ARR = MRR * 12 // Annual Recurring Revenue // Annual Revenue = SUM of 12 monthly MRR values Annual_Revenue_Y1 = SUM(Jan_MRR:Dec_MRR) // === KEY SAAS METRICS === Net_Revenue_Retention = (End_MRR - Churned_MRR) / Start_MRR Customer_LTV = Avg_MRR_Per_Customer / Churn_Rate CAC_Payback_Months = CAC_Per_Customer / Avg_MRR_Per_Customer
One of the most common beginner mistakes: writing =B5 * 0.35 directly in a formula instead of referencing a named assumption cell. When you need to change the tax rate (and you always do in scenario analysis), you'll have to hunt through hundreds of cells to find every hardcoded 0.35. Instead, put all assumptions in a single dedicated tab, give them named ranges, and reference them consistently: =B5 * Tax_Rate. Changing one cell then updates the entire model. This is what separates professional models from amateur ones.
5. DCF Valuation: Walking Through Discounted Cash Flow
Discounted Cash Flow (DCF) valuation answers the question: "What is this company worth today, based on the cash it will generate in the future?" The core principle is the time value of money - a dollar today is worth more than a dollar in five years - so future cash flows are discounted back to present value using a discount rate. The risk dimension of discount rates - how required returns reflect investment risk - is covered in Board Infinity's Personal Finance and Investment Planning guide.
A DCF has three components: the free cash flow forecast (usually 5-10 years), the terminal value (the value of all cash flows beyond the forecast period), and the discount rate (WACC - weighted average cost of capital).
The most important Board Infinity resource for understanding how DCF is used in real analyst contexts: the Goldman Sachs GBM Private Summer Analyst interview guide covers exactly how Goldman analysts apply DCF and comparables in investment banking scenarios. For risk-focused analyst roles that also require stress-testing DCF outputs, Board Infinity's Goldman Sachs Finance Risk Analyst guide explains how scenario and sensitivity analysis is applied in a risk management context.
// === STEP 1: CALCULATE FREE CASH FLOW (FCF) === // FCF = EBIT * (1 - Tax Rate) + D&A - Capex - Change in Working Capital EBIT = [from Income Statement] Tax_Shield = EBIT * (1 - Tax_Rate) // NOPAT (tax-adjusted EBIT) Add_Back_DA = [from D&A Schedule] Less_Capex = -(Revenue * Capex_Percent) Less_WC_Change = -(Revenue_Change * WC_Percent) Unlevered_FCF = Tax_Shield + Add_Back_DA + Less_Capex + Less_WC_Change // === STEP 2: CALCULATE WACC (DISCOUNT RATE) === Cost_of_Equity = RiskFreeRate + Beta * EquityRiskPremium // CAPM Cost_of_Debt_AfterTax= Cost_of_Debt * (1 - Tax_Rate) Weight_Equity = Equity / (Equity + Debt) Weight_Debt = Debt / (Equity + Debt) WACC = (Weight_Equity * Cost_of_Equity) + (Weight_Debt * Cost_of_Debt_AfterTax) // Typical WACC range: 8-12% for most public companies // === STEP 3: DISCOUNT FCF TO PRESENT VALUE === PV_FCF_Y1 = FCF_Y1 / (1 + WACC)^1 PV_FCF_Y2 = FCF_Y2 / (1 + WACC)^2 PV_FCF_Y3 = FCF_Y3 / (1 + WACC)^3 PV_FCF_Y4 = FCF_Y4 / (1 + WACC)^4 PV_FCF_Y5 = FCF_Y5 / (1 + WACC)^5 Sum_PV_FCF = SUM(PV_FCF_Y1:PV_FCF_Y5) // === STEP 4: TERMINAL VALUE (Gordon Growth Model) === Terminal_Growth_Rate = 2.5% // long-run GDP growth - typically 2-3% Terminal_FCF = FCF_Y5 * (1 + Terminal_Growth_Rate) Terminal_Value = Terminal_FCF / (WACC - Terminal_Growth_Rate) PV_Terminal_Value = Terminal_Value / (1 + WACC)^5 // === STEP 5: ENTERPRISE AND EQUITY VALUE === Enterprise_Value = Sum_PV_FCF + PV_Terminal_Value Equity_Value = Enterprise_Value - Net_Debt Implied_Share_Price = Equity_Value / Shares_Outstanding
In most DCF models, the terminal value - the value of cash flows beyond your explicit forecast period - accounts for 60 to 80% of the total enterprise value. This means small changes to your terminal growth rate or WACC assumption have a disproportionate impact on your final valuation. Always sanity-check your terminal value by comparing the implied EV/EBITDA exit multiple to comparable companies. If your terminal value implies a 50x EBITDA multiple for a mature company, your growth rate assumption is too aggressive.
6. Sensitivity Analysis and Scenario Planning
A single-point estimate from a financial model is never the full picture. Every assumption has a range of plausible values. Sensitivity analysis quantifies how much your output (enterprise value, net income, free cash flow) changes when you vary one key input. Scenario planning combines multiple assumption changes into named cases - Base, Bull (optimistic), and Bear (pessimistic) - to show a range of outcomes. Understanding how sensitivity and scenario outputs feed into investment decision-making at institutional level connects directly to Board Infinity's guide on mastering investment banking - where scenario-based deal analysis is a core deliverable.
Sensitivity tables in Excel use the Data Table function to automatically recalculate your model across a grid of input combinations.
Scenario Manager (Data > What-If Analysis > Scenario Manager) stores named sets of assumption values so you can switch between them with one click.
// === 2-VARIABLE SENSITIVITY TABLE === // Goal: show Implied Share Price across a range of WACC and Terminal Growth Rate // Setup in Excel: // 1. In a blank area, place the formula you want to sensitize in the TOP-LEFT cell // e.g. =Implied_Share_Price (your DCF output cell) // 2. Down the LEFT COLUMN: range of WACC values (8%, 9%, 10%, 11%, 12%) // 3. Across the TOP ROW: range of Terminal Growth Rates (1.5%, 2%, 2.5%, 3%, 3.5%) // 4. Select the entire table range (including the formula cell) // 5. Data > What-If Analysis > Data Table // Row input cell: = your Terminal_Growth_Rate assumption cell // Column input cell: = your WACC assumption cell // 6. Excel populates the entire grid automatically // Output looks like: // 1.5% 2.0% 2.5% 3.0% 3.5% // 8.0% $42.1 $45.3 $49.2 $54.1 $60.8 // 9.0% $38.4 $40.9 $43.9 $47.6 $52.4 // 10.0% $35.1 $37.2 $39.7 $42.6 $46.3 // 11.0% $32.2 $33.9 $36.0 $38.4 $41.4 // 12.0% $29.6 $31.1 $32.8 $34.8 $37.3 // === BASE / BULL / BEAR SCENARIO SETUP === // In assumptions tab, add a scenario selector: Scenario = "Base" // change to "Bull" or "Bear" Revenue_Growth = IF(Scenario="Bull", 20%, IF(Scenario="Bear", 5%, 12%)) Gross_Margin = IF(Scenario="Bull", 65%, IF(Scenario="Bear", 55%, 60%)) WACC_Scenario = IF(Scenario="Bull", 9%, IF(Scenario="Bear", 12%, 10%)) // All model formulas reference these - changing one cell flips entire model
7. Common Financial Modeling Mistakes to Avoid
Most financial modeling errors fall into a small set of repeating patterns. Knowing these in advance will save you hours of debugging on your first real project. These same patterns are what senior analysts at firms like Goldman Sachs, Colliers, IndusInd Bank, and KPMG check first when reviewing a junior analyst's model - Board Infinity's IndusInd Bank multiple roles interview guide covers the financial analytical standards that banking institutions assess at every seniority level.
| Mistake | What Goes Wrong | How to Fix It |
|---|---|---|
| Hardcoded assumptions in formulas | Can't run scenarios - must edit hundreds of cells | Put all inputs in a dedicated Assumptions tab |
| No balance check | Model appears to work but has silent errors | Add =IF(Assets=Liab+Equity,"OK","ERROR") |
| Circular references | Excel iterates endlessly or crashes | Build supporting schedules to break debt/interest circle |
| Mixed absolute/relative references | Formulas copy incorrectly across rows/columns | Use F4 to toggle lock - test by copying formula before finalizing |
| Over-precise projections | False precision misleads decision-makers | Always show sensitivity/scenario ranges - not single-point outputs |
| No documentation of assumptions | No one (including you) understands where numbers came from | Add a Sources tab explaining each assumption's basis |
A model that crashes on opening is easy to fix. A model that silently produces wrong numbers because of a broken linkage or a misplaced absolute reference is far more dangerous - especially if it's being used to make investment decisions. Build a checks tab in every model: verify that the balance sheet balances, that cash flow matches your cash line, that your ratios are in reasonable ranges for the industry. If you're aiming for analyst roles like those at Goldman Sachs or Colliers, model integrity is the first thing a senior analyst checks - see Board Infinity's Colliers Financial Analyst guide for what reviewers look for.
Your Financial Modeling Learning Roadmap
Here is a practical sequence for building financial modeling skills from scratch to job-ready:
| Week | Focus | Deliverable |
|---|---|---|
| Week 1-2 | Financial statement reading - IS, BS, CFS of a real public company | Manually recreate 3 years of statements in Excel from a 10-K |
| Week 3-4 | Excel functions - SUMIFS, INDEX-MATCH, IFERROR, NPV, IRR | Complete 20 modeling-specific Excel exercises |
| Week 5-7 | Build a 3-statement model from scratch using historical data | Working integrated model that balances, with 3-year forecast |
| Week 8-9 | Add DCF valuation - FCF, WACC, terminal value, implied share price | Complete DCF output with sensitivity table |
| Week 10-12 | Scenario analysis, ratio analysis, presentation of results | Full model with Base/Bull/Bear + executive summary output page |
Further Reading
Board Infinity Guides:
- Colliers Financial Analyst - Real Estate Interview Guide
- Goldman Sachs GBM Private Summer Analyst Interview Guide
- Goldman Sachs GIR Summer Analyst Interview Guide
- Goldman Sachs Finance Risk Analyst Interview Guide
- Introduction to Banking: A Beginner's Essential Guide
- Personal Finance and Investment Planning
- Introduction to Equity Investing
- A Deep Dive into Mutual Funds
- Introduction to Goal Setting and Risk Profiling
- Mastering the Art of Investment Banking
- How Data Science in Financial Modelling Helps Businesses
- KPMG Associate Consultant Interview Guide
- IndusInd Bank Multiple Roles Interview Guide
External Resources:
- Corporate Finance Institute - Three Financial Statements
- Investopedia - Discounted Cash Flow (DCF) Explained
- Wall Street Prep - Financial Modeling Best Practices
Master Financial Analysis & Modeling Fundamentals on Coursera
This Coursera course by Board Infinity applies every concept in this guide through a structured, 16-hour curriculum. Build integrated financial models, master DCF and comparables valuation, run scenario and sensitivity analysis, and use Excel, Google Sheets, and Python - all through real, project-based learning.
✓ Certificate available · ✓ Self-paced · ✓ 16 hours of structured content
Conclusion
Financial modeling is a learnable skill - not a talent. It's built on a small number of well-understood principles: three statements, connected by defined linkages, driven by explicit assumptions, validated through scenario and sensitivity analysis. Excel is the medium, but the thinking is the skill. Anyone who understands how the statements connect, what drives revenue and margins, and how cash flows translate to value can build a solid financial model.
The most important discipline to develop early: keep assumptions separate, keep formulas clean, and always build a balance check. These habits take five extra minutes when you start a model and save you hours of debugging when something breaks. They're also what separates a model a senior analyst trusts from one they have to rebuild from scratch.
From here, the natural progression is ratio analysis to diagnose financial health, Python and Power Query to automate data collection, and DCF refinements like multi-stage growth models and sector-specific adjustments. Board Infinity's course on financial analysis and modeling fundamentals covers every one of these topics through a structured, real-project curriculum - a direct path from the concepts in this guide to job-ready modeling skills.