Skip to main content

27 May 2020

Using Excel to track current and actual spending – Online Course 27/05/2020

Target Audience

Any with a good working knowledge of Microsoft Excel who is looking to enhance their skills in areas detailed below. Basic Excel knowledge of formulas, sorting and filtering, navigating worksheets will be assumed.

Prerequisites

Participants should know how to:

  • Construct basic formulas in Excel i.e. use plus/minus/divide/multiply and autosum
  • Sort and filter a list
  • Would be great if they knew vlookup() but not essential.

Methodology

Delivered virtually via Zoom. Sessions will be recorded and sent on afterwards.

Outline

The course will take place over 5 sessions as per details below:

Session One (90 minutes) 27th May starting at 11am

  • Revision of basic Excel concepts
  • Understanding why correct data setup makes such a difference and allows easy maintenance
  • Identify and list your revenue sources, expenses and projects
  • Setting up your spreadsheet with dropdown lists to ensure accurate data entry
  • Setting up a cheque journal which includes project tracking options

Homework: Set up a version of a cheque journal and submit to tutor

Session Two (90 minutes) 3rd June starting at 11am

  • Check in on homework and any questions
  • Introduction to the Sumifs function
  • Use the Sumifs() function to create formulas - that automatically update - that shows Actual vs Budget spending on a monthly basis
  • Use the Sumifs() function to create formulas to create formulas - that automatically update - that shows Actual vs Budget spending on a Year to Date basis by Project

Homework: Set up a version of a Receipts and submit to tutor. Complete homework around working on Sumifs()

Session Three (90 minutes) 10th June starting at 11am

  • Check in on homework and any questions
  • Introduction to Pivot Tables & Charts - learn how to summarise your data in different ways
  • Introduction to Conditional Formatting - how to apply to a pivot table

Homework: Practise creating a pivot table and chart and applying conditional formatting

Session Four (90 minutes) 17th June starting at 11am

  • Check in on homework and any questions
  • Create charts for the Budget vs Actual
  • How to identify variances between Actual and Budget

Homework: Practise creating charts and creating formulas to identify variances

Session Five (90 minutes) 24th June starting at 11am

  • Check in on homework and any questions
  • Revision of entire process
  • Troubleshooting
  • Review

Instructor

Anne Walsh

www.the-excel-expert.com

Anne Walsh has been an IT trainer since Excel came on Windows 95. She has been a freelance IT trainer since the mid-90s. She is a MOS Master Instructor. She is also the author of “Your Excel Survival Kit: Your guide to surviving and thriving in an Excel world” which has been receiving consistent 5 star reviews on Amazon. She blogs regularly at www.the-excel-expert.com

You can read testimonials for her work at http://www.the-excel-expert.com/training-2/testimonials/.

Outcomes

  • Participants will understand how to set up their spreadsheet so that it will be easy to analyse and summarize
  • Participants will be able to use Excel formulas to track actual vs budgeted spending month by month and also on Year to Date basis
  • Participants will be able to colour code variances and create charts to illustrate trends.

Fees

€140

Book Now