Project Modelling Using Excel

This course is designed for accountants, finance professionals and managers who need to increase their knowledge of project finance.  The course provides participants with a thorough understanding of how to build a robust financial model from start to finish.  The course will consider drivers for revenue, operations and maintenance costs, capital expenditure, depreciation, debt and equity modelling and taxation.  During the course participants will run a range of scenarios and adjust the timing of key events.  It is designed to be highly interactive with a mix of theory, demonstrations and instructor led Excel exercises which culminates in the completion of a financial statement into the viability of the new project. 

This course delivers:

A comprehensive overview of the theory of project modelling in Excel;

The modelling of revenue, expenses, capital expenditure and finances;

A thorough grounding in the tools and functions available in Excel including: INDEX, MATCH, VLOOKUP, SUMIFS, SUMPRODUCT, OFFSET, PPMT, XNPV, MIN, and nested IF Statements;

The creation of a corkscrew account which will assists in modelling debt;

Modelling revenue, expenses, capital expenditure and the finances to fund the project;

Using the equity discount rate, the model will automate a project cost using Excel’s goal seek tool and some VBA (which will be provided);

A more streamlined method to calculate straight-line depreciation where the asset commissioning date drives the calculations;

Map the impact of inflation on salary, operational and maintenance costs;

Creating NPV and IRR calculations using both project and equity cash flow;

Practical guidance on the layout and structuring of workbooks for analysis and project forecasting.

Modelling Component

The course will focus on a bottom up approach to model creation as the class builds two distinct models to address common organisational problems.  Firstly, the class will compare two logistics options against one another to work out the optimal result for the organisation.  The other is the acquisition of an entity.  Revenue and costs are built from the up and a discount factor is used to value the potential purchase.   The course uses tested modelling approaches to produce models that are robust, flexible and user friendly.

Practical Component

A set of review exercises are worked as the course goes through the setup, design and build of a project financial model from scratch.  The model will incorporate sound modelling methodology with a separation of inputs, calculations and outputs.  Along with the formulas mentioned above the course is designed to follow the following key objectives;

·         Follow a logical, structures and disciplined approach towards designing and building financial models;

·         Build two distinct models from start to finish.

·         Gain a stronger understanding of project finance transactions and the types of models used when and their layout.

·         A strong focus on the outputs of these models and defining their meaning in a way that can be translated to management.

·         Adding simple VBA code to a model to automate common Excel tasks which will aid with model calculations;

·         Learn ways to reduce modelling errors making your models more robust.

Course Materials

The course materials include a 90+ page booklet which will act as a reference guide during the course while acting as a valuable reference following the course should participants wish to refresh their knowledge.  In addition, a set of files will be provided which includes the sample versions and worked versions of the course files, as well as a number of reference documents, and a set of links to other useful resources including relevant parts of thesmallman.com.