Free lesson of "Financial Modelling in Spreadsheets" by Jonas Lindqvist, 9 min
A model that only its author understands is a liability. The cure is structure. Split the workbook into four areas: an inputs sheet where every assumption lives, calculation sheets that only contain formulas, an output sheet with the summary and charts, and a checks sheet. Information flows one way, from inputs to calculations to outputs.
Follow three rules. First, never type a number inside a formula. A formula such as revenue times 1.08 hides the growth rate, and when it changes you will not find all the places. Put 8 percent on the inputs sheet and refer to it. Second, one row, one formula, copied across all columns. If a row has a different formula in the middle, it is a trap, so mark any exception with a colour and a note. Third, keep time in columns, always in the same order, with months on the left and yearly totals on a separate block.
Use a colour code that everyone learns in a minute: blue text for inputs, black for formulas, green for links to other sheets. Add units to every row label, such as EUR thousand or customers, and keep units constant. A model with mixed euros and thousands gives errors that are hard to see.
Name your sheets clearly and put a short cover page with the purpose, version, author and date. Save a new version before a big change, with the date in the file name.
Task: take a model you use and list every number typed inside a formula. Move at least five of them to an inputs sheet.
This was the first of 12 lessons. The full course is €219, with a certificate at the end.
Sales open soon