Dashboard Reporting in Excel

Course overview

When you think of data, do you think of endless rows and columns in spreadsheets? This interactive and practical learning course shows you how to make the most of your data by exploring new ways to conceptualize and present key information and produce reports that are impressive and easy to understand. Learn how to:

- Develop interactive data dashboards

- Creative stunning visuals to represent trends

- Define KPIs and measure business performance against goals

This course is designed for accountants & auditors, finance professionals and business & financial analysts. Suggested audience titles include accounts manager, finance manager, finance director, CEO, CFO, audit manager, MIS manager etc.

Pre-requisites

- Microsoft Excel basic working knowledge

- Business acumen

Take away

- Organize and cleanse data for reporting

- Prevent invalid data entry and control data input

- Use slicers and filters to control dashboards

- Design visual dashboards with charts

- Learn advanced chart types, pivot charts, scatter graph & combo charts

- Use visual formatting features like conditional formatting, spark lines, traffic lights and conditional formatting

Duration

2 days

Course outline

Day 1

Advanced Pivot tables

Pivot table basics

Calculating cumulative % and running totals

Conditional formatting in pivot tables

Linking multiple pivot tables with slicers

Performing yearly, quarterly and monthly analysis

Understanding Dashboards

What is a Dashboard

Sample dashboards

Design concepts & best practices

Data validation, named ranges & tables

Named ranges in Excel

Excel range vs tables

Finding invalid data & formula errors

Understanding data validation

Preventing incorrect data entry

Custom error message for data entry

Using drop-downs

Using formulas in tables

Using names in formula to simplify report building

Conditional formatting, spark lines & shapes

Understanding conditional formatting

Data bars, color scales & Icon sets

Identifying outliers, top/bottom values

Using Spark lines & columns

Using formulas in conditional formatting

Day 2

Advanced charts

Chart basics

Combo charts

Customizing charts

Dynamically update chart range

Scatter graph charts

Using Pivot charts

Advanced Pivot tables

Breaking apart pivot table for customized dashboard

Custom calculation in Pivot tables

Generating multiple sheet reports from Pivot tables

Pareto analysis with pivot tables - top N values

Retrieving values from pivot table

Advanced Excel formulas

Logical functions (IF, AND, OR, NOT)

Lookup function II (GETPIVOTDATA, OFFSET)

Lookup functions (VLOOKUP, HLOOKUP, INDEX, MATCH)

Text functions (LEFT, RIGHT, MID, LEN, CONCATENATE, FIND)

Introduction to array formulas

Using form controls

Combo box & check box

Spin button & option button

Using form controls to control dashboard

Training and Development: Training Needs Analysis

A targeted Training Needs Analysis is effective is started with proper planning and first steps are always crucial.

Training needs assessment is all about: -

• Plan your planning for TNA (Training Needs Assessment/ Training Needs Analysis)
• Be decisive on training needs of your staff members
• Keep in mind the organization goals
• Gather data for the team members
• Conduct a survey to gather information and individual needs and prepare action sheet

How an effective but standard Training Needs Analysis should be like?

Following are the five steps of Training Needs Analysis Process: -

TRAINING REQUEST

Training request either can be initiated from the staff members or it can also be assessed by the HR/ Training and Development Manager. The first thing needed is the gathering of basic information, this information is mainly pointing out the audience, their career background and current skill set.

The next thing to decide whether the training can be arranged/ developed internally or there will be a need of external vendor.

TNA (Training Needs analysis) PLANNING

In planning phase you will be required to gather content and organize it according to the needs of the organization. You’ll also need a plan for refining your instructional goal to make sure it aligns with business objectives. Have all the pieces of information on one paper like the training vendor (is it in-house or public), how to find training vendor and how to conduct the training of the staff.

DATA GATHERING

Data collection is vital its where the Training Manager/ Learning and Development Manager collect data and refines the plan based upon the data. Data can be collected by various sources like interviews, group discussion, performance analysis, surveys.

DATA ANALYSIS

Once the essential data is in one place now is the time to start analysis and bring things in order. In this process the training goals may change in presence of data analysis.

At this level you should know about When, Where and How? Will it be done internally or there might been need from outside vendor.

FINAL TOUCH to PLAN NEXT STEPS

A detailed report will do the trick on your way to final step in the Training Needs Analysis serving as the road map for your training solution and needs. The contents of this detailed report will mainly be:

• Organization development goal
• Profile of the target audience
• Learning objectives
• Summary of course outline, case studies, methodologies
• With all five steps of the Needs Analysis process completed, you should be well on your way to developing an effective learning solution.

DOWNLOAD TRAINING NEEDS ANALYSIS WORKSHEET

To ease the pain of training needs assessment I have created a simple worksheet to help you Kickstart your Training Needs Analysis.

Use this template and make a sound plan for identifying the right training solution for you and your team members.