Building a Pro Forma Analysis

Need to run scenarios for your business? Pro Forma analysis is the perfect tool! Let’s discuss what a Pro Forma is, how it is used, and how you can start building a Pro Forma analysis in Excel.

What is a Pro Forma Analysis?

A Pro Forma is a way of creating financial statements based on hypothetical scenarios. This is a great tool to test strategies and management decisions before executing. At its core, Pro Forma blends actuals and known information with projections. The scenarios that result can then be used to inform management of decisions.

Common Use Cases

• Full-year projections – If you have ever done a mid-year forecast, you were technically doing a Pro Forma. By taking 5 months of actuals and blending it with 7 months of the forecast, you created a full-year Pro Forma.
• Investment projections – Pro Forma analysis is a great tool to evaluate investments, whether they be assets, new product lines, or really anything. You can project the impact of the investment as a standalone or roll it into your overall financials to get the complete picture.
• Adjust for acquisitions or divestitures – When buying or selling a company, it is helpful to create financial statements showing the business with or without the change. That way, you can better-set expectations for future performance. You can also have a better benchmark for management reporting.
• Adjustments to accounting standards – When major accounting standards change, such as ASC 606, you can create a Pro Forma to show financial statements with or without the change. Similar to acquisitions and divestitures, this provides a better benchmark for reporting and future performance.

Building a Pro Forma Analysis

Step 1: Determine the goal and set expectations

Before starting the analysis, you need to understand the goal. Which of the common use cases above are you working on? That will determine the inputs you need to collect and how you approach the model. It is also important to set expectations. A Pro Forma isn’t magic; it is simply a set of assumptions layered onto known data. Since assumptions have limitations, you may want to include ranges or caution that the results are subject to change.

Step 2: Compile inputs

Now, you will want to compile all of the inputs. In almost every case, start by collecting the existing actuals and forecast data for the business. Once this is complete, you will collect the revenue, expense, cash flow, and other impacts from the business you are evaluating. Include any impact, big or small, that the change will have on the businesses’ financial statements. Start by laying out the base case, or current state.

Step 3: Build the Analysis

In this step, we begin by modeling in Excel. First, you will layout the base case or current state. After this, you will layout the incremental changes. This can be pure profits and loss impacts, or it can evaluate balance sheet and cash flows. Finally, add the current state to the incremental changes to create the future state or scenario case. It is critical that you clearly show all three stages of the analysis to allow for thorough analysis and review.

Many times, you will be asked to go back and compare actual performance to the Pro Forma. Think about how you can build rate and volume analysis into the pro format to make it easy to compare back to. Clearly showing the incremental change allows you to evaluate its performance against the Pro Forma, even if the base case changes.

Step 4: Run scenarios as needed

Once you have created the core model, you can copy it off and run scenarios using different assumptions. This can help create ranges and identify the limitations of the different assumptions. Scenarios are a great tool for leadership to evaluate multiple impacts of the change being proposed.

Example: Invest in a new delivery vehicle

Step 1: Determine the goal and set expectations

A new delivery vehicle is an asset that we want to invest in, so this is an investment projection. We take our existing business performance and add the extra delivery capacity and costs. The major limitation of this pro forma will be that we can not be certain of the excess demand available. We can, however, be fairly certain of the new expenses. For this exercise, we will be evaluating the profit and loss impact and not the cash flow or balance sheet impact.

Step 2: Compile inputs

The inputs in this case can be grouped into three main categories:

• Current financials
• Potential revenue from the new vehicle
• Expenses from the new vehicle

Step 3: Build the Analysis

We will lay out the current financials as a Profit and Loss statement and the new vehicle details as inputs to the model. Pro Formas can become extremely complex, so make sure to keep the Excel workbook organized.

Base Case – Profit and Loss Statement

Inputs for Vehicle Expenses

Now that the inputs are laid out, we will add an incremental change section to the profit and loss statement. This will show just the revenue and expense changes from the new vehicle. Finally, sum the base case and the incremental change to create the finished Pro Forma.

Step 4: Run scenarios as needed

In the analysis, we assumed 100% utilization of the new delivery vehicle. In step 1, we determined that the highest risk to the analysis was that we couldn’t be certain of demand. So let’s run a scenario where the new delivery vehicle is only utilized 80% and confirm that the investment is still profitable.

The incremental investment is still profitable, although the margin is much tighter. This means you have a margin of error but should carefully evaluate the demand to ensure this third vehicle isn’t overshooting.

Have any questions on building a pro forma analysis? Are there other topics you would like us to cover? Leave a comment below and let us know! And make sure to subscribe to our Newsletter to receive exclusive financial news right to your inbox.