Modelling tips and tricks: Care with modifying numbers
Welcome to our series of applied tips and tricks in Excel for accountants and financial analysts wherever you may be. Liam Bastick looks at the dangers of rounding numbers “the wrong way”.
Often when building even the simplest financial model, unit conversion becomes necessary. For example, your business may sell 437,911 widgets at a unit price of £12.50 each. However, in your output summary sheet for senior management, do you really wish to show that the revenue earned is £5,473,888 or is it more likely that you will display this figure as £5.5m?
Sound familiar? If the latter idea strikes accord, you may be in danger of making a mistake in your additions. As a model auditor with c.30 years’ experience, I note that most modellers will modify their number with a calculation such as
Yes, cell references may be used instead and the million factor may be a range name, but in essence, the value is derived by dividing the product of volume and unit sales price by one million.
It’s just so much easier to read and basically so much more dangerous.
Let me explain: output sheets are usually dashboards of various key factors that management requires in order to make informed decisions, eg
The problem is all sorts of different figures may appear on such an output. For example, key outputs could include:
- Unit prices (in £’s)
- Overall profitability (in £m)
- Internal Rate of Return (%)
- Payback date (date)
- Asset turnover (multiple)
- Other outputs (MWh, GBytes, KHz)
I hope that modellers are not adding percentages to KHz (not much I can do about that), but it is common for modellers to confuse financial data, eg
=£5.5m-£3.76k = £1.74??
Not only have I no idea of units, the answer should be £5,470,128. If you don’t think you have ever made that mistake, are you sure you had the units right for your accounting ratio or debt credit metric? It can be a scary thought.
This issue is easily overcome. We need to remember from our example that £5.5m is actually £5,473,888 and we should make the number appear to be £5.5m (by formatting) rather than actually be 5.5 (by modifying).
To do this, we use number formatting (CTRL+1) on the calculation:
In the above illustration, the unit price has simply been multiplied by the volume. Then, the resulting product has been custom number formatted as displayed in the dialog box:
This may not be immediately obvious. Time for a crash course in hieroglyphics:
- £ is the currency symbol. Excel recognises various symbols, including the euro, the dollar and the yen
- # means put a number (if necessary)
- 0 means put a number whether it’s necessary or not, eg the number format “000” used on the number 7 would provide you with a Licence to Kill: 007
- , (comma) is the thousands separator and serves two purposes. It puts a comma in at the thousand/million/billion mark when the syntax “#,###” or “0,000” etc is used, but it also divides by a thousand each time it is entered after the number formatting so “0,” will be in thousands, “##,” will be in millions and so on
- . (decimal point/period) is the decimal point
- “m” – whatever is inserted inside inverted commas is added as text to the number format.
Therefore, £#,##0.0,,“m” will display numbers in £m to one decimal place (ie to the nearest hundred thousand) but the underlying number will still be 5,473,888 and hence avoid a common mistake in financial modelling.
This is particularly beneficial if your model forecasts data in units such as MW, GWh, $, $m and so on. You will never end up adding or subtracting two numbers of different orders of magnitude again.
Word to the wise
Before everyone writes in and comes up with 34,380 (should that be 34.4k?) reasons why a calculation should be divided by a conversion factor rather than just given the appearance it has been, I do recognise sometimes this needs to be done. My point is, often it does not and using a divisor is fraught with risks – so why not avoid the issue?
You might also be interested in
Recognised by Microsoft as one of 104 Most Valuable Professionals (MVPs) in Excel worldwide by Microsoft, Liam has over 30 years’ experience in financial model development/auditing, valuations, M&A, strategy, training and consultancy. He has headed Ernst & Young’s modelling team in Melbourne and was an Assistant Director in their...