What?

This course is a second level, intermediate, Excel 2007 course aimed at the general user who wants to move up from the basic level. The course is hands on and highly practical with examples being used that cover a wide variety of business and commercial settings.

Whilst the course will begin with a review of some functions and utilities from earlier versions of Excel, the majority of this course will relate to Excel 2007.

• What you will be able to do

• In summary, delegates will come to enhance their general spreadsheeting knowledge and skills and all within this five day programme. The objectives of the course include enabling delegates to be able to:

• Investigate the functions new in Excel 2007
• Use the critical financial, mathematical, statistical and logical functions built into a spreadsheet to increase reporting productivity and design
• Create efficient formulae for a wide variety of uses
• Create stunning visual reports by using conditional formatting
• Explore the benefits of hidden yet exceptionally powerful formatting
• Enhance your dashboards with a variety of advanced charts
• Analyse data using both the Analysis ToolPak and Excel 2007 in built functions
• Use goal seek and SOLVER features to maximise your financial models
• Use array formulas
• Use pivot tables to enhance the reporting functionality of your spreadsheet model
• Learn and use a range of shortcuts and tips for spreadsheet users

• How we will do it

• Delegates must bring their own personal laptop computers to the course with a full installation of Excel 2007® including the complete ToolPak and SOLVER Add Ins (call or email us for help here if necessary). The training comprises sessions in which the trainer will work through a live demonstration of a topic: delegates will work through the demonstration in real time too. A follow up exercise will normally then follow. Delegates will be provided with templates as appropriate throughout the course.

• Some demonstrations will be video based and the delegates will be given free copies of all such videos for their further training back at their place of work and at home.

Why?

The use of Excel 2007® as a tool to assist in resolving issues will greatly enhance delegates’ skills in using this powerful software package. Basic modelling techniques will be explored to enable participants to return to the work place and build models appropriate to their company and industry.

Each person on the course will develop an improved understanding of Excel 2007®. Moreover, the power of this major software package from Microsoft will become more and more available to delegates as they work through each element of the course.

Key reasons why you should attend this course:

1. The presenter is an accounting practitioner as well as a spreadsheet practitioner and trainer so his knowledge and expertise covers both areas to a high level
2. All materials and examples are up to date and comprehensive and many have been specifically devised for this course
3. The materials and examples are often based on real life situations encountered by the presenter as well as high quality situations taken from other case studies
4. Delegates will receive copies of all materials: hard copies and soft copies of all files, examples and cases used throughout the course

Who Should Attend?

• Spreadsheet users who are seeking to take their knowledge and skills to a higher level and who wish to enhance their understanding of the practicalities of spreadsheet modelling and reporting.
• Financial and Non Financial employees whose work may require them to compile and/or respond to financial and/or quantitative reports
• Any Staff seeking to enhance their spreadsheet and database knowledge and skills

• Prerequisite: Participants should already have at least a basic understanding of Excel 2007and must bring their own laptop computer with a full installation of Excel 2007®, including the complete ToolPak and SOLVER Add Ins.

How?

How are we going to achieve the objectives we have set for this course?

Throughout this course we will be using the Best Practice Spreadsheet Modelling Standards version 6.0 as published by Spreadsheet Standards Review Board.

Functions new to Excel 2007

• IFERROR
• SUMIFS
• AVERAGEIFS
• COUNTIFS

We will also review a wide variety of inbuilt functions under the following headings:

• Financial Functions FV, RATE, NPER, PV, IPMT, PMT, PPMT, IRR, MIRR
• Mathematical ABS, INT, POWER, PRODUCT, ROUND ... DOWN ... UP, SUMIF, SUMPRODUCT
• Statistical AVERAGE, AVERAGEA, CORREL, COUNT, COUNTA ... BLANK, FREQUENCY, MEDIAN,
• MODE, STDEV
• Logic AND, IF, NOT, OR
• Text CHAR, CONCATENATE, FIND, LEFT, MID, REPT, RIGHT, SEARCH, TEXT

Conditional Formatting

• Basic formatting examples: format all cells based on their values
• Format only cells that contain
• Format only top or bottom ranked values
• Format only values that are above or below average
• Format only unique or duplicated values
• Use a formula to determine which cells to format

