Microsoft Excel – Overview of the Financial Modeling Process

For the novice financial modeler, knowing where to start a model can be a difficult and somewhat daunting task. Forget thinking about formulas, structure, flow and results, but how does one even begin the process? The good part about this problem is that when you have worked on or developed hundreds of models, the starting point for model creation much more simplified in one's mind. This, of course, it similar to needing experience to get a job, but requiring a job for experience - thus, where to start?

In general, I have found that the map for creating a successful financial model follows these steps:

• Identify the problem to be solved or purpose of the model

Why do I need a model? This could be something like simple calculations to see what car payments may be like over a fiver-year period or the creation of family budget. It may be a more advanced model that calculates changes in interest rates based on a debt refinancing or the impact to a company's earnings from an acquisition. These are reasons why a financial model may be required.

• Identify the variables or assumptions needed to populate the model

What factors impact the analysis I am trying to perform? Do I need to consider macro economic factors like interest rates and foreign currency exchanges? Are there commodity pricing issues that will impact my analysis? If modeling a company's forecast, what sorts of growth expectations exist and what level of profitability in the foreseeable future? It is important at this point to make these mental assumptions prior to trying to create the model. The clearer one is in thinking through the steps, the easier the development will be.

• Think about the results (the answer you are seeking)

This may seem odd to think about what the answer to the question is prior to developing the model, but this helps form how the model is to be developed. For example, if you are curious to see what net income a company will generate based on a series of assumptions, you are likely to lean toward a standard income statement development with some for of summary table. If you are more interested in creating a Black-Scholes options pricing model, than the assumptions are relatively straightforward, and the answer you are seeking is the value of a call option or put option. If you are trying to determine which salesforce sells the most of widget A in the first week of each month for the past five years, the resulting model needs to have a summary that is easy to read expressing that exact answer. As you think about the purpose of the model and the assumptions affecting the output, you should begin to visualize the model in your head.

• Start building

Once you have the first three components determined, it is time to begin constructing the base of the model. For example, a typical leveraged buyout or acquisition model will have an income statement, balance sheet and cash flow as the primary driver of results. These are all linked together and require some basic concepts of accounting to be understood. From this, it is common to incorporate debt repayment schedules, various financial ratios covering cash flow and interest, and value to the buyers. For you novices, this will become more evident over time as you further hone your modeling skills, so just have patience.

• Double Check

In my career in investment banking, nothing causes more angst (other than losing a deal) than receiving an analysis from the deal team and quickly noticing that there are obvious errors. I learned from the old school methods of modeling, which dictated printing out the entire model and double checking the formulas with my trusty HP and a pencil. It is not necessary to check every single cell, but you need to provide the model with a sanity check. This will also help you build more robust models, as you will eventually start double checking the materials as you are developing the model, thus saving time.

These five steps will allow you to become a more efficient model builder, and allow you to progress from novice to intermediate in no time. Subsequent postings will include very simple examples of spreadsheets to help the reader better understand the process of financial modeling. For now, please just try to visualize the five steps above and I will be back in the next post with some basic beginning building blocks.