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.

Advanced Budgeting Techniques

 Price: $25 incl GST

Duration: 1 hour

If you work on a calendar year it’s nearly that time of the year when your thoughts turn to budgets.

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

Excel is the main application used to create budgets for small to medium organisations. Even large organisations use it as part of their budget process.

You can create flexible budgeting models in Excel by combining its many built-in features with its various functions.This webinar with demonstrate techniques to

  • choose monthly allocation methods on a line by line basis
  • select different inflation rates to apply on a line by line basis
  • choose between percentage-based allocations or per unit allocations
  • turn inflation rates off and on at the click of a Check Box
  • use drop down lists for selecting options
  • create a flexible validation system

These techniques could also be applied to forecasting and financial modelling.

Date: February 14, 2019
Time: 1:00 PM AEDST
Event: Webinar - Advanced Budgeting Techniques
Topic: Advanced Budget Techniques
Public: Public
Registration: Click here to register.

Webinar – Three Report Budget

Price: $25 incl GST

Duration: 1 hour

This session examines how you can create a combined Profit & Loss, Balance Sheet and a Cash Flow budget.

We will focus on reviewing existing formulas and structures.

The techniques covered include

  • display and format techniques for budgets
  • functions used EDATE, IF, SUMIFS, INDEX, MATCH
  • using centralised logic calculations
  • different budget assumption calculations
  • handling single or monthly assumptions
  • getting a flexible starting position for the balance sheet
  • using a workings sheet
  • loan schedule
  • handling GST
  • validations that handle error cells

Plus I am will sharing extra tips along the way.

 

Date: February 21, 2019
Time: 1:00 PM AEDST
Event: Webinar - Three Report Budget
Topic: Three Report Budget
Public: Public
Registration: Click here to register.

Webinar – Gantt Chart Workshop

Price: $25 incl GST

Duration: 1 hour

A Gantt chart is a project management tool to help you manage multi-task projects.

There are many online apps that will help you create one but I thought I would use a Gantt chart as a way to demonstrate how to use many of Excel’s features together to create a reporting model.

The techniques covered include

  • date formulas
  • text formulas
  • conditional formats
  • scroll bar control
  • formatted tables
  • range names
  • data validation

Plus a few others and extra tips along the way.

 

Date: February 20, 2019
Time: 1:00 PM AEDST
Event: Webinar - Gantt Chart Workshop
Topic: Gantt Chart Workshop
Public: Public
Registration: Click here to register.

FREE Webinar – Format As Table

In this session you will learn all about Excel’s formatted tables.

Many of Excel’s features and functions work seamlessly with formatted tables. They can help you improve the structure and reliability of your spreadsheet files.

Formatted tables can allow you to create powerful reports like those in a relational databases.

Topics covered

  • advantages and limitations of formatted tables
  • keyboard shortcuts
  • using formatted tables with formulas
  • solutions to some of the limitations of formatted tables
  • using range names with formatted tables
  • using formatted tables with data validations
  • creating a running total
  • using PivotTables
  • Relationships (Data tab)

As always I will share a few other tips.

 

 

Date: February 13, 2019
Time: 1:00 PM AEDST
Event: FREE Webinar - Format As Table
Topic: FREE - Format As Table
Public: Public
Registration: Click here to register.

FREE Webinar – Excel Yourself 2018

My annual review webinar where I examine four of my Excel Yourself articles from the monthly INTHEBLACK magazine from the past year.

Includes extra content not included in the magazine. Content applies to all Excel versions.

This year’s smorgasbord webinar includes

  • how to handle validations
  • dynamically highlighting the current row in a table (uses a macro)
  • centralising logic – how and why to do it – alternatives to the IF function
  • creating a slope chart (includes a macro to speed up the process)

As always there will be a few more tips and tricks shared in the session.

Date: December 12, 2018
Time: 1:00 PM AEDST
Event: FREE Webinar - Excel Yourself 2018
Topic: FREE - Excel Yourself 2018
Public: Public
Registration: Click here to register.

Chapter 13 – Validations

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

Duration: 1.0 hour

The 20th in my series of webinars based on my book and the last free one.

The book is the manual.

We are up to page 337 in the printed book. We will start and finish Chapter 13 which covers report validation techniques.

The session covers

– Centralised validations
– Rounding Tolerances
– Error tracking and debugging
– Precedents and dependents
– Identifying new codes
– Conditional formats

If you own the book you can use the answer to a question as the voucher code to attend for FREE.

Date: November 29, 2018
Time: 1:00 PM AEDST
Event: Book Webinar - Chapter 13 - Validations
Topic: Book Chapter 13
Public: Public
Registration: Click here to register.

Webinar – Macros 6 – Workshop 1

Price: $25 incl GST

Duration: 1 hour

Learn how to bring together many of the techniques from previous sessions to solve real world problems.

A variety of macros are covered showing you different ways to achieve similar outcomes. Lots of tips and tricks along the way.

The four macros covered are

  • conditionally hide rows (different macros shown)
  • conditionally hide columns (also run via an event)
  • clear input cells (different macros shown)
  • convert a MYOB report in to a data table
Date: November 27, 2018
Time: 1:00 PM AEDST
Event: Webinar - Macros 6 - Workshop 1
Topic: Macros
Public: Public
Registration: Click here to register.

Webinar – Macros 5 – Function Macros

Price: $25 incl GST

Duration: 1 hour

Excel Function macros allow you to create your own cell-based functions, just like Excel’s built-in functions. These are called User Defined Functions (UDFs) and can be used to simplify complex calculations.

Macro Functions also allow you to create re-useable code which can centralise your business logic and make code maintenance easier.

This session will examine a number of Functions to explain how, why and when to use them.

This session will cover

  • the SUMBOLD UDF – add up bold values
  • the JOIN UDF – joins text togther from a range
  • functions for sheet, file and user names
  • alternatives for the SEARCH and FIND functions
  • the SUMCOLOUR UDF – adds up specific values in cells with a certain fill colour
  • an “Are You Sure?” function
  • a function to identify if a one of a list of words is used in a cell
Date: November 26, 2018
Time: 1:00 PM AEDST
Event: Webinar - Macros 5 - Function Macros
Topic: Macros
Public: Public
Registration: Click here to register.

Webinar – Excel Data Validation

Price: $25.00 incl GST

Duration: 1.5 hours

Excel has a built-in Data Validation feature that you can use to limit and control the entries that go into certain cells. You can use this to make sure users comply with your input requirements.

Unfortunately the feature has one failing. Learn what it is and how to handle it.

This session will take you through some useful techniques to create flexible data validations including in-cell drop down lists, as well as how to cope with the major failing of the system.

The session covers

  • setting upper and lower parameters – fixed and flexible
  • creating custom error messages
  • how to validate the validations and show the user when an error has been made
  • creating in-cell drop down lists
  • creating flexible lists that automatically expand and don’t require any complicated formulas
  • how to create dependent drop downs – one drop down affects the entries in a second drop down

As usual I will throw in some useful general tips along the way.

Date: November 22, 2018
Time: 1.00 PM (AEDST)
Event: Webinar - Excel Data Validation
Topic: Excel Data Validation
Public: Public
Registration: Click here to register.

Excel Advanced Range Names

 Price: $25 incl GST

Duration: 1 hour

This session covers many advanced techniques. These techniques can be applied to reporting models, dashboards, budgets and forecasts.

The session covers

  • Naming conventions – what they are and how to use them to simplify formula creation
  • Non-formula uses for range names – create an unbreakable hyperlink
  • Linking between files – technique to reduce broken, or corrupted links
  • Advanced techniques – creating a sheet name range name and some advanced ways to refer to ranges
  • The INDIRECT function and range names
  • One Formula to Rule Them All – an amazing technique that can be applied to many situations – a single formula to extract Actuals, Budgets and Forecasts
  • Advanced range reference techniques

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

Date: November 20, 2018
Time: 1:00 PM AEDST
Event: Webinar - Excel Advanced Range Names
Topic: Excel Advanced Range Names
Public: Public
Registration: Click here to register.

Excel Range Name Essentials

 Price: $25 incl GST

Duration: 1 hour

Learn the basics, plus some powerful techniques to make your range names more dynamic. This session has everything you need to get started with using range names effectively.

Even if you have been using range names this session provides some useful shortcuts and techniques that you may not be aware of.

This session covers

  • the three ways to create a name (some are much easier than others)
  • naming rules and suggestions
  • keyboard shortcuts
  • name intersections
  • formula-based names
  • using names with formatted tables

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

Date: November 15, 2018
Time: 1:00 PM AEDST
Event: Webinar - Excel Range Name Essentials
Topic: Excel Range Name Essentials
Public: Public
Registration: Click here to register.

FREE Webinar – Excel 2016 Mouse Shortcuts

Following up from the keyboard shortcuts session in October, in November we will look at quick ways to do common tasks with the mouse.

Some of the topics and techniques you will learn include:

  • the various cursor shapes and how to use them correctly
  • right clicking options you may not know exist
  • copying anything quickly with the mouse, even sheets
  • drawing perfect circles and squares
  • lining up charts and other shapes perfectly
  • cool techniques with the Fill Handle
  • something you may never have tried with the right mouse button
  • using the the Office clipboard
  • a trick to use the Series dialog

This session will demonstrate lots of techniques using the mouse and also using the mouse with the keyboard.

Even if you prefer using the keyboard you might learn a few useful new techniques.

Date: November 21, 2018
Time: 1:00 PM AEDST
Event: FREE Webinar - Excel 2016 Mouse Shortcuts
Topic: Excel 2016 Mouse Shortcuts
Public: Public
Registration: Click here to register.

FREE Webinar – Excel 2016 Mouse Shortcuts

Following up from the keyboard shortcuts session in October, in November we will look at quick ways to do common tasks with the mouse.

Some of the topics and techniques you will learn include:

  • the various cursor shapes and how to use them correctly
  • right clicking options you may not know exist
  • copying anything quickly with the mouse, even sheets
  • drawing perfect circles and squares
  • lining up charts and other shapes perfectly
  • cool techniques with the Fill Handle
  • something you may never have tried with the right mouse button
  • using the the Office clipboard
  • a trick to use the Series dialog

This session will demonstrate lots of techniques using the mouse and also using the mouse with the keyboard.

Even if you prefer using the keyboard you might learn a few useful new techniques.

Date: November 13, 2018
Time: 1:00 PM AEDST
Event: FREE Webinar - Excel 2016 Mouse Shortcuts
Topic: Excel 2016 Mouse Shortcuts
Public: Public
Registration: Click here to register.

Excel 2013-2016 Advanced PivotTables

 Price: $25 incl GST

Duration: 1 hour

This updated session assumes you are already using pivottables. Topics covered include

  • Calculated Fields – what they are and how and why to use them
  • Calculated Items – what they are and how and why to use them
  • Advanced filtering
  • Useful Report Layouts
  • Conditional Formatting
  • Adding relationships so you can report on two tables as once
  • Data normalisation and variance analysis example using Power Query
  • Two handy macros that can speed up pivot table creation

 

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

Date: October 24, 2018
Time: 1:00 PM AEDST
Event: Webinar - Excel 2013-2016 Advanced PivotTables
Topic: Excel 2013-2016 Advanced PivotTables
Public: Public
Registration: Click here to register.

Excel 2013-2016 PivotTable Essentials

 Price: $25 incl GST

Duration: 1 hour

This updated session will cover the basics of pivot table creation as well as solving some common frustrations. Topics covered include

  • pivot cache what it is and why it is important
  • data rules that make creating pivot tables easier
  • format as table and why you need to use it
  • populating missing entries in the data – manually and with Power Query
  • the basics of creation
  • pivot charts
  • filtering with slicers
  • grouping

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

Date: October 22, 2018
Time: 1:00 PM AEDST
Event: Webinar - Excel 2013-2016 PivotTable Essentials
Topic: Excel 2013-2016 PivotTable Essentials
Public: Public
Registration: Click here to register.

Excel 2013-2016 Chart Hacks

 Price: $25 incl GST

Duration: 1 hour

This session will show you how to achieve improved charts and how to create charts that aren’t built-in to Excel.

Topics covered:

  • using text boxes with charts
  • improving Excel’s default charts – before and after charts
  • Variance chart
  • High – Low Chart (column and line)
  • Conditional colours in a chart
  • Waterfall/Bridge chart
  • Adding banding to a chart
  • handling expanding data ranges

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

Date: October 17, 2018
Time: 1:00 PM AEDST
Event: Webinar - Excel 2013-2016 Chart Hacks
Topic: Excel 2013-2016 Chart Hacks
Public: Public
Registration: Click here to register.

FREE Webinar – Excel 2016 Keyboard Shortcuts

Yes you my be really good with the mouse but sometimes it is quicker to use the keyboard.

When I ran some face to face training sessions recently I was reminded how much people LOVE keyboard shortcuts. So I thought I would update my keyboard shortcut webinar and run it this month.

Using the mouse isn’t always the quickest way to perform tasks in Excel. Keyboard shortcuts can speed up your work and save you hunting through screens and dialogs. Some of the keyboard shortcuts you will learn in this session are:

  • wrap text and other useful formats
  • applying row and column grouping
  • selecting a table quickly
  • copying visible cells only
  • apply and remove Freeze Panes
  • returning after following a hyperlink
  • how to avoid an annoying feature of formula and reference dialogs when you press an arrow key to move around
  • get the most out of the Tab key

There will be lots of other shortcuts as well. Even if you prefer using the mouse you might learn a few useful new techniques.

 

Date: October 18, 2018
Time: 1:00 PM AEDST
Event: FREE Webinar - Excel 2016 Keyboard Shortcuts
Topic: Excel 2016 Keyboard Shortcuts
Public: Public
Registration: Click here to register.