Summary:
Following Pro Forma Models best practices improves the model user interface and provides transparency
It helps to understand the pro forma model purpose, desired outcome, and end users before beginning development
Microsoft Excel comes with prebuilt formatting tools that are accessible via short cuts and widely understood
Utilizing standardized formatting, data validation, and checks within the model increases effectiveness and usability
Don't get discouraged if your model is not perfect on the first iteration, pro forma model development is an iterative process
Model Lifecycle:
A typical pro forma model lifecycle is illustrated below:
Identify Purpose:
It can be tempting to dive head first into pro forma model development, but before embarking on development it is worthwhile to spend time clearly understanding the purpose of the model. At a high-level, it's beneficial to be able to answer the following questions at completion of this phase:
Why do I need to develop this model?
What is the problem I am trying to solve?
Do I have an existing model that can solve this problem?
Clarify Desired Outcome:
Once you have a clear understanding of the pro forma model purpose, it's helpful to visualize the outcome. It helps to understand the purpose, expectations, and desired outcome before starting the model development. Key questions to answer during this phase include:
What are the key metrics or outcome calculations required?
What data do I need to incorporate into the model to solve the problem?
How will I measure model success?
Understand End User:
The developer of a pro forma model may not always be the end user for the output or the individual tasked with maintaining the model. Due to this, it is important to understand the end user to ensure that you are meeting their needs now that you know the model purpose and desired outcome. Important questions to answer during this phase are:
Who will be the end user of the model?
What is the technical skill-level of the individual that will be running the model once complete?
Who will be the end user of the output of the model?
What is the format the end user of the output desires?
Structure Model:
Structuring can make or break a pro forma model. We have seen high-quality and accurate pro forma models be rendered unusable due to poor structure and format. The model structure can be viewed as the user interface and a key piece to providing transparency. The most important component of structuring is consistency within the model. It is important to select key formatting aspects and stick with those selections through the entire model. Below are some best practices to be applied when developing pro forma models:
Number Formatting: There are a variety of custom number formats that can be utilized in Microsoft Excel. We suggest sticking with the basic number formatting items accessible from the task bar that can be quickly accessed with shortcuts (for example, Control + Shift + $ to employ the currency number format). These formats are standard and easy to understand for users, which should reduce confusion for number values and units.
Account Formatting: A pro forma model best practice for account formatting is:
Revenues: Bold with top border
Expenses: Bold with top border
Earnings Before Taxes: Bold with top and bottom border
Net Earnings: Bold with single top border and double bottom border
Assets: Bold with top border
Liabilities: Bold with top border
Equity: Bold with single top border and double bottom border
Cashflow From Operations: Bold with top border
Cashflow From Investing: Bold with top border
Cashflow From Financing: Bold with top border
Cell Color Formatting: This helps users clearly understand which cells they can type into and which cells are calculations
Yellow fill with blue text: User input/assumption
Black: Calculations and references on the same tab
Green: Calculations and references to other tabs withing the model
Conditional Formatting: Microsoft Excel has an extremely valuable built in feature called conditional formatting, which can apply formatting to cells based on a set of rules you program. For example, conditional formatting can be applied to highlight a cell green if actual revenue is greater than budget or highlight a cell red if actual revenue is less than budget. As a rule of thumb, we like to stick with the preprogrammed Microsoft Excel conditional formatting styles but users can get creative and customize formatting. Conditional formatting is a useful tool to highlight key items to end users without having to manually update formatting whenever numbers change.
Heading Formatting: A pro forma model best practice is to follow a simple formatting structure consisting of the following for headings:
Heading: Dark color with a light font
Subheading: Lighter shade of color used in the heading with black font
Featured Line Items: Light bright fill, such as yellow or orange, with black font
In addition to the above, it is important to think about how many tabs will be utilized and how all the tabs will communicate with each other. The below questions are also helpful to answer during this phase of pro forma model development:
How detailed does the model need to be?
What inputs and outputs are required?
What are the formatting requirements of the model?
How will I organize the assumptions and inputs?
Preserve Integrity:
Pro forma models in Microsoft Excel can be as dynamic, complicated, and robust as you desire. With added complexity comes increased importance to preserve the model integrity to ensure all model components continue to function as intended. Some best practices to employ for protecting model integrity include:
Model in sanity checks throughout the model - for example, ensure that assets = liabilities + shareholders equity by having a check to ensure the balance sheet balances
Lock cells, tabs, and full files (if needed)
Utilize data validation as needed to ensure users are entering data that can be controlled (for example, yes/no triggers) as intended
Utilize conditional formatting to highlight outliers or inconsistencies
Test & Reiterate:
Once the pro forma model is complete, load dummy data and stress to ensure the model functions as intended. A couple of basic examples of model sanity checks include:
Increase the revenue growth assumption by 3% annually then check the income statement shows annual revenue growth of 3%
Increase the expense growth assumption by 3% annually then check the income statement shows annual expense growth of 3%
Pay down debt in year 3 by 50% of outstanding balance then check the balance sheet to confirm the lower debt value and the cash flow statement to show the paydown of debt
Performing a variety of the above stress-tests and sanity checks will help build confidence in your financial modeling capabilities as you will find errors before turning the model over to end users. If you find an error in the model or determine the model is not achieving the desired outcome, reiterate until you are satisfied with the pro forma model outcome.
Pro Forma Models employs the above best practices in all models we develop. Some examples of top selling models that showcase the best practices include:
Comentários