Custom Formatting

• formatting cells according to a wide variety of criteria

• text
• values
• how to combine text and values
• using such operators as ?, 0 and many more: doing away with the need for complex IF(...) statements

Charts

• Selecting data
• Formatting your chart
• Quick ways to create charts

Advanced charts

We will deal with as many of the following as time allows:

• Combination charts
• Picture charts
• Custom chart types
• Regression and trendlines
• Creating chart templates

• Category Axis Contains Labels from Three Columns
• Conditional Colours
• Waterfall Chart
• Create a Stacked Column Chart
• Finished Waterfall Chart
• Alternatives
• Percentage Waterfall Chart
• Radar Chart
• Self Expanding Chart
• Selecting Data from a Combo Box
• Table and Chart Selection Including a Combobox
• Charts with Scroll Bars

• Basic Chart and Rates of Change Chart

Excel Tables

New to Excel 2007: we will explore the simple but very versatile world of the Excel 2007 table. These tables are ideal in the situation where new data will be added: both rows and/or columns. Excel 2007 tables can also be used in advanced charting scenarios ... learn how!

Data Analysis

Using the statistical analysis tools that are built into Excel 2007

• Descriptive statistics
• Correlation
• Histograms
• Regression

What if? and other types of Analysis

In this section we will use three very useful techniques that are also built into Excel 2007:

• Scenarios
• Goal Seek
• SOLVER

Having used these techniques you will be able to construct budgets, models, forecasts. Start with a basic budget or model or forecast and then change a variable ... what happens now?

Collaborating with Others

When we want to share files with colleagues and others, there are things that we need to think about: in this section we will consider how to protect, track and manage the various aspects of collaborating with others.

• Protect Files
• Share a Workbook
• Set Revision Tracking
• Review Tracked Revisions
• Merge Workbooks
• Administer Digital Signatures
• Restrict Document Access

Auditing Worksheets

Even if we are perfect(!), there are times when things just don’t work. We need a strategy for dealing with these situations and in this section we will develop and work on that strategy.

• Trace Cells
• Troubleshoot Errors in Formulas
• Troubleshoot Invalid Data and Formulas
• Watch and Evaluate Formulas
• Create a Data List Outline

Array Formulas

Experience shows that the majority of basic users of Excel spreadsheets have neither heard of nor use array formulas. By the end of this section you will come to see why you NEED to appreciate and use array formulas.

• Single cell array formulas
• More complex array formulas for a variety of situations

LOOKUP Functions

• HLOOKUP, VLOOKUP, LOOKUP
• Using MATCH to find values
• Combining MATCH and INDEX
• Using OFFSET
• The INDIRECT function

Pivot Tables

• Creating pivot tables
• Swapping rows, columns and pages
• Grouping fields
• Drill down
• Calculated Fields ... and their limitations
• Calculated items
• Analysing one set of data in many ways: sum, average, count ...
• Pivot charts

What?

This intensive and interactive course provides you with the ideal opportunity to take just a few days out of your busy schedule to get to grips with a wide variety of spreadsheet techniques that will increase your knowledge, skills and ability in analysing the financial performance of a n organisation or situation.

You will learn many tips, tricks and serious and detailed spreadsheeting wizardry. For example you will learn how to use many useful shortcuts, a variety of little used but extremely useful techniques as well as how to set up and use charts that you will find useful when making presentations, such as animated charts, interactive charts, multiple charts that you could use on a dashboard.

You will also have the opportunity to set up and use financial models, Pivot tables: exploring, for example, a sales ledger database; and analysing data that are taken directly from the Internet, such as company financials, stock market prices or even economic statistics.

• What you will be able to do

• In summary, delegates will come to enhance their financial, statistical and reporting skills and all within this five day programme. The objectives of the course include enabling delegates to be able to:
• Create efficient formulae for a wide variety of uses
• Using pivot tables to enhance the reporting functionality of your spreadsheet model
• Use goal seek and SOLVER features to maximise your financial models
• Use dialog boxes to capture and validate data
• Use the critical financial, mathematical, statistical and logical functions built into a spreadsheet to increase reporting productivity and design
• Call up and use the Analysis ToolPak add in for greater insights into the nature and behaviour of your data and information
• Create links between your spreadsheet and a relational database thus enhancing even further your reporting capabilities
• Learn and use a range of shortcuts and tips for spreadsheet users

