LEVEL 1: EXCEL FUNDAMENTALS (2 DAYS)

Course Focus: This course focuses on equipping participants with key foundational
principles of understanding Microsoft Excel and its functions, data
management tools, formatting, and presentation tools.

Target Group: People who use Excel lightly and who are not involved in complex
reporting but simple Excel reports.

SECTION A: MICROSOFT EXCEL AND ITS ENVIRONMENT

• EXCEL ENVIRONMENT: Introducing the Excel Environment Including Ribbon, Tabs,

Groups, Contextual Tabs, Quick Access Toolbar, Name Box, Formula Bar, Row & Column
Headers, Cells, Commands, etc.

SECTION B: INTRODUCING EXCEL FORMULAS AND FUNCTIONS & THEIR KEY PRINCIPLES

• Introducing Excel’s rich library of functions including golden function rules.

• Copying formulas down and across workbooks – Relative, Mixed & Absolute referencing.
• DATE & TIME FUNCTIONS: Extracting the YEAR, MONTH, DAY, WEEKDAY, EOMONTH,
EDATE, DATE, and WEEKNUM from dates for further multi-dimensional analysis including
the use of the TODAY() and NOW() functions.
• STATIC NAMING OF CELLS & RANGES: The backbone of model development: Naming
Cells and ranges statically and the application thereof.
• TEXT FUNCTIONS: LOWER, UPPER, PROPER, TEXT, MID, LEFT, RIGHT, VALUE & the TRIM
function and the application thereof.
• ERRORS: Understanding the most common ERRORS in Excel including CIRCULAR
REFERENCES & use of the IFERROR FUNCTION
• Introducing some useful Mathematical & Trigonometric functions: ABS & ROUND,
SUBTOTAL.
• FORMULA AUDITING: Tracing formula dependents and precedencies & error checking.
SECTION C: EXCEL’S FORMATTING AND PRESENTATION TOOLS

• CONDITIONAL FORMATTING: A look at various CONDITIONAL FORMATTING RULES,

including using conditional formatting to identify duplicates.
• TABLES: Converting a range to a TABLE and the benefi ts thereof.
• EMBEDDING & LINKING: COLLABORATION OF EXCEL and MICROSOFT WORD &
POWERPOINT for efficient report creation.
• GROUPING & UNGROUPING: Grouping & Ungrouping tools for data summarization & drill
down.
• Simple Formatting Techniques: Merge Cells, Wrap Text, text alignment, format painter,
headers & footers, print areas, styles & themes.
• CHARTS & CHARTS FORMATTING. How to create appropriate and various kinds of
CHARTS & SPARKLINES and to give them a professional look.

SECTION D: EXCEL’S DATA MANAGEMENT TOOLS

• SUBTOTALLING: Using Excel’s Subtotal data management tool to insert subtotals and
outline data.
• Converting both Fixed Width and Delimited Text to columns using the TEXT TO COLUMNS
tool.
• SORTING DATA: How to sort data by multiple levels including sorting by cell color, font
color, and cell icons.
• REMOVING DUPLICATES: Using the remove duplicates data management tool to
eliminate duplicates or unique values from a data range.
• SIMPLE DATA FILTERING: Filtering data using the data fi ltering data management tool.
• ADVANCED FILTERING: Filtering data in place and copying to another location using
advanced fi ltering techniques.

SECTION E: EFFICIENCY TOOLS IN MICROSOFT EXCEL

• Simple Efficiency Techniques: Custom Lists, Autofi ll, Freeze panes, Paste Special
including Transposing, and Inserting Comments including splitting and arranging
worksheets and workbooks.
• SHORTCUTS: Using Excel’s shortcuts and efficiently execute tasks.
 LEVEL 2: INTEMEDIATE EXCEL -
RECONCILIATIONS, DATA ANALYSIS &
REPORTING (3 DAYS)

This course focuses on how to reconcile and analyze data using Excel

reconciliation model and a data analysis model and present their

preparing reports including graphical analysis.

SECTION A: REVISION OF SOME OF THE KEY LEVEL 1 CONCEPTS

▪ A recap of the Excel Environment Including Ribbon, Tabs, Groups, Contextual Tabs, Quick
Access Toolbar, Name Box, Formula Bar, Row & Column Headers, Cells, Commands, etc.
▪ A recap of Excel’s rich library of functions including golden function rules.
▪ A recap of copying formulas down and across workbooks – Relative, Mixed & Absolute
referencing.
▪ A recap of DATE & TIME FUNCTIONS: Extracting the YEAR, MONTH, DAY, WEEKDAY,
EOMONTH, EDATE, DATE, and WEEKNUM from dates for further multi-dimensional
analysis including the use of the TODAY() and NOW() functions.
▪ A recap of STATIC NAMING OF CELLS & RANGES: The backbone of model development:
Naming Cells and ranges statically and the application thereof.
▪ TEXT FUNCTIONS: CONCATENATE, LOWER, UPPER, PROPER, TEXT, MID, LEFT, RIGHT,
VALUE, TRIM & the SUBSTITUTE function and the application thereof.
▪ Understanding the most common ERRORS in Excel including CIRCULAR REFERENCES &
use of the IFERROR FUNCTION
SECTION B: LINKING AND CONSOLIDATING WORKBOOKS & WORKSHEETS

▪ LINKING WORKBOOKS: Applying the naming of cells and ranges in the linking of multiple
spreadsheets according to best practice.
▪ CONSOLIDATION: Consolidating multiple fi les using SUMMING THROUGH SHEETS,
CONSOLIDATION BY CATEGORY and CONSOLIDATION BY POSITION

