Data Sourcing for Financial Modelling
Written by Paul Barnhurst and Lance Rubin
Introduction to the co-author
Paul Barnhurst is the Director of Financial Planning & Analysis at Solera. Solera is a global private equity held Automotive SaaS company and Paul supports the division that provides software services to automotive dealerships in the US and Canada.
Paul’s career started working for the Navy as a Civilian writing procurement contracts but for the last 10+ years he has been working in finance. During that time, he has worked as a report writer focusing on KPI’s and as a forecaster providing FP&A support for three different companies. He loves the challenge of FP&A and has always enjoyed sourcing the data necessary to build models required to track and measure the performance of the business.
Paul graduated with a Master of Science (MSc) in Information Management and Master of Business Administration (MBA) in Finance, from the WP Carey School of Business in Tempe, AZ. During MBA school he had an internship where he worked for American Express as part of a data integrity team analyzing the accuracy and integrity of credit card data. Since that time, he has always considered himself somewhat of a “Data Guy” even though he works in the finance profession.
Paul is constantly striving to learn new skills and over the last few years has been focused on growing his Power Query, Power BI and Excel skills through reading books, and learning from various Excel MVP’s on YouTube and the web. He recently signed up for the Advanced Financial Modeler Exam from the Financial Modelling Institute, after hearing about the benefits of the exam from Lance for quite some time.
Why did Paul select the topic and why is he passionate about it?
This article focuses on Data Sourcing and a holistic approach to understanding and sourcing the right data for each project. Paul selected the subject because he has always had a passion for data and has believed good data is critical to ensuring a financial model provides valuable outputs.
A recent study found that”84% of CEOs are concerned about the quality of the data they’re basing decisions on, according to KPMG’s “2016 Global CEO Outlook.” If a company’s leadership cannot trust the quality of the data, they lose faith in the analysis conducted and conclusions drawn from the data. This often can result in suboptimal and emotional decision making that are political and often not in the best interests to customers, shareholders and staff.
Early in my career while working for the Navy I saw firsthand the impact of bad data as I worked with a software that allowed anyone to easily manipulate the inputs into the database and the results were disastrous. As a department we struggled with having reports we could trust because the source of the data was so bad.
It took a lot of extra effort and time to get quality data due to poor design of the software tool we were using. We had an entire data analysis team and we spent most of our time figuring out how to get good data into and out of the system.
This was a major waste of resources and money that could have been better spent elsewhere had a proper database been built. It also prevented the organisation from leveraging the value that data can provide through insight and foresight rather than simply extracting and reporting it. The time to analyse the data was severely impacted.
More recently Paul’s interest in the topic grew due to a work project that nearly fell apart due to bad data. It took several months to understand and source the right data due to poorly designed databases, lack of proper data dictionaries and inconsistencies in reporting.
If the data sources had followed best practices and if the data had been easy to source the time to gather the data would have went from months to a couple weeks or less at most. Working through this project to eventually provide a valuable revenue model reignited his passion for what he likes to call “Good Data”.
Topic and context in no more than a few sentences
At its core and in its most simple form data sourcing is the process one goes through to gather the data necessary to build a financial model. The key parts of data sourcing include:
Knowing where to find your data (think treasure hunt).
Knowing how to interpret the data and deciding what data to use (think an antique dealer assessing the treasurer to understand what it is and its use).
Understanding the quality of the data sourced (think that same dealer but now after some research he can assign some value to that item).
Automated Extraction of the data (think of a machine digging out the treasure vs a human with a spade).
Creating a Data Dictionary (think of a GPS locator to understand where to go vs a old ripped treasure map in hieroglyphics) .
Each of the above areas are necessary to understand data sourcing and to develop a holistic approach to finding valuable inputs to the financial model.
If you had to teach this topic in a class to school kids what key tips would you give them to focus on?
If I was teaching this subject to kids in school, I would focus on the importance of knowing how to interpret the data and understanding the quality of the data.
Most people who go into financial modelling will often start by working in the investment banking industry.
With that in mind I would tell kids to focus their time on understanding a public companies’ financial disclosures (think their report card of how well they are doing in the school of business) .
For a U.S. company one would focus on understanding the 10Q (quarterly report or think school term report) and the 10K (annual report or think of the end of year report).
I would recommend starting with the 10K and reading them for a few companies that you are interested in a 10K will include some of the following information (think of it like reading the ingredients on a cereal packages, some of it is good for you and other bits are not so good):
Overview of business and risk factors
Legal proceedings – pending lawsuits and other legal proceedings
Management’s Discussion and Analysis (MD&A) of the business
Quantitative and qualitative disclosures about market risk
Financial statements and supplementary data
As one is learning to understand and interpret a company’s financial statements, I would focus on first understanding the industry the company operates and its stated strategy (think future plans).
This information can be found in the overview of the business and the management discussion and analysis sections. Many people look at financial modelling as a quantitative exercise but being able to qualitatively analyze the data will help set you apart.
I learned this valuable lesson from my favorite finance professor in MBA school Professor Gallagher.
For every assignment we did he always required us to do a complete strategic analysis in our write-up of the case prior to creating the financial model.
Reviewing the data and looking at the business strategically always helped me understand what data to use and the future assumptions to make in the modelling.
In addition to doing a strategic review of the data as you source it, I recommend spending time understanding the notes to the financial statements.
This will tell you about changes in accounting methods, inventory method used, depreciation schedules, treatment of leases and other key financial information to interpret and understand the financial statements from a business and cash flow perspective
The financial statements and notes are truly key to understanding the data you have to source.
It is easy to just extract the data and to start modelling a business but without understanding the supplementary information one will not be able to build a robust model that is fit for purpose and solve the problems that will add the most value in any decision.
If you don’t have a clear understanding of the financial statements, notes and the strategy then going on a treasure hunt for data without understanding what you are looking at and for or where you are going strategically is pretty pointless.
Nothing like wondering in the data desert for 40 years like Moses, only thing is that you are unlikely to get divine intervention to sort out your data issues.
By understanding the financial statements, you will understand where you need to go and what it should look like when you get there and most importantly that is aligned to the strategy and the problem you are trying to solve.
Solving real business problems and helping better decision making is where the pot of gold lies.
If you understand the financial statements whilst hunting for treasure it's like being told which cave to go looking into rather than randomly diving down many different rabbit holes or caves and getting lost.
What practical steps can everyone take now to learn more?
For anyone focused on learning how to source data whether for a financial model, data model or some other purpose we would recommend learning some best practices when working with data.
A key aspect of this is also related to data management being the foundations of good data practices.
So many people waste a lot of time in trying to analyze data because they do not know how to source and structure the data in such a way to make the analysis easy and efficient.
Spend more time understanding the process flow, management and definitions of the data rather simply diving into the deep end.
We have all seen and we would wager to guess many of us have built a model that is hard to follow whilst we simply dumped all the data, we thought would be useful over time.
The way we sourced, extracted and layered the data probably played a big part in making the model more challenging to follow than it needed to be if we considered it in a more structured manner.
In our careers we have all spent way too much time sourcing, cleaning, and extracting data, rather than analysing it.
We have all worked in organizations where the data was a mess.
Without data management and modelling skills we would have never been able to build the model or conduct the analysis required to help drive the business forward.
We would recommend for anyone looking to learn ways to become better at sourcing data to do the following:
To use data effectively we must be effective at data management. This link from Stanford Libraries is worth a closer look on the data management best practices. Another great link is on 7 best practices for effective data management is from Leadspace Inc.
Become familiar with the various data terminologies and understand their benefits eg data dictionary, process flows and associated acronyms. It can feel like a different language but once you learn how to understand and speak the language of data it will be much easier.
Gain an understanding of data modelling best practices, not so that you can start to do complex coding and modelling, but just so that you can understand what it means and how you can leverage the skills often gained by other data specialists. Here is a good article on normalizing and managing data for modelling purposes and related helpful tips.
Gain an understanding of the fundamentals of ETL (Extract, Transform & Load), even if it’s a basic data structure using existing tools in #Excel and #PowerBI, which are free with Office 365.
Become familiar with the different websites that one can use to obtain public financial statements, some of which are listed below for ease of reference.
Spend time learning how to read and understand public company disclosures. Pick a favorite company or industry and start reading next time you have free time to get a deeper knowledge of the company or industry. Especially if you are going for a job interview in that company or industry.
Learn how to actually extract data from publicly available data sources and statistics that you can scrape through either #Excel (via #PowerQuery) or #PowerBI (which has the same functionality as #PowerQuery embedded as the query tool).
Research and play with data so that it becomes familiar. Don’t worry about getting to complex into code writing etc, leave that for the hard core IT and data people, but try to get closer to what they do and work closely with them.
Where are good places (links) to find out more on the topic?
Below are some places people can find good data. Depending on the data needed and the country you live in the website or sources you go to obtain the data will vary.
Company website you are analyzing will have links to all public fillings.
You can also find information on public filings with the government agency that are required to file there public records with such as the SEC:
10-K – This is filed annually includes overview of the business, management discussion of the business, and audited financial statements.
10-Q – Provides an update on the company’s financial position every quarter and contains unaudited financial statements.
8-K.
Proxy Statements
Below is a list of websites that can be used to find pubic filings and research on public companies:
Australia - www.asx.com.au/asx/share-price-research/company/TLS (Telstra example)
Globally available information from www.bloomberg.com
Below is a list of some great websites to find demographic and economic data:
The above list contains a few of the many websites one can go to in order to find financial, economic, and demographic data that one might use in building a financial model.
How important is this skill in the context of learning FM?
In order to become a complete modeler (both data and financial modeler), we believe understanding and developing a holistic approach to data sourcing is critical to being a good financial modeler.
We have all heard the adage “Garbage in Garbage out” and that is especially true when it comes to data and modelling.
One can be an expert at setting up models in #Excel, or any other tool for that matter, but if one does not understand the business problem, strategy and how to source and interpret what data should go into a model the results, value and insight you may extract will be of limited value to those that matter most.
The amount of time spent sourcing data and how important it is will also vary depending on the kind of projects one is modelling.
If you plan and think about the end in mind first before going in to just scoop up all the data and dump it into #Excel that you can get your hands on, you will save a lot of wasted time gathering too much data that is of little value and having to sift through to find the nuggets.
If one is only modelling large public companies finding, interpreting and automating the data will be easier than modelling a project or a start-up where there is limited data available for that project or start-up.
For modelling a public company, the most difficult part is making sure one fully understands the financial statements, strategy and problem that the data is trying to solve and only then where to find data to ensure the model accurately reflects the nuances of the companies accounting practices, strategy and financial statements.
At the end of the day being able to source the correct data is critical to financial modelling.
The ultimate purpose of a financial or data model is to provide assistance in making better business decisions and that starts with sourcing the right (quality) data for the right problem.
How does all this disruption, AI and automation talk impact this topic?
All the current AI and automation talk will play a role in data sourcing and can help in the process of collection data quicker and even to some extent transforming and cleansing it automatically.
The advances being made in AI and machine learning will make it easier to find and automate the data.
Compared to only a few years ago it has become significantly easier to automate the pulling of financial data from a source into a model vs copying and pasting the data or even manually entering it into an Excel spreadsheet.
Tools like Power Query go a long way to make this easy for even the non tech savvy with no coding required.
As voice assistants and text searches continue to improve it will make finding and sourcing the data we want even easier than it is today.
What it will not do is eliminate the need for judgment and understanding of the problem and strategic nuances that define the type of data that is needed.
Gut feel, experience and professional judgement on the quality of the data and the outputs you are working with will still play a very big part.
What parts of the data you need for your model will be driven by the strategy and the problem and will therefore remain a useful skill to have and difficult to replicate through AI.
If you want to find out more and follow the rest of the article series be sure to download the Financial Modelling App
If you want to find more information on financial modelling and content visit the Model Citizn website.