• How we will do it

• Delegates must bring their own personal laptop computers to the course with a full installation of Excel 2007® including the complete ToolPak and SOLVER Add Ins (call or email us for help here if necessary). The training comprises sessions in which the trainer will work through a live demonstration of a topic: delegates will work through the demonstration in real time too. A follow up exercise will normally then follow. Delegates will be provided with templates as appropriate throughout the course.

• Some demonstrations will be video based and the delegates will be given free copies of all such videos for their further training back at the place of work and at home.

Why?

Delegates will arrive at the course with an understanding of the objectives of reporting. In addition, the use of Excel 2007® as a tool to assist in resolving issues will greatly enhance their skills in using this powerful software package. Basic modelling techniques will be explored to enable participants to return to the work place and build models appropriate to their company and industry.

Each person on the course will develop an improved understanding of Excel 2007®. Moreover, the power of this major software package from Microsoft will become more and more available to delegates as they work through each element of the course.

Key reasons why you should attend this course:

1. The presenter is an accounting practitioner as well as a spreadsheet practitioner and trainer so his knowledge and expertise covers both areas to a high level
2. All materials and examples are up to date and comprehensive and many have been specifically devised for this course
3. The materials and examples are often based on real life situations encountered by the presenter as well as high quality situations taken from other case studies

Delegates will receive copies of all materials: hard copies and soft copies of all files, examples and cases used throughout the course

Who Should Attend?

1. Senior Financial and Project Managers seeking efficiencies and who wish to enhance their understanding of the practicalities of spreadsheet modelling and reporting.
2. Non Financial eg Technical, Commercial, Scientific etc, whose work may require them to compile and/or respond to financial and/or quantitative reports
3. Any Staff seeking to enhance their spreadsheet and database knowledge and skills

Prerequisite: Participants should already have at least a basic understanding of financial statements and must bring their own laptop computer with a full installation of Excel 2007®, including the complete ToolPak and SOLVER Add Ins.

How?

How are we going to achieve the objectives we have set for this course?

Throughout this course we will be using the Best Practice Spreadsheet Modelling Standards version 6.0 as published by Spreadsheet Standards Review Board.

Specific financial reports, cases and exercises will be used throughout this course as delegates are given hands on experience of using Excel 2007 in a financial performance setting for themselves.
Day 1

An introduction to Excel 2007:

1. what's new since Excel 2007 including demonstrations of what they mean for the user
2. The ribbon
3. Keyboard shortcuts
4. Backwards compatibility
5. File sizes
6. PDF creation
7. Conditional formatting
8. Nesting in formulas
9. Sorting levels
10. Styles for tables and graphs/charts
11. SmartArt

Developing good habits: setting up a credible spreadsheet

1. Documenting your work
2. Input sections
3. Cascading within work sheets or separate worksheets?
4. Output sections and their requirements
5. Comments

Charts
Preparing charts to use in presentations:

1. animated
2. self expanding
3. interactive
4. using a combobox
5. multiple charts for dashboards

Formulas

1. using formulae to calculate
2. using names in formulae
3. array formulae
4. counting and summing formulae

Cost Modelling and Curve Fitting
Using Excel 2007® for

1. modelling costs and fitting curves for analysis and budgeting: also very useful for cost and management accounting analysis and reporting
2. forecasting accounting and financial information

Day 2

Pivot Tables

Creating

1. pivot tables from a worksheet eg sales analysis based on entries from the sales ledger
2. pivot tables from an external database: eg an Access database or a page on the internet
3. multiple pivot tables

Reporting and Budgeting with pivot tables, for example:

1. a marketing report/budget
2. an operational expense report/budget
3. a training report/budget
4. the master report/budget

Pivot Tables will now be used as appropriate at various stages throughout the rest of the course.
Day 3
Alternative ways of entering data in a spreadsheet Forms

1. creating forms
2. form controls
3. specify values for a form control
4. customise form controls with a macro

Dialogue Boxes

