By Tim Heng
This article is based on a tongue-in-cheek blog posted on Corality’s website in April. We will look at an edited version of the blog, and dig deeper into what appears, at face value, to be intuitive and logical.
A “Simplified” Modeling Style?
April 1, 2013
The modern financial model has become bloated—often 10MB or greater in size—often taking up unnecessary space and creating problems transmitting large files between parties. With the start of the new Japanese financial year, this is an opportune time to introduce our latest innovation and encourage companies still using Excel 2003 to upgrade and harness the potential of Excel 2007 and onwards.
This modeling style focuses on removing the unnecessary distractions in a model. We calculate only what is necessary to achieve the business requirements at hand, utilizing the increased processing ability of Excel 2007.
Smaller files reduce the cost of data storage. They also prevent email transmission issues associated with sending huge models between parties in a transaction environment. As a result, smaller files can reduce corporate overheads and streamline the flow of information.
Removing excess calculations from a worksheet allows users to focus on the important information instead of being distracted by extraneous information. This reduces the time required by users to find relevant information in the model.
Model audit costs are typically calculated on the number of unique formulae in a model, with an adjustment for formula complexity. Lower numbers of unique formulae will therefore result in reduced audit expenses, lowering corporate and transaction costs.
“Best” Practice Modeling
As you read these extracts, I’m sure you have been nodding in agreement. Transparent presentation of key requirements; smaller file size; reduced numbers of unique formulae—these are all key characteristics of a good financial model. While this process of simplification and transparency holds for the model at large, it must also apply to the formulae within.
If you try to achieve too much in a single formula, it becomes less clear, and the lack of transparency makes it very easy for an error to be introduced. Let’s look at some of the best practice modeling flaws that this simple model contravenes.
Transparent Financial Modeling
The general principle of model transparency is that the architecture of a model, and the calculations comprising it, must be easily understood and checked. This must hold true for all stakeholders—model developers, model owners/users and model auditors/reviewers.
I use the word “developer” as a throwback to the previous article I wrote for CompAct in relation to Treating Excel as an IT system. In the article, I highlighted the importance of treating the development of an Excel spreadsheet in much the same way that an IT developer would treat an IT system. As part of the development process, steps in the calculation process should be identified to get to the desired outcome. Developers or model builders should be able to review their work and clearly identify each step, so they can be matched back to the requirements specified in their scope of work.
Model owners and users require a different type of transparency. They need to understand what the model is doing in a general, high-level sense. As a user, they need to be able to look at a page and intuitively understand how a number is derived. This way, they can spend productive time questioning how to change X inputs to get Y results, rather than questioning how Y is calculated.
Finally, model auditors and reviewers have their own view on transparency, especially relating to the architectural elements of a model. In many respects, their view is very similar to the model user’s perspective, in that items that are intuitively understood will speed up the review process. We will go into more detail on this below.
Modeling Architecture and Calculations
I’ll be honest—I built this formula, and I struggle to understand what it is doing. It manages to avoid using nested “IF” statements, but at the same time, it uses up to eight bracket nests to get the result. That being said, no individual part of the formula is particularly difficult to understand. There are no complex or user-defined functions—the ‘hardest’ function in the formula is a LOOKUP or ROUNDUP function. The struggle in understanding is in the impenetrable wall of text that it represents.
The calculation involves the following steps:
- Bring in inputs for mining forecasts relating to the ore dug out of the ground, and the characteristics of said ore.
- Calculate the revenue associated, based on a price forecast profile.
- Determine the fixed and variable costs associated with the production.
- Work out the impact of debtor and creditor timing.
- Calculate the depreciation profile based on a capital expenditure program to get to the EBIT line in a P&L.
- Work out annuity-style repayments on debt borrowed to fund the capital expenditure to get the debt servicing cash flows and the interest expense.
- Calculate tax payable on the profit before tax.
- Finally, determine how much free cash flow is available for equity distributions.
The calculation also involves using the following intermediary line items:
- The number of quarters of mining operations undertaken in each period displayed
- How many years have passed since the start of operations
- An inflation factor
- Days in each period
- The calendar year.
Now, wouldn’t you agree it is much more transparent having a separate line for each of these items?
Broadly speaking, it’s the difference between taking an approach that says “net cash flow is Y, therefore IRR is X percent,” and one that says “we are receiving dividends of A, capital returns of B, and making investments of C, which result in an IRR of X percent.” The principles of transparency are that if we can make it clearer how we derive our results, we should do so. This not only aids model developers in being able to match back their work to the business scope, but also assists model users in cutting through the calculation mechanics and looking at outputs intuitively.
Model Review—How About the Auditors?
I mentioned earlier that I would come back to how the model auditors view “transparency.” From a high level, they rely on intuition as much as the model users do. The ability to look at a series of calculations and understand the general “flow” is crucial to getting the “big picture” understanding of what a model is doing.
However, model auditors are also faced with the challenge of interrogating individual cells to determine if an item has been calculated correctly. As a result, items that might seem to be intuitively straightforward may need to be broken down into several component steps in order to achieve “transparency.” For example, instead of calculating a dividend payment of X, it could be calculated by:
- Identifying a firm’s dividend policy in relation to the payout of earnings
- Testing a firm’s capability to repay (cash flow and existing cash balance)
- Checking for restrictions in repayments due to debt covenants or regulations (e.g., debt service coverage ratio breaches, net asset/insolvency tests).
Consider these items—these fairly intuitive calculation steps easily demonstrate how much a firm would pay in dividends. We must keep drilling deeper into a model, and testing the intuitive nature of our calculations, if we are to pursue the best practice goal of transparency.
It is important to note at this stage that while I have used financial modeling examples to illustrate the concept of transparency, the same principles apply to any form of spreadsheet modeling. Whether you are calculating policy premiums, managing investment income or determining the amount of oil in a field, the principles of transparency can only be beneficial for stakeholders and reduce the risk of error.
Model Development—Back to the Problem
It’s worth noting that the risk to the developer doesn’t stop when the model is signed off by the business, the auditor, and subsequent delivery. If something needs to be revisited (such as model changes, additions or bug fixes), the lack of an intuitive, transparent approach can increase the time required to amend the model.
It is telling that many complaints about Excel are in relation to spreadsheets that were built by “someone who left the company.” I don’t think this is due to users looking for scapegoats or other ways out of trouble. The majority of spreadsheets have been through multiple owners’ and developers’ hands, and the lack of an intuitive architecture and framework will lead to a model being “re-learned” by each person whose hands it passes through.
The tragedy isn’t in the number of hours and days that this wastes; rather, that so many people struggle with a spreadsheet that is hard to comprehend, instead of rebuilding it into something that they (and their successors) do understand.
Final Observations
We learn early on in school that we should take the time to lay out our calculations step by step. We divide both sides by two, then we simplify. We multiply top and bottom by X, then we simplify. We are incentivized every step of the way by being given staged progress marks for presenting our “working out,” even if we get the result wrong. Our teachers can identify what elements of the calculation we’ve done correctly and reward us for those steps.
Somewhere along the way, spreadsheet modelers have cast aside those teachings in favor of the faster, “no workings” approach, which ignores transparency in pursuit of a quicker result. As in school, this approach not only increases the risk of an error occurring, it drastically increases the risk that the error isn’t identified when the calculation is checked. By the time the error is spotted, it’s usually too late to make any changes, and the penalties and consequences could be costly. In school, those penalties could mean losing marks and/or failing an exam. In the real world, the consequences can and will be a lot worse.
If we go back to our roots and focus on this best practice modeling principle of transparency, we can identify and catch errors in the development and review stages, reducing the risk and avoiding the consequences.
Tim Heng is an associate at Corality. He can be contacted at tim.heng@corality.com.