Articles

4 Steps to Getting the Information You Need to Build a Financial Model

By AFP Staff
Published: 7/31/2023

When it comes to skills that are critical to success in finance, being able to build a model tops the list. Finance professionals are confronted with a multitude of problems every day; each problem has a unique solution — and in many cases, requires a custom-built model.

A key step in the model-building process is getting the information you need. And the better information you have, the better your model will be. Here are four steps to follow when gathering information to build a model.

1. Understand the end product

The first thing you need to do is meet with everyone with a stake in the end product to come up with a clearly defined goal. What do they want the model to accomplish? What do they not want? When do they need this by? Getting a clear picture of the end goal will not only prevent you from doing unnecessary work but also set expectations for your stakeholders.

To accomplish this, you need to identify the problem, the goals and the uncertainties. The problem is the business issue to be resolved. The goals are the methods used to understand or resolve the problem. And the uncertainties are the internal and external forces that must be considered, as well as the unintended consequences that could result from a certain business decision.

Next, you need to define the required outputs. The outputs will be used to answer the business question and therefore need to be specific, measurable, achievable, relevant and time-based (also known as SMART). The outputs may change and evolve over the course of the process as you gather information (inputs) and build/tune the calculation engine.

You’ll also have both voluntary and mandatory constraints placed on the output. Voluntary constraints include things such as the time period to be studied, organizational level to be studied, currency to be used, and whether to use a fiscal or calendar year. Mandatory constraints are contractual, legal or regulatory constraints placed on business activities.

When discussing what the end product should look like, make sure you have an understanding of the relevant context, such as the organizational strategy or business unit operating plan, the regulatory environment or the competitive marketplace. Linking the results and recommendations to the strategy or operating plan allows you to show that you understand the business unit’s needs.

Finally, set a realistic timeframe to meet your stakeholders’ needs and your abilities, as this will affect the scope of the effort.

2. Define key inputs and input-to-output logic

When deciding what inputs to use in the model, ask yourself: What critical factors do we need to know about the situation and the future that will drive the outputs?

Direct inputs are, as the name implies, entered directly into the model. They can be variables, constants or semi-variables.

Variables are based on value drivers, the most up-to-date historical data or assumptions, and can assume any one of a set of values as needed or expected in the model. You can divide these into two types: things that change in the environment (e.g., interest rates, tax rates) and things that are company-controlled where you can typically drive activity.
Constants, also known as “givens,” are values that aren’t expected to change. These include assumptions, historical data, internal policies or facts.
Semi-variables, also known as step variables, are inputs used for relationships that are stable over a given relative range, and then “step” up or down to a new stable level once the range is exceeded.

There are also contextual drivers/indirect inputs and derived inputs. The former are inputs or drivers not used in the model directly, but which may help in determining model logic or could be used in descriptive summaries to support scenarios or conclusions and recommendations. The latter are the outputs of calculations in a model that are used as inputs to different calculations in the model.

Identifying value drivers and KPIs can help you decide which inputs to use by framing the big picture and clarifying the purpose of the end product prior to getting into the details of the model. It can also help you understand the financial and economic relationships between inputs, and it can help you identify potential project or operational risks and opportunities. Value drivers that are not found to be helpful as direct or derived inputs are often still valuable as contextual inputs.

3. Gather data and assumptions and identify gaps

Gathering data and assumptions for financial projections can be challenging as it involves source systems and persons, and possibly estimates depending on the age of the project.

The three types of values that are used to generate the model logic and calculations are as follows:

Data: Objective and verifiable facts typically expressed in numerical form.
Assumptions: Either axioms, hypotheses or projections about conditions, data or business decisions that are taken for granted in a model, OR the simplification of steps used in models to approximate more complex real-world relationships.
Estimates: Casual or methodical assumptions made about the value of data.
Data are objective and verifiable; assumptions and estimates are not. When working with assumptions, you will need to take the extra step to substantiate them with reason and logic or find some other way to corroborate them.

Once you’ve identified the information you need, you need to create a plan — a simple list or detailed set of tasks, depending on the scope of the project — for acquiring it. It makes sense to start by gathering the data that’s easiest to acquire first, then move on to the more challenging pieces.

The number one source of information about an industry and its competitors? The people with whom you work. This includes internal business partners, customers and suppliers — even colleagues in finance at other companies.

There are also some specific documents that can prove helpful in your research. They include:

Income statement. If there is a profit, the income statement will tell you. It can also provide information about the company’s cost of managing its debt. Just be wary about what might not be specifically stated in the income statement; for example, one-time income from asset sales can be folded into revenue and costs and skew net income.
Balance sheet. The balance sheet contains three basic areas of information: assets, liabilities and equity. With regard to assets, be sure to look at the amount of cash on hand, inventory turnover, the accounts receivable ratio, and the change in the level of fixed assets.
Cash flows statement. Shows you the sources and uses of cash produced from operations, interest on investments and financing.

Other good sources of information include professional associations, data collected by government agencies, and ratings agencies such as Moody’s, Standard & Poor’s (S&P) and Fitch.

As each piece is collected, you will need to validate it, checking for outliers or bias, and document the source of the data. Validation is important to ensure you understand exactly what you’re getting, which includes knowing how the information is categorized, counted, the units of measure, etc.

And be sure to place all data in a repository. The repository can be worksheets with various categories of data or a database exclusive to the project.

Because the accuracy of the model is directly affected by the accuracy of the estimates and assumptions, it is very important to set rules as to how they are made and documented. Documentation should include (at least) the following: the estimate or assumption, name of the person who provided it, the date it was made, and any notes regarding assumptions the provider made when generating an estimate so that estimates from other sources are comparable.

Estimated data are required whenever historical data and facts are not available or incomplete. This becomes particularly important in a case where no base or seed data are available, which happens when the information is related to a new product or area of the business. In these cases, you can’t just guess. Instead, employ one of the following methods: Find a similar situation, product or project; find an empirical basis to justify your estimate or assumption; or use triangulation.

An information gap analysis is as its name implies: comparing the current set of information to the desired set of information to assess where the gaps lie. At the same time as you’re gathering data, you need to be listing all identified data gaps. Why is it important to do this concurrently? By doing it concurrently, you are simultaneously identifying the areas where planning or analysis can begin, as well as those spots where additional data is needed first.

For example, you can’t perform a cost analysis until you have all the cost data.

4. Identify missing information

Now you’re going to drill down looking for the less obvious information gaps. Missing information tends to fall into one of two categories: (1) The required data has not been captured, or (2) the data is “sparse,” meaning it has been captured, but there are holes in it.

It’s important to determine why information gaps exist — can you identify the root cause? — and establish which gaps are critical, or at least material, to the analysis.

Start by performing a preliminary data review with the goal of determining:

If there are any gaps within individual data sets.
If the information appears to be reasonable and useful.
If existing data could be used to extrapolate values for some of the gaps.

Where there are data gaps, do you know the reason why? It’s important to get to the bottom of it in order to prevent it from happening again. Some of the most common reasons for missing data include poor database controls, worksheet errors, miscommunication and data entry errors. But more often than not, the real root cause is that no one has ownership and accountability for the quality of the data.

Which of the missing data or variables are the most relevant and require additional effort to gather? Which could be replaced with proxies or assumptions? Which could be omitted entirely? This is the next step in the process — making these determinations. To help you make that determination, you’ll want to consider criticality, or how the data will be used in the model, and materiality, or the level of impact having better precision would provide.

Now it’s time to identify and contact the owners of the missing information. Hopefully you’ve established a clear network and knowledge of who has the requisite information. You should also be aware of who their backup person is, or at least who would know the person you should talk to.