Understanding the Big Picture in Financial Modeling

It is time to revisit a basic but fundamental aspect of financial modeling: The Big Picture. What exactly is The Big Picture ("TBP")? TBP refers to the overall goal of the financial model and what answer the model is specifically being developed to answer.

Many times in my career I have been asked questions about financial modeling in Excel, questions that really indicated that TBP was not yet established. This is not a knock on anyone's intellectual capacity but rather, it confirms what I have always believed: people cannot sit down in front of a computer and miraculously create a financial model that provides the answers to relevant questions without first addressing the overall scope and purpose of the exercise.

Let us assume, for the moment, that you are a budding young financial analyst at Toyota and you have assigned to analyze domestic (U.S.) cars sales. If we assume that Toyota, being a global entity, has all of the reporting and data readily available, there are no information glitches in the exercise and it is all up to you, the financial analyst, to develop this model. So, how would you begin?

If the corporate office was interested in number of Camrys versus Tauruses sold in the state of Michigan, the exercise is straightforward. You could easily pull information on total car sales by make and model and have the answer. If the analysis is, however, to determine how much of an advertising campaign is required to increase market share by 3.0%, there will be more thought required to create the model. In this case, TBP can be thought of in a somewhat formulaic relationship: TBP = advertising impact on market share. This becomes the basis of the model.

Given the way that the formula is presented, this may be an opportunity to use a data analysis function incorporated in Excel like the regression analysis. Whenever an analyst is trying to determine relationships, taking historical advertising expenditures and running that against changes in market share may provide the appropriate regression equation from which the forecast can be created. For example, you may take 5 years of monthly data and determine that increases in advertising translates directly into increases in market share (all other variables held constant), and this will allow you to understand what the future market share over the next year might be based on those relationships. If the model displays profit margins, return on invested capital or reductions in debt, than TBP was not addressed. Those items reflect supporting data, but remember TBP: impact of advertising on market share.

The purpose of this brief article is really to beat into your head the need to focus on TBP. Part of being an effective analyst, consultant or advisor is listening to the problem and providing the relevant solution. If you need 10 apples for a pie, finding 12 oranges is not helpful. Focus on the purpose of the model before you start creating spreadsheets and you will find your efficiency in financial model development increasing significantly.