## FREE Webinar – Financial Functions

Excel has many functions dedicated to financial calculations. These functions take three or more arguments and there are a few things you need to know to use them correctly.

Most involve loans or calculations associated with the time value of money.

This session will cover the following functions

• PV – Present Value
• PMT – loan payments
• CUMIPMT – cumulative interest for loan schedules
• RATE – interest rate
• FV – future value
• NPER – number of period

The session finishes with a loan model that calculates the “missing” value for a loan scenario.

These sessions fill up very quickly, so please register early to secure your place.

Date: February 26, 2016 1:00 PM AEDST FREE Webinar - Excel Financial Functions Financial Functions Public Click here to register.
| | Posted in Events

## FREE – Final Book Webinar – Q & A session

#### Duration: 1.0 hour

The 25th in my series of webinars based on my book.

Anyone can submit questions (owners of my book get priority) either at registration or via email.

Example data provided will be made generic before being used in the session.

Date: June 13, 2019 1:00 PM AEST FREE - Final Book Webinar - Q & A session Book Q & A Public Click here to register.
| | Posted in Events

## FREE Webinar – Financial Functions Part 1

Excel has many functions dedicated to financial calculations. These functions take three or more arguments and there are a few things you need to know to use them correctly.

Most involve loans or calculations associated with the time value of money.

This session will cover the following functions

• PV – Present Value
• PMT – loan payments
• CUMIPMT – cumulative interest for loan schedules
• RATE – interest rate
• FV – future value
• NPER – number of period

The session finishes with a loan model that calculates the “missing” value for a loan scenario.

Date: June 11, 2019 1:00 PM AEST FREE Webinar - Excel Financial Functions Part 1 Financial Functions Public Click here to register.
| | Posted in Events

## Webinar – Dashboards #4 – PivotCharts and Slicers

### Price \$25 incl GST

This is the fourth in the series and focuses on creating a dashboard using PivotCharts with Slicers.

PivotTables are extremely powerful and allow you to summarise large data sets very quickly without using any formulas. PivotCharts are charts that are based on PivotTables and are extremely interactive. This dashboard will use Slicers to add interactivity to the dashboard so that users can change the dashboard.

The session includes

• how a PivotTable works
• creating a PivotTable that works well with a chart
• formats for large values
• making PivotCharts display better for dashboards
• making changes to PivotTables to improve headings
• sorting and Top 10 reports
• use Slicers to control filters on multiple PivotTables
• using the GETPIVOTDATA function to create specify reports/charts

As always, I will share other tips and tricks along the way.

Date: May 30, 2019 1:00 PM AEST Webinar - Dashboards #4 - PivotCharts and Slicers Dashboards #4 - PivotCharts and Slicers Public Click here to register.
| | Posted in Events

## Webinar – Dashboards #3 – Sparklines and Conditional Formats

### Price \$25 incl GST

This is the third in the series and focuses on Sparkline charts added in Excel 2010 as well as using Conditional Formatting with dashboards.

Sparkline charts are small charts that are specifically designed for dashboards.

We will add ticks and crosses using conditional formats to identify measures (good and bad) that may need review.

The Chart Data sheet structure has already been populated so we will review some of the formulas used in its creation.

The session includes

• reviewing report formulas
• MATCH function
• SUMPRODUCT function
• handling high score is bad vs low score is bad in a formula
• default settings you may need to change for Sparkline charts
• controlling the display of ticks and crosses
• formats for dashboards
• using templates

As always, I will share other tips and tricks along the way.

Date: May 29, 2019 1:00 PM AEST Webinar - Dashboards #3 - Sparklines and Conditional Formats Dashboards #3 - Sparkline and Conditional Formats Public Click here to register.
| | Posted in Events

## Webinar – Dashboards #2 – Formulas and Functions

### Price \$25 incl GST

This is the old school technique of creating dashboards in Excel.

We will create summary reports from the data and from those summary reports we will extract the chart data which we will then base our charts on.

We will look at techniques to create small charts and how we can combine those on a dashboard.

The session includes

• formula basics
• formulas that work with dates
• SUMIFS function
• INDEX function
• formats for dashboards
• using templates

As always, I will share other tips and tricks along the way.

Date: May 9, 2019 1:00 PM AEST Webinar - Dashboards #2 - Formulas and Functions Array Formulas Public Click here to register.
| | Posted in Events

