← Financial Modeling in Excel from Scratch
Lesson
Model audit and capstone case: justify your investment conclusion
Run an audit of your own model for typical mistakes, and justify an investment conclusion in writing with a self-check.
The model audit checklist and the logic of an investment conclusion
The model audit checklist and the logic of an investment conclusion
A financial model is a chain: assumptions → calculations → result → stress test → conclusion. A mistake in any link distorts the outcome. A model audit is a systematic check of every link before you present the results.
The core audit checklist: (1) No hardcoding: assumptions sit in an inputs block, and formulas reference cells instead of hardcoded numbers like *1.05. (2) Consistent formulas across a row: if B5 is =B3*B4, C5 must be =C3*C4. (3) No unintended circular references; they're fine only in deliberately iterative models (e.g., interest on the debt balance).
(4) The statements tie out: ending cash on the cash flow statement equals Cash on the balance sheet; retained earnings = prior balance + net income − dividends. (5) Assumptions are documented and realistic: each key figure's source is next to its cell or on the Assumptions sheet. (6) NPV timing: the time-0 investment is added outside the NPV function: =NPV(rate,cash_flows)+time0.
The capstone case brings together every topic of the course: first you build the assumptions and the cash flow forecast, then calculate NPV and IRR, run sensitivity analysis and scenarios, and only then state an investment conclusion — “accept” or “reject” — with a justification and a self-check. Disclaimer: all case studies in this course are for educational purposes only and are not investment advice.
Lesson notes
The model audit checklist and the logic of an investment conclusion
A financial model is a chain: assumptions → calculations → result → stress test → conclusion. A mistake in any link distorts the outcome. A model audit is a systematic check of every link before you present the results.
The core audit checklist: (1) No hardcoding: assumptions sit in an inputs block, and formulas reference cells instead of hardcoded numbers like *1.05. (2) Consistent formulas across a row: if B5 is =B3*B4, C5 must be =C3*C4. (3) No unintended circular references; they're fine only in deliberately iterative models (e.g., interest on the debt balance).
(4) The statements tie out: ending cash on the cash flow statement equals Cash on the balance sheet; retained earnings = prior balance + net income − dividends. (5) Assumptions are documented and realistic: each key figure's source is next to its cell or on the Assumptions sheet. (6) NPV timing: the time-0 investment is added outside the NPV function: =NPV(rate,cash_flows)+time0.
The capstone case brings together every topic of the course: first you build the assumptions and the cash flow forecast, then calculate NPV and IRR, run sensitivity analysis and scenarios, and only then state an investment conclusion — “accept” or “reject” — with a justification and a self-check. Disclaimer: all case studies in this course are for educational purposes only and are not investment advice.