Training Course on Advanced Microsoft Excel

Course Description

The course shows how to analyze and review data extracted from accounting systems and databases in order to prepare management reports and build efficient financial models. The course delves into Microsoft Excel’s functions and how to use these effectively. The program also shows how to access the Visual Basic Editor and use macros and user defined functions in order to enhance Excel’s functionality. Participants will also learn how to use Excel’s Pivot Table functionality. Pivot tables are often poorly understood and yet are the most powerful feature in Excel. A basic pivot table will allow you to summarize 50,000 rows of data in 30 seconds

The course is highly practical and delegates will be exposed to case study examples in Microsoft Excel and real research and financial data throughout the training. This Training Course on Advanced Microsoft Excel offered by Phoenix Center for Policy, Research and Training will empower you with the widely sought-after skills necessary to effectively analyze large sets of data. Once the data has been analyzed and prepared for presentation, you will also learn how to present the data using interactive dashboard reports.

Course Objectives

Upon the successful completion of this Training Course on Advanced Microsoft Excel, participants will be able to:

• Understand the principles of data analysis
• Uncover the tools to analyze data and construct reports using Excel
• Understand how to use visualization techniques to improve presentation of information
• Understand how to condense, present and convey data clearly and succinctly
• Enhance the efficiency of executing mundane tasks through recording, writing and editing macros
• Understand the principles of great dashboard design and how to present data vividly
• Perform advanced and dynamic data validations
• Design exceptional visualization charts, dashboards, scorecards, and flash reports
• Build custom reports using advanced form controls and buttons
• Comprehend the principles and practice of data analysis for project M&E and the context in which this operates
• Recognize how to apply big data analytics across various industries

Training Methodology

The course is designed to be highly interactive, challenging and stimulating. It will be an instructor led training and will be delivered using a blended learning approach comprising of presentations, discussions, guided sessions of practical exercise, case study review, web-based tutorials, group work, exploration of relevant issues collaborative strength training, performance measurement, and workshops of participants’ displays, all of which adhere to the highest standards of training. The training technique is built on learning by doing, with lecturers using a learner-centered approach to engage participants and provide tasks that allow them to apply what they’ve learned. Experiential knowledge is also given equal importance within the format of training. Our facilitators are seasoned industry professionals with years of expertise in their chosen fields. All facilitation and course materials will be offered in English.

Who Should Attend?

This Training Course on Advanced Microsoft Excel would be suitable for, but not limited to:

• Business Professionals
• Finance Analysts/Managers/Controllers, Senior and Junior Accountants
• Business Analysts
• Research Analysts
• Marketing and Sales, Administrative Staff, Supervisors,
• Specialists engaged in data analysis and dashboard reporting using Excel
• Any other person who needs to learn and apply state-of-the-art techniques to their daily business reporting, reconciliations, and analysis

Course Content

Module 1: Advanced Excel Shortcuts 

• Extensive exercise on Excel shortcuts
• Doing away with the mouse
• Mastering the shortcut window

Module 2: Data Presentation with Charts 

• Types of charts and their specific uses- Trend line and advanced charts.
• Modifying and formatting existing charts.

Module 3: Data Analysis Report with Pivot Tables & Pivot Charts 

• Analyzing and generating reports of large data using PivotTables.
• Creating PivotTables, editing and updating changes in PivotTable.
• Using Slicers in Pivot Table
• Creating Pivot Chart

Module 4: Sorting and Filtering Data 

• Sort data as per value, colour, icons
• Filter based on Font/Cell colour
• Turning Auto Filter off
• Use subtotal features and work with database functions

Module 5: Understanding References 

• Relative, Absolute & Mixed reference
• Referencing different workbooks & Merging
• Consolidating data, linking cells in different work books
• Utilizing Dynamic Named Ranges for writing formulas and functions

Module 6: Manipulating Dates 

• Dates formats
• Extracting Day, Month or Year from a date value
• Extracting Day, Month or Year from a date value using TEXT Formula
• Extracting end month from a date value

