Updated: Oct 23, 2020
Picture by KG Shreyas Thimmaiah, CC BY-SA 4.0 <https://creativecommons.org/licenses/by-sa/4.0>, via Wikimedia Commons
Today's post is the first of three articles about evaluating pro forma financial models. During my career as a commercial banker and consultant, I have had many opportunities to create and modify pro forma models, and I'd like to share my observations with you.
"Pro Forma" is just a fancy term for a forecast. It is an important management tool, as it is used by management and investors to make critical decisions. For management, the pro forma financial helps determine operating expenses, headcount, borrowing needs, and the profit available for reinvesting in the company. For investors, the pro forma financial provides useful insights on burn rate, growth rate, and the potential rate of return to the investor.
When evaluating a pro forma financial model, there are three key considerations:
The integrity of the spreadsheet - is the model structured properly, without any errors?
An analysis of the actual forecasted data - are the assumptions reasonable and do the numbers make sense?
The correctness of the model - does the pro forma financial model accurately reflect how the underlying business operates.
So the actual process for evaluating the pro forma financial model has three steps:
Audit the spreadsheet for errors.
Confirm that the pro forma financial model is a true representation of the business model.
Analyze the financial statement.
The rest of this post will review the process of auditing the spreadsheet for errors.
The first step is to look at how the spreadsheet tabs are organized. A proper model will put the data, assumptions, calculations, and reports (text or graphic) in separate tabs to facilitate scaling and modification of the model, as well as preserve the integrity of the data.
Also, the spreadsheet tabs should be organized in a logical flow, as illustrated in the picture below.
Next, take a look at the spreadsheet to see if there are any mistakes. If the modeler has a good process for creating pro forma financial models, so that the model is reviewed for errors during the creation of the model, then there should be few, if any errors.
However, mistakes can happen despite a very thorough review of the completed model, for several reasons:
Developing a spreadsheet model is a complex task.
"Small mistakes" are hard to detect,
We make more mistakes than we realize.
Related to (3), we catch fewer errors than we think we do.
More often than not, mistakes are caught once the spreadsheet is in use, not during the development stage.
I won't go into great detail about spreadsheet mistakes because I have already written about this subject in a different series of posts. For more information, please check out these links:
When the spreadsheet review is completed, the next step is to confirm that the spreadsheet model accurately reflects the business model. We cover that in the next article in this post. To view, click the link below: