Course Detail(IT083v : Enhancing Financial Reporting and Analysis with VBA made easy for Accountants (Live Webinar))
Not Available

UTAP Funding

IT083v : Enhancing Financial Reporting and Analysis with VBA made easy for Accountants (Live Webinar)

7.00 CPE Hours (Category 5)
Live Webinar

 

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

Competency Mapping

Category 5 = 7.00 Hours

Schedule & Fees
Date & Time

28 Oct 2024 (9:00 AM - 5:00 PM)

Fee (inclusive of GST)

For Members: $ 381.50
For Non-Members: $ 458.89

Programme Facilitator(s)

Terry Wong

Venue

Live Webinar in or outside of
Singapore

Testimonial


This course has provided me with meaningful insights and knowledge that is applicable to my work. Thank you
Terry!

Past Participant


improves work efficiency by reducing repetitive works (through Macro) for monthly reports.

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)


Terry Wong
 
Mr. Terry Wong (ACCA) is an experienced trainer and heavy user of MS Excel. He started his career in IBM’s Shared Service Center as an Accounting Analyst where he is required to be involved in many process improvement projects and initiatives, including leveraging on creative Excel to build templates, streamline processes and automate reports and tools. Subsequently, he became an Accounting System Analyst in IBM where his role is involved in system technical support.  This gave him a platform to provide numerous in-house training, to more than 300 accountants across finance and accounting department. His training received great feedback and was invited to provide training to non-accounting department and also to offices outside the region such as emerging market in Far East countries.

Being the technical support that understand both the technical knowledge and with the proper finance professional qualification, Terry was able to quickly understand and help his audience to brainstorm, provide smart and practical insights in dealing and solving many real-life work challenges using MS Excel.  

His strong analytical skill, capability to quickly research coupled with detail process driven nature makes him a competent trainer in helping participants to come out with creative ideas in solving work problems. He is frequently engaged to conduct small group coaching in guiding internal advanced MS Excel users to further streamline and enhance the reports and work processes through MS Excel.  

 

 

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

Competency Mapping

Category 5 = 7.00 Hours

Programme Facilitator(s)

Terry Wong
 
Mr. Terry Wong (ACCA) is an experienced trainer and heavy user of MS Excel. He started his career in IBM’s Shared Service Center as an Accounting Analyst where he is required to be involved in many process improvement projects and initiatives, including leveraging on creative Excel to build templates, streamline processes and automate reports and tools. Subsequently, he became an Accounting System Analyst in IBM where his role is involved in system technical support.  This gave him a platform to provide numerous in-house training, to more than 300 accountants across finance and accounting department. His training received great feedback and was invited to provide training to non-accounting department and also to offices outside the region such as emerging market in Far East countries.

Being the technical support that understand both the technical knowledge and with the proper finance professional qualification, Terry was able to quickly understand and help his audience to brainstorm, provide smart and practical insights in dealing and solving many real-life work challenges using MS Excel.  

His strong analytical skill, capability to quickly research coupled with detail process driven nature makes him a competent trainer in helping participants to come out with creative ideas in solving work problems. He is frequently engaged to conduct small group coaching in guiding internal advanced MS Excel users to further streamline and enhance the reports and work processes through MS Excel.  

 

Upcoming Schedule

Date & Time

28 Oct 2024 (9:00 AM - 5:00 PM)
Fee (inclusive of GST)

For Members: $ 381.50
For Non-Members: $ 458.89

Programme Facilitator(s)

Terry Wong

Venue

Live Webinar in or outside of
Singapore


Other courses you might like

View Courses 
14.00
CPE Hours
Classroom

Excel Budgeting Techniques

19 Apr 2024 - 22 Apr 2024

  • SkillsFuture Credit
  • UTAP
14.00
CPE Hours
Classroom

Creating Professional PivotTable Driven Reports (Intermediate Level)

23 Apr 2024 - 24 Apr 2024

  • SkillsFuture Credit
  • UTAP
7.00
CPE Hours
Live Webinar

RPA Implementation Strategy for Accounting Firms (Live Webinar)

30 Apr 2024 - 30 Apr 2024

  • UTAP