1. create a custom dialogue box
2. dialogue box called by a macro
3. capture input from a dialogue box
4. validate input from a dialogue box

Scenario Planning
Developing scenarios from accounting and reporting data and information

1. Sensitivity Analysis
2. SOLVER

Financial and Other Functions

Using the built in financial functions for the analysis and interpretation of financial data and results: for example, delegates will practice functions that include

DB, DDB, FV, INTRATE, IPMT, IRR, MIRR, NOMINAL, NPER, NPV, PMT, PV, XIRR, XNPV,
SUM, MIN, MAX, RANGE, IF (including nested IF), VLOOKUP, CONCATENATE, AVERAGE, COUNT, COUNTIF, INT, ROUND, RANDBETWEEN, SUMIF, SUMPRODUCT, ISERROR, STDEV
A logical and practical set of examples ranging from the simple to the complex will be used to illustrate the use of these functions.
Day 4
Using the Analysis ToolPak Add In
Install the add in and

1. calculate descriptive statistics
2. prepare histograms
3. use rank and percentile routine
4. carry out correlation analysis: relationships between two or more variables
5. perform regression analysis
6. undertake analysis of variance (ANOVA)

This session will include examples of keyboard and other shortcuts as well as the use of appropriate inbuilt functions

Data Functions

Excel's Data Functions: used to extract information from data in a table on a worksheet. Also known as the D Functions of which there are twelve, as shown in the list below. They all work essentially the same way, so we will look at only the DSUM function in this article. The D Functions are:

1. DAVERAGE, DCOUNT, DCOUNTA, DGET, DMAX, DMIN, DPRODUCT, DSTDEV, DSTDEVP, DSUM, DVAR, DVARP

The INDIRECT Function

The INDIRECT worksheet function is a useful tool for creating cell or range references on demand, as a formula is evaluated, rather than hard coding them into the formula.

Links to a Database

1. A data analysis case study that integrates both Excel and Access

This session will include examples of keyboard and other shortcuts as well as the use of appropriate inbuilt functions

Day 5

Consolidating Case Exercises

This final day will revolve around case studies involving the application of the techniques we will have explored in days 1 to 4 of this course. The emphasis of the cases will be to encourage delegates to apply their learning within the confines of a specific brief. This means that the outputs required of the delegates will be highly focused and will require a critical appreciation and application of their learning in the Excel 2007® environment.

The following comments came from delegates who attended my two day course on Financial Statement Analysis in Hi Chi Minh City in August 2010.

All participants found the course informative and all aspects of program content were useful to them, namely in particular, PIVOT Table & RATIOS formula, financial model – Excel model, using excel for Financial Statement Analysis, all methods explained - many are new.    

Some verbatim:

• All program are useful and I learn it so much.
• I know more the graph, calculation the Excel and more information about the course.
• New techniques were outlined.

The following comments came from delegates who attended my two day course on Advanced Excel for Financial Managers in Bangkok in August 2010.

All participants found the course informative and trainer ‘Very Good and a lot of experience, Good presenter”.  On program content, most of them found they were all useful to them, particularly, all techniques, excel formula & some tactics, Pivot, Array, techniques to use excel function more effectively, the speaker explains very clearly and allow audiences to practice along.  There was only one stated that ‘array’ was not too useful.  What pleased all participants the most were ‘content, experience of speaker, new things that can work faster and more efficient, excel formula, proper techniques to use in excel/report, pivot table, waterfall chart’.

Some verbatim:

• Very good and a lot of experience.
• Good presenter
• The speaker explains very clearly and allow audiences to practice along.
• Experience of speaker
• New things that can work faster & more efficient

A week or so after the course, two of the delegates wrote to each other and copied me in on their exchange ... this is what they said:

Dear P'Ch...,

Hi, how are you ka?

Can you remember me? I'm S..., who attended the training class together with you last month. I'm now try to apply many techniques with my spreadsheet. It really works!!

Hope you are fine naka.

Keep in touch

The reply to that first message:

Yes, I surely remember you....
Have to give a big compliment to our Guru, Duncan...

Dear Duncan,
The Pivot table has saved my life, after knowing it, it is so useful.
I heard that you will deliver IFRS as well, unbelievable.
Have a great day to all.