Financial model hacks and considerations (part two): Testing and reviewing your model

• Use change tables: An easy way to measure the impact of changes to a scenario or an assumption is to set up “change tables” below key statements. Create two mirrored copies of any statement; for example, a P&L, one of which is a hard coded version, the other of which compares the live P&L to the hard coded version. Once these are in place, they will enable you to measure the line-by-line impact of any single or combined assumption change like a change in revenue, COGS, etc. This approach applies equally to a balance sheet, cash flow, DCF, or other models. The hard coded version can be reset at any point to continually assess the impact of additional layered assumption changes which will help craft scenarios and help check the validity of the model.
  
• Track and document assumptions: Keep careful track of each assumption used and document those that have a meaningful impact on the model or may be important to the user. This is best done in brief written form with a justification for each assumption used, and/or a source of the assumption, rather than simply using a list of numbers. This provides users with a clear understanding of what is driving the outputs and helps with the feedback loop.
• Use an “offline” review: Modelers often spend many hours building and refining a model right up until the time it’s ready to share with the end user.  Refining a model until the last minute increases the likelihood of errors. A good tactic is to periodically print out a hard copy of the model and sit down with the proverbial red pen to review and edit the document when you’re away from your computer (i.e., I call this the “Starbucks review” because a change of scene can help provide a fresh point of view) so that you are unable to make changes on the fly. It is amazing what you can see on a hard copy that you wouldn’t necessarily see on the screen.
• Think like the end user: I can’t emphasize enough the importance of looking at each model from the end user’s perspective. While every modeler is familiar with their own model, the end user will likely be unfamiliar with its layout and intricacies. They will need guidance with the outputs and will need to understand the way that it was built, its interdependencies, and the assumptions. If the user can get comfortable with the architecture of the model, they will have more confidence in the model conclusions.  
• Stress test your model: It is important to stress test a model before it is finalized and released.  This includes adjusting major assumptions and confirming that these changes provide the expected results elsewhere in the model. The change tables described earlier are especially helpful in this process. For instance, if you change revenue, there should be appropriate changes in accounts receivable and cash. A good approach for stress testing is to make large, rounded changes to each assumption as doing so will best illustrate corresponding changes throughout the model.
• Hire an outside firm to validate your model when the model is critical to your business: Many companies invest a significant amount of time and money building complicated models upon which their business relies. When you’re dealing with a very complex model, it’s important to have it tested by someone other than the team that built the model (i.e., an independent third party) who can act impartially and help ensure the model is reliable and functioning exactly as it was intended. Having financial models validated by an independent third party can help identify areas of weakness and mitigate risk to your business. However, the consequences can be quite severe and costly if you’re making important business decisions using an unreliable model that is generating inaccurate outputs.
• Don’t overlook security measures: Security should always be an important consideration because almost all financial models contain sensitive and confidential data. Decisions should be made on whether to password protect a model or take other steps to protect the underlying integrity of the data when shared across email or networks. Password protection may only need to be applied to certain parts of a model rather than to the whole model. For instance, a treasury group may need access to a 13-week cash flow that includes payroll data. In that instance, it may be necessary to protect the payroll data from being viewed by anyone other than certain authorized parties.
• Continually learn and add to your skill set: Take the time to remain current on best practices and be willing to use new formulas and methodologies that will enhance the usability and output of a model.

The testing and reviewing phase of financial models is an important step of the whole development process. These considerations can help ensure you’ve done a thorough review of your financial model.

Antony Walker, FCA, CIRA, Principal, Restructuring and Dispute Resolution Practice

617.478.9471