Development of Financial Models

We offer customized financial models to access the viability of their decisions. Our financial  models provide instant and accurate answers to the most complex investment and financing issues.

The models are tailored and custom-made to the client’s needs, its structure, and nature of operations. Financial Models include:

Financial Viability

Leverage Buy Out(LBO) Model

MBO Models

Three Statement Model-Balance Sheet, Cashflow, Profit or Loss/Income Model

Discounted Cash Flow (DCF) Model

Merger Model (M&A)

Initial Public Offering (IPO) Model

Sum of the Parts Model

Consolidation Model

Budget Model

Forecasting Model

Option Pricing Model

Valuation/ Investment Evaluation models

Funding and Optimal Financing models

Sophisticated Value Creation models that integrate the investment and financing decisions

Working Capital Management and Budgeting

Appraisal and Asset Pricing models

Three Statement Model

The 3 statement model contains three statements (income statement, balance sheet, and cash flow). The models works with given assumptions that drives it with all the statements linked together. Knowledge of accounting, finance, and Excel skills are required

 

Discounted Cash Flow (DCF) Model

This model utilizes the 3 Statement model to predict valuation of a company, net present values, costs of capital etc. It is particularly used in valuation of a company. The future cashflow values are discounted at todays cost of capital.

Merger & Acquisition Model (M&A)

The Merger Model analyzes the merger of two companies under mutual agreement. Whether it is a combination of two companies under mutual agreement where shares are exchanged, one company pays cash to acquire shares in another or pay both cash and shares to acquire majority stakes, it is a merger.

It is given here below:

 

Company A + Company B = Merged Co.  The level of complexity can vary widely and is most commonly used in investment banking and/or corporate development.

The mains steps for building a merger model are:

• Making Acquisition Assumptions
• Making Projections
• Valuation of Each Business
• Business Combination and Pro Forma Adjustments
• Deal Accretion/ Dilution

 

1. Making Acquisition Assumptions

Create an  operating forecast for both companies and determine the feasible range for the proposed purchase price. The acquiring company can offer cash, stock or a combination of both as consideration for the purchase price.

Where the buyer’s stock is undervalued, the buyer may decide to use cash instead of equity since they would be forced to give up a significant number of shares to the target company.

In contrast, the target company may want to receive equity because it might feel more valuable than cash. Finding an agreeable consideration to both parties is a crucial part of striking a deal.

Key assumptions include:

• Purchase price of the target
• Number of new shares to be issued to the target (as consideration)
• Value of cash to be paid to the target (as consideration)
• Synergies from the combination of the two businesses (cost savings)
• Timing for those synergies to be realized
• Integration costs
• Adjustments to the financials (mostly accounting related)
• Forecast / financial projects for target and acquirer

#2 Making Projections

Make assumptions about revenue growth, margins, fixed costs, variable costs, capital structure, capital expenditures, and all other accounts on the company’s financial statements as in the 3 Statement model or DCF.

3 Valuation of Each Business

Conduct a DCF analysis of each business based on comparable company analysis and precedent transactions.  Assumption to be made are:

• Performing comparable company analysis
• Building the DCF model
• Determining the weighted average cost of capital (WACC)
• Determining the terminal value of the business

#4 Combination and Adjustments

Where company A acquires company B, the balance sheet items of company B will be added to the balance sheet of company A.  Some of these adjustments may comprise the value of goodwill, the number of shares, cash equivalents, etc.

Key assumptions include:

• The form of consideration (cash or shares)
• Purchase Price Allocation (PPA)
• Goodwill or impairment calculation
• Any changes in accounting practices between the companies Synergies calculation

#5 Deal Accretion/ Dilution

We conduct an  accretion/dilution analysis is to determine the effect of the target’s financial performance on the buyer’s Pro Forma Earnings per Share (EPS). A transaction is deemed accretive if the buyer’s expected EPS increases after acquiring the target company. Conversely, a transaction is viewed as dilutive if the buyer’s EPS declines after the merger. The buyer should estimate the effect of the target’s financial performance on the company’s EPS before closing a deal.

Key assumptions include:

• Number of new shares issued
• Earnings acquired from the target
• Impact of synergies

4 Initial Public Offering (IPO) Model

