Description of the Profitability Model
PJ August 21 2006
Pall Jensson, Professor
Faculty of Engineering
University of Iceland
Profitability Assessment Model
Reykjavik, Iceland, August 21, 2006
1. Introduction
This paper describes a computer model of profitability analysis. It has been developed with investments in small and middle scale industries in mind. The computer model has been used widely in feasibility studies in Iceland as well as other countries. It should also be mentioned that the model forms the basis for a course on profitability assessment. There the participants actually develop the model themselves during the course.
A profitability analysis model as described in here can be defined as a simulation model of an initial investment and subsequent operations. The cash flow from operations must be estimated carefully on the basis of a thorough business plan. The cash flow and the development of balance sheet is then simulated over the lifetime of the investment. The model is based on given assumptions which are deterministic, however random variables (for example normally distributed) reflecting uncertain factors can easily be added. The time unit is a year, so we have a deterministic, yearly simulation of company operations. A typical example of a cash flow series calculated by a model like this could look as Fig. 1 shows.
Fig. 1: An example of Cash Flow Series for a planned investment
The model can be used in many ways besides evaluating investment projects. It is a kind of a laboratory allowing studies of for example taxation, dividend payments, etc. What-if questions can be asked to analyse different company policies or governmental regulations. Several companies in Iceland have actually used a model of this type as a yearly or quarterly cash flow forecasting system.
The main components of the model are shown in Fig. 2. The discussion in the following is organized in sections according to this structure. Each component is implemented in a separate Excel sheet in the same workbook.
In the case used in this description we will assume one year of construction and investment, and after that 10 years of operational lifetime. These assumptions can of course be changed easily in the Excel model.
Fig. 2: The main components of the Profitability Model
and their relationships
2. Assumptions and Results
This component of the model is for the input of the assumptions for the calculations to follow. Also, the main results of the profitability analysis are shown here. If needed, additional assumptions sheets can be inserted before this sheet for details like for example a breakdown of the Investment Costs and of Operational Costs, as is done in our case study. The colour code used is blue for input cells and yellow for results. We note that all subsequent components are based on these assumptions sheets and contain only formulas but no input cells at all.
In the left part of the Assumptions and Results Summary sheet the assumptions are stated, including the investment costs. The top section to the right also includes the main results, i.e. the Net Present Value (NPV) and the Internal Rate of Return (IRR) for the project, so that these can be seen on the same sheet as the main assumptions.
The case study used here is based on the following assumptions regarding investment costs:
The totals of these estimates, for buildings 50 MUSD, equipment 200 MUSD and other investment 50 MUSD, is brought to the top of the Assumptions and Results Summary sheet.
Another estimation method, addressing the uncertainties in a more advanced way, the Three Point Method, is described in Supplement 1.
The Operational Cost Estimates are as follows:
The cost estimate totals are brought to the Summary sheet shown below.
The Summary of Assumptions and Results of model calculations looks like this:
3. Investments and Financing
Next component of the model is called Investment and Financing. First there is the assumed breakdown of the investment cost into Buildings, Equipment and Other Investment (Engineering and Diverse Start-up Costs).
Depreciation is calculated in this analysis mainly with the purpose of getting an accurate estimate of income tax. The depreciation is calculated here with the straight-line method. Buildings are depreciated by 4 % each year, machines by 15 % and other investment by 20 %. Note that equipment is only depreciated by 90% down to 10% of original value.
Financing is assumed to be paid equity on one hand, which is 30% of the total capital, and the drawdown of a single loan on the other hand. In our case the repayment period of this loan is only 6 years with a one year grace period (year 2006) and the loan interest is 12 %. The calculation of interest is based on the principal at the end of the last year. We assume a loan management fee of 2%.
In our case study the total investment is assumed to be 300 MUSD; however the total capital needed is higher. The difference is the estimated need for Working Capital, the method to do that estimate will be described later.
4. Operating Statement
This component has the purpose of calculating the Revenue and Costs year by year, the Income Tax and other taxes, and the Appropriation of Profit. When we have subtracted Total Production Costs from the Revenue we reach the line called Operating Surplus, which is the basis for cash flow calculation in the next section. Note that we assume here that Diverse Taxes are zero.
Total depreciation was calculated in the previous section. Stock movements are calculated as changes in the Stock under Current Assets on Balance Sheet, which in turn is based on the Inventory build-up, see the Assumptions and Results Summary sheet.
Subtracting all these items (except adding stock movement) brings us to the Operating Gain/Loss. From that we subtract Interest of loans calculated in the previous component. This gives us the Profit before Tax, and we enter the tax calculations.
The tax law in many countries allows loss to be transferred over several years. In our case the loss transfer is allowed. Income Tax Basis is calculated from Profit before Tax and Loss Transfer. This gives us the Taxable Profit and the Income Tax for corporations is in our case study 18% of that. Net Worth Tax is zero according to the case study assumptions. Net Worth is defined as the Total Capital (bottom line of Balance Sheet) minus Equity plus Taxes Payable.
Appropriation of Profit is determined in the line called Total Dividend. The conditions in our case study are that Profit after Tax is high enough and that Profit and Loss Balance on the Balance Sheet allows payment of dividend. In the case shown here the Total Dividend is 30% of the Profit after Tax, under the above-mentioned conditions. This can of course be adjusted at will. Finally Net Profit/Loss is calculated and added to the Profit and Loss Balance on Balance Sheet.
5. Balance Sheet
This component of the model might seem of little value as it is in particular the cash flow series that is of interest for profitability evaluation. However, it gives a more complete picture to be able to follow the forecasted development of the balance sheet. Also, financial ratios can be calculated. Finally the balance sheet is used in the model as a verification tool as many logical errors would result in a difference between Total Assets on one hand and Total Debt and Capital on the other hand (both these lines are shown with double underlining below).
Financial Ratios are calculated according to common accounting traditions. Later all the ratios will be presented graphically. Here we will only give few comments on the interpretation of the ratios.
The two first ratios, the profit ratios, some times show a decreasing trend after the middle of the lifetime. This is not because profits are getting smaller but because the capital in the nominators is increasing as profit and loss balance grows.
The liquidity ratios are high through all the lifetime and debt service coverage, which is cash flow after taxes divided by repayment and interest of loans, is acceptable. The debt ratios are also well in order.
Most of these ratios are presented graphically in Fig. 3 and 4.
Fig. 3: Financial Ratios, part 1
Fig. 4: Financial Ratios, part 2
6. Cash Flow
The Cash Flow calculation begins with the Operating Surplus from Operating Statement in previous section. Debtor and Creditor changes are calculated on basis of Debtors and Creditors on the Balance Sheet, giving Cash Flow before Taxes. Note that Debtor and Creditor changes can not be calculated until the Balance Sheet is ready.
Taxes are paid the year after they are calculated, and subtracting these gives us Cash Flow after Taxes. This together with the total invested capital (equity and loan) is of interest as a measure of the profitability of the project regardless of how it will be financed, see later.
Interest and repayment of loans (calculated earlier) are now subtracted and the resulting line is called Net Cash Flow. This will be used later to measure the profitability of the equity.
Finally Cash Movements are found by adding to Net Cash Flow the difference between Financing (Drawdown of Equity and Loans) and Capital Expenditure, i.e. the Working Capital, and subtracting Paid Dividend. The Cash Movements are then added to the Cash Account on Balance Sheet, see later.
Source and Allocation is an optional section and will be described in a Supplement 2.
There are two important cash flow related ratios that will be described here. The Debt Service Ratio is defined each year as Cash Flow after Tax divided by Repayment and Interest of Loans. This should preferably be greater than 1.5, ensuring that each year the cash flow is enough for payments of loans. The Loan Life Cover Ratio is defined each year as Net Present Value of Cash Flow after Tax for the rest of the lifetime divided by the Principals of loans that year. This should also be greater than 1.5, ensuring that future cash flow is enough to pay back the loan.
These cash flow related ratios are shown graphically on Fig. 5.
Fig. 5: Cash Flow related Ratios
7. Profitability Calculations
This component of the model calculates the profitability of the investment. Two measures are used in the model: The Net Present Value (NPV) with a discounting factor chosen by the user and the Internal Rate of Return (IRR). The theoretical background for this can be found in textbooks. These measures are calculated for the following cash flow series:
1. Total Capital invested and Cash Flow after Taxes
2. Equity and Net Cash Flow
The NPV and IRR are calculated for all years through the lifetime. By this we are able to see the development of the accumulated discounted cash flow and impact of lifetime on IRR. This becomes clearer when studying the graphs of NPV and IRR on Fig. 6 and 7.
Fig. 6: Net Present Value accumulation over the planning horizon
Fig. 7: Internal Rate of Return over the planning horizon
8. Sensitivity Analysis
Sensitivity analysis for exploring and better understanding the effects of uncertainties can be done in many different ways. Here we will use three methods:
1. Impact Analysis
2. Scenario Analysis
3. Monte Carlo Simulation
Impact Analysis deals with only one uncertain item at the time, for example sales price, sales quantity or cost of equipment. To facilitate this we insert so-called impact factors as shown on Assumptions and Results sheet on page 4, all with the default value 100%. These are multiplied with the uncertain items (in cells next to the right). Next step is to apply Data Tables in Excel in order to calculate results like Internal Rate of Equity for a range of impact factors, say from 50% to 150%. This is done for each of the uncertain items. The result is a table as shown below. Finally this is presented in a graphical way in Fig 8.
Fig. 8: Impact Analysis on IRR of Equity
Scenario Analysis deals with simultaneous changes in more than one uncertain item. Excel Scenario Manager is used for this purpose. The changing cells are selected and their values for each scenario, for example that the cost of equipment is in the best case only 90% of the base estimate but 120% in the worst case. The scenarios are named, for example Pessimistic Scenario and Optimistic Scenario. Result cells are selected, in our case the IRR of Equity. The result of this is:
Monte Carlo Simulation is the most advanced of the sensitivity analysis methods. To do this we need an add-in to Excel like for example @Risk or Crystal Ball. These simulation tools allow the user to specify a probability distribution for each of the uncertain items. In our case we use the Three Point Method assuming a Triangular or Beta distribution for each item. This means that the user gives an optimistic estimate a, a most likely estimate m and finally a pessimistic estimate b, see further Supplement 1.
The tools then use built-in random number generators to make as many observations of these items as the user finds necessary. The result will be a histogram of a selected output item, in our case the IRR of Equity. Next page shows is an example of such a histogram. Let us assume that the user has decided that IRR = 20% is critical, i.e. values under this threshold are not sufficient. He will now be able to see what the probability of this is.