Why Most Financial Models Do Not Stack Up - 5 Principles for Improvement

“The financial markets generally are unpredictable. So that one has to have different scenarios... The idea that you can actually predict what's going to happen contradicts my way of looking at the market." - George Soros

Whether forecasting/planning volumes for a CPG company, customer attrition at a telecom, or balance sheet/cash flows for an oil and gas company, we can agree that A.) No one can predict any future event with certainty and B.) Scenarios are a critical element to understanding how we can prepare/plan for changes in things like customer behavior, markets and macro-economic drivers.

Enter the financial model, wearing a red cape, whose sole purpose is to help steer organizations around and through this future uncertainty and provide senior leadership with data/analyses to support decision-making. 

Should we repurchase shares this quarter? Should we open a new production facility in China? Should we re-structure our sales reps/territories? Should we allocate capex to update our Equipment? Should we repay our senior debt tranche? Should we hedge against the euro? All of these questions have financial models behind the scenes providing some insight.

Having spent over 10 years in the enterprise performance management space, working with dozens of clients and seeing hundreds of financial planning models, I can count on my hand the number of truly good models that I’ve seen. Too often I’ve seen organizations (including multi-national Global 1000s) make decisions based on one person’s spreadsheet that, when put under some scrutiny, failed to deliver. Also, more than a few times I have actually heard the words “I cannot believe we’re running the business on this model” uttered from an executive. Hard-coded formulas, broken reports, lack of collaboration, inability to run/perform scenarios… all of these things still permeate today’s financial planning world and frustrate senior leadership teams.

Whether building a model in excel or an enterprise planning platform, the same core principles should be applied when creating a model that will be used to make decisions across the enterprise. 

There are dozens of considerations that should be carefully reviewed, but from a high-level, following these (5) principles will lead to a more robust/collaborative model and ultimately, better decision-making across the enterprise.

1. Clear flow

There should be a flow that is easy to follow and incorporates the basic idea of input > process/calculation > output (where inputs=drivers, calculations =model logic, output = reports/KPIs).  The model assumptions should be clear, the calculations clear and the reports organized well…  An Executive should be able to pick up the model, work backwards from the reports and follow it through to the assumptions/drivers.   This is especially important when the model has multiple users accessing it as organization will help with user adoption and collaboration.

2. Clean (transparent and communicable)

Less is typically more when it comes to financial modeling.  Using index/match formulas or writing formulas that contain (15+) nested If/then formulas is fun to show your colleagues, but when an Executive asks you to walk him/her through the model and you stumble around, that’s a fail.   Last, once you have the answer, focus on how to communicate/distribute this clearly across the enterprise so the right people have access to the this information at the right time.  Distributing the information is a real key that often gets overlooked... it’s very important to understand frequency and timing as well.

3. Scenario-focused

Flexibility and speed are the keys here.  Models should always be flexible enough to change assumptions and see immediate impact to financials.   If the model is not set up to be able to pull the levers and triangulate around the unexpected, it is not a fit for the purpose.  I can think of very few models that shouldn’t have some scenario capability built in.  For many clients it takes days or a week to turn around key scenario outputs…if you fall into this box, things need to change in a hurry.

To demonstrate the value of scenarios, let’s use an example of (2) coal mining companies, both having similar operations, both public companies and both have a quarterly earnings call approaching. The day before the earnings call, the price of coal drops 30%... the CFO of Company A asks the analysts to run a few scenarios incorporating the 30% drop in price to reflect projected earnings/EPS going forward and Company A turns this around in 2 hours so the CFO is well prepared for earnings call, and the market has confidence in Company A’s projections and as a result, the share price of Company A drops only slightly.

On the other hand, Company B is not able to perform this analysis given their constraints with models/scenarios/systems, etc. The CFO of Company B gives his/her best Oscar performance but it’s not good enough as the market sees through the fact that Company B doesn’t have a clear handle on the uncertainty/risk and as a result, Company B sees its share price cut in half. 

Company A has an obvious, tremendous competitive advantage over Company B. The unfortunate reality for organizations like Company A, is that the speed of information delivery is critical and without it, these organizations will have to fight to stay relevant.

4. Repeatable integration from data source

Most models require a feed from source data. Depending on the frequency of the modeling (daily, monthly, quarterly), the need for the automation of updated source data becomes more valuable.  Establishing a solution for integrating source data into one’s model (vs. manually typing numbers), will provide obvious benefit of giving analysts more time for value-add analyses. Every minute that analysts spend on reconciliation or manually inputting data, has a true opportunity cost and most analysts spend 50%+ with non-value add work. Automating integration will help a great deal. Also, if you’ve developed a model already and are trying to figure out how to automate feeds, map it out with some process flows and capture the “current-state” of the data flow before trying to automate anything.

5. Future-proof

This is easily said in theory, more difficult to apply in practice but too often I’ve seen models built that only answer today’s questions. Often times, analysts are excited to build a slick new model and want to jump right to building the model vs. taking a more calculated/comprehensive approach to designing it. This often leads to inflexible models, excessive costs (to change/rebuild), and frustrated senior leadership who have to wait days and weeks to get their analytical questions answered…

“Measure twice, cut once” - English proverb

One of the better ways to ensure models can pivot/change pending shifts in the business/market is to spend an adequate amount of time reviewing model blue prints/designs with business leaders/strategy group ahead of building the model… the leadership/strategy team should be best positioned to advise on specific risks/opportunities for the business and these ideas/concepts should be baked into the model in some respect.

I’d recommend creating a simple list and documenting what questions the model needs to answer today vs. what risks/opportunities are coming and then aligning these questions to the actual model design. Tactically, this could mean building in possible placeholder accounts or adding extra placeholder entities or logic that can be turned on at a later date to accommodate changes.

By building a model in a vacuum without input from a leadership team who need the outputs, there is a high probability that the effort will go to waste. 

However, by gaining support from senior leadership, working backwards to determine what questions the model needs to answer and then spending an adequate amount of time on the design of the model to create the most flexibility, whether your model is in excel or a performance management tool, it will have a better shot at standing the test of time and providing actionable data to the right people at the right time.

Article written by Drew Brieman
Want more? For Job Seekers | For Employers | For Influencers