The hard work is in the modeling

Introduction:

Today we live in a world of instant answers and gratification, where it seems like everything is a click away. We can watch the latest episode of our favorite TV show, order breakfast, do our weekly shopping and catch up on the news without getting out of bed.

This idea of instant answers comes at us from every aspect of our life and in the business world it is no different, vendors are promising to deliver amazing insight with a click of a few buttons. Take Business Intelligence (BI) for example the ads by these vendors almost always seem to imply that all you do is buy the software and hook it up and your good to go, instant insight. Attend a demo and you come away thinking with a simple connector and 15 minutes of work, your team will be building awesome reports and fancy dashboards.

There are also many people out there selling excel templates with the idea that all you do is plug in your data and fifteen minutes later you will get instant insight. Type in revenue template on the web and you will find over 200,000 results and thousands of different templates. They make you think that a simple template can be used in almost any scenario to provide great insight.  

The reality is far from this as anyone who has spent time working with business intelligence or building a financial model knows the real work is in the modeling. In the finance world whether building BI reporting or financial reporting most of the time you will spend over 80% of your time modeling. Even though everyone likes to focus on the visuals the reality is displaying the visuals is usually the easiest part, it is building the model to display the visuals and get the answers where the real work happens.

Modeling:

When building a model, whether a data model or financial model the key is taking the time to ensure the model is developed properly. I am sure all of us have experienced that moment when you try to use a model someone has built only to realize it is wrong or it is so complex that it will take you a month to understand what it is doing. Therefore next time you build a model there are a few key things you should keep in mind to make sure it adds value. A properly built model should have at a minimum the following:

·       Accuracy – Whatever type of model you are building it is critical to ensure accuracy in the outputs. 

·       Speed – If the model is so slow that no one will wait for the results than it does not matter how good the outputs are because no one will see/use them.

·       Simplicity – If the model is so complex that you need a PHD to understand what it is doing start over and try again.

Accuracy – At first glance this seems so simple how would anyone produce a model without checking it for accuracy. However, the reality is many models are released with errors. The following article talks about how nearly 90% of all spreadsheets contain errors: Sorry, Your Spreadsheet Has Errors (Almost 90% Do). When it comes to building a spreadsheet, any good modeler knows they need to build in error checking. This problem of accuracy also applies to data models

Just like financial models contain errors many data models can also contain errors. These errors are often in the calculations we make or the granularity of the data but none the less they are errors that limit the value of the final output. If you do not scrub your data and properly build your model it is easy to end up with reports that have bad data. Anyone who has written an SQL or access query has experienced that moment when they look at the results and realize something went wrong as it shows your company’s revenue to be something like 5 trillion dollars.  No matter how pretty that final graph or chart might look if you did not take the time to ensure your model has all the right data at the right levels its value will be limited.

Speed – Raise your hand if you have ever had that experience you know the one where you open that awesome Excel file go to lunch while you wait for it to open and run all the calculations come back to only find that Excel has frozen and you will have to start the whole process over.

How about opening up your BI tool to look at a report and you click on a filter only to find it has taken you five minutes to apply the filter and you have another ten filters to apply before you get the data you want. At that point you close the report and decide to find another way to get the data you want.

A key part of modeling is to build your model in such a way that it runs quickly. If your using Excel this means writing formulas that are efficient and avoiding overuse of volatile formulas that must recalculate every time excel recalculate. If your building a data model this means ensuring that your model is at the right granularity and has the minimum number of tables necessary. 

I remember the first data model I built in Power BI it provided great insight but fail victim to poor design and ran painfully slow. Every time I did analysis using the data model, I wanted to rebuild it to make it run faster. For a model to maximize its value it needs to be able to provide results in a reasonable time. 

Simplicity – When building a model one should try and follow the “Kiss” principle, Keep It Simple Stupid. When one is first learning to model one often has the idea that building a complex model is better and shows how smart you are but overtime one realizes that building a simple model is often harder than building a complex model. One of my favorite financial modelers on LinkedIn, Lance Rubin calls this being a spreadsheeter. He talks about how if one hopes to become a true financial modeler one must learn the art of simplicity in design. 

This is not to say that you will not have complexity because what we are asked to model is often complex but if you try to model for every single possible scenario and to include all possible variables you will often find the model becomes impossible to maintain and will fall under its own weight.

Conclusion:

At the end of the day all of us finance guys like to show off some fancy report, graph, or dashboard that provides key insights and metrics that help our business partners be successful. However, what a lot of people fail to realize that showing you that graph, or report was the easy part the real work was in building the model that allowed for the outputs to be displayed in such a way that they added value.