A financial model is an important basis for making lending or investment decisions. When assessing project finance, for example, cash-flow models reveal a project’s economic viability and help to identify any associated risks.
In many companies, Microsoft Excel has become the standard tool for financial calculations. This is both a curse and a blessing, because while almost everybody is familiar with the program, very few people understand how the calculations work. The potential for error when using Excel is therefore relatively high. A great deal of knowledge and experience is required to develop a resilient financial model. Specific parameters must be modelled in a way that takes all the project’s significant risk factors into account, and that accurately calculates key indicators based on the underlying contracts. The KISS principle has proved its value as a basis for modelling. Modellers who follow the “Keep It Smart and Simple” approach use clear formulas, solve complex calculations in multiple steps, and document the logic behind the formulas by adding suitable comments. Quite simply, the more complex a model becomes, the more difficult it is to detect errors.
A financial model should clearly separate the input field from the calculation area and the well-organised presentation of results. As a rule, the level of detail should only be as high as it needs to be to enable users to make reliable assertions. Key elements in the dynamic analysis include a cost-revenue perspective, a tax computation, a drawing mechanism for borrowed capital or equity investors’ additional funding obligations during the expansion phase, as well as (often complex) repayment mechanisms and dividend payments during the operating phase. But once all these interdependencies have been programmed into the model, project planners can do precisely what high-powered corporate executives have long dreamed of doing: use their financial model as a kind of command centre for defining various scenarios so they can see the potential outcomes at a glance.
Well-designed models include a complete cash-flow statement so that in addition to the cash-flow waterfall (liquidity), the P&L (operating result) and balance sheet (assets) are also modelled over the entire duration of the project. If all three components are modelled, this has three advantages. The first is the correct computation of tax expenses, including amortisation and potential losses carried forward. Second, the user obtains valuable information on debt and equity movements over the term of the project. And third, programmers are provided with a higher level of certainty. A dynamic model that keeps the balance sheet balanced over the term of the project is an excellent indicator that the financial model is working properly.
Financial modelling is often criticised for producing results that only appear to be accurate, and also because, at the end of the day, “it’s impossible to predict anything with 100% accuracy”. These criticisms are not without foundation. After all, a financial model merely anticipates certain scenarios and calculates their outcomes – it is not a crystal ball. Furthermore, it is used for projects that are inherently much less complex than an actual company. Indeed, this is precisely why it is feasible to tackle projects using financial models in the first place. In most instances, the main dependencies are relatively well known. So the aim is to incorporate these interdependencies into a dynamic model which can then be used to analyse the project’s resilience or profitability. The former is primarily the concern of lenders, the latter a high priority for equity investors.
Once the financial model has been prepared for the user, they are able to analyse the project’s main risk drivers, identify weak points and eliminate them from the project structure. Macroeconomic risks are due to interest-rate and currency risks, or the impact of economic developments on the project. What happens if demand changes in unexpected ways, or commodity prices suddenly rise? Project-specific issues can usually be traced back to contracts. What happens when long-term supply contracts expire, or performance-linked repayment terms change? Meaning, how do various scenarios impact repayment? And because external factors clearly affect external ones, it is also important to devise scenarios that combine both in order to identify a project’s presumed “predetermined breaking points”.
The need for financial modelling is multifaceted. For project planners, it is absolutely vital. But equity and debt capital providers involved in traditional project finance are rarely able to do without financial models if they want to be sure that a project is suitably robust. Thus financial modelling is also an important tool for credit analysts, and for company valuations during M&A processes. At Frankfurt School, you can take our Financial Modelling with Excel certification course and learn how to program your own financial model.