Financial modeling is an art and a science. There is not one perfect way to craft a model, but there is a set of best practices. The following paper is set forth to share common pitfalls and possible solutions. In building and review models, mistakes generally fall into the following buckets: model goal, data collection/aggregation, forecasting, and visualization.
The first mistake made with financial models, is focus. There are many types of financial models, and a conscious decision needs to be made on direction. According to the Certified Financial Institute there are at least ten different types of financial models. There is the Three Statement Model, which shows the income statement, balance sheet and cash flow statement. There is the discounted cash flow model which is primarily used for company valuations. Also there is a leveraged buyout model, which is one of the most complicated ones. It involves modeling the use of debt instruments to purchase another company. Each model has a particular focus. In order to avoid the mistake of not focusing, people must spend some time thinking about the purpose of the model. Who is the model going to? What decisions are they trying to make?
It is also important to mention that each model type requires a specific skill set. It would be difficult to build a three statement model if someone does not understand the financial statements. Before embarking on any model it is important to have the underlying knowledge or access to that knowledge.
The second type of mistake made with models is related to design. Many models are disorganized. A disorganized model maybe accurate, but if no one can follow or audit the output it is useless. People in the Finance field typically expect rules in regard to model design. Firstly, pool all the assumptions together into one spreadsheet. The centralization of assumptions helps in auditing process. Secondly, have a legend available to describe the key inputs and units of measurement to name a few. These steps help reviewers of the model to quickly digest its meaning and implications. Thirdly, use color sparingly, but use it to denote cells which should be manually changed and those restrictively driven by formulas. Fourthly, use interactive elements in the spreadsheet to quickly run various scenarios. Interactive tools would be items like spin button, scroll bar or combo box, to name a few. The elements that are typically aided with interaction on a webpage can be used in Excel as well.
The third type of mistake made with financial models is with data collection and aggregation. In most cases a model requires some historical information. In the case of a three-statement model someone would typically need historical information to accompany projections for the upcoming year(s). One of the mistakes I see often would be the improper grouping of category level information. For example, administrative salaries might show up in supplies category. Or, perhaps, someone would like to see a full year of data, but for some reasons, the only half a year’s worth of data is pulled.
The fourth type of common mistake made with financial models is related to forecasting. Typically models are built in order to make informed decisions about the future. Often when people build a forecast, it is simply driven by one inflator. The inflator is typically an arbitrary figured which mirrors the inflation rate. In many cases, I see people just throwing a standard 3 percent growth rate. This is a huge mistake. Forecasts should be driven by combination of historical information and expected market conditions. Here are a few forecasting methods which take those conditions into account.
One method of forecast would be what I call the year over year ratio method. Let’s say someone has data for quarter 1 in the current year. See what ratio of revenue was collected in the prior year. Use that ratio to develop a forecast. This approach would take into account if the Q1 in that year is better or worse than the prior year Q1.
The final area where mishaps occur frequently would be in visualization. Many models contain charts, graphs and dashboard visually depict historical information and forecasts. Very often the visualizations are unclear, cluttered or communicate the message poorly. While there are many ways to go wrong, I’ll just focus on the frequent offenders. The first is color. Use color judiciously to tell a story. One might be found of colors like turquoise and hyacinth, but it may not be the best choice for a business document. Use a palette that is conservative. If someone is building a bar or waterfall chart red must be used sparingly. It should only be used to highlight declines or problematic events. Green should be used for positive gains or increases.
The above pitfalls are a brief synopsis of typical errors with financial models and techniques to overcome them. In short, being mindful of typical pitfalls can help in the creation of robust financial models.