The financial model is the central tool used throughout the financial evaluation. The model is developed using a computer spreadsheet (usually Microsoft Excel). All the financial information relevant to the project is entered into the spreadsheet. Formulas produce financial statements and calculate key financial performance measures. The model can also automatically generate charts and reports for presentation of the model's results.
Financial modelling requires skill and care. The financial analyst needs to be highly competent in financial valuation theory and the structure of financial statements as well as skilled in excel. The financial analyst will work with other experts, such as engineers and transport economists, to develop the inputs for the model. The financial analyst will maintain documentation for the model and will ensure that all financial reports are properly supported by valid model runs. The LGU should designate the terms of reference for financial modelling. This capacity is developed through training and experience gained from actual PPP projects.
Financial modelling takes place at all stages of the PPP preparation process. A simple model is built when the project is first considered for PPP delivery. Progressive refinement takes place as the project moves towards the transaction phase. The most important inputs to the model take place during the project feasibility and transaction packaging phases, but the model will also be used during contract negotiations, and also subsequently, as part of the contract monitoring process.
For better illustration of the financial modelling concept, the discussion below will focus on a water supply project example. It assumes that the user is competent in basic spreadsheet skills. For more information on spreadsheets, the reader should consult one of the many available reference works, such as Microsoft Excel Bible. The Help function in Excel is also a valuable reference tool.
A financial model suitable for the financial evaluation of water supply projects can have as many as ten linked worksheets:
a. Summary worksheet shows key assumptions of the model, and presenting key performance measures. Most inputs to the model are made on the summary worksheet.
b. Invest 1 worksheet shows the capital investment required for the project, and associated maintenance expense;
c. Invest 2 worksheet is an expansion of Invest 1, showing capital expenditure and maintenance costs for each year of the concession;
d. Demand revenue worksheet calculates the revenue for the project for each year of the concession. Revenue is built up from forecasts of traffic volume multiplied by the tariff;
e. Costs worksheet shows the operations and maintenance costs for each year of the concession, based on data from invest 2 and demand revenue worksheets.
f. Depreciation worksheet calculates asset depreciation for each year of the concession.
g. Debt worksheet outlines the raising and repayment of the project loans. Financial structure performance measures are shown on this worksheet, summarizing the key operating parameters of the project throughout the concession period, i.e., the debt service cover ratio (ADSCR) and the loan life debt service cover ratio (DSCR);
h. Profit and loss worksheet presents the profit and loss statement for the project for each year of the concession, drawing on data from demand revenue, costs, depreciation, and debt worksheets;
i. Balance sheet worksheet presents the balance sheet for the project for each year of the concession;
j. Cash flow worksheet summarises the cash flow statement for the project for each year of the concession. The cash flow statement is the basis for the calculation of NPV and IRR.