Financial model hacks and considerations (part one): Eight model basics

• Start by focusing on the end goal: I often use the adage: “Don’t build a Rolls Royce if you only need a Ford” (with apologies to Ford). Before building any model, you must have a clear understanding of what it needs to achieve. Is it intended to be a single use model to answer specific questions, i.e. a fixed valuation, or is it intended to be a working model that will continue to be modified and updated, i.e. a weekly cash flow model. It’s critical that your model has the requisite level of functionality without superfluous details that will add time and costs to the development process.  
• Build the foundation first: Rather than trying to build out everything at once, it is helpful to construct a basic foundational model and then add additional layers of functionality from that base line. Taking an evolutionary approach will enhance your understanding of the business and prevent you from getting lost in the weeds early in the process. 
• Leverage historical data: Many forecasts don’t include historical data for reference purposes.  It can be helpful to assess the feasibility of forecasts by reviewing historical data in the same file, be it weekly or monthly. This may sound simplistic and obvious, but it is often overlooked and can be quite impactful as the past, plus discrete quantifiable changes, is a good benchmark for the future.
• Remember, simple is better: Avoid using unnecessarily complicated formulas as they impress few people and are hard to review and modify. You’d be surprised how many intermediate formulas and functions accomplish the same tasks that you may think require complicated formula/function combinations. 
• Invest time in formatting: A model that looks clean, professional, and well-laid-out will go a long way to assist users in understanding it. This includes having well-marked and delineated tabs, a clear index of content, professional looking schedules that are formatted for easy printing, and a clear indication of what can be modified and what is formula-driven; i.e., hard coded data should be easily identifiable by different coloring. Never mix and match hard coded data with formulas within the same cell as this can undermine a model’s integrity. Also, if you tend to use a template as a starting point, ensure it is modified so that the terminology aligns with the end-user’s reports. The end-user needs to be able to recognize their own data rather than trying to figure out different descriptions or groupings of data on a budget, forecasts, and financial statements.
• Collaborate early and often with end users: Don’t work in a vacuum.  Work as closely as possible with the end-users to identify trends and key assumptions.  The best results occur when you talk through and refine a model with those who live and breathe the numbers every day. 
• Use integrated models to reduce errors: Wherever possible, use integrated models to reduce the risk of error and help ensure that key components are not overlooked. If a balance sheet is included in a model, make sure that it remains in balance at all times. If it doesn’t balance, the reasons why are unlikely to be immediately apparent so use a methodical approach to finding and fixing underlying errors. Save the model as a separate ‘test’ version and start zeroing out assumptions one by one to determine those that impact the imbalance. This will help diagnose issues and allow you to go back and fix errors in the ‘live’ model. Another helpful approach to tracing the cause of an error is to significantly increase the value of an assumption (i.e., increasing it by 10x or even 100x) as the resulting amount makes it a lot easier to track the flow of those items through the model to find a disconnect. Fix the underlying issue and then continue with this process of zeroing out individual assumptions until additional errors are found and fixed and the balance sheet is balanced.
• Build in flexibility: Models aren’t typically intended to provide one definitive answer to a question; rather a model is typically intended to provide an accurate picture of a number of possible outcomes. Models are developed with a set of assumptions which, when brought together, provide an accurate picture of a variety of possible outcomes. It is therefore important that the model allow a user to easily run different scenarios, using different combinations of assumptions.  Make sure you provide an easy way for the user to make changes and to be able to measure the impact of those changes; i.e., how do the changes impact the valuation, cash flow, profitability, or other primary outputs of the model?

In the part two of this series, learn about important considerations during the model testing and review phase.

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

617.478.9471