Chapter 17 - Financial Modeling and Pro Forma Analysis

Introduction - Financial Goals of Long-Term Planning

As discussed in previous sections, the role of a financial manager is to maximize stockholder value. To facilitate this, managers use financial planning and modeling. One objective of using long-term planning is to identify important linkages that exist (for example) between sales, costs, capital investments and financing. With this, you analyze future impact of certain courses of action such as buying new machinery or investing in updated technology. We also use long term planning to analyze the impact of potential business plans. By building a long term model of your business in terms of finance, you can analyze exactly how such a business plan will impact the firm's free cash flows and value. Another incentive of building a long term model for your business is the fact that it allows you to plan for future funding needs. According to your forecasts, you can analyze when in the future your firm might need financing and this allows you time and more planning to acquire such resources.

Percent of Sales Method

A common place to start with forecasting is with the percent of sales method, which assumes that as sales grow, many income statement and balance sheet items will grow proportional to sales. In this case, assuming costs last year were 20% of sales, and last year's sales were $200,000 - meaning costs are $40,000 last year. If sales go up by 10% this year, we will have sales of (200,000 x .10 = 20,000 + 200,000 = 220,000 in sales this year). This means that costs will be 20% of 220,000, or $44,000.
With this information and assuming other given factors (for example) we can construct pro forma income statements that will outline the total effect of such an increase in sales. Not only will cost of goods be affected now, but so will the depreciation, interest expense, pretax income, income tax, and net income. It's a cascading effect. The slight change of one item will change the rest proportional to sales as well.
The pro forma balance sheet can also be constructed, but not until we can make assumptions about how our equity and debt will grow in proportion to sales. Once we make these assumptions, it's essentially the same process. With the pro forma balance sheet, you can point out where you will need new financing to fund growth. For example, if your assets are not balancing with liabilities and equity (and assets are higher), you will know that you need new financing. Net New Financing is the amount of additional external financing we need to secure to pay for the planned increase in assets. This can be computed as:

Net New Financing = Projected Assets - Projected Liabilities and EquityThis new financing needed is sometimes referred to as the plug. It is the amount of net new financing that needs to be added to the liabilities and equity side of the pro forma balance sheet to make it balance. However, as you are projecting this, you should keep in mind that if you take out more debt in the upcoming years, it may throw off your interest assumptions staying constant.

Forecasting a Planned Expansion

Although the percent of sales method is useful, it's shortcoming is handling the realities of fast growth requiring "lumpy" investments in new capacity. Real world firms usually don't have the pleasure of being able to smoothly add capacity in line with expected sales. In order to more accurately forecast the free cash flows, we must go through some initial steps. First, we must analyze and estimate a firm's financing needs based on the capital expenditures required for the expansion. In this step of analysis, we look at how a purchase such as a machine will help us acquire our production goals and how such a purchase will affect our income statement and balance sheet pro forma. Along with this, we must think about how we can finance the expansion. Will we loan the money or get it from other sources? Once we settle with this, we can look at the PRO FORMA INCOME STATEMENT. The value of any investment opportunity arises from the future cash flows it will generate. To estimate these, we first start with projection of future earnings. We look at how such a purchase will increase our production, and how such an output will increase our sales and cost of goods sold. From here, we go down the line in forecasting depreciation, interest expense, tax numbers, etc. In the closing steps, we must forecast a company's working capital needs, because an increase in working capital reduces free cash flows. With this, we look at things such as minimum cash required in order to keep the business running smoothly and allow for daily variations in the timing of income and expenses. With this, we know how much cash we will need to plan for in our financing activities. Will the excess cash be distributed as dividend or will it be re-invested? Those are all outcomes that must be analyzed.
With all this data, we can finally put together a forecasted balance sheet for a planned expansion. Once again, it is here that you can tell if you will need more financing in the future if assets are not balanced and greater than liabilities and equity. When the opposite is the case, where assets are less, this means we have generated more cash than we needed and can use the excess for things such as building up extra cash reserves, paying down debt, distributing to shareholders as dividends, or even repurchasing shares.

The Planned Expansion's Value

