Advanced financial modelling in Excel 2018 - Workshop

Overview

This workshop is designed to increase your financial modelling and analytical skills in Excel. During the course you will build a financial forecasting model which is scalable, flexible and reusable. You 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, and 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.

During the workshop you will create the sensitivities on the assumptions sheet, which will involve the generation of one off costs and creating a range of business case scenarios. A depreciation schedule will be generated which will be able to change with changing drivers (useful lives, asset classes). A fully allocated loan schedule will be created with generation of interest and principle repayments which are allocated to the various departments. A P&L template will be calculated from scratch and the worksheet will act as a template to generate up to 20 different reports. An Excel Slicer will be created to act as the consolidated front end of the report and we will add VBA to generate a change event which is linked to the Slicer.

Key topics:

• 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

Objectives

• 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
• Create a slicer which summarises the report by cost centre
• Automate the summary sheet using VBA with an On-change 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

Click on the here for Marcus Small’s biography.

Audience

This workshop is designed specifically for finance and accounting professionals who use Excel in their day to day roles.

Participants will gain the maximum benefit from this course if they are already competent spreadsheets users. The course is designed for people who use Excel on a regular basis, and are comfortable with using its tools and functions.

Please bring a USB.

Please note enrolments close 2 working days prior to the workshop date. Please enrol at least 3 working days prior to the workshop to ensure any dietary and special requirements can be accommodated.

Looking for a customised learning solution for your organisation or team? Contact us today to discuss in-house learning options.