## FREE Webinar – Dashboard #1 – Guidelines and Techniques

This is first session is a series of four webinars on Excel dashboards. The other three sessions are paid sessions.

In this free session we will focus on chart and dashboard guidelines plus some techniques used to create better dashboards.

We will also look at creating a bullet chart which is a better alternative to a gauge or dial chart.

The session will cover

• general chart guidelines
• questions to ask yourself to develop better charts
• best charts to use
• charts and formats to avoid
• using text boxes on dashboards
• how to create small charts
• chart templates
• lining up and re-sizing charts
• useful number formats for dashboard charts
• using the #N/A error with charts
• how to create a bullet chart

As always I will share a few other tips.

Date: May 8, 2019 1:00 PM AEST FREE Webinar - Dashboard #1 - Guidelines and Techniques FREE - Excel Dashboard Guidelines Public Click here to register.
| | Posted in Events

## Chapter 15 – Case Study Two – 12 Month P&L Report

#### Duration: 1.0 hour

The 23rd in my series of webinars based on my book. The book is the manual.

We are up to page 379 in the printed book. These case study chapters are the most important chapters in the book as they bring the techniques together to form reporting solutions.

It will take two sessions to complete Case Study Two, both will be run in April 2019 which is the 5th anniversary of the publication  of the book.
Case Study Two is a 12 Month P&L Report, this session will include
• creating a 12 month P&L report with current month and YTD budget or forecast comparisons
• including last year’s figures
• using structured range names
• formulas and functions used to create the report

If you own the book you can use the answer to a question as the voucher code to attend for \$10. If you don’t own the book the price is \$25.

Date: April 18, 2019 1:00 PM AEST Book Webinar - Chapter 15 - Case Study Two Book Chapter 15 Public Click here to register.
| | Posted in Events

## Chapter 15 – Case Study Two – 12 Month P&L Report

#### Duration: 1.0 hour

The 24th in my series of webinars based on my book. The book is the manual.

We are up to page 394 in the printed book. These case study chapters are the most important chapters in the book as they bring the techniques together to form reporting solutions.

This is last session of Case Study Two and the book. This session may go over the hour to make sure the chapter is complete.
Case Study Two is a 12 Month P&L Report, this session will include
• finalising the 12 month P&L report with current month and YTD budget or forecast comparisons
• creating a dashboard
• creating a detail state report using GETPIVOTDATA
• formulas and functions used to create the reports and dashboards

If you own the book you can use the answer to a question as the voucher code to attend for \$10. If you don’t own the book the price is \$25.

Date: April 29, 2019 1:00 PM AEST Book Webinar - Chapter 15 - Case Study Two - Part 2 Book Chapter 15 Public Click here to register.
| | Posted in Events

## Webinar – Introduction to Array Formulas

### Price \$25 incl GST

Array formulas are an advanced topic. They are not for everyone. After macros and pivot tables they are the topic I get asked most about at training courses.

Array formulas can perform calculations that no other formula can perform. Array formulas can be complex, confusing and difficult to understand. In this session I hope to remove some of the confusion and explain how they work with some useful examples and techniques.

If you use arrays or have wondered what they are, then this session will be useful.

WARNING: You need to have at least intermediate Excel skills to be able to follow this webinar. Whilst I will attempt to simplify the complexity for you, the last two examples in the session have quite complex formulas that may make your head hurt.

The session includes
• array syntax – what it is and how to use it with other non-array formulas
• simple single cell array formulas
• single cell validation formula
• multi-cell array formulas demonstrated and explained
• dynamic sorting with an array formula
• dynamic extract unique values with an array formula

As always, I will share other tips and tricks along the way.

Date: April 17, 2019 1:00 PM AEST Webinar - Introduction to Array Formulas Array Formulas Public Click here to register.
| | Posted in Events

## FREE Webinar – Custom Number Formats

In this brand new webinar we examine Custom Number Formats which hide away at the bottom of the Number Format tab. These custom made formats offer some useful techniques.

They can

• display negatives in red and with brackets
• format mobile phone numbers correctly
• display numbers and text together and still perform calculations
• hide zeroes
• display rounded numbers to thousands and millions
• display the day of the week
• create customised dates
• be saved and be available in new sheets and files using a Template
• be saved as a Style

See examples and demonstrations of many different custom number formats and learn how to create your own.