Once we have an idea of debt, net income, and working capital needs, we can better determine if the expansion is a good idea. We do this by forecasting free cash flows. To compute free cash flows (excluding leverage), we start by adding back the after-tax interest payments associated with the net debt in its capital structure. After-tax Interest Exp = (1 - Tax Rate) x (Interest Earned on Debt - Interest Paid on Excess Cash). The same method can be used to calculate unlevered net income, starting with the EBIT and deducting taxes. To compute the free cash flow from unlevered net income, we add back the depreciation and deduct increases in net working capital/capital expenditures. This computes the total cash available to all investors. To determine the amount paid out to shareholders, we can adjust the free cash flow to account for all payments to or from debt holders. If we chose to pay out all excess cash as dividend, your answer here should match that of the dividend forecasted in the previous sections. So what kind of effect will this have on the firm's value? People generally estimate a firm's continuation value at the end of the forecast horizon using the valuation multiple. Explicitly forecasting cash flows is useful in capturing specific aspects of a company that distinguish the firm from it's competition in the short term outlook. However, the long term expected forecasts should move toward one another. This means that long term expectations of multiples are likely to be relatively homogeneous across firms, bringing us to assume that a firm's multiple will move towards the industry average. In other cases, the EBITDA is used most often. It's more reliable than sales or earnings multiples because it accounts for the firm's operating efficincy and it's not affected by leverage differences between firms. To get this continuation value:Continuation Enterprize Value at Forecast Horizion = EBITDA at Horizon x EBITDA Multiple at HorizonThis shows that the EBITDA multiple at horizon will have a large impact on our value calculation.Moving on, we can finally have all the pieces in valuing the expansion. First, we compute the present value of the forecasted free cash flows of the firm over the years used. These represent the cash flows available to bondholders and equity holders, making them free of leveragae. Next you calculate the present value of the continuation value. At last, we must calculate the interest tax shield by taking the NET INTEREST EXPENSE x TAX RATE. We calculate the present value of the interest tax shield using the interest rate on debt as the discount rate. THE TOTAL VALUE OF THE EXPANSION IS THE SUM OF THE PRESENT VALUES OF THE FORECASTED UNLEVERED FREE CASH FLOW, THE CONTINUATION VALUE OF THE FIRM, AND THE INTEREST TAX SHIELD. The same process is used to calculate the value of a firm withouth expansion. Once you get the firm value numbers, you can decide if you want to delay the expansion or not. Here, you value your short term loss in production in comparison to the ultimate larger financial outlay that is greater. This timing analysis is yet another important factor.

Growth and Firm Value

We must keep in mind that not all growth is favorable. Expansion may strain managers' capacity to monitor and handle the firm's operations. Also, in the net, it may be worth less. Careful NPV analysis can eliminate bad growth decisions from good ones. Internal growth rate, or the maximum growth rate a firm can achieve without restoring to external financing is often what confuses the distinciton. It's esentially the growth a firm can supply by reinvesting its earnings. A more commonly used measure is the sustainable growth rate, or the maximum growth rate a firm can achieve without issuing new equity or increasing its debt-to-equity ratio. The Internal Growth Rate can be calculated by:

Internal Growth Rate = (Net Income / Begining Assets) x (1 - payout ratio) = ROA x retention rate

This retention rate is often called the plowback ratio, calculated as one minus the payout ratio of the firm. The Sustainable Growth Rate is calculated by:

Sustainable Growth Rate = (Net Income / Beginning Equity) x (1 - payout ratio) = ROE x retention rateSince your ROE will be larger than your ROA anytime you have debt, the sustainable growth rate will be greater than your internal growth rate. Even though the internal assumes no financing, the sustainable assumes you will make some use of outside financing equal to the amount of new debt that will keep your debt-to-equity ratio constant as your equity grows through reinvested net income. When forecasting growth greater than your internal growth rate, you are going to have to minimize your payout ratio, plan to raise additional external financing, or a combination of both. If your growth forecasted is bigger than your sustainable growth rate, you will have to increase plowback ratio, raise additional equity, or increase leverage. The downside of these rates is that they cannot tell you if your planned growth increases or decreases a firm's value.

