Financial Modeling Framework

It is important to discuss these topics upfront with key stakeholders. If you are deficient in any of the above categories you might fail to deliver on the goals you have set. In that case, you might need to alter the scope of your model design or revisit the above constraints.

KEEP IT SIMPLE

In the beginning stages of building a financial model, it is best to start simple. Another good mantra to keep in mind here is “walk before you run." This will allow you to ease into model development and avoid potential pitfalls. One way you can walk before you run is to develop a “rapid prototype" model. Rapid prototyping is the process of quickly developing a model, reviewing it and then refining it based on your review. This process is repeated until your model is finalized. It may take several iterations until you have a finished product, but you will be building it in more digestible pieces, thereby reducing the overall complexity of the entire exercise.

As you set out to build your model, look for areas of simplification. There are many, but some of the most common are listed below.

OUTPUT

Generally, when building a financial model you should have an understanding of the output you need to answer your questions. Most of the time you do not have to reinvent the wheel as the output already exists. For example, financial statements can help answer questions about operating results, financial position or cash flows. Leveraging existing work makes the process of developing a financial model much simpler. You already know the components of the financial statements and have the historical data to back up the line items. In that way, you have already set the highest level scope of the model as the projected financial statements.

Once you understand the output, it is a good idea to delve into the major sub-components. A reasonable next step would be to separate strategic and non-strategic line items within your financial statements. The distinction between the two is that strategic line items are the major drivers of your financial standing. Generally, they represent the biggest piece of what makes up your output. They need to be isolated because they are usually items that need to be modeled in further detail. On the other hand, non-strategic line items are those that do not have a large impact on the financial position of your institution. Generally, it is sufficient to project these line items as a defined schedule (e.g., typed in values) or on a percentage basis into the future.

Understanding the output and its major sub-components is a good first step in outlining your basic “strawman" model. Remember, the intent here is simplification. If you find yourself scoping out hundreds of detailed line items within the financial statements or considering every line item strategic, it is a good idea to take a step back and revisit your goals. At this juncture, you are looking to develop a rapid prototype to get started.

Data is extremely important in any model. It is also an area where modelers seem to get carried away. Because of this, it is critical to analyze your data early in the process. If you don't, you could be adding excessive overhead, structure, and complexity to your model. Therefore, it is very important to review the following:

• Availability – know what data is available.
  • How is the data formatted?
  • How is it organized?
  • Is the data accessible?
  • What is the timing of the data? When is it available?
• Detail – how much data do you need?

There are two major reasons to analyze your data. The first is to determine what is possible. You do not want to spend an exorbitant amount of time developing the model’s structure when the underlying data necessary to use the structure does not exist. The second is so you can be selective and disciplined about designing the model’s structure with complex data requirements. If the data is hard to wrangle, manage, and project, you will have a very difficult time informing your model with the very numbers necessary to produce results. The more data, the higher the commitment. Depending on the scope, this can be a very large draw on your resources, time, and effort. Therefore, as you design and build your rapid prototype, it is advisable to minimize the data requirements as much as practical.

COMPLEXITY

To start, you should look to limit the number and complexity of the business objects within your model. As mentioned above, a good approach is to be selective in the areas in which you wish to develop more complex business logic. This allows you to limit the data, time and resource requirements to build, use and manage the model. Additionally, it is a good idea to winnow down the list of key-dependent and independent variables within your model. One of the major objectives of creating a model is to distill down to the fewest number of variables necessary to produce a projection. This will produce a more usable model while still delivering the answers to your questions.

DESIGN AROUND USE

It is important to remember that after a financial model is built it needs to be used. Therefore, all design decisions should be made in consideration of the end-user. If your model is not usable, what good is it? Therefore, as you outline your model, design business objects and establish data requirements with the end-user in mind. A good place to start is with the assumptions, as these are the major items that are used in developing a projection and performing sensitivity analysis. They are the items an end-user will be most interested in interacting with regularly. You can perform this exercise as you are separating the strategic and non-strategic line items discussed in the Output section above. As you are isolating the strategic items, ask yourself the following: What are the major variables that a user will want to alter to change the course of the projection? These variables should be placed front and center for the end-user.

Non-strategic line items have a lower impact on your organization and therefore can be modeled at a higher level. This might mean bucketing objects together so they can share assumptions, which means cutting down on the total number of assumptions the end-user will have to set to get results out of the model.

MODEL INTEGRITY

Guaranteeing model integrity is all about having trust and confidence in your model. Therefore, as you set out to develop your model, you need to institute a process to protect its integrity and accuracy. Consider the following options:

MULTI-USER ENVIRONMENT

Collaboration has tremendous value in fostering model integrity. It is important in building consensus and making smarter business decisions. If key stakeholders are looking to make decisions based on your financial model they will either want to be involved in the model's creation or at least be able to understand the logic and assumptions it uses to drive results. Therefore, it is important to give users the opportunity to work together in the same model simultaneously. Otherwise, you run the risk of alienating key decision-makers as they may see your model as a black box.

VERSION CONTROL

Creating financial models typically requires multiple people building, reviewing, approving and presenting the results. For many organizations, this means passing around files and data, which sometimes leads to accuracy, data integrity, and version control issues. Therefore, as you set out to build a financial model it is important to consider how you will manage this process. While collaboration is very important, you still need to protect the model that represents the consensus.

AUDIT TRAILS

As mentioned above, it is very important to include collaboration in any model building exercise. That said, it cannot be done at the expense of your model integrity. This means that if you have a multi-user environment it is critical to develop a record of changes that have occurred in your model. This will allow you to have an audit trail of all user activity so you can understand who changed what and why. That way, if you have questions about a particular change you can always use the audit trail to find the person who made the change and have a conversation about it.

TRANSPARENCY

If the underlying formulas, calculations, and assumptions in your model are not transparent, stakeholders will not be confident in the outcomes. This will lead to questions about the model's usefulness and usability. Because of this, it is advisable to develop tools to be able to diagnose how the numbers within your model are produced. Being able to drill down to see the components of a formula will allow you to very quickly perform forensics on questions about the formulas and assumptions used to derive the figure in question. This will increase confidence and trust in the results your model is producing.

SUMMARY

The challenges associated with modeling may seem daunting, but if you meet them head-on and develop a plan for each you will realize success. Hopefully, now that you have read about many of the common challenges and reviewed some guiding principles you will feel better prepared to build a financial model. If you take on the challenge of building a financial model, your organization will have a trusted partner in your decision-making process to help better understand your financial future.