Determining Value Building Proforma Models in Excel This memorandum will develop proforma models of increasing complexity in a set of four steps. 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 borrowings. 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. Formulas: 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 2 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. (c)Taxes 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 behaviour. 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- C36)>B35;C28+C29-C33-C34-C36;B35) 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 1400 1200 1000 800 0% FKFA 10% 20% 600 400 200 0 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 =NPV($B$55;C52:G52) 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 =PV($B$55;5;;-B59) 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.
Pages to are hidden for
"Building Proforma Models in Excel"Please download to view full document