Excel Modelling for Accounting and Controlling Course

Excel is used increasingly for budgets, data analysis and reporting and new features are added with every new version of Excel.  Similarly the tools for data analysis and business intelligence continue to grow in importance.  This workshop demonstrates the importance of modelling standards and how to build models efficiently which make use of a wide range of Excel features and techniques.

Course Objectives

This three day workshop explores:

  • Importance of model design
  • Implementation of modelling standards
  • Efficient data analysis
  • Comprehensive reporting

The workshop is highly practical and involves building analysis and reporting models.  Upon completion of the workshop, delegates will understand a range of Excel methods and techniques and will be able to implement the methods in their own models.

Course Teaching Method

The programme is taught using formal lectures combined with practical and interactive case studies and exercises to reinforce the concepts covered in each teaching session. Emphasis is placed on delegates gaining practical, hands-on experience of the construction of financial models in Excel.

Comprehensive product notes and modelling software will be provided for future reference.  Delegates receive a full pack of Excel software and templates for future reference as part of the course materials.

What you will gain

  • Model design and structure
  • Building accounting models
  • Principles of consolidation models
  • Range of forecasting methods
  • Data analysis techniques
  • Advanced pivot tables
  • Dashboards and reporting
  • Auditing and testing

Delegate Level for Those Attending the Course

Delegates will be expected to have finance knowledge and a working knowledge of Excel, including:

  • Opening and closing Excel files
  • Excel screen menu and standard toolbar
  • Auto fill
  • Moving around a worksheet
  • Moving around the sheets in a workbook
  • Creating files
  • Deleting files and individual sheets
  • Changing column width and row height
  • Entering simple formulas
  • Entering labels
  • Cell referencing
  • Centring titles and merging cells
  • Simple cell formatting
  • Number formats
  • Changing font sizes and colours
  • Copy, cut and pasting cell contents
  • Inserting graphic objects
  • Custom views
  • Previewing worksheets
  • Printing documents and ranges
  • Basic financial functions – NPV, IRR, NPER, PV, PMT, RATE, FV

Day One

Model Design and Introduction

Module 1

Model Design and Overview

  • Financial modelling objectives
  • Accounting financial models
  • Model design
  • Exercise: analysing existing models

Module 2

Budget Model 1

  • Building a budget model from basic information
  • Financial analysis basics – financial statements
  • Cash flow and ratios
  • Exercise: building basic templates

Module 3

Budget Model 2

  • Improving basic template
  • Excel methods and techniques
  • Exercise: completing budget model

Module 4

Auditing and Testing

  • Spreadsheet errors
  • Methods for auditing model
  • Exercise: practical auditing

Day Two

Module 5

Consolidation

  • Consolidating budgets
  • Spreadsheet links
  • Error checking
  • Exercise: producing consolidated budget

Module 6

Forecasting Methods

  • Overview of forecasting methods
  • Dealing with circular reference and other potential errors
  • Forecasting consolidated accounts
  • Excel methods – trends, regression, seasonality
  • Exercise: forecasting data using different methods

Module 7

Data Analysis

  • Databases in Excel
  • Extracting information
  • Useful functions and methods
  • Exercise: formatting and analysing a dataset

Module 8

Variance Analysis

  • Including actual data
  • Analysing variance
  • Exercise: reviewing actual, budget and forecast data

Day Three

Data Analysis and Reporting

Module 9

Pivot Tables

  • Preparing datasets
  • Data cleaning
  • Creating pivot tables
  • Adding fields
  • Exercise: creating and formatting pivot tables
  • Pivot table reporting and options
  • Calculated fields
  • Advanced features
  • Pivot charts
  • Example: producing a pivot table report

Module 10

Business Intelligence

  • PowerPivot and Power BI
  • Importing data
  • Data visualisation
  • Producing reports
  • Example: analysing datasets

Module 11

Dashboards and Performance Indicators

  • Preparing data
  • Creating dashboard template
  • Populating and checking
  • Example: developing a working dashboard

Module 12

Reporting and Charting

  • Types of charts
  • Advantages and limitations
  • Clarity of presentation
  • Exercise: completing the budgeting and reporting model

This is a course for analysts and practitioners who want to use Excel as a tool to assist with the decision making process. Senior financial professionals using Excel spreadsheets who should attend include:

  • Financial analysts
  • CFO’s
  • Financial controllers
  • Financial planners
  • Budget managers
  • Analysts
  • Credit managers
  • Risk managers
  • Corporate finance staff

kodukula

Mr. A. Day

Mr. Day is a finance professional with extensive experience of financial modelling, finance and leasing.  Alastair was previously a director of a start-up structured finance company, which grew rapidly and was subsequently sold to a public company.

Later, he established an independent consultancy to concentrate on assignments in structured finance, leasing and financial model development.

Past projects have included investment analysis, debt restructuring, valuation, aircraft leasing, power and project finance models prepared for a variety of clients. In addition, Alastair designs and delivers courses covering financial modelling, credit analysis and equipment leasing.

Mr. Day has a degree in German and Economics from London University and an MBA.  He has held positions as an associate lecturer at the OUBS and the ifs University College (Chartered Institute of Bankers).

Books Authored By Mr. Day

He is the author of standard modelling textbooks such as Mastering Financial Modelling, Mastering Risk Modelling, Mastering Cash Flow and Valuation Modelling and Mastering Financial Mathematics in Excel, together with a number of leasing and credit books.

Note:  His full professional Biography is available on request.

Venue:

Holiday Inn Hotel, Sandton – Johannesburg

Dates: 8th  – 10th May 2017

Course Prices and Discounts can be viewed after registration.

Register For This CourseBecome a Member