i3BAR

Duration

1 day

Module 1: Proficiency in Core Excel Functions

a) Referencing tools

• Reduce formula recalculation time
• Enter formulas quickly by shortening sheet names
• Select & protect cells containing formulas
• Use auditing tools to check for errors s
• Check data by using trace precedents

b) Text functions

• Change case of text
• Join text from multiple columns
• Combine text and formatted numbers
• Convert imported text format into numbers
• Break imported date field into individual columns
• Extract a name field into three columns

c) Lookup reference

• Use VLOOKUP to find specific data
• Use HLOOKUP to find values in rows
• Use INDEX & MATCH to retrieve data

d) Logical functions

• Use IF function to prevent division by zero
• Use ISERROR function to avoid error display
• Create multiple conditions using nested IF

e) Form controls

• Using Forms Controls
• Create Combo Box Control
• Use Spinner Control
• Create Scroll Bar Control
• Create Option Buttons Control

Module 2: Integrate Multiple Data Sources

a) Understand the key components of an integrated model

• Downloading and Installing PowerPivot

b) Input, logic and presentation blocks

• Importing Access Data
• Importing Excel Data

c) Construct and design a database

• Integrating Data with Relationships
• Creating a PivotTable with PowerPivot Data

d) Database design rules

Module 3: Build and Present Interactive Reports

a) Understand bad practices that lead to static reports

b) Discover the secrets of interactive reports illustrated in three case studies

c) Understand the principles of developing interactive charts that change at the click of a button

• Create a PivotChart Report
• Publish PivotTable Reports to the Web

d) Incorporate interactive commentaries that auto-reflect changes made in data

• Creating a PivotChart Based on External Data
• Refreshing External Data
• Creating a Slicer

e) Turn the integrated, interactive model into an easy to-navigate Board presentation

Module 4: Budget Simulation and Intelligence

a) Common reasons for delay in budget completion

b) Design of input templates incorporating data validation and protection

• Reduce data entry errors by using data validation
• Select & protect cells containing formulas

c) Modelling business process to develop intelligent planning models for

• - Profit & Loss, Cash Flow and Balance Sheet
• - Capital Expenditure Approval Model

d) Common reasons for difficulties in creating an Excel model for 'what-if' analysis

e) Develop a 'what-if' simulation model to aid good decision making

• Using Goal Seek
• Using Data Tables
• Using Solver

Programme scope, delivery arrangements and suitability are confirmed during enquiry.