This model values a business  in advance of the company listing its equity on an Exchange or going public called Initial Public Offering. The model looks at Comparable Company Analysis alongside specific assumption, adjusting for IPO discounts before market listing.

5 Leveraged Buyout (LBO) Model

A leveraged buyout transaction typically requires modeling complicated debt schedules and is an advanced form of financial modeling.  An LBO is often one of the most detailed and challenging of all types of financial models as the many layers of financing create circular references and require cash flow waterfalls.  These types of models are not very common outside of private equity or investment banking.

A leveraged buyout is the acquisition of a public or private company with a significant amount of borrowed funds or debts. A firm (or group of private equity firms) acquires another company using debt instruments as the majority of the purchase price. In most cases, post purchase of the company, the debt/equity ratio is generally greater than 1.0x (debt generally constitutes 50-80% of the purchase price).  Upon assuming controlling shares and management, the new  ownership uses the cashflows of the acquired company to service the debts. The overall return realized by the investors in an LBO is determined by the exit cash flow of the company (EBIT or EBITDA), the exit multiple (of EBIT or EBITDA), and the amount of debt that has been paid off over the time horizon of the investment. Companies of all sizes and industries can be targets of leveraged buyout transactions, although certain types of businesses, as discussed earlier, make better LBO targets than others.

A sample LBO model given to candidates during interviews can be used to test on a variety of issues:

Determining a fair valuation for a company (including an ability-to-pay analysis)

Determining the equity returns (through IRR calculations) that can be achieved if a company is taken private, grown, and ultimately sold or taken public

Determining the effect of recapitalizing the company through issuance of debt to replace equity

Determining the debt service limitations of a company from its cash flows

In order to prepare for an LBO modeling test, the first step is to understand the key assumptions and the process.

Step 1: Assumptions of Purchase Price, debt interest etc

Step 2: Creating Sources of funds and The Uses of Funds for the buy out

With the information of purchase price, interest etc, then a table of Sources and Uses can be

Step 3: Financial Projections

In this step, we project financial statements i.e. Income Statement, Balance Sheet, Cash flow Statement, usually for the period of 5 years

Step 4: Balance Sheet Adjustments

Here, we adjust the Balance Sheet for the new Debt and Equity.

Step 5: Exit

Once the Financial Projections and adjustments are done, assumptions about the private equity firm’s exit from its investment can be made.

A general assumption is that the company will be sold after five years at the same implied EBITDA multiple at which the company was purchased (Not necessary)

Step 6: Calculating Internal Rate of Return (IRR) on the initial Investment

There is a reason why we calculate the sale value of the company. It allows us to also calculate the value of the private equity firm’s equity stake which we can then use to analyze its internal rate of return (IRR) to determine how much you are going to get back on your initial investment.

#6 Sum of the Parts Model

“Sum-of-the-parts (“SOTP”) or “break-up” analysis provides a range of values for a company’s equity by summing the value of its individual business segments to arrive at the total enterprise value (EV). Equity value is then calculated by deducting net debt and other non-operating adjustments.

For a company with different business segments, each segment is valued using ranges of trading and transaction multiples appropriate for that particular segment. Relevant multiples used for valuation, depending on the individual segment’s growth and profitability, may include revenue, EBITDA, EBIT, and net income. A DCF analysis for certain segments may also be a useful tool when forecasted segment results are available or estimable. “http://macabacus.com/valuation/sotp

Applications

SOTP analysis is used to value a company with business segments in different industries that have different valuation characteristics. Below are two situations in which a SOTP analysis would be useful:

Defending a company that is trading at a discount to the sum of its parts from a hostile takeover

Restructuring a company to unlock the value of a business segment that is not getting credit for its value through a spin-off, split-off, tracking stock, or equity (IPO) carve-out

This analysis is a useful methodology to gain a quick overview of a company by providing a detailed breakdown of each business segment’s contribution to earnings, cash flow, and value. many companies can be viewed as a candidate for break-up valuation. The table below provides a number of examples: http://macabacus.com/valuation/sotp

Methodology

(1) Gather segment-level data from any of the following sources:

Investor presentations

Corporate web sites

Research reports

Latest annual report, 10-K, or 10-Q

Moody’s company profiles, S&P tearsheets

(2) Spread LTM and, to the extent possible, projected financial data for each business segment

Typical financial metrics used include EBITDA, EBIT, and net income