As always I will share a few other tips.

Date: April 16, 2019 1:00 PM AEST FREE Webinar - Custom Number Formats FREE - Excel Custom Number Formats Public Click here to register.
| | Posted in Events

## Excel Logic Formulas

### Price \$35 incl GST

Discover how to add decision making structures into your spreadsheets to make them more flexible. There’s a lot more to learn than just the IF function.

The session covers

• using logic calculations without the IF function
• validations
• the IF, AND as well as the OR functions
• nesting IF functions and formula shortening techniques
• working with parameters for reasonableness checks
• error handling
• IS functions

As always, you will also pick up a few tips and tricks along the way.

Date: March 27, 2019 1:00 PM AEDST Webinar - Excel Logic Formulas Logic Formulas Public Click here to register.
| | Posted in Events

## Excel Lookup Formulas

### Price \$25 incl GST

This session explains and demonstrates Excel’s lookup functions and formulas. Discover how you can extract data from data tables using formula. (There’s a lot more to lookups than just the VLOOKUP function!)

• VLOOKUP and HLOOKUP functions – how to use them and why they don’t always work and what breaks them
• INDEX-MATCH combination technique – create flexible lookups
• Error handing – see how to handle the #N/A! error differently from other errors
• Working with vertical data – discover how to convert vertical data (like addresses) into a table layout
• Two criteria lookups – learn the formula that can extract data based on two criteria
• Extracting data with multiple instances of entries – find out how to extract the first, second or third instance from a data table.

As always, you will also pick up a few tips and tricks along the way.

Date: March 26, 2019 1:00 PM AEDST Webinar - Excel Lookup Formulas Excel Lookup Formulas Public Click here to register.
| | Posted in Events

## VLOOKUP Function

#### Duration: 1 hour

Over the years this popular function has been the subject of the most questions to me from CPA’s from around the world. So I thought I would dedicate a whole webinar to how and why to use it. I have also included techniques to get around the limitations of the function. Most content applies to all Excel versions.

In this session I will cover

• the Exact match VLOOKUP – when to use it
• the Approximate match VLOOKUP – when to use it
• tips and shortcuts on using VLOOKUP
• common problems with VLOOKUP and their solutions
• why Format is Table is so important
• handling errors
• handling multiple tables
• removing the left column limitation
• two column lookups (non array solution)
• using the Relationship option to avoid VLOOKUPs (Excel 2013 and later only)

As always, I will share a few general tips along the way.

Date: March 21, 2019 1:00 PM AEDST Webinar - VLOOKUP Function VLOOKUP Function Public Click here to register.
| | Posted in Events

## SUMPRODUCT Function (\$25 – 1.5 hours)

I am excited about this webinar because it covers my favorite Excel function. SUMPRODUCT is the Swiss Army knife of functions. It can sum, count and perform criteria-based calculations that normally require special array formulas. The SUMIF and SUMIFS functions have two important limitations that SUMPRODUCT doesn’t, discover what they are.

The session outcomes will include

• The limitations of the SUMIF and SUMIFS functions
• The limitations and advantages of the SUMPRODUCT function
• Explanation of the standard use of the SUMPRODUCT function
• Explanation of undocumented techniques of the SUMPRODUCT function
• How to create powerful multi-criteria calculations without an array formula
• Learn about array syntax and how you can use it with SUMPRODUCT
• Discover how you can use INDEX, OFFSET and other functions with SUMPRODUCT
• How to perform 2-D summing calculations
Date: March 20, 2019 1:00 PM AEDST Webinar - SUMPRODUCT Function (1.5 hours) SUMPRODUCT Function Public Click here to register.
| | Posted in Events

## Price: \$25 incl GST

#### Duration: 1 hour

Power Query allows you to automatically perform data cleansing routines on your data sources – no manual intervention required. Simply refresh and your data is ready to use. You can use csv files; txt files; databases and existing Excel tables as your data sources.

Building on the skills covered in the Introduction session, we will start working with multiple files. For example you may have 12 separate CSV files in a folder. All with the same layout, one for each month of the year. Power Query can import all 12 files as if they were a single file and create a table for the whole year

This session covers
• importing multiple CSV files from a folder
• a technique to capture the file name in a field (column)
• importing multiple Excel files
• merging data from multiple tables
• Extracting header information into a column from multiple CSV files

As always, I will share other tips and tricks along the way.

