Automating Month End / Weekly Reporting with Excel VBA

HomeAutomating Month End / Weekly Reporting with Excel VBA
 

Automating Month End / Weekly Reporting with Excel VBA

***PLEASE NOTE: OUR PUBLIC TRAINING FOR Q1 AND Q2 2018 IS SOLD OUT FOR THIS COURSE ***
For those who work with Excel daily and would like to get a lot more done with Excel in less time.

Course length: 2 days (9.15am – 4.30pm)
Location: London, venue details confirmed closer to date
Course Objective
Expect to save between 1 to 2 days per month of effort in reporting as we teach you the tools and skills to use Excel/VBA to dramatically speed up the time-consuming task of weekly/month end reporting.

£650.00

Clear
SKU: ct4 Category:

Description

***PLEASE NOTE: OUR PUBLIC TRAINING FOR Q1 AND Q2 2018 IS SOLD OUT FOR THIS COURSE ***

Course Details

Day 1 – Zero To VBA Expert

Reminder of what Excel is and how VBA benefits us
We begin all our sessions with the question, what Excel is confused for, what Excel really is and how to get the most out of Excel and VBA for your work.
Benefit: Rather than jumping from feature to feature and learning arbitrary Excel skills, we want to take a few minutes to establish clear goals with Excel VBA so we can get the most out of it.

Sprint Introduction – programming in minutes, regardless of skill level
Controlling cells and ranges, copying and pasting, controlling fonts and colouring cells, how to select multiple lines and clearing and column widths
Benefit: We bypass all the jargon and unnecessary functions and rapidly get you comfortable with programming in Excel VBA.

Introduction to Automation: Loops and Logic are the foundation
Building Logic with IF and Select Case and when to use each, the importance of Loops for automating our work.
Benefit: You will see how loops are a brilliant shortcut to more productivity and how to implement logic to truly automate.

Using Macros to dramatically speed up your work
Manoeuvring and Macros, Constants and Variables (unusually we cover this a lot later than other courses and you will find out why), the MOST important line in VBA you need to know, putting together everything so far to demonstrate Automation.
Benefit: The Macro recorder is not just a tool to record actions but it also teaches us to write VBA.

A quick look at key settings
Macro file formats, Developer tab and Macro Security, Modules, macros and relative references
Benefit: The key settings that we need to have in place to ensure that our Macros and VBA code work as expected.

Simple approach to dealing with errors
Predictable way to audit and resolve problems, handling errors and sheet issues, why Macros can go wrong and what to do about it.
Benefit: You WILL get errors, it’s a part of creating working code, here’s how to deal with it in a predictable way.

Create charts in seconds: Automation you can use at work immediately
A first look at Buttons, Charting driven by Macros, using loops, logic, macro recording and charting to solve a real problem.
Benefit: By working through a very practical example of automation with charts you are likely to have opportunities to apply this in your own work.

60-minute Q & A with Instructor (Optional)
Delegates can stay back and ask questions, reinforce skills learned over the day and get guidance on implementing the skills in your actual work. You are encouraged to bring in any files report you work on for input on improving them.

Day 2 – Automating the entire reporting process

Getting data from systems
The easy rule and approach to get our data from systems.

Getting data from colleagues and stakeholders
How to set up data collection templates that will reduce time wasting during your process, best practice for engaging with stakeholders for data.

Gathering our inputs and collating all the data
How to save all our data to make the analysis easier and using Do While loops to get only the data we need.

Automating the Data Analysis
Using VBA to control Autofilters, working with variable ranges seamlessly and using VBA’s in built formulas to simplify our coding significantly.

Automating the Reports/Dashboards
Using a set it and forget it approach where all the work is done in month 1, updating our monthly data set, automating the checking/validation of data (not recommended to automate the quality assurance), When to use Formulas Vs VBA.

Workshop Q & A and next steps
Delegates can stay back and ask questions, reinforce skills learned over the day and get guidance on implementing the skills in your actual work. You are encouraged to bring in any files report you work on for input on improving them.