## Chapter 17 - Financial Modeling and Pro Forma Analysis

## Introduction - Financial Goals of Long-Term Planning

As discussed in previous sections, the role of a financial manager is to maximize stockholder value. To facilitate this, managers use financial planning and modeling. One objective of using long-term planning is to identify important linkages that exist (for example) between sales, costs, capital investments and financing. With this, you analyze future impact of certain courses of action such as buying new machinery or investing in updated technology. We also use long term planning to analyze the impact of potential business plans. By building a long term model of your business in terms of finance, you can analyze exactly how such a business plan will impact the firm's free cash flows and value. Another incentive of building a long term model for your business is the fact that it allows you to plan for future funding needs. According to your forecasts, you can analyze when in the future your firm might need financing and this allows you time and more planning to acquire such resources.## Percent of Sales Method

A common place to start with forecasting is with thepercent of sales method, which assumes that as sales grow, many income statement and balance sheet items will grow proportional to sales. In this case, assuming costs last year were 20% of sales, and last year's sales were $200,000 - meaning costs are $40,000 last year. If sales go up by 10% this year, we will have sales of (200,000 x .10 = 20,000 + 200,000 = 220,000 in sales this year). This means that costs will be 20% of 220,000, or $44,000.With this information and assuming other given factors (for example) we can construct pro forma income statements that will outline the total effect of such an increase in sales. Not only will cost of goods be affected now, but so will the depreciation, interest expense, pretax income, income tax, and net income. It's a cascading effect. The slight change of one item will change the rest proportional to sales as well.

The pro forma balance sheet can also be constructed, but not until we can make assumptions about how our equity and debt will grow in proportion to sales. Once we make these assumptions, it's essentially the same process. With the pro forma balance sheet, you can point out where you will need new financing to fund growth. For example, if your assets are not balancing with liabilities and equity (and assets are higher), you will know that you need new financing.

Net New Financingis the amount of additional external financing we need to secure to pay for the planned increase in assets. This can be computed as:Net New Financing= Projected Assets - Projected Liabilities and EquityThis new financing needed is sometimes referred to asthe plug.It is the amount of net new financing that needs to be added to the liabilities and equity side of the pro forma balance sheet to make it balance. However, as you are projecting this, you should keep in mind that if you take out more debt in the upcoming years, it may throw off your interest assumptions staying constant.## Forecasting a Planned Expansion

Although the percent of sales method is useful, it's shortcoming is handling the realities of fast growth requiring "lumpy" investments in new capacity. Real world firms usually don't have the pleasure of being able to smoothly add capacity in line with expected sales. In order to more accurately forecast the free cash flows, we must go through some initial steps. First, we must analyze and estimate a firm's financing needs based on the capital expenditures required for the expansion. In this step of analysis, we look at how a purchase such as a machine will help us acquire our production goals and how such a purchase will affect our income statement and balance sheet pro forma. Along with this, we must think about how we can finance the expansion. Will we loan the money or get it from other sources? Once we settle with this, we can look at the PRO FORMA INCOME STATEMENT. The value of any investment opportunity arises from the future cash flows it will generate. To estimate these, we first start with projection of future earnings. We look at how such a purchase will increase our production, and how such an output will increase our sales and cost of goods sold. From here, we go down the line in forecasting depreciation, interest expense, tax numbers, etc. In the closing steps, we must forecast a company's working capital needs, because an increase in working capital reduces free cash flows. With this, we look at things such as minimum cash required in order to keep the business running smoothly and allow for daily variations in the timing of income and expenses. With this, we know how much cash we will need to plan for in our financing activities. Will the excess cash be distributed as dividend or will it be re-invested? Those are all outcomes that must be analyzed.With all this data, we can finally put together a forecasted balance sheet for a planned expansion. Once again, it is here that you can tell if you will need more financing in the future if assets are not balanced and greater than liabilities and equity. When the opposite is the case, where assets are less, this means we have generated more cash than we needed and can use the excess for things such as building up extra cash reserves, paying down debt, distributing to shareholders as dividends, or even repurchasing shares.

## The Planned Expansion's Value

