Financial Modeling Techniques PDF

BM2109 FINANCIAL MODELING TECHNIQUES Error Avoidance Strategies There are certain strategies a modeler can employ during the build process that w...

BM2109 FINANCIAL MODELING TECHNIQUES Error Avoidance Strategies There are certain strategies a modeler can employ during the build process that will help identify errors as they occur. Even extremely skilled modelers make mistakes all the time. However, a good and experienced modeler will pick up these errors as they happen and avoid situations where errors are likely to happen. A modeler may encounter two (2) general errors: the formula and logic errors. Given below are some important strategies for avoiding them (Fairhurst, 2019). a. Avoiding simple formula errors. Simple formula mistakes are the easiest to avoid and the most embarrassing when they are not picked up. It commonly involves picking up the wrong cell or putting a currency sign in the wrong place. Given below are some strategies that one may follow to avoid formula errors (Fairhurst, 2019): o Use the “Enter” key. Many Excel users get into the bad habit of clicking elsewhere on the page after typing a formula. This is a dangerous practice. Instead, a modeler must always hit the Enter key when he/she is finished with a formula. Given below is an example of an error committed by clicking elsewhere. Figure 1. Clicking Elsewhere Instead of Using the Enter Source: Using Excel for Business and Financial Modelling (2020), pg. 82 As can be observed from the above figure, it is easy to accidentally pick up the wrong formula if the cursor happens to be in the wrong place. Also, it can be seen that the modeler is moving on to another cell without thinking about the formula that has just been written. To avoid the said errors, a modeler must deliberately type the formula. Once finished, the modeler must hit the Enter key. o Check the work and use the “F2” shortcut key. The worst errors are that the modeler is unaware of. These errors can be minimized through constant rough checks on the results of the formulas upon building the model. Every time a modeler hits the Enter key, he/she must check the result. It must be noted that most modeling is trial and error; thus, it is okay to make mistakes. However, one must make sure to pick up those mistakes before someone else does. The only thing worse than finding an error in a model is not finding the error in the model, and thus, the model is wrong. There is no faster way to lose credibility as a modeler or analyst than to have one’s mistakes pointed out in public. Clicking in the cell or hitting the F2 key will also show the source cells or precedents of a formula. This is most useful if the source cells are nearby and on the same sheet. When copying down all the cells in a block of data, do a spot check with the F2 key to make sure it’s picking up the correct cells. Note that this shortcut now works in Excel for Mac, or one can also use the Control + U shortcut on a Mac as well. 03 Handout 1 *Property of STI  [email protected] Page 1 of 6  BM2109 o Have someone else check the work. Once the model has been constantly checked, it must be good to let someone audit or review it to bring a fresh perspective about the model. Sometimes, when a modeler looks at his/her model for so long, he/she can’t see the glaring error on it. Working in pairs can be very effective. However, if the model is high-profile or critical to the business, it might be worth getting it audited by a model audit firm. Many organizations specialize in conducting professional model audits. This is the best way to be confident that there are no errors and that the model is working correctly. o Conduct stress testing. Test the technical workings of the model by stress testing it (i.e., varying the inputs to see how much the outputs change). Insert some ridiculous numbers into the inputs and see if the results are as expected. The following are some stress tests one may perform: ▪ Set inputs to zero and check that the outputs respond as expected. For example, by setting the price to zero, one would expect revenue also to be zero. ▪ Double your headcount. Does your staff cost roughly double? ▪ Try setting the indexation percentage to zero and see if the costs remain flat. ▪ If one doubles the pricing, does gross revenue double as well? If not, why not? The answer could be due to the effect of discounting or a complex pricing structure. One must make sure to get to the bottom of any anomalies and discrepancies. ▪ Chart metrics on a line graph such as cost per head, price per unit, cost to serve a customer, or other metrics relevant to your business. Look at the curve and make sure that every trend, spike, and downturn will be explained. b. Avoiding logic errors. Compared to formula mistakes, logic errors can be more difficult to spot. They include incorrect timing, inserting the wrong inputs and source data assumptions, and using pre-tax instead of post-tax inputs, to name a few. Sometimes the mistakes can be a combination of formula and logic errors, and we need to guard against both types of errors diligently. o Validate the assumptions. A model is only as good as the accuracy of the assumptions. The phrase “garbage in, garbage out” has never been more relevant than in the context of financial modeling. Even the best-designed model will be completely worthless if the assumptions that go into it are not validated. This is one of the most important points of financial modeling best practice. Important decisions are made based on the model's outputs, and it is critical to list clearly and carefully the assumptions that have gone into the model. While a good model can significantly aid one’s business or decision-making process, it is important to remember that models are only as good as the data they contain; thus, the answer they produce should most certainly not be taken at face value. When presented with a model, a smart decision-maker will query all the assumptions in the model and the way it is built. The role of the financial modeler is to ensure that all the assumptions have been validated. He/She must document clearly where the numbers came from so that there can be no possible misinterpretation of the assumptions. The assumptions can then be revisited and possibly revised at a later date. o Document the methodology. The modeler must document the inflows and outflows of data within the model using a flowchart format. This helps to check your logic and is useful in explaining the methodology of your model to other people. For example, if one has a petshop, he/she can calculate the number of customers by determining the total population within the community. Then multiply it to the percentage of pet owners. Then multiply the number of pet owners with a take-up rate to get the number of possible customers. 03 Handout 1 *Property of STI  [email protected] Page 2 of 6  BM2109 o Use models in the right context. Some managers treat models as though they can produce the answer to all their business decisions and solve all their business problems. Sometimes, it is terrifying to see the blind faith that many managers have in their financial models. Anyone with any real experience in model building will appreciate how incredibly easy it is to get a dollar sign in the wrong place and end up with the wrong result. Models should be used as one tool in the decision-making process rather than the definitive solution. Building Error Checks Basic reconciliations can be built within a model. As much as possible, a well-built financial model should have error checks included. It is to allow users or modelers to see at a glance if the formulas are calculating correctly. For example, when creating management reports, the modeler may check the sum of each department’s report adds to the company-wide total. This can be done by inserting a simple IF function or several other methods. However, it must be noted that that error checks are not substitutes for good practices such as checking and auditing the formulas. Error checks are most appropriate for capturing errors a subsequent user has made; they are less likely to highlight a model-building mistake (Fairhurst, 2019). Illustrative Example: Let’s say that a modeler has a stock of cars in a showroom. He wanted to know how many of each type of transmission he has in stock. He created a SUMIF formula to summarize how many of each type are in stock as shown in the figure below: Figure 2: Stock Report Source: Using Excel for Business and Financial Modelling (2020), pg. 82 If someone updates the model and accidentally types “Autmatic” in cell B3 instead of Automatic, the totals at the bottom will be wrong. In cell C18, he can create an error checking formula that will alert if something like this happens. 03 Handout 1 *Property of STI  [email protected] Page 3 of 6  BM2109 There are two (2) different formulas the modeler may put in cell C18 that will alert him if the model is improperly balanced: =C𝟏𝟕=C𝟏𝟑 will return the value TRUE if they are the same, or FALSE if not. However, this may also be subjected to a false error. =C𝟏𝟕 − C𝟏𝟑 is the easiest formula to build that would serve as an error check, as it would return a value in the case of an error. Although this would not necessarily alert the user immediately that an error had been made, it is certainly quick and easy to follow. For this reason, a fairly common error check is favored by many modelers. It is good to format it using the Comma Style (found on the Home tab in the Numbers group) and then remove the decimal place and format it to a red font. This will mean that the zero (0) will not show if there is no error, and a red number will show an error. Allowing Tolerance for Error Sometimes, when one checks the equality of values, they get a false error result even though the values are the same. This is a bug caused by the fact that Excel carries calculations to 14 decimal places. After that, it truncates the value and can cause a minute discrepancy, which will report an error when it’s only 0.00000000000001 off. In the above illustrative example, one may also use the formula =IF(C𝟏𝟕C𝟏𝟑,"error",𝟎) for checking. It is a superior formula, but on a small number of occasions, there can be an issue with it. The formula usually works, but it can occasionally return a false error result. To avoid this potential issue, one could use an absolute value (ABS) formula to allow tolerance for error. The ABS formula takes the absolute value of the result; thus, it does not matter if it is a positive or a negative number. In the above case, the modeler may use the formula =IF(ABS(C𝟏𝟕-C𝟏𝟑)> 𝟎.𝟏, "error", 𝟎). It will allow the values to be off by 0.1 before it reports an error. Adding Error Check Alerts One of the ways to detect errors is to add a checking page at the very back of the model that links through to all the error checks in the entire model. This page can be hidden, as the user does not need to see it, and the modeler can unhide it if necessary. To do so, one may create a summary cell that will identify, in a single cell, whether or not there are any errors in the entire model. This can be achieved with a COUNTIF formula. The COUNTIF function returns a certain value if there are errors. For example, the modeler in the above problem is building an error check alert by opening a new sheet and entering =COUNTIF($A$𝟑∶$A$𝟐𝟏,"error"). He may nest the said formula with an IF statement to show the text “Errors exist within this model.” if any of the error checks have been triggered. Thus, the formula can be expressed as follows: =IF(COUNTIF($A$𝟑∶$A$𝟐𝟏,"error")>𝟎,"Errors exist within this model.",𝟎). The formula can be copied to somewhere prominent at the top of each page (such as within the header) to create a global error check indicator, which will alert users to any error as soon as it is triggered. Global error- check alerts are particularly useful for a modeler who has users working with the model regularly. The error check will alert the user that something has gone wrong in the model. Hopefully, the user will be able to correct the input, ensuring the continuing integrity of the model. Avoiding “Error” Displays in Formula Sometimes a formula may return an error message. It happens mostly because of an error of the input. For instance, if a modeler is calculating a percentage in a column and one of the entry fields contains a zero, he/she will get a #DIV/0! error. Or if in using a VLOOKUP function, the modeler entered a criterion that does not exist in the data; hence, a #N/A error appears. To avoid the errors mentioned above, one may use the IFERROR formula in excel. However, it must be noted that some modelers do not agree with avoiding error displays. It is because using error suppression may mask other “genuine” errors. Thus, it is advised to use the IFERROR function with caution. 03 Handout 1 *Property of STI  [email protected] Page 4 of 6  BM2109 Circular References A financial modeler using Excel for any length of time probably came across a circular reference. A circular reference exists when the formula is trying to refer to itself. A common cause of this is when a sum range includes the sum itself. As an example, observe the figure below. Figure 3: Example of Circular Reference Source: Using Excel for Business and Financial Modelling (2020), pg. 86 As can be seen, the formula for the Total Salaries located in cell B11 refers to/includes B11. It is like asking the function in cell B11 to perform a calculation that includes the result of its calculation. Such a formula is impossible, and thus, MS Excel will only return a zero value. How to Fix Circular References There are several methods to deal with potential circular references: (Rees, 2018): Correct the mistakes that result in circular formulae. Circular formulae that have been implemented by mistake should be removed or corrected. This should be done as soon as they are detected because it is generally complex to audit completed models to find how the circularity arises. Since there is no starting point for a circularity, tracing precedents and dependent can become time-consuming and frustrating. One may need to delete formulae on the circular path and rebuild the model in some way as an interim step to find and correct the circularity. Avoid a logical circularity by modifying the model specification. In some cases, a real-life situation may contain circular logic, but it may be possible to ignore this yet build a model whose accuracy is regarded as sufficient. For example, many corporate valuation models ignore the circular logic relating to the discount rate and the cost of debt. Similarly, one could eliminate the circularity for the example concerning ending cash balances by assuming that interest is earned on the opening cash balance only using the following formula: Cashend = Cashbeg + Cop + IntRate.Cashbeg. This approach is simple to implement in practice but may not be sufficiently accurate in some cases. An improvement in accuracy can be achieved by introducing more sophistication and complexity. Interest is earned on the total of the opening balance plus the average non-interest cash inflow: Cashend Cashbeg + Cop + IntRate. (Cashbeg + Cop)/2. Of course, a reformulation will alter the value of some calculations and outputs, which may or may not be acceptable to the context. For example, calculating the bonus based on pre-bonus income (rather than on post-bonus or net income) would eliminate the circularity. However, 03 Handout 1 *Property of STI  [email protected] Page 5 of 6  BM2109 the presentation of a result in which the bonus is inconsistent with the final net income figure may not be acceptable or credible, especially since it is a figure which may attract particular attention; thus, an inconsistency in less visible figures may be acceptable. Use iterative methods. The role of a variable on both sides of an equation can often be determined by an iterative solution method. This means that one starts with a trial value (such as zero) for a variable and substitutes it into one side of the equation (where the other side is the isolated value of this same variable). For example, in the computation of bonus with the formula: B6 = 1 + (1/10)*B6. Using an initial value of B6 as zero (0) on the right-hand side, the process results in the sequence 0, 1, 1.1, 1.11, 1.111. This shows that where a single and stable correct figure exists (i.e., 10/9), iterative methods generally converge very quickly to this. In theory, an iterative sequence could be explicitly replicated in Excel by building multiple copies of a model. The first is populated with trial values, and the outputs of this are used to provide inputs to the second copy, and so on. For example, Figure 4 shows the bonus calculations (using a bonus level of 5% of net income). The net income (Cell D5) is determined after subtracting the bonus (Cell D4) from the pre- bonus income figure, and the bonus (Cell D4) itself depends on the net income (Cell D5) thus creating a circular reference. Note that when the circular formula is entered for the first time, the result may evaluate to zero (Cell D4). At this point, the figures are not consistent, i.e., the bonus figure as shown is equal to 0% (not 5%) of the net income. Figure 4: Circular Reference from Equilibrium Logic Source: Principles of Financial Modelling (2018), pg. 122 On the other hand, Figure 5 illustrates the iterative process that uses a sequence of models, where the output of each is an input to the next. The values in Row 4 (cells D4:I4) and Row 5 (cells D5:I5) rapidly converge to stable figures that are consistent with each other. Of course, it is generally not practical to build multiple copies of a model in this way. Rather, iterative methods within the same model are required. Figure 5: Iterative Process by Using Multiple Copies of the Model Source: Principles of Financial Modelling (2018), pg. 123 References: Fairhurst, D. (2019). Using Excel for business and financial modeling: A practical guide (3rd Ed.). John Wiley & Sons Ltd. Rees, M. (2018). Principles of financial modeling: Model design and best practices using Excel and VBA. John Wiley & Sons, Ltd. 03 Handout 1 *Property of STI  [email protected] Page 6 of 6