Before getting up to your eyeballs in spreadsheets and calculations it worthwhile to do a pre-feasibilty study
In simple terms a project is financially feasible if you can make sufficient money out of the project to
If the project was financed from savings (that is, no loan was required) then the project must return more than an alternative investment, for example interest from a fixed deposit savings account.
One of the best ways of determining if a project can make enough money to make it feasible is Discounted Cash Flow (DCF) analysis. DCF analysis has many tools, the two most useful are;
NPV is equal to the sum of the discounted future cash flows of the project. It is usual that the the first cash flows are flows of money outwards, that is money is paid out for the purchase of the plant (these are negative cash flows). Later cash flows are usually positive as project begins to earn income. The calculation is given in the equation below
NPV = ΣCFi / (1 + r)i i = 1..N
The cash flows are the real cash flows from the project that are a result of the capital expenditure. Depreciation and loan repayments are added back to nett profit to yield cash flow
The discount rate to be used is the weighted average cost of capital for the investor. For industrial projects normally only the first ten years' cash flows are considered. After the ten years are up your project can still earn you money, so you need to include a terminal value that represents the value of the project at the end of the analysis period (as if you were to sell the project).
A project is feasible if the NPV is greater than zero. If you are comparing alternative projects the project with the highest NPV is the best project.
IRR is the rate of return that yields a nett present value of zero. A project is feasible if the IRR is greater than the interest rate you can borrow at. If you are comparing alternative projects the project with the highest IRR is the best project. Do not use IRR to compare mutually exclusive projects. Mutually exclusive projects those where you can only execute one of the alternatives. For example if you are planning to build a sugar factory and you have a choice of building a low efficiency - low capital cost plant or a high efficiency - high capital cost plant, then these two projects are mutually exclusive, you must choose one of the two, you can't do both.
An example of non-mutually exclusive projects are a sugar factory with an attached refinery or a sugar factory with an attached ethanol distillery. (It is quite possible to do both (if you have enough money!)
The main reason you should not use IRR to compare mutually exclusive projects is because the IRR calculation assumes that excess revenues are reinvested at the IRR rate, which is more often than not, not true and can provide a misleading decision parameter.
Inflation rates, interest rates and foreign exchange rates are all very closely related to each other; unfortunately the relationship between them is not fixed or even easily definable. This is because in a market economy these rates are largely sentiment driven which is a polite way of saying they are subject to irrational erratic fluctuations
A financial model that includes these rates will be exquisitely sensitive to the differences between these rates, and even if you are the governor of the reserve bank you will not be able to predict the differences between these rates to make your model sufficiently robust.
Fortunately, for an investment decision it is not necessary to include inflation in the analysis, provided that the real discount (or interest) rate is used (ie nominal interest rate minus the inflation rate) in the NPV calculation. If you are using IRR as your investment criterion the IRR must be greater than the real interest rate (ie nominal interest rate minus the inflation rate)
At some point in your feasibility study you are going to be faced with choices regarding the selection of technologies, for example
These technology choices each have different capital and operational costs. In order to assess which of the choices is more financially attractive some form of financial analysis is required. It is not practical or necessary to do a complete financial model of each technology. A simplified financial analysis is sufficient to enable a choice between two or more options.
Generally the best of method calculating the projected cash flows is by means of a financial model of your project. Spreadsheet software makes these calculations quite easy. Spreadsheets models while easy to construct can easily become very complex and internally interlinked making it very difficult for anyone other than the original author to understand them and modify them. Even the original author can have difficulty understanding his model some months after it was constructed.
If a spreadsheet is to be used, it must be planned carefully before its construction, taking care to make the internal links as logical and as consistent as possible and to liberally use notes and comments throughout.
Once you have spent many weeks constructing your model which carefully simulates every aspect of your project, and have calculated the NPV and IRR for your project, one thing you can be sure of is that in real life you will not get the NPV and IRR you calculated.
Your analysis period was say ten years, in that ten years all sorts of things will change, prices will go up and down, exchange rates will fluctuate, the plant's technical performance will change randomly.
Your financial model should have some means of reflecting these random fluctuations. There are at least two methods that can be used
Monte Carlo analysis gets its name from the casino in Monaco. You spin the roulette wheel or throw the dice to get random inputs to your model and then see what the outcome is. It is important to understand that one allows all of the random variables to fluctuate simultaneously
One needs to throw the dice many hundreds of times to get a complete picture of the probability of achieving a certain return on investment. The graph below shows the result of calculating the IRR over 300 10-year periods while allowing all the variable input parameters to randomly assume values between their expected maximum and minimum values.
From the above graph there is zero probability that the IRR will be below 11% and zero probability it will be above 22%. I would feel very comforted by a statement like that if I was an investor and I understood what my consultant was telling me.
While a risk simulation approach allows all of the random variables to simultaneously; the sensitivity analysis allows only on random variable to change keeping all the others at their expected values. The random variable under consideration is varied from its predicted minimum through its expected value to its predicted maximum
The most useful result of a sensitivity analysis is a spider chart. The spider chart shows how the IRR (or NPV) varies as a result of varying one of the input parameters while keeping the others constant. The project is most sensitive to the line with the steepest curve
One can see from the spider chart above that the project is most sensitive to changes in the sugar price, and least sensitive to changes in operating costs.
Some very useful add-ins for MS-Excel to help do some the calculations above are available from www.decisiontoolpak.com
Subject to the usual disclaimer you can download and use a financial model that hopefully addresses all of the issues above. It does require the RiskSim add-in from www.decisiontoolpak.com
The balance sheet tab in the spreadsheet is incomplete and probably wrong. Please