Advanced Financial Modelling Course

This course is designed for Excel users to vastly increase their financial modelling and analytical skills, particularly in the areas of accounting, finance and management. During this workshop, participants will be building a financial forecasting model which is scalable, flexible and reusable. We will use assumptions and historical data to produce a 5-year forecasting model which uses clear and straight forward formula to derive a forecast of revenue and expenses for each region/year. The model will incorporate loan repayment schedules, allocations of the costs to regional and head offices. A VBA technique will be used in conjunction with an Excel Slicer to create a fully interactive and consolidated P&L and cash flow.

This course delivers:

• Practical guidance on how to layout and structure a set of cost centre reports to create seamless consolidation;
• Create a sensitivity page which allows for multiple business cases and one off events which will flow through to the P&L;
• Create a fully allocated loan repayment schedule with principal and interest;
• Generate a 10 year fixed asset register which allows the changing of asset classes and useful lives;
• The creation of a slicer which summarises the report by cost centre.
• The automation of the summary sheet using VBA with an Onchange event and an Excel slicer. All VBA coding will be provided so no prior knowledge necessary.
• Working with GOAL SEEK to generate predetermined ROA for the model, create some VBA to automate the production of the results.

Tools Used

• Data Summary using SUMIFS Formula
• OFFSET Formula
• INDEX & MATCH Formula
• SUMPRODUCT formula
• LOOKUP, VLOOKUP formula
• IPMT & PPMT formula
• Nested IF with AND statements
• HYPERLINK formula
• COLUMN & ROW formula
• RIGHT, CELL & SEARCH formula
• Adding straight line depreciation using SLN formula
• Creating Slicers for consolidation with VBA
• INDIRECT & Address formula to summarise data which is isolated in the Slicer