Nigerian Seminars and Trainings

Search conferences, training, seminars, courses and workshops in Nigeria and around the world

business logo

Advanced Excel Course for Professional Accountants and Managers

By: Gimel Nigeria

Enugu State, Nigeria

21 - 23 Oct, 2016  3 days

Follow Event

  

NGN 50,000

The objective of this class is to educate participants with knowledge of advanced Excel tools using hands-on exercises that are relevant for managers, accounting and finance professionals. The programme will enable the participants to apply these tools in their day to day work to prepare final accounts in IFRS and IPSAS formats, project financials, prepare budget reports and perform financial analysis to increase efficiency and productivity.

Key outcomes

Attending this course will be a valuable opportunity to capitalise on the trainer’s well rounded experience and in-depth knowledge.

By the end of this course, participants will have a greater understanding of:

  • Advanced Excel tools
  • Preparing final accounts using Pivot Tables and Excel Pivot Table Dashboard
  • Projecting financial statements including key schedules
  • Performing financial statement analysis
  • Performing sensitivity and scenario analysis for budgeting
  • Developing Business Applications using MS Excel Macros and VBA
  • Leverage features of Microsoft Excel to facilitate business decisions
  • Develop intelligent worksheets to quickly identify KPIs
  • Using VLOOKUP Function with Multiple Criteria and Creating a Dynamic Heat Map in Excel
  • Perform "what-if" analyses for developing budget and project plans
  • Summarise and analyse large amounts of data using PivotTables and Excel features Automate Excel processes

Who should attend?

Ideally, participants will be intermediate Excel users and have a background in numeric, accounting or finance. However, this course will also benefit individuals with a working knowledge of MS Excel. The primary target of this course includes:

  • Accountants
  • Auditors
  • Bankers
  • Analyst and Managers at all levels,
  • As well as managers who want to learn in order to reinforce their skills and train their staffs.

Topics covered:

Module One: Getting Started with Essential Features

Data Handling

  • Entering and editing data
  • Data types
  • Entering and editing formulas
  • Absolute and relative cell referencing
  • Formula evaluation and editing
  • Working with a Worksheet
  • Moving and copying data across sheets
  • Copy and paste (including paste special)
  • Copying formulas using Auto Fill
  • Formula anchoring

Building Basic Formulas

  • The Math Basics of Excel
  • Building a Formula
  • Editing a Formula
  • Copying a Formula
  • Relative vs. Absolute References
  • Using the Status Bar to Perform Calculations

Using Styles, Themes, and Effects

  • Using Conditional Formatting
  • Using Table Styles
  • Using Cell Styles
  • Formatting Cells
  • An Overview of the Page Layout Tab
  • Changing the Theme

Excel Presentation: SmartArt and Objects

  • Inserting SmartArt
  • Editing the Diagram
  • Adding Pictures
  • Adding Text Boxes
  • Drawing Shapes
  • About the Contextual Tabs

 Module Two: Advanced Data Management Skills

Using Financial Functions: What If Analysis and Auditing

  • Using Goal Seek
  • Using the Scenario manager
  • Using a One Input Data Table
  • Using a Two Input Data Table
  • Using the Auto-fill and Forecasting
  • Tracing Precedent cells
  • Tracing the Dependents of a Cell
  • Displaying Formulas Within the Sheet
  • Adding, Displaying, Editing and Removing Comments

Applying Advanced Chart Features

  • Using Chart Elements
  • Using Chart Styles and Colours
  • Changing the Chart Style
  • Using Chart Filters
  • Working with Data Labels
  • Add a Trend Line
  • Using Secondary Axes
  • Using Chart Templates

Working with PivotTables and Pivot Charts

  • Inserting a PivotTable using Excel Recommendations
  • Choosing Fields and Grouping Data
  • Overview of the Pivot Table Tools Tabs
  • Changing the Data Displayed and Refreshing the PivotTable
  • Creating a Pivot Chart from a Pivot Table or Data
  • Working with PivotTable and PivotChart Tools
  • Creating a Slicer in an Existing PivotTable
  • Creating a Standalone Slicer
  • Formatting a Slicer
  • Sharing Slicers with Another PivotTable
  • Disconnect or Delete a Slicer

Managing Your Data

  • Transposing Data from Rows to Columns
  • Using the Text to Columns Feature
  • Checking for Duplicates
  • Creating Data Validation Rules
  • Consolidating Data
  • Grouping Data
  • Adding Subtotals
  • Outlining Data
  • Viewing Grouped and Outlined Data

Working with Multiple Tables in Data Analysis

  • Using Multiple Tables in Your Data Analysis
  • Connection to Additional Data Sources
  • Creating Relationships between Tables
  • Using a Timeline to Show Data for Different Time Periods

Module Three: Managing Advanced Formulas and Functions

Performing Advanced Formula and Function Tasks

  • Using the Watch Window
  • About the Excel Calculation Process
  • Setting Calculation Options
  • Enabling or Disabling Automatic Workbook Calculations
  • Working with Functions and Basic structure of functions
  • Using the IFERROR Function to Evaluate Formulas
  • SUMIFs, COUNTIFs and SUMPRODUCTs
  • IF and nested IFs
  • Conditional formatting
  • Data validation and creating drop down lists
  • VLOOKUP and HLOOKUP
  • INDEX and MATCH
  • Data table and sensitivity analysis
  • NPV and IRR
  • Time value of money functions

Working with Array Formulas

  • Using Named Ranges
  • Understanding Formula Errors
  • Using the Trace Errors Commands
  • Using Error Checking
  • Evaluating Formulas
  • About Array Formulas
  • Creating One-Dimensional and Two-Dimensional Constants
  • Creating a Simple Array Formulas
  • Creating an Advanced Array Formulas

Working with Macros and VBA

  • Displaying the Developer Tab
  • Recording and Running Macros
  • Changing the Security Level
  • Customizing and Changing the Quick Access Toolbar
  • Assigning a Macro to a Command Button
  • Running a Macro by Clicking an Area of a Graphic Object
  • Configuring a Macro to Run Automatically Upon Opening the Workbook
  • Changing a Macro

Module Four: Advanced Presentation and Data Security

Working with Forms

  • About Excel Forms, Form Controls, and Active X Controls
  • Using a Data Form
  • Using a List Box Control
  • Using a Combo Box Control
  • Using a Spin Button Control
  • Using a Scroll Bar control

Working with Power View

  • About Power View
  • Creating a Power View Sheet
  • Connecting to Different Data Modules in One Workbook
  • Modifying the Data Model without Leaving the Power View Sheet
  • Using Drill Down, Drill Up, and Cross Drill

Working with Permissions and Options, and Sharing Workbooks

  • Marking a Workbook as Final
  • Encrypting with a Password and Restricting Permissions
  • Protecting the Current Sheet or the Workbook Structure
  • Adding a Digital Signature
  • Setting Excel Options
  • Managing Versions
  • Saving a Workbook as an Excel Template
  • Inspecting a Document
  • Sharing a Workbook
  • Editing a Shared Workbook
  • Tracking Changes
  • Merging Copies of a Shared Workbook

Module FiveExcel for Accounting and Finance Professionals

Preparing Final accounts from Trial Balance using Pivot Tables

  • Financial Forecasting
  • Income Statement (Revenue, Expenses, etc.)
  • Balance Sheet (Working Capital, Fixed Asset Schedule, etc.)
  • Cash Flow Statement
  • Amortization Table
  • Sensitivity Analysis

Financial Statement Analysis Tools

  • Ratio Analysis
  • Common sized financial statement analysis
  • Charting using Excel methodology
  • Learning concepts and presentations

NOTE: Registration Deadline: 1 Week to the Event. Participants are to come with their Laptops.

 

 

Crystal Palace Hotel, 2 Mount Street, Opposite Golf Course in Enugu TIME: 9:00am Oct 21 - 23 Oct, 2016
NGN 50,000.00((Course Fee covers Tea, Lunch, Training Bag and Materials on CD and Certificate of Attendance.))
(Convert Currency)

+2349099900655

Discount applies for Group Nominations. Please call to obtain your Coupon Code.
Mr. Maurice A’David, ACIA, - CEO, Gimel Nigeria Limited; Mr. Joseph Agboola, FCA - CEO, Asoft Consulting Limited