Building Proforma Models in Excel by stariya


									                                     Determining Value

                          Building Proforma Models in Excel
This memorandum will develop proforma models of increasing complexity in a set of four

Initially it is assumed that the turnover in year 0 is 5000, issued capital is 200, and retained
earnings from the previous year are 100. All other items in the income- and balance statement
will be generated automatically from assumptions to be made. It is thought that these
assumptions are based on a thorough strategic, accounting- and financial analysis of the
company. The following assumptions are made:

Assumptions for the income statement              Assumptions for the balance statement
Turnover grows at an annual rate of 10%           Intangible assets is 60% of next years
(constant)                                        planned turnover
Operating expenses is 80% of the turnover         Current assets is 20% of turnover
Financing costs (interest) is 8% of loans and     Long-term liabilities is the plug of the model
The corporate tax rate is 28%                     Short-term liabilities is 5% of turnover
50% of the net profit is used for dividends

Long-term liabilities = total assets – short-term liabilities-(issued capital+share
premium+reserves) – retained earnings (e.g. balanced profits).

The assumption that long-term liabilities is the plug give that funds are raised only by raising
new long-term debt. All surpluses (net of investment expenditures and dividends) are used to
consolidate long-term debt.

The worksheet to be constructed needs to be adjusted as a consequence of this plug to avoid
the circular reference created as the long-term debt is referring back to themselves.

Chose Tools – Options – Calculation

And select ‘Iteration’, and then click on OK
Building the first model
In Excel do the following:
    1. Prepare the balance sheet by adding assumptions and headings to the income- and
       balance statements.
    2. Provide the inputs in cells B12; B26 and B27
    3. Write adequate formulas for year 1 (see below)
    4. Copy the formulas for year 1 over the other years.


Item                                          Formula
Turnover                                      =B12*(1+$B$2)
Cost of sales                                 = - C12*$B$3
Interest                                      = - C28*$B$4
Profit before taxes                           =SUM(C12:C14)
Income tax expense                            =-C15*$B$5
Profit after tax                              =SUM(C15:C16)
Dividend                                      =C17*$B$6
Retained profits                              =C17-C18
Intangible assets                             =C12*(1+$B$2)*$B$7
Current assets                                =C12*$B$8
Total assets                                  =C22+C23
Stock capital                                 =B26
Free reserves                                 =B27+C19
Long-term debt                                =C24-C26-C27-C29
Short-term debt                               =C12*$B$9
Total equity and liabilities                  =SUM(C26:C29)
The worksheet should then look like:

Task 1
Use the model that you now have available. Analyze the solvency (equity to asset ratio) over
the five-year period. How sensitive is the development to the assumed turn-over growth?
Check this by letting g equal 5, 10, and 15 %, respectively. Show your results in a graph.
What explains this effect?

Then: What is going to happen if you would prolong the model over some years? Illustrate
and explain what you find.

Expanding the first model
This first model is full of weaknesses and simplifications. The next step will therefore be to
make the model more complex but also more realistic. The following areas are simple to
extend to model to accommodate. Note: take each step separately – that means save each
extension in a separate worksheet.
(a) Financing costs
Interest should be based on a weighted average of opening and closing levels of borrowings
and loans:

long  term debt year t  long  term debt year t  1
                                                       int erest

Adjust the worksheet for this from year 2 and onwards.

(b) Varying revenues and expenses
All parameters in model 1 is assumed to be fixed and this is obviously not realistic. The
turnover is likely dependent upon the business cycle, product phases etc. Adjust the model to
the following assumption regarding the growth rate in turnover:

Likewise, the cost of sales should probably (in most cases) be modeled as varying, and in
addition with larger precision.

Again, assume a growth rate of 10%. The formula included for calculating net profits is valid
only if profits before taxes are positive. What happens in the model when the company makes
losses? Try!

To allow for a more realistic treatment here replace the tax formula for year 1 with the
following formula: =IF(C15>0;-$B$5*C15;0).

This formula provides a logical test and return one value if the test is true (-B5*C15), and
another value if the test if falsified (= 0).

Task 2
Make the necessary changes in model 1 and re-run Task 1 for each (a) to (c). Is there any
changes? Explain your findings.

Model 2. A proforma model with depreciation
The first model did not considered depreciation of intangible assets. That is a huge
simplification that needs to be reconsidered. Assume now that the acquisition costs of
intangible assets are a function of the turnover. In addition, the depreciation schedule is linear.

Prepare a new worksheet according the following illustration:
New formulas need to be inserted for the following items:
Item                                           Formula
Depreciation                                   =$B$10*((C24+B24)/2)
Acquisition cost                               =C13*(1+$B$2)*$B$7
Accumulated depreciation                       =B25-(-C15)
Intangible assets (net value)                  =C24-C25

Make sure that the balance identity is preserved after having inserted new rows and after
copying formulas.

