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
Methodology
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
information@premiercs.co.uk
|