Follow Up
This is a very intense course and follow is a key part of the progression here. The instructor will arrange a 15-30 min follow up call with each delegate and look at how the training has been implemented. If you haven’t had a chance to implement then the instructor will give you further guidance.

Additional information

Date

05/03/2018 – London, 12/02/2018 – London, 22/01/2018 – London

FAQ's

What is VBA
Visual Basic for Applications is the programming language used to control Excel and other Microsoft applications automatically. The benefit to your company is huge.

What is Automation
Automation is the automatic controlling of Excel with VBA so it does what we want it to do on Autopilot. Tasks that take minutes and hours can often be accomplished in seconds. The benefit to companies is huge.

I am not very technical minded; will this go over my head?
Not at all, this course has succeeded in teaching programming to very non-technical people. It’s designed with a proprietary curriculum that avoids jargon and focuses on the most important aspects of controlling Excel.

What skill level do you need to be at with Excel?
This is not for beginners in Excel, it’s an intense but transformative course. You should be an experienced, though not necessarily advanced user of Excel.

What are the prerequisites for this course?
The more advanced you are the better but you should be comfortable with Finding data with VLOOKUPS, Sorting Data, Charts and Conditional Formatting. If you are not entirely confident, we will give you access to online materials that will prepare you for the course as soon as you register for the course. Our Beginner to Intermediate course prepares you well.

What skill level do you need to be at with Excel?
This is not for beginners in Excel, it’s an intense but transformative course. You should be an experienced, though not necessarily advanced user of Excel.

What are the prerequisites for this course?
The more advanced you are the better but you should be comfortable with Finding data with VLOOKUPS, Sorting Data, Charts and Conditional Formatting. If you are not entirely confident, we will give you access to online materials when you register that will prepare you for the course. Our Beginner to Intermediate course prepares you well.

Will I forget these skills?
This is a very real occurrence for many delegates who don’t get a chance to use their skills. We have countered this in two ways:
1. Our curriculum focuses on teaching only the most useful practical Excel skills. It has been designed by Sohail Anwar who has 10+ years of using Excel in consulting for major organisations. The course material and teaching approach has been extensively tested on over 1000 students and refined over the last 5 years.
2. We have a strong support aspect which is detailed below.

What kind of support is there for delegates?
At the end of each day there will be an extensive open-ended Q & A with the instructor. The instructor will have some one on one time which each delegate and after the course the instructor will follow up with each delegate with a progress check and a recommended roadmap to improve skills further. Delegates will also get e-mail /forum support for up to a year afterwards.

Who is the ideal attendee?
This is for Analysts, PMO’s, Portfolio Analysts, Management Accountants, Finance Business Partners, Finance Analysts and Controllers with at least intermediate level Excel skills.

Which sectors is this course suitable for?
The approaches have been proven over dozens of projects/organisations in Finance, Energy, Retail, Pharma, Consulting and Public Sector.

Which version of Excel will be used?
We will be using Office 365 which is equivalent to Excel 2016, however you are welcome to bring your own laptop providing your version of Excel is at least 2007.

Will the course have breaks?
Yes, there will be two short breaks and a 30-min lunch break. All food and refreshments will be provided by us.

Where is the venue?
We will let you know the venue details closer to the actual course date.

When does the course start?
We recommend getting to the venue for 9am with a strict 9.15am start. The course will go on till 4.30 pm with an optional 60minute Q & A after with the instructor where delegates can get further guidance.

Can you deliver the course at our company’s premises?
Yes, please get in touch and we can arrange an on-site course, fully tailored to give maximum benefit to your company.

Our promise to you:

If you have taken the time and cost to learn from us then us will make the most of that time, we will ensure the skills you learn will give you a great return on your efforts.

What we’ve found is that the more things we try to teach in one day the more overwhelmed people become and retention of the skills diminishes quickly. So, the goal isn’t to teach you lots of things but to really focus on the few key things that will have the biggest impact to you and your company when you go back in to work the next day.

Following the session, if you are not happy about the training, you can get in touch with us within 30 days and we can arrange for you to attend another course free of charge or issue a full refund.