Once we have an idea of debt, net income, and working capital needs, we can better determine if the expansion is a good idea. We do this by forecasting free cash flows. To compute free cash flows (excluding leverage), we start by adding back the after-tax interest payments associated with the net debt in its capital structure. After-tax Interest Exp = (1 - Tax Rate) x (Interest Earned on Debt - Interest Paid on Excess Cash). The same method can be used to calculate unlevered net income, starting with the EBIT and deducting taxes. To compute the free cash flow from unlevered net income, we add back the depreciation and deduct increases in net working capital/capital expenditures. This computes the total cash available to all investors. To determine the amount paid out to shareholders, we can adjust the free cash flow to account for all payments to or from debt holders. If we chose to pay out all excess cash as dividend, your answer here should match that of the dividend forecasted in the previous sections. So what kind of effect will this have on the firm's value? People generally estimate a firm's continuation value at the end of the forecast horizon using the valuation multiple. Explicitly forecasting cash flows is useful in capturing specific aspects of a company that distinguish the firm from it's competition in the short term outlook. However, the long term expected forecasts should move toward one another. This means that long term expectations of multiples are likely to be relatively homogeneous across firms, bringing us to assume that a firm's multiple will move towards the industry average. In other cases, the EBITDA is used most often. It's more reliable than sales or earnings multiples because it accounts for the firm's operating efficincy and it's not affected by leverage differences between firms. To get this continuation value:Continuation Enterprize Value at Forecast Horizion = EBITDA at Horizon x EBITDA Multiple at HorizonThis shows that the EBITDA multiple at horizon will have a large impact on our value calculation.Moving on, we can finally have all the pieces in valuing the expansion. First, we compute the present value of the forecasted free cash flows of the firm over the years used. These represent the cash flows available to bondholders and equity holders, making them free of leveragae. Next you calculate the present value of the continuation value. At last, we must calculate the interest tax shield by taking the NET INTEREST EXPENSE x TAX RATE. We calculate the present value of the interest tax shield using the interest rate on debt as the discount rate.

THE TOTAL VALUE OF THE EXPANSION IS THE SUM OF THE PRESENT VALUES OF THE FORECASTED UNLEVERED FREE CASH FLOW, THE CONTINUATION VALUE OF THE FIRM, AND THE INTEREST TAX SHIELD.The same process is used to calculate the value of a firm withouth expansion. Once you get the firm value numbers, you can decide if you want to delay the expansion or not. Here, you value your short term loss in production in comparison to the ultimate larger financial outlay that is greater. This timing analysis is yet another important factor.## Growth and Firm Value

We must keep in mind that not all growth is favorable. Expansion may strain managers' capacity to monitor and handle the firm's operations. Also, in the net, it may be worth less. Careful NPV analysis can eliminate bad growth decisions from good ones.Internal growth rate, or the maximum growth rate a firm can achieve without restoring to external financing is often what confuses the distinciton. It's esentially the growth a firm can supply by reinvesting its earnings. A more commonly used measure is thesustainable growth rate, or the maximum growth rate a firm can achieve without issuing new equity or increasing its debt-to-equity ratio. The Internal Growth Rate can be calculated by:Internal Growth Rate = (Net Income / Begining Assets) x (1 - payout ratio) = ROA x retention rate

This retention rate is often called the

plowback ratio, calculated as one minus the payout ratio of the firm. The Sustainable Growth Rate is calculated by:Sustainable Growth Rate = (Net Income / Beginning Equity) x (1 - payout ratio) = ROE x retention rateSince your ROE will be larger than your ROA anytime you have debt, the sustainable growth rate will be greater than your internal growth rate. Even though the internal assumes no financing, the sustainable assumes you will make some use of outside financing equal to the amount of new debt that will keep your debt-to-equity ratio constant as your equity grows through reinvested net income. When forecasting growth greater than your internal growth rate, you are going to have to minimize your payout ratio, plan to raise additional external financing, or a combination of both. If your growth forecasted is bigger than your sustainable growth rate, you will have to increase plowback ratio, raise additional equity, or increase leverage. The downside of these rates is that they cannot tell you if your planned growth increases or decreases a firm's value.