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
Time: 1:00 PM AEST
Event: FREE Webinar - Excel Financial Functions Part 1
Topic: Financial Functions
Public: Public
Registration: Click here to register.

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
Time: 1:00 PM AEST
Event: Webinar - Dashboards #4 - PivotCharts and Slicers
Topic: Dashboards #4 - PivotCharts and Slicers
Public: Public
Registration: Click here to register.

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
Time: 1:00 PM AEST
Event: Webinar - Dashboards #3 - Sparklines and Conditional Formats
Topic: Dashboards #3 - Sparkline and Conditional Formats
Public: Public
Registration: Click here to register.

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
  • automating headings
  • 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
Time: 1:00 PM AEST
Event: Webinar - Dashboards #2 - Formulas and Functions
Topic: Array Formulas
Public: Public
Registration: Click here to register.

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
Time: 1:00 PM AEST
Event: FREE Webinar - Dashboard #1 - Guidelines and Techniques
Topic: FREE - Excel Dashboard Guidelines
Public: Public
Registration: Click here to register.

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

Price: $25 incl GST or $10 if you own the book

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
Time: 1:00 PM AEST
Event: Book Webinar - Chapter 15 - Case Study Two
Topic: Book Chapter 15
Public: Public
Registration: Click here to register.

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

Price: $25 incl GST or $10 if you own the book

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
Time: 1:00 PM AEST
Event: Book Webinar - Chapter 15 - Case Study Two - Part 2
Topic: Book Chapter 15
Public: Public
Registration: Click here to register.

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
• advantages and disadvantages of arrays
• 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
Time: 1:00 PM AEST
Event: Webinar - Introduction to Array Formulas
Topic: Array Formulas
Public: Public
Registration: Click here to register.

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
Time: 1:00 PM AEST
Event: FREE Webinar - Custom Number Formats
Topic: FREE - Excel Custom Number Formats
Public: Public
Registration: Click here to register.

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
Time: 1:00 PM AEDST
Event: Webinar - Excel Logic Formulas
Topic: Logic Formulas
Public: Public
Registration: Click here to register.

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
Time: 1:00 PM AEDST
Event: Webinar - Excel Lookup Formulas
Topic: Excel Lookup Formulas
Public: Public
Registration: Click here to register.

https://attendee.gototraining.com/r/3310267062681105154

VLOOKUP Function

 Price: $25 incl GST

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
Time: 1:00 PM AEDST
Event: Webinar - VLOOKUP Function
Topic: VLOOKUP Function
Public: Public
Registration: Click here to register.

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
Time: 1:00 PM AEDST
Event: Webinar - SUMPRODUCT Function (1.5 hours)
Topic: SUMPRODUCT Function
Public: Public
Registration: Click here to register.

Power Query and Multiple Files

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
Time: 1:00 PM AEDST
Event: Webinar - Power Query and Multiple Files
Topic: Power Query and Multiple Files
Public: Public
Registration: Click here to register.

Excel 2016 Power Query Introduction

 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.

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
Time: 1:00 PM AEDST
Event: Webinar - Introduction to Power Query
Topic: Introduction to Power Query
Public: Public
Registration: Click here to register.

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
Time: 1:00 PM AEDST
Event: FREE Webinar - Mastering Excel's Form Controls
Topic: FREE - Mastering Excel's Form Controls
Public: Public
Registration: Click here to register.

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
Time: 1:00 PM AEDST
Event: Webinar - INDIRECT Function
Topic: INDIRECT Function
Public: Public
Registration: Click here to register.

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

Price: $25 incl GST or $10 if you own the book

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
Time: 1:00 PM AEDST
Event: Book Webinar - Chapter 14 - Case Study One
Topic: Book Chapter 14
Public: Public
Registration: Click here to register.

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
• adding validations to your budgets

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
Time: 1.00 PM (AEDST)
Event: Webinar - Excel Budget Template
Topic: Budget Template
Public: Public
Registration: Click here to register.

Excel Budget and Power Query

 Price: $25 incl GST

Duration: 1 hour

Discover how to use Power Query to solve two major issues in multi file budget systems. Example uses a P&L budget.

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

These techniques have been applied to a budget, but could also be used for forecasting and reporting.

Learn how to

  • quickly create a budget template to speed up development time
  • link all budget files to central tables that expand
  • create dynamic drop down lists
  • compile multiple Excel budget files into a single budget layout
  • create a normalised budget listing ready for use in pivottables and formula-based reports
  • how to use folders for versions and easily update the folder in your compilation file.

This is a practical, hands-on application of Power Query in a budgeting setting.

 

Date: February 26, 2019
Time: 1:00 PM AEDST
Event: Webinar - Excel Budget and Power Query
Topic: Budget and Power Query
Public: Public
Registration: Click here to register.