Financial Modeling - Recent Examples

In this article, we highlight two recent financial modeling projects performed by BPS for clients, including functionality added to provide management with the information to achieve its specific goals.

Example 1: Potential Acquisition

A client (the “Buyer”) was considering a strategic acquisition of a similar company in a different geographic market (the “Target”). To help determine if the potential return on the acquisition could justify the purchase price, the Buyer needed forecasted financial results of the potential consolidated company (i.e. the client’s financial results + the Target’s results). The Buyer had a three-year forecast for its company and a five-year forecast from the Target. However, combining the two forecasts would not consider synergies obtained through the acquisition, differences in financial reporting, or other matters that could impact forecasted future results.

The client asked us to develop an Excel-based model to:

1. Merge the two forecasts into a consistent format that generates ten-year projected consolidated balance sheets, consolidated income statements and consolidated statements of cash flows (“Consolidated Financial Statements”).
2. Create a model with input cells where purchase price, project and other assumptions could be changed to produce updated Consolidated Financial Statements and the additional financial schedules requested.
3. Create a schedule linked to the model to help the client analyze certain aggregated and disaggregated data of the consolidated entity.
4. Create a schedule linked to the model comparing the Target’s existing production capacity (fixed assets) plus planned capital expenditures to the Buyer’s existing production capacity plus planned capital expenditures.
5. Ensure the model would reflect how the acquisition would impact the financial forecast of the Company, including debt service, tax implications and intangibles.

Using this model, the Buyer was able to alter inputs and analyze different scenarios to forecast potential post-acquisition synergies (instead of the normal “we’ll realize synergies — but don’t ask us what they are or how much we think they’ll be”).

Example 2: Plan for a Potential Sale

A client was creating a plan to sell its business in the next five to seven years. The client specifically wanted to be able to consider strategic and operational changes that could maximize the sales price, without putting significant financial stress on the business.

The client asked us to develop an Excel-based model to:

1. Create a model with input cells that would generate forecasted balance sheets and cash flow statements based on assumptions and forecasted income statements (in order to assess the impact on financial ratios and debt covenants compliance).
2. Create a schedule linked to the model to generate forecasted earnings before interest, taxes, depreciation and amortization (“EBITDA”) and forecasted adjustments to EBITDA* that would likely be made by potential buyers to normalize cash flows. Using adjusted EBITDA, this schedule forecasted purchase price based on an expected multiple, which could be adjusted.

*EBITDA adjustments include typical management adjustments (add backs for unreasonable compensation, non-recurring expenses, charitable contributions, restructuring charges, if any, etc.). EBITDA adjustments likely to be proposed by buyers also included certain accounting adjustments and removal of non-acquired operations.

Using this model, the client was able to forecast various operational changes (for example, higher revenues but lower margins) to determine the potential impact on the forecasted selling price. The client also used the model to assess operational changes that would maximize the selling price.

Each model is customized to the specific circumstances and needs of a business. These models are valuable tools to help answer questions and make business decisions. The insights gained can significantly benefit your company.