The SOTP financial information should equal the consolidated financial information for the entire company

As necessary, an “other” category may be used, but care should be taken to determine the nature of this category in order to assess multiples, value, etc.

Allocate corporate overhead to divisions based on percent of revenues, EBIT, or industry norms for each segment. It is also acceptable to value overhead as a standalone item

If depreciation and amortization are not provided by segment, allocate to divisions using methodologies that may include percent of assets, revenues, EBIT, or industry norms for each segment

Use your judgment to the extent necessary

(3) Determine an appropriate range of multiples for each business segment by applying metrics and multiples which are most relevant for each business segment

Use either trading or transaction comps, as appropriate, for each industry to determine the appropriate range

Use a range of multiples, not point estimates

To the extent overhead was not allocated, apply blended multiples to determine the “negative value” of overhead. Since this may create misleading values for the individual segments, allocating overhead is preferable, assuming there is sufficient data

DCF valuations may be useful for certain business segments

(4) Sum the values of each business segment, offset by corporate overhead, if appropriate. The result is an aggregate EV range for the consolidated company.

(5) Deduct net debt and add/subtract other non-operating/financial items from the EV range to determine a range of equity values.

(6) Divide by the sum of diluted shares outstanding to arrive at a range of equity values per diluted share. Be sure to include any in-the-money options and convertible securities.

(7) Other considerations:

Minority interest could be attributable to a single segment or may have components from all segments. If attributable to a single segment, be sure to make note of it in the valuation analysis

Similarly, certain liabilities may be attributable to one or more segments, or may be entirely separate

Interpreting the Analysis

Compare the range of results to current trading levels and ask: “At the current share price, is the company being undervalued or overvalued compared to the SOTP equity value per share?” Also, compare results to any of the following metrics and look for consistency with the calculated range:

52-week high and low

Comparable companies analysis

Wall Street research

It is also important to “sanity check” the results. To do so, ask yourself what the key value drivers are and whether or not one segment is driving/distorting the overall company value.

http://macabacus.com/valuation/sotp

#7 Consolidation Model

“This type of model includes multiple business units added into one single model. Typically each business unit is its own tab, with a consolidation tab that simply sums up the other business units.  This is similar to a Sum of the Parts exercise where Division A and Division B are added together and a new, consolidated worksheet is created. Check out CFI’s free consolidation model template”.

 

#8 Budget Model

Budget models are typically designed to be based on monthly or quarterly figures and focus heavily on the income statement. Budgeting and financial forecasting are tools that companies use to establish a plan regarding where management ideally wants to take the company (budgeting) and whether it is heading in the right direction (financial forecasting).

Budgeting quantifies the expectation of revenues that a business wants to achieve for a future period.

Budgeting

A budget is an outline of expectations for what a company wants to achieve for a particular period, usually one year. Characteristics of budgeting include:

• Estimates of revenues and expenses
• Expected cash flows
• Expected debt reduction

A budget is compared to actual results to calculate the variances between the two figures.

Budgeting represents a company’s financial position, cash flow and goals. A company’s budget is usually re-evaluated periodically, usually once per fiscal year, depending on how management wants to update the information. Budgeting creates a baseline to compare actual results to determine how the results vary from the expected performance.

#9 Forecasting Model

This is building a forecast that compares to the budget model. Financial forecasts estimate future results based on assumptions, historical firm data, and external market indicators. A financial forecast allows you to expend resources and understand your financial performance, make better decisions, and prepare for the future.

Financial projections are a forecast of future revenues and expenses for a given business. Typically, the projection will account for internal or historical data and will include a prediction of external market factors.

Developing financial models, forecasts and projections can be overwhelming.

Our service include:

Assisting you in understanding your true financial position, operational outcomes, and cash flows. Offer Top-level reporting to detailed financial models.

Calculating cash inflow and outflow

Predicting slowdowns in sales or shortfalls in cash reserves

Suggesting ways to improve cash flow

Determining your borrowing needs

Detailed review of current cash flow environment

Planning for asset purchases, expansion, and taxes

Developing growth and investment strategies

Budget review and analysis

 

#10 Option Pricing Model

We make use of Binomial Trees and the Black-Scholes Model for Option Pricing. The model deploys mathematical formulae

 

To learn more about how we can help you, please contact us or Call/Whatsapp 08023200801