Course · Personal Finance

Financial Modeling in Excel from Scratch

A hands-on course in financial modeling: model structure and best practices, how the P&L, balance sheet, and cash flow statement link, revenue and costs from drivers, the time value of money (PV, FV, NPV, IRR, PMT — including the NPV timing trap), DCF valuation basics, and sensitivity and scenario analysis — with interactive exercises where you type real Excel formulas. Not investment advice.

Course authors

PepelenCourse author

0 / 18 lessons done

New lesson →

What a financial model is and how to build one

What a model is for: the “assumptions → calculations → outputs” logic

7 exercises · Explain that a financial model is a structured calculation of the future based on assumptions, and tell apart the model's three layers: input data, calculations, and outputs.

Best practices: no hardcoding, references to inputs, consistency, color coding

6 exercises · Apply the basic rules of model hygiene: put every number in an assumption cell and reference it in formulas instead of hardcoding constants.

Common beginner mistakes and how to avoid them

7 exercises · Recognize common mistakes in models (hardcoding, circular references, inconsistent formulas, undocumented assumptions) and suggest a fix.

The three financial statements and how they link

The P&L, the balance sheet, and the cash flow statement: what each one shows

7 exercises · Tell apart the purpose of the three statements and their core equations: the P&L (revenue − expenses = profit), the balance sheet (assets = liabilities + equity), and cash flow (cash inflows/outflows).

How the statements link: net income, retained earnings, and the cash balance

7 exercises · Trace how the statements link: net income from the P&L flows into retained earnings on the balance sheet and to the top of the indirect cash flow statement, and the ending cash balance on the cash flow statement equals the cash on the balance sheet.

Profit ≠ cash: accrual vs. cash, and diagnosing the gap

6 exercises · Explain why a profitable company can run out of cash, telling apart the accrual basis and actual cash flow.

Revenue, costs, and drivers

Revenue: price × volume and a driver-based model

7 exercises · Build a revenue forecast in two ways — as price × quantity and from drivers (customers × average order value × frequency) — and enter the matching formula.

Fixed and variable costs, and contribution margin

7 exercises · Separate fixed and variable costs and calculate the contribution margin and contribution margin ratio with the formula (price − variable cost per unit).

Depreciation and working capital: the effect on cash

7 exercises · Explain how depreciation (a non-cash expense) and working capital (inventory, receivables, payables) affect profit and cash flow.

The time value of money

Future value and present value: FV and PV

7 exercises · Apply the idea that “money today is worth more than money tomorrow” by calculating future value FV=PV·(1+r)^n and present value PV=FV/(1+r)^n, including with the FV and PV functions.

NPV and IRR: the NPV>0 rule and the first-cash-flow timing trap

7 exercises · Calculate net present value and the internal rate of return with the NPV and IRR functions, apply the NPV>0 rule, and avoid the initial-investment trap.

The PMT annuity payment and choosing among the financial functions

7 exercises · Calculate an annuity loan payment with the PMT function, and deliberately choose the right function among PV/FV/NPV/IRR/PMT for the task.

DCF valuation basics

Forecasting free cash flow

7 exercises · Explain that DCF values a business based on its future free cash flows, and understand what makes up FCF at a basic level.

Discounting, the discount rate (WACC), and terminal value

7 exercises · Assemble a DCF valuation from its parts: discount the forecast cash flows at a rate (conceptually, WACC) and add the terminal value.

The limits of DCF and its dependence on assumptions

5 exercises · Critically assess a DCF result, understanding its sensitivity to assumptions and the “garbage in — garbage out” principle.

Sensitivity, scenarios, and model audit

Data tables: sensitivity to one and two variables

7 exercises · Build a sensitivity analysis with data tables (one and two variables) to see how the result depends on a key assumption.

Scenarios: base, upside, and downside

7 exercises · Build and compare internally consistent scenarios (base/upside/downside) by changing several assumptions at once.

Model audit and capstone case: justify your investment conclusion

6 exercises · Run an audit of your own model for typical mistakes, and justify an investment conclusion in writing with a self-check.