Premier Home Page

Home | Training | Solutions |Contact Us

t: +44(0)20 7729 1811

About Us | IT Training | Financial Training | Management Training | Course Dates | Delegate Information

Accounting, Financial Modelling and Excel Training Course

An intensive and highly practical 4 day course designed to equip managers, accountants, auditors, book-keepers, controllers, finance directors, tax professionals and bankers with a sound, thorough understanding of spreadsheet techniques and skills, with an emphasis on financial modelling.

Course Description:
The course shows how spreadsheets can be used to enhance management reporting, based on traditional double-entry accounting systems. The course also reviews the financial mathematics tools available in Excel and considers the dual perspectives of risk and return.
The course shows how to analyse and review data extracted from accounting systems and databases in order to prepare management reports, including using Excel’s graphing capabilities. The course studies Excel’s functions and how to use these effectively. The programme also shows how to access the Visual Basic Editor and use macros and user defined functions in order to enhance Excel’s functionality. Half a day will also be spent showing attendees how to use Excel’s pivot table functionality. Pivot tables are often poorly understood and yet are the most powerful feature in Excel. A basic pivot table will allow you to summarize 50,000 rows of data in 30 seconds
The course is highly practical: delegates will be exposed to examples (in Microsoft Excel) throughout the 4 days. Note that this course aims to cover a very wide field in a relatively short time and will thus provide more of an overview (with examples) than an in-depth, highly technical course.

Learning Objectives

  • The principles and history of financial modelling
  • Excel concepts – Workbooks, worksheets and charts
  • Excel’s functions
  • The principles of financial reporting – the accounting equation
  • Review the profit and loss account, balance sheet and cash flow statements
  • Practise designing and structuring models
  • Use Excel’s financial modelling tools and capabilities
  • Understand sensitivity analysis
  • Explore investment return appraisal tools and techniques
  • Review the cost of capital, the capital asset pricing model and WACC
  • Investigate the use of macros and user defined functions in the VBA environment
  • Introduction of pivot-tables principles – handle large volumes of data and prepare reports

Who Should Attend?

  • Financial managers, accountants, auditors, book-keepers, finance controllers, finance directors, tax professionals and banking professionals

The course is highly interactive, and formal lecture content is kept to a minimum. Modelling techniques are explained and demonstrated by using extensive case studies, adhering closely to real life examples. Throughout the programme emphasis is laid on the importance of reality-checking and the dangers of spreadsheet blindness, on a fundamental understanding of key issues and on the skills of communicating conclusions from modelling techniques to colleagues and financial partners.
Delegates will be expected to be highly participative and motivated to learn, and to express their needs and priorities clearly.

Knowledge Pre-Requisites
Attendees will be expected to be using Excel in the context of a finance related role and be comfortable with using Excel for data entry. A basic knowledge of Excel will therefore be assumed, but if delegates need a refresher on specific items then, time permitting, the course director will try and meet their needs.

The course is also suitable for more senior finance professionals who may not have had specific spreadsheet training in financial modelling, who have developed their skills over time and who would like to develop a broader knowledge of what Excel can offer, as well as reassurance about their existing spreadsheet techniques.

Candidates ideally should bring their own computers with Excel loaded in order to benefit from the course.

Day One

Session 1: Developing financial models:

  • The history of spreadsheet modelling
  • Spreadsheet faults – examples of poor design
  • Principles of good design
  • A design methodology for developing financial models

Session 2: Basic spreadsheet concepts:

  • Workbooks, worksheets and chart sheets
  • Cells, formulae and ranges
  • Evaluating formulae
  • Formula auditing
  • Filing conventions and disciplines
  • Workbook and worksheet protection
  • Naming conventions
  • Scope of names: workbook and worksheet names
  • Formatting and presentation techniques
  • Conditional formatting
  • Data validation

Session 3: Excel’s functions and tools

  • Date and time functions
  • Financial functions
  • Text functions
  • Statistical functions
  • Maths functions
  • Lookup and reference functions
  • Database functions
  • Information functions
  • Logical functions
  • Array formulae
  • Goal seek
  • Solver
  • Data tables
  • Filters
  • Using charts
  • The different charts available
  • Using Excel’s functions to graph and estimate curves

Delegates will consolidate their understanding of Excel techniques and functions with a series of practical exercises.

Day Two

Session 4: Accounts preparation

  • The trial balance
  • The extended trial balance and journals
  • Profit and loss account – income statement
  • Management P&L compared to statutory accounts P&L
  • The Balance Sheet
  • Fixed assets and depreciation
  • Current assets
  • Inventories case study, specific identification, average cost, FIFO and LIFO
  • Work in progress and long-term contracts
  • Current liabilities
  • Long-term liabilities
  • Provisions for liabilities
  • Equity
  • Working capital
  • Cash flow statements
  • Statement analysis
  • Ratio analysis
  • Profitability
  • Operating efficiency
  • Gearing and capital structure
  • Interest cover and banking covenants

Delegates will prepare a full set of accounts from trial balance, through journal adjustments to profit and loss account, balance sheet and cash flow statement

Session 5: Budgeting and Forecasting

  • Pro Forma financial statements
  • Pro Forma to budget
  • Forecasting and projections
  • Preparing a profit and loss, balance sheet and cash flow forecast
  • Cash budgeting
  • Break-even point analysis
  • Variance analysis
  • Management reports
  • Scenarios

Delegates will complete a fully functioning financial forecast

Day Three

Session 6: Time value of money

  • Understanding perhaps one of the most important ideas in financial mathematics
  • Present and future values
  • Excel’s basic financial functions PV, FV, NPER and RATE
  • Loan amortization schedules
  • Other financial functions used with amortization schedules
  • Excels’ advanced financial functions NPV, IRR, XIRR and XNPV

Delegates will prepare interest based computations using Excel’s functions

Session 7: Investment appraisal

  • Preparing a business case
  • Investment appraisal methodology
  • Payback
  • Accounting rate of return
  • Net present value
  • Internal rate of return
  • Sensitivity analysis
  • Using financial forecasts for investment appraisal
    Capital rationing
  • Delegates will prepare investment analyses using Excel’s functions

Session 8: The cost of capital: Estimating the hurdle rate for investment appraisal

  • Sources of finance: Equity and debt
  • Interest rates
  • Estimating Beta
  • Adjusting for risk – The capital asset pricing model and ß
  • Weighted average cost of capital – WACC
  • Risk, capital budgeting and diversification

Delegates will review and evaluate CAPM and WACC calculations

Day Four

Session 9: Excel Macros – the power of automation

  • The power of macros
  • Building basic macros
  • The macro recorder
  • Editing macros
  • Error testing and stepping into routines
  • Variables and constants
  • The range object
  • Basic user input
  • Conditional logic
  • Looping through code
  • User Forms
  • Menus and toolbars
  • Debugging techniques and error handling

Delegates will prepare a series of macros. These will then be swapped between delegates and error tested.

Session 10: Pivot tables – handle large volumes of data efficiently

  • Pivot table fundamentals
  • Creating a basic pivot table
  • Customising pivot tables
  • Formatting pivot tables
  • Pivot table calculations
  • Pivot charts

Delegates will prepare a series of pivot tables, pivot table reports and charts.

State of the art facilities
Delegates can study in our comfortable hi-tech learning environment in London, near Liverpool Street Station.

Back to top

Accounting, Financial Modelling and Excel

Course Venue: London EC2