Dashboards and Business Intelligence Analysis in Excel

Course Content
Charting in Excel
Charting case studies: Over the course of the charting session the attendees will be provided with varies forms of raw data that will need to be “prepped” before it can be charted.
 The anatomy of a chart
 Basic chart design principles
o Avoid 3D
o Avoid data heavy charts
o Conservative use of gridlines
 Creating a chart
 Switching row and column orientation
 Changing the chart type
 Chart styles
 Formatting chart elements
 Adding a new series
o Extending the highlight
o Copy paste to chart
o Paste special to chart
 Dealing with numeric category labels
 Charting a non-contiguous range
 Handling missing data
 Axis considerations
o Value vs. category (numerical scale)
o Key for bubble and scatter charts
o Automatic value axis scaling (min/max)
o Axis scale comparability and interpretation
o Date scales (vs. standard category)
 Multiple axis charts
Multiple axis chart case study: The attendees will be a multiple axis chart from scratch using raw “un-prepped” data.
 Multi-line horizontal axis disclosure
o Embedding chart data in axis
o Embedding visual symbols
 Data labels and tables in charts
 Data filtering with charts

 Dynamic chart titles and labels
 Working with the legend
 Chart annotations
 Trending
o Implied labelling
o Comparative trending techniques
o Trending options in charts
 Font formatting the entire chart
 Rotation of charts with the camera
 Specialist charts:
o Combo charts
o Waterfalls / bridges
o Bubble charts
 Dynamic chart data (INDEX)
 Final presentation - converting charts into pictures
 Additional 2013 skills
o Filters
o Formatting
o Chart elements
Case study: Attendees will be presented with raw data and ask to produce waterfall and bubble charts.
Data mining in Excel
 Data mining large data volumes
 Pdf data extraction
 Working with text functions in excel
o RIGHT and LEFT
o FIND
o SEARCH
o MID
o LEN
 Data extraction formulae in Excel
o SUMIF
o COUNT(A)
o SUMIF and COUNTI “Wildcards”
 The LOOKUP school of functions
o VLOOKUP
o HLOOKUP
o CHOOSE
o INDEX
o INDEX (MATCH)

Case study: Attendees will be presented with a variety of raw data
Building sensitivity analysis for presentation
 Data (sensitivity) tables
o Data tables are very useful in testing sensitivity of various inputs to a model. These will be used in the subsequent DCF model building and examples will be introduced here.
o 1-dimensional data tables
o 2-dimensional data tables
o Efficient techniques for updating data tables as inputs change
o Data table diagnostics
o Building self-centering data tables
 Design issues
 Avoiding data tables circularity
Case study: Attendees will be presented with a variety of raw data, build a small calculation model with sensitive assumptions. These assumptions will be flexed into to present a sensitivity matrix that will self-centering automatically.
 Building a scenario manager and switch
o Adding flexibility so the model can be run under different sales growth/EBITDA margin scenarios – i.e. base, upside, downside, management etc
o Using
 CHOOSE,
 INDEX,
 SUMIF
 VLOOKUP,
 HLOOKUP,
 OFFSET and MATCH functions
o Drop down menus/Visual basic tools to enable the efficient switching between different scenarios will be introduced
Case study: Attendees will be presented with a variety of raw data for a multi-level scenario analysis. They will then build a scenario manager that drives a flexible case driven chart.
Building data extractors
 Find unique references to define lists:
o OFFSET
o INDEX

Creating dashboards with pivot tables
 When to use Pivot tables
o Kaleidoscope view on data
 Pivot table data preparation
o Tabular / Columnar format
o Single data headings (Alt+enter)
o Avoid heading repetition
o Eliminate gaps in reported data
o Format the source data
o Redefining normal
 Good data design:
o 1st row headings
o Each column – unique data category
o Each row – items within categories
o No columns double as data items
Data set preparation case study: The analysts are provided with a data set that is unsuitable for pivot table preparation. They are required to reformat and reorganize the data, so that the data set is fit for purpose. This case study reinforces the need to work fast with keyboard shortcut and the formatting skills touched on earlier on in the programme. Without these skills, this absolutely necessary reformatting exercise can be inefficient.
Pivot table case studies: The pivot table module will include a number of case studies that will develop and sequentially expand the pivot table skills. Pivot tables require practice and these case studies will provide practice and development of these skills
 Building a basic pivot table
o Cell selection
o Pivot table positioning
o Using the field list
o Adding fields to the report
 Filters
 Columns
 Rows
 Values
o What am I measuring? $USD sales
o How do I want to see it? By Region
o (Where to place the fields)
o Adding layers to the pivot table
o Rearranging the pivot table
o Creating a report filter
o Renaming your pivot table
o Moving the pivot table Defer layout update (large data sets)
o Pivot table clear all

o Data refresh
o Data source expansion (Change data source)
 Quick analysis options
 Right click options
o Field settings
o Formatting
o Pivot table options
o Recommended pivot tables feature
Creating dashboards with pivot tables
 Using slicers
o Introduced in Excel 2010
o User friendly filtering tool
o Creating a slicer
o Selections
o Multiple selections (Control)
o Connect to multiple pivot tables
o Report Connections
o Creating a timeline slicers (Excel 2013)
o Expanding the range
o Period drop downs
 Share the Pivot cache
o File size and memory management
o Alt D + P – Pivot Wizard
o Force a separate pivot cache (No)
 Customizing a pivot table (Chapter 3)
 Creating rankings
 Creating frequency analysis
 Filters
 Pivot charts
Capture all case study
The analysts are provided with a data set that is unsuitable for pivot table preparation. The analysts are required to:
 Prep the data
 Report data preparation
 Pivot table build
 Ranking analysis
 Frequency analysis

 Slicers
 Timelines
 Standard charts
 Dynamic labels
 Pivot charts

Course duration: 2 days for 5hours