SECTION C: INTRODUCING FINANCIAL MODELLING & DATA ANALYSIS

▪ Introducing the concept of financial modeling and Best Practice Modelling Standards.
▪ Introducing Data Analysis – The concept, the Data fields, the analysis dimensions, and
the analysis methods.

SECTION D: RECONCILING TWO SETS OF DATA, AUDIT & IDENTIFY DIFFERENCES WITH
SPEED AND PRECISION

▪ VLOOKUP & HLOOKUP: Introducing the VLOOKUP & HLOOKUP functions, their uses, and
weaknesses, including the use of the IFNA function.
▪ THE XLOOKUP FUNCTION: Introducing the ultimate LOOKUP function – the XLOOKUP and
how to use it in place of the VLOOKUP & HLOOKUP functions. How to use its FUZZY

LOOKUP functionality to audit spreadsheets.

▪ DEVELOPMENT OF A RECONCILIATION MODEL. How to build a reconciliation model to
reconcile any two data sets with references, including how to create references if
necessary.

SECTION E: THE SIX (6) KEY STEPS TO ANALYZING DATA AND GENERATING DYNAMIC
AND PROFESSIONAL REPORTS USING MICROSOFT EXCEL FORMULAS AND DATA
MANAGEMENT TOOLS.

▪ LOGICAL FUNCTIONS: Introducing the dynamic logical functions – the IF function and the
NESTED IF function.
▪ DATA ANALYSIS PREPARATION STEPS: Preparing Data for Analysis by naming columns
and creating calculated fields as well as designing a report template.
 ▪ MATHEMATICAL & TRIGONOMETRIC FUNCTIONS: Introducing some useful Mathematical
& Trigonometric functions: ABS & ROUND, SUBTOTAL, SUM & SUMIF.
▪ STATISTICAL FUNCTIONS: Extracting useful information from data using MATHS &
STATISTICAL FUNCTIONS: COUNT, COUNTA, COUNTIF, COUNTIFS, & AVERAGE,
AVERAGEIF, and AVERAGEIFS.
▪ CHARTS & CHARTS FORMATTING. How to create appropriate and various kinds of
CHARTS & SPARKLINES and to give them a professional look.
▪ DYNAMIC NAMING OF CELLS & RANGES and the application thereof in dynamic modeling.
▪ PIVOT TABLES: Multi-dimensional data analysis using PIVOT TABLES & PIVOT CHARTS
including the use of SLICERS and TIMELINES in navigation and designing a DASHBOARD.

SECTION F: FINALISING AND AUTOMATING THE MODEL ACCORDING TO BEST PRACTICE

▪ HYPERLINKS: How to insert hyperlinks from Cell to Cell, Sheet to sheet, Workbook to
Workbook, and File to File.
▪ UNDERSTANDING MACROS: How to record and edit Macros including adding a
COMMAND BUTTON.
▪ DATA VALIDATION: Validating model inputs using the predefined DATA VALIDATION
tools.
▪ PROTECTION: Protecting model cells, sheets, and workbooks.
 LEVEL 3: ADVANCED EXCEL - BUDGETING AND
FORECASTING (1.5 days)

This course focuses on advanced Excel functions, graphs, and the

Power Pivot; the key techniques of preparing a budgeting model and
how to link the income statement, cash flow statement, and balance
sheet including performing what-if analysis, goal seeking, and
optimization.

This course is for advanced users who are already familiar with
levels 1 and 2 and who are involved in budgeting, forecasting, and what-
if analysis, and those who work with huge volumes of data.

SECTION A: ADVANCED EXCEL FUNCTIONS

▪ FURTHER LOGICAL FUNCTIONS: Further variations of the IF, including nested if, AND, OR,
and their use.
▪ INFORMATION FUNCTIONS such as the ISTEXT, ISODD, ISEVEN, ISLOGICAL, ISNONTEXT,
ISNUMBER, and ISBLANK functions and their use with logical functions.
▪ The DATEDIF function for calculation time.
▪ FURTHER LOOKUP FUNCTIONS: The GETPIVOTDATA & the OFFSET function for lagging.
▪ FINANCIAL FUNCTIONS: Including the SLN, DB, NPV, IRR, IPMT, PMT, PPMT, and the
preparation of a loan amortization table.

SECTION B: BUILDING A BUDGETING AND FORECASTING MODEL

▪ Development of a Budgeting & Forecasting Model.

 ▪ ADVANCED DATA ANALYSIS & FORECASTING FUNCTIONS: Analyzing the correlation
bet ween t wo data sets including the TREND, SLOPE, FORECAST, CORREL, GROWTH, and
FREQUENCY functions including preparing a forecast sheet.
▪ Validating model inputs using custom information functions.

SECTION C: WHAT-IF ANALYSIS, OPTIMIZATION, AND GOAL SEEKING

▪ CONTROL BUTTONS: The use of Control buttons in a model, including OPTION BUTTONS,
COMMAND BUTTONS, CHECKBOX, COMBO BOX, SLIDER (SCROLL BAR).
▪ WHAT-IF AN AL Y SIS : What-if Analysis, Goal Seeking, and Optimization using
DATA TABLES, the GOAL SEEK feature, and the SOLVER.
▪ The COVARIANCE, the HISTOGRAM, and MOVING AVERAGES.
▪ ADVANCED CHARTS: The Combination Chart, Gantt Chart & Waterfall Charts.

SECTION D: THE EXCEL’S POWER PIVOT

How to use Excel’s POWER PIVOT to get External Data, Create Relationships and
Analyze huge amounts of data.