Workshop on Microsoft Excel Course for Accounting Professionals

Program overview:

Microsoft Excel is one of the most powerful software used for analyzing data to make a better decision. It helps in data organizing, data analyzing, and data interpretation. Utilizing Microsoft Excel for accounting is an easy way to cut costs, save time, and organize accounting information. This course will take participants through intermediate to advanced excel tools and features, they will understand how Excel work. They will learn complex functions, nesting, data manipulation and automation using basic macros in excel. They will further be taken through tutorials on how to use excel for accounting functions. The course is for both intermediate and advanced excel and its dynamic application

In this hands-on course, participants will go through intensive demonstration, hands-on practical exercises to ensure individual overall learning objectives are met.

Note: Participants should bring their laptops along with Excel software installed.

For whom:

This program is designed for inspired individuals from the Accounting field such as the Accountants and the Finance Professionals. Other Non-Financial Professionals who are SME business owners, Entrepreneurs and Individuals that want to add to employability skills that are eager and want to learn these life time skill-sets will also benefit from this course.

Learning objectives:

At the end of the program, participants will be able to:

• Create Accounting template and thereafter applying the connectivity within a Financial Statement (i.e. Balance Sheet, Profit & Loss Statement and the Cash-flow Statement);
• Set Data validation and specify an accounting error alert;
• Know and apply Advance Function which will expedite accounting works when processing raw data;
• Manage Accounting Database such as listing feature toolbar and database functions;
• Consolidate accounting data by category, position and also creating links from Consolidation;
• Create Scenario and audit accounting worksheet;
• Create Macro for the raw accounting data and run it from a toolbar button; and
• Create Pivot Table and customize it.

Course outline:

Day 1 Module 1: Working with Templates focusing on accounting data

• Creating a Template
• Applying a Template
• Editing a Template

Module 2: Data Validation

• Setting Data Validation
• Specify Input Message
• Specify an Error Alert
• Circle Invalid Data
• Clear Validation Circles

Day 2 Module 1: Advanced Functions

• IF Function
• IF (AND) AND IF(OR) Functions
• COUNTIF Function
• VLookup Function
• HLookup Function

Day 2 Module 2: Database

• Sorting
• Data Forms
• Filter Records
• Advanced Filter
• Subtotal
• Outlining a worksheet
• List Feature
• List Feature Toolbar
• Database Functions

Day 3 Module 1: Data Consolidation

• Data Consolidation by Category
• Data Consolidation by Position
• Adding Source Area,
• Change a Reference
• Delete a Reference
• Creating links from a consolidation

Module 2: Scenario Manager

• Creating a Scenario
• Displaying a Scenario
• Editing a Scenario
• Deleting a Scenario
• Merging an Accounting Scenario
• Generating a Scenario Accounting Report

Day 4 Module 1: Audit accounting worksheets

• Tracing cell precedents
• Tracing cell dependents
• Tracing cell errors
• Removing tracer arrows

Module 2: Create Macro for the Accountants 

• Record a Macro
• Run a Macro
• Edit a Macro
• Run a Macro from a Button
• Deleting a Macro

Day 5 Module 1: Pivot Table for the accounting and business data

• Creating a Pivot Table
• Editing the Pivot Table
• Updating a Pivot Table
• Filtering Data by creating page fields
• Creating a Chart from the Pivot Table

Day 5 Module 2: Customize a Pivot Table

• Grouping items with Numeric labels into Ranges
• Work with Dates and Time in Power Pivot
• Grouping items by Date or Time Intervals
• Paging or Filtering a Pivot Table
• Hiding grand-totals, sub-totals
• Displaying subtotals for multiples Fields in Row or Column
• Formatting Numbers

Training Methodology:

Hands-on Lectures, discussions, exercises, and case studies will be used to reinforce these teaching/learning methods.