Financial Modeling Discipline – Never Use Magic Numbers
A good financial analyst never uses “magic numbers” in forming calculation formulas when performing financial modeling; always make implicit assumptions explicit.
Instead of using numbers in a formula, use the “name” of the number. For example in the case of exchange rate conversion:
- State clearly in a separate assumptions section the value of the number
- Name the assumption cell (e.g. EUR_USD_exrate).
- Use the name in a formula, e.g. =sales * EUR_USD_exrate.
However, this rule does have one exception: the constant 1 is allowed for growth, and date series, for instance:
- = ( 1 + growth ) * previous_years_sales
- = last_year + 1 where last_year is a relative reference to the values of the previous year.
If you're new to Financial Modeling Guide, you may want to join our sister site, the Finance 3.0 network, to enroll in online financial training programs, ask questions, attend seminars, connect with others and download free finance spreadsheet templates about financial modeling, financial management, corporate finance & valuation, quantitative finance and accounting.
Over 65,000 finance professionals, entrepreneurs, analysts and investors from 195 countries have benefited.
Don't miss out on this outstanding opportunity for high quality, globally recognized financial education: Sign Up For Free Now!
Hmmmm, well this advice can easily be debated by professionals who have been at this a long time.
First off, correct, no reasonable debate on avoiding commercially relevant ‘magic numbers’ (technical name: embedded constants).
However, do not conflate requirement to make inputs explicit (good advice) with use of Excel Names — the two have no bearing, and one certainly does not need Names to achieve the primary objective. Using Names is very debatable, as there are legions of problems with them. See http://www.fast-modeling.net/fast/Excel_Names
As well, to say there is ‘one’ exception to embedded constants (the 1 in your example) may be a bit brave. There are probably dozens of instances that would be seen as acceptable, and will vary under the circumstances. Do I need “Days in LIBOR year” explicit, e.g. 360 or 365? Yes, probably good idea. However, “Hours in Earth day” = 24, so I can change to 40 or whatever when we move business to Mars? Not obvious and the 24 will generally be easily recognized in the formula for what it is.
http://www.fast-modeling.net/fast/Formula_design:_Detailed_points
See Point No. 9 with Link to Financial Mechanics blog, “Are hard-wired constants always bad?”
All good stuff. Keep up the good work,
John Richter
Financial Mechanics