by Matthew Schott, ICMA (Australia)
We are all familiar with the Nike Air More Uptempo accounting term “substance over form”. It defines the importance of presenting the economic substance over the legal form in financial statements.
I prefer the term “substance and format” for management accounting purposes. As management accountants we need to provide information that helps managers with decision making, and how this is formatted is vital. Consistency and clarity of format are fundamental to providing information that managers can trust and comprehend to add value. In effect, for the economic substance to be understood by managers, whether financial or non-financial, the format must be right.
Delivering consistent and clear formatting can involve transforming data in Excel, often from multiple sources. These are some of my preferred methods for achieving these aims in Excel management reports with a couple of common issues I have found in the field and preferred ways of treating them.
Firstly, the most common issue I encounter is when the base data is in $ units and for presentation it is transformed into $ thousands (or $ millions etc). A common way to treat this is to use the Paste Special > Divide function. This Nike Air More Uptempo is quick and simple but poses a couple of issues:
- It changes all data that has been selected and pasted over causing SUM functions to all divide again, so in effect double division has occurred for totals.
- The base data has lost integrity as it has been manipulated to contain a new number. Note that where the original data was simply a number, the new number will show no traces of manipulation and this can cause confusion later.
The preferred way to change the data and overcome these issues is to use the Format method as follows:
- Select range > Right click Format Cells > Number tab > Select Custom from list > place cursor in “Type:” box
- Using the example above ($ units into $ thousands) we would type the custom code: “$#,” each comma effectively removes”000s”, so a second comma would turn $ thousands to $ millions. To add two decimals use “$#.##,”.
Note: the structure of custom code is as follows:
positive numbers; negative numbers; zeroes; text.
So when you are building a custom format remember to include rules on all necessary components, not just positive numbers. There are many rules on syntax for custom formats Google “Excel custom format syntax”, and you will find many resources explaining them including how to change the colour for numbers e.g. red for negative.
Benefits of the Format method are that the Nike Air More Uptempo base data is not altered, therefore:
- The formatting has only changed the presentation layer to make the information clearer by removing detail. This leaves an audit trail of the original source data and no chance for error.
- The data will sum correctly every time as the base data integrity has been maintained.
Secondly, a common change made to Excel data is to switch the sign of the data. For example, accounting system data where revenue is a credit (a negative when exported to Excel) and it is changed into a positive number for management with the same change needed for expenses. Again there are a few ways to approach this sign switch issue. You could simply go to each cell to make a sign change which is time consuming and error prone.
Alternatively, use the custom format code approach discussed above by building a code that makes positive numbers appear negative and vice versa. I think this approach is a good option depending on the data users
and audience, but it can be cumbersome to switch views depending on the circumstances.
Another way could be to use the Paste Special > Multiply function by copying a cell that contains “-1” and pasting it to all cells. But the data will be permanently altered, which is not ideal as discussed, so I commonly use a variation of this approach, as follows:
- Enter “-1” into a cell somewhere outside of the Nike Air More Uptempo range you want to manipulate e.g. A1.
- Then link this cell to another cell with an anchor e.g Cell A2 = $A$1
- Now Copy Cell A2 > Paste Special Multiply to the base data, except for SUM or other functions in the range.
The benefits of this approach are that you now have a dynamic cell A1 that allows you to switch the view from positive to negative just by changing the number contained. The drawback is the need to avoid pasting this into cells with functions such as SUM as these will also be affected and cause errors.
This technique is, however, a simple way to change data consistently and can be used in other ways for different objectives. To add sensitivity to the range for example, you could input 1.1 for a 10 per cent increase in all data.
You will also see the cells pasted to will have the original number multiplied by the anchor cell [e.g. $100,000 * ($A$1)] which allows for checks against source data.
The techniques discussed may seem inconsistent, but to provide information that is clear and consistent often involves using different methods that allow for accuracy and flexibility depending on the situation. http://www.scarpe2016jordan.it/
Tips and practical hints for this column are welcome, please send your ideas to the editor at [email protected]