Task 3
Analyze how the rate of depreciation affects the equity ratio. Assume an economic life-time
of: (a) 20 years; (b) 15 years; and (c) 25 years. Explain your findings!
Model 3. A proforma model with a fixed capital structure
You should now copy the Model 2 to a new worksheet and continue from there. Our previous
models developed had long-term debt as the plug. This means that all future financing of the
firm was established by raising new long-term debt.

The model to be developed in this section does not make this strong (and strange) assumption.
Instead a fixed capital structure (fixed ratio between debt and equity) is assumed. This means
that the financial risk of the firm will be maintained despite capital growth. The growth of the
firm is financed at a pre-set mix of debt and equity.

Some new assumptions will be made here. The equity ratio increases from 25% in year 0 to
40% from year 3 and onwards. It is also assumed that the dividend ratio is set to zero since the
idea is to increase the equity ratio.

Start to insert necessary text in your work sheet and the new section on rows 11-12. Also note
that the rows in the Balance statement is different from earlier models. Then insert values in
column B (year 0) in the Balance statement.

The major difference in this model is that the stock capital is used as a plug and that the long-
term debt is given by the required equity ratio according to the following:

Item                                             Formula
Stock capital                                    =B30-B33-B35-B36
Long-term debt                                   =B30*(1-B12)-B36

The completed model should have the following look:
Task 4: Analyze how changes in the turnover growth will affect the passive side of the
Balance statement. Explain your findings!

Model 4. A Proforma Model with double plugs
Models 1 and 2 had in common that capital growth was financed by new debt. All funds
available after dividend payments and capital expenditures were used to consolidate the debt.

This section will assume that any excess funds will be deposited on a short-term basis and
earn interest, while at the same time capital growth will be financed by new debt. It is quite
common to do like this, especially if there is a need to build a buffer for future investment. A
set of new rows and items will be introduced in the worksheet model to include this

Note that long-term debt will be the plug if the firm needs to raise new debt. On the other
hand, if the firm has excess funds available the plug is the short-term deposit. This means that
the amount of debt does not decrease from one year to another. A logical test is introduced to
govern the financing choice.
The following new items and formulas should be taken into account:

Item                                             Formula (year 1)
Interest income                                  =C30*$B$5
Short-term deposits                              =IF((C37-C28-C29)>0;C37-C28-C29;0)
Long-term debt                                   =IF((C28+C29-C33-C34-
Please, note that some of the assumptions in the input area field have been changed in this
model extension. Be careful in preparing this worksheet!

Task 4
Calculate the equity ratios for this model. Then change the turnover growth rate to 20%
instead of 10%. How does this affect short-term deposits and the equity ratios? Explain!
Part 2 Using Proforma Models for valuation
Next we will use the Model 4 but with turnover growth rate set to 20%. The purpose is to
calculate Cash Flows and subsequently to use these Cash Flows in valuation models.

Copy and save model 4 to a new worksheet. Then start by adding text and formulas so that the
model looks like the Figure below:
Then add formulas as follows:
Item                                              Formula (year 1)
Profit after tax                                  =C19
Depreciation                                      =-C16
Interest paid (after tax)                         =-C18*(1+(C20/C19))
Interest received (after tax)                     =-C17*(1+(C20/C19))
-increase in current assets                       =B29-C29
Increase in short-term debt                       =C36-B36
Available Cash Flow before Inv                    =SUM(C40:C45)
- Investment in non-current assets                =B28-C28-C16
FKFF                                              =SUM(C46:C47)
Increase in long-term debt                        =C35-B35
+Interest paid (after tax)                        =C42
-Interest received (after tax)                    =C43
FKFA                                              =SUM(C48:C51)

Task 5
Provide a Table showing for each of the five years how sensitive FKFA is to various levels of
the growth rate in sales. Assume growth rates to be 10%; 20% or 40%. Hint: use the Data
menu and then the Table sub-menu for this task. Ask if you need assistance! Explain your
findings! This explanation is non-trivial so please pay thorough attention to this explanation.

You should also prepare a Figure based on your sensitivity analysis. The Figure should be like







                Year 1       Year 2          Year 3          Year 4         Year 5
Final step
We then turn to the calculation of the value of the firm using the FKFA measure. Assume that
the required return on equity is 20% and that the permanent income growth rate after five
years is 5% in order to calculate the value of the firm (based on FKFA).

In this step you should add formulas to the worksheet in order to:
    1. Calculate the present value of annual FKFAs during year 1-5. Apply the formula
    2. Calculate (using a growth value formula) the perpetual value after year 5. This is
         denoted ‘Ending value’. Apply the formula =G52*(1+B56)/(B55-B56)
    3. Transform the Ending value to a present value at t = 0. Apply the formula
    4. Add the values under step 1 and 3. That will be the value of the firm

Successful completion of these steps should give a worksheet that looks like
Finalize this assignment by showing how sensitive the value of the firm is to the assumed
permanent growth rate.

To top