Module 7: Advanced Excel Formulas

• Ifs and Nested Ifs Statements
• AND, OR and NOT Functions
• Error handling using IFERROR, ISNA and ISERROR

Module 8: Text Formulas 

• CONCATENATE
• FIND V/S SEARCH
• LEFT, RIGHT, MID
• UPPER, PROPER, LOWER

Module 9: Lookup Formulas 

• VLOOKUP & HLOOKUP
• INDEX & MATCH
• SUMPRODUCT Function

Module 10: Data Validations and Protection 

• Specifying a valid range of values for a cell
• Specifying a list of valid values for a cell
• Creating Drop down list
• Managing Themes
• Creating and use of templates

Module 11: Financial Analysis and Formulas 

• Using IRR
• Using NPV
• Loan Modelling
• Modelling instalments
• Modelling loan balances
• Making a decision between a flat-rate v/s reducing balance priced loans
• Effective interest rates

Module 12: Budgeting Under Scenarios 

• Introductions to planning scenarios; Best Case, Base Case & Worst-Case Scenarios
• Perform a what-if analysis
• Perform statistical analysis with the Analysis ToolPak
• Create interactive data with Power View.
• Use of Combo Box and CHOOSE function to automate scenario analysis
• Build a scenario-based budget model

Module 13: Excel Macros and VBA for Programming 

• Introduction to Excel Macros
• Recording and Running a Macro
• Absolute vs Relative
• Assigning Buttons to Macros
• Assigning Macros to an Auto Shape and to an icon on a Tool bar
• Sheet Controls and User Form Controls
• Deleting a Macro

Requirements

• Participants should be reasonably proficient in English.
• Applicants must live up to Phoenix Center for Policy, Research and Training admission criteria.

NOTE

1. Discounts: Organizations sponsoring Four Participants will have the 5th attend Free
2. What is catered for by the Course Fees: Fees cater for all requirements for the training – Learning materials, Lunches, Teas, Snacks and Certification. All participants will additionally cater for their travel and accommodation expenses, visa application, insurance, and other personal expenses.
3. Certificate Awarded: Participants are awarded Certificates of Participation at the end of the training.
4. The program content shown here is for guidance purposes only. Our continuous course improvement process may lead to changes in topics and course structure.
5. Approval of Course: Our Programs are NITA Participating organizations can therefore claim reimbursement on fees paid in accordance with NITA Rules.

Booking for Training

Simply send an email to the Training Officer on training@phoenixtrainingcenter.com and we will send you a registration form. We advise you to book early to avoid missing a seat to this training.

Or call us on +254720272325 / +254737566961

Payment Options

We provide 3 payment options, choose one for your convenience, and kindly make payments at least 5 days before the Training start date to reserve your seat:

1. Groups of 5 People and Above – Cheque Payments to: Phoenix Center for Policy, Research and Training Limited should be paid in advance, 5 days to the training.
2. Invoice: We can send a bill directly to you or your company.
3. Deposit directly into Bank Account (Account details provided upon request)

Cancellation Policy

1. Payment for all courses includes a registration fee, which is non-refundable, and equals 15% of the total sum of the course fee.
2. Participants may cancel attendance 14 days or more prior to the training commencement date.
3. No refunds will be made 14 days or less before the training commencement date. However, participants who are unable to attend may opt to attend a similar training course at a later date or send a substitute participant provided the participation criteria have been met.

Tailor Made Courses

This training course can also be customized for your institution upon request for a minimum of 5 participants. You can have it conducted at our Training Centre or at a convenient location. For further inquiries, please contact us on Tel: +254720272325 / +254737566961 or Email training@phoenixtrainingcenter.com  

Accommodation

Accommodation is arranged upon request and at extra cost. For reservations contact the Training Officer on Email: training@phoenixtrainingcenter.com or on Tel: +254720272325 / +254737566961