Course Detail()

NOTE: The course title has been changed from IT083v : Enhancing Financial Model with VBA made easy for Accountants (Live Webinar) to IT083v : Enhancing Financial Reporting and Analysis with VBA made easy for Accountants (Live Webinar). The outline has been updated with effect from Aug 2021.

Programme Objective

Frustrated with the operational task that is repetitive, yet time consuming when updating a financial report?

Keen to learn how to automate the Financial Reporting and Analysis tasks with Macros & VBA in MS Excel?

With VBA automation

Finance data consolidation with a click of button and more time available for analysis and decision making
One-time effort to create a rule-based Excel VBA to synchronise financial data from various systems or departments
Enhanced management decisions with instant updates of finance dashboard
Rule-based, auto running of steps avoids human error

EXPECTED OUTCOME
At the end of the course, you should be able to:

Create Excel templates that help you to perform repetitive tasks
Use VBA Control Tools to execute various VBA tasks in the template according to the reporting needs
Create spin button and scroll bar for sensitivity test for various financial scenarios
Deal with various formula errors by creating VBA template to consolidate monthly financial data into a summary sheet

Programme Outline

MODULE 1: WHAT ARE MACROS & VBA?

Introduction of Macros and VBA in Excel
How to enable Macros recording Function in Excel?
Creating simple Macros through recording
Introduction of various keyboard shortcut in Excel VBA
Challenge 1: Record simple Macros to unhide Excel worksheet

MODULE 2: PROTECTING FINANCIAL REPORT WITH HIDE/ UNHIDE WORKSHEET PASSWORD & FLEXIBILITY

Understand Macros recorded: The Scripts
Enhancing the Macros - Modify Macros recorded for greater flexibility
Challenge 2: Prepare hide/unhide worksheet Macros with password
Challenge 3: Modify Macros to allow Worksheet Name Change
Challenge 4: Learn to work with VBA code: “select”, “resize”, “offset”

MODULE 3: CREATING USER FRIENDLY MESSAGE BOX FOR FINANCIAL REPORT INTERACTION

Introduction to various VBA control tools
Linking Macros to Buttons
Challenge 5: Create VBA message box to notify user
Challenge 6: Incorporate spin button and scroll bar for sensitivity test for various financial scenarios

MODULE 4: AUTOMATING REPETITIVE TASKS / PROCESSES WHEN PREPARING MONTHLY FINANCIAL REPORTS

Save time on manual copy and paste (reduce formula errors by creating VBA template to consolidate monthly financial data into a summary sheet)
Learn to use VBA code “loop” to enhance and automate repetitive tasks
Challenge 7: Create Excel VBA template to consolidate financial data for reporting

MODULE 5: CREATING LIVE FINANCIAL REPORTING WITH PIVOT THAT REFRESHES AUTOMATICALLY

Create Basic Pivot Table & Pivot chart
Challenge 8: Linking Message Box, Worksheet Protection with Pivot Table/chart that refreshes automatically
Final Tips: How to enhance the Excel VBA to run faster and seamlessly

Pre-requisites

Please take note of the following admission requirements:

Trainings will be conducted on Zoom platform, thus video camera and microphone are compulsory
Mandatory for video camera to be turned on throughout the course
Display your official name (as per NRIC) in Zoom, to facilitate attendance taking

Training Methodology

Lecture
Hands on practices and challenges
Discussions

Closing Date for Registration

1 week before programme or until full enrolment.

Intended For

Financial Controllers
Accountants
Finance Managers
Financial Analysts

Schedule & Fees

Testimonial

Past Participant

Funding

1] NTUC Union Training Assistance Programme (UTAP)

NTUC members enjoy 50% *unfunded course fee support for up to $250 each year when you sign up for courses supported under UTAP. NTUC members aged 40 and above can enjoy higher funding support up to $500 per individual each year, capped at 50% of unfunded course fees, for courses attended between 1 July 2020 to 31 December 2025.

*Unfunded course fee refers to the balance course fee payable after applicable government subsidies. This excludes material fees, registration fees, misc. fees etc.

This course is approved for UTAP support for intakes conducted between 08 February 2024 – 31 March 2025.

As UTAP is given on calendar year basis, and calculated based on year of training taken, it cannot be accumulated.

Maintained paid-up NTUC membership before course, throughout course duration and at the point of claim and;
Course by training provider must be supported under UTAP and training must commence within the supported period and;
Unfunded course fee must not be fully sponsored by company or other types of funding
Unfunded course fee must be S$20.00 and above, and;
Member must achieve a minimum of 75% attendance for each application and sat for all prescribed examination(s), if any and;
UTAP application must be made within 6 months after course ends.

For more information on UTAP Funding and to submit for UTAP claims, please visit https://www.ntuc.org.sg/uportal/programmes/union-training-assistance-programme. Terms and conditions apply.

Programme Facilitator(s)