Date: March 19, 2019 1:00 PM AEDST Webinar - Power Query and Multiple Files Power Query and Multiple Files Public Click here to register.
| | Posted in Events

## Excel 2016 Power Query Introduction

#### Duration: 1 hour

Power Query allows you to automatically perform data cleansing routines on your data sources – no manual intervention required. Simply refresh and your data is ready to use. You can use csv files; txt files; databases and existing Excel tables as your data sources.

Learn the basics, plus an advanced technique to automate data cleansing routines on your data sources.

This session covers

• fixing dates so that Excel can recognise them
• formatting columns as text – retaining leading zeroes in CSV files
• deleting unwanted rows and columns from your data
• removing leading and trailing spaces
• populating blank values with zeroes
• populating blanks with entries from above
• correcting trailing minus signs
• unpivot a report – converting a report layout into a table layout
• converting a MYOB report into a data table
• capture header information in a column

As always, I will share other tips and tricks along the way.

Date: March 14, 2019 1:00 PM AEDST Webinar - Introduction to Power Query Introduction to Power Query Public Click here to register.
| | Posted in Events

## FREE Webinar – Mastering Excel’s Form Controls

Learn how to build better spreadsheet interfaces using form controls like
• check boxes
• scroll bars
• option buttons
• combo boxes

Form controls can reduce the number of input errors; simplify and speed up input plus add structure to a file.

Discover the function that you need to use to get the most out of these controls. Find out a few tips and tricks to make creating and using form controls easier.

The session finishes with a loan calculator that incorporates all four controls.

As always I will share a few other tips.

Date: March 12, 2019 1:00 PM AEDST FREE Webinar - Mastering Excel's Form Controls FREE - Mastering Excel's Form Controls Public Click here to register.
| | Posted in Events

## Webinar – INDIRECT Function

### Price \$25 incl GST

Few Excel functions warrant their own webinar – INDIRECT does. It can allow you to do things that no other function can do. It is often the function of last resort as it allows you to use text to create cell references and solve difficult issues.

The outcomes from this session will be

• learning the basics of the INDIRECT function
• discovering a common problem with sheet references and how to fix them
• seeing how to extract data from structured and unstructured data
• finding out the power and flexibility of using ranges names with the INDIRECT function
• learning how to link one data validation list to another

As always, you will discover a few other tips during the session.

Date: March 13, 2019 1:00 PM AEDST Webinar - INDIRECT Function INDIRECT Function Public Click here to register.
| | Posted in Events

## Chapter 14 – Case Study One – Month and YTD P&L

#### Duration: 1.0 hour

The 21st in my series of webinars based on my book. The book is the manual.

We are up to page 351 in the printed book. These case study chapters are the most important chapters in the book as they bring the techniques together to form reporting solutions.

It will take two sessions to complete Case Study One.
Case Study One is a Month and YTD P&L Report, this session will include
• creating a month and YTD P&L report with budget or forecast comparisons
• including last year’s figures
• using structured range names
• formulas and functions used to create the report

If you own the book you can use the answer to a question as the voucher code to attend for \$10. If you don’t own the book the price is \$25.

Date: February 27, 2019 1:00 PM AEDST Book Webinar - Chapter 14 - Case Study One Book Chapter 14 Public Click here to register.
| | Posted in Events

## Excel Budget Template (1.5 hrs)

Price: \$25.00 incl GST

Duration: 1.5 hours

In this webinar I will share a budget structure I have used successfully to create a budget that allows a couple of levels. This structure has some flexibility and incorporates a number of advanced Excel features that work well together. The budget template is scalable and can be applied to numerous scenarios.

If you have a Jul-Jun financial year you might want to get a head start on next year’s budget/forecast.

The Budget Template session outcomes will include
• discover a scalable, flexible Excel budget structure
• learn formulas that can easily consolidate budgets from multiple sheets, making uploads into accounting systems easier
• find out about a little-used function that can make allocations easier to create
• see how to use tables to add structure and flexibility to your Excel files
• a range name trick that involves formatted tables

As always, you will also pick up a few other tips and tricks along the way.

This is a longer than normal session (1.5 hours) and covers many advanced topics that can simplify your budget process.

Date: February 19, 2019 1.00 PM (AEDST) Webinar - Excel Budget Template Budget Template Public Click here to register.
| | Posted in Events