Data Analysis and Modeling

Using Advanced Microsoft Excel Techniques

Lets Take You a Step Further – Bring Your Work In For Advice and Tips

Upcoming Courses

Workshop Outline

• Maths & Statistical Functions.
• Rich analytical features of Excel which include Pivot Tables,
• Pivot Slicers and decision analytics.
• The use of LOGIC, LOOKUP and REFERENCE Functions.
• Advanced Formulas and creating NESTED functions.
• Data validation and creating drop down lists.
• Excel’s Database functions and advanced filtering.
• Date & Time Functions.
• Text Functions.
• Utilising rich functionality in graphing and conditional
  formatting including SPARKLINES & ICON Sets.
• Using Excel’s Consolidation function and subtotals.
• Excel’s What If Analytical & Optimisation tools, GOAL SEEK & SOLVER.
• Automation using MACRO’s.

Who Should Attend

This course is designed for Finance professionals, Analysts, Operations, Sales, Marketing and HR staff from all public and private sector organisations in all industries wishing to gain advanced skills in spreadsheets.

Prerequisite or Equivalent Knowledge

This course assumes a good understanding of spread sheets using Microsoft Excel 2010.It is also assumed that attendees have a general understanding of personal computers and the Windows operating system environment.

Presenter:
Herbert Chitate

Herbert is an Associate member of CIMA (the Chartered Institute of Management Accountants). He is a Registered Public Accountant (RPAcc) and holds an MBA from Leeds Business School (UK). He has exposure and experience in different operational environments and systems, having held various finance posts in the US, UK and Zimbabwe. He comes with a broad range of financial and management accounting, operational and IT skills. Herbert has presented for CIMA CPD events in Data Analysis both in Zimbabwe and Botswana. He consults for a number of companies in the area of performance management and business data analysis.

Detailed Outline

Data Analysis & Modeling Fundamentals

• The Golden rules
• The Outcomes
• The Data

Data validation & Drop Down Lists

• Drop down lists, Validation for numbers, Dates & Text
• Handling Invalid Data, Input & Error Messages

Create and Use Labels and Names in a Workbook

• Labels & Range Names
• Naming Constants & Names Selections
• Managing & Documenting Names

Excel as a Database & Advanced Filtering

• Filters & Advanced Filtering
• Wildcards in Filters
• Database Functions: DSUM, DCOUNT, DVERAGE, DMAX, DMIN

Date & Time Functions

• NETWORKDAYS, DAY, DATE, MONTH, NOW, WORKDAY

Grouping , Outlining, Summaries using Subtotals

• Auto & manual grouping and outlining
• Regular and nested subtotals

Analyse Data with Logical Functions in Excel using:

• IF and Nested IF
• AND, OR, NOT, TRUE, FALSE
• SUMIF(S), COUNTIF(S), AVERAGEIF(S)
• Text Functions associated ISBLANK, ISTEXT, ISNUMBER

Error Trapping

• IFERROR
• ISERROR

Maths & Statistical Functions

Data Consolidation Techniques

Advanced Conditional Formatting

• Data Bars, Colour Sets, Icon Sets
• Sparklines

Handling Multiple Workbooks Simultaneously

Automation with MACROS & Introduction to VBA

Text Functions

• PROPER, TEXT, TRIM, CONCATENATE, LEFT, LEN, MID, RIGHT, LEFT, TEXT to COLUMNS

Lookup and Reference Functions

• CHOOSE, INDEX, MATCH
• ROW(S), COLUMN(S)
• ADDRESS INDIRECT, OFFSET

Use Nested Functions and Scoping Complex Formulae

PivotTables & PivotCharts

• Fundamentals, formatting
• Calculated fields & items
• Pivot Slicers

Audit & Trouble Shoot Excel Spread Sheets

• Formula auditing and managing errors
• Precedents & dependants

What if Analysis & Optimisation

• Goal Seek
• Solver
• Managing scenarios