Building & Automating Interactive Business Reports with Excel

Introduction

Organizations collect extensive data during normal operations, including purchase orders, paychecks, invoices, and operations reports. This granular data is often consolidated into databases and used for business analysis and reporting. Managers need this data summarized for effective decision-making, which typically requires significant time and resources.

However, there is a solution that:

• Designs reports to meet the needs of multiple managers simultaneously.

• Generates reports directly from the data without needing summary tables.

• Allows the same report to be updated with one button for the next reporting cycle.

All of this can be achieved using MS Excel.

About the workshop

This 1-day workshop is designed to provide a broad overview of the analytics value chain; specifically, how to build interactive reports directly from databases and how it helps organizations make data driven decisions. Delegates will get the opportunity to get their hands “dirty” with a sizable dataset and learn join disparate tables, find and clean data errors, calculate new data fields. Delegates also learn how to build and maintain interactive engineering ops report built from the processed data.
 

Learning outcomes

• Understanding of the analytics process, and the role of that Data Management plays in the value chain

• Develop technical skills in data management, including merging data across multiple tables, managing data errors & building new calculated data fields to support the analytics process

• Learn how to how to turn the processed data tables into interactive dashboard reports; as well as to automatically update them with new data

Target Audience

• Analysts or Professionals with data management and/or reporting responsibilities

• Analysts or Professionals looking to pick up new tips and tricks to enhance their skills in data management and reporting

• All other professionals with a keen interest in developing strong foundations in data analytics

Logistics Requirements

Participants are required to bring their own laptops with MS Excel and Powerpoint (version 2013 or later) to participate in the data exercises

 

Course Outline

What is Analytics and What it Takes to Build Sustainable Capabilities

• This segment seeks to define what analytics is and isn’t, why it matters in today’s world, and what it takes to build sustainable value.

The Analytics Value Chain and the Role of Data Management and Reporting

Overview:

• An introduction to the processes and capabilities required to deliver value through analytics.

• Understanding the role that Data Management and Reporting play in the process.

Key Points:

• Data Management: Refers to the process of extracting, curating, transforming, and storing the data before analytics even starts.

• Importance: Highlights the consequences of poor data management.

Data Management: Preparation and Transformation

Objective:

• To build skills in the precursor to data analytics: data preparation.

• A hands-on session with multiple computer-aided exercises on various data problems.

Activities:

• Merging Multiple Data Tables

• Finding and Addressing a Range of Data Issues

• Understanding System vs. Calculated Data Fields

• Using Key Functions and Tools within MS Excel

Interacting with Data

Objective:

• To teach students how to build meaningful data summaries using Pivot Tables.

Topics:

• The Thought Process to Finding Answers with Pivot Tables

• Sorting, Re-ordering, Summarizing, and Visualizing Data

• Building Calculated Items and Fields

Building Interactive Reports

Overview:

• Regularly used data summaries can be automated using Pivot Tables.

• Enhanced with interactivity to allow different users to see summaries of different populations in the data.

Activities:

• Designing and Building Interactive Dashboards and Infographics with MS Excel

  • Principles of Data Visualization

  • Design and Build Interactive Data Visualizations

Demos:

• Data Visualization Tools: Tableau, PowerBI

Class Exercise:

• Building Interactive Dashboard Reports with MS Excel

Course Fees (Incl GST)

SCAL member: $385.00

Non-member: $435.00

Trainer Profiles

Derrick Yuen, MBA, BEng

Founder and Principal Consultant

Derrick consults and leads engagements to help clients liberate their data for analytics, to glean relevant and actionable insights from their data; engagements span across many sectors, functions and regions including Pharmaceutical, FMCG, Government, Human Resources, Finance & F&B across the Asia Pacific region

Prior to establishing FYT, Derrick led the formation and establishment of the Global People Analytics Centre of Excellence (COE) for a multinational healthcare company with >US$30 Billion revenue. He is also an experienced management and analytics consultant with top-tier firms, multinational corporation and government agencies, spanning various functions, sectors and geographies

He is also an ACLP certified Associate Trainer with the Civil Service College, Aventis School of Management and BCA Academy, specifically focused in developing and delivering analytics related curriculum

Derrick holds a Bachelor’s degree in Civil Engineering from the National University of Singapore as well as an MBA from the University of Southern California.

Michael Lee, MBA, BSc

Principal Consultant

An IT professional by training with more than 25 years of consulting & practical experience across many functions (sales, client management, business operations & customer service) throughout various roles (Vendor, Systems Integrator, Distributor and End-user) in the public & private sectors

Prior to joining FYT, Michael managed the Asia Pacific Sales Operations for Linksys; his portfolio included the day-to-day operations within Asia Pacific. He was also running the China sales. Prior to that, Michael led the Asia Pacific Sales Operations for Cisco System and the Business Operations for Customer Service in Asia Pacific & Japan in Cisco. In addition, he headed a global Metrics & Business Intelligence team comprising members from Asia Pacific, Europe and the Americas.

Michael holds a bachelor’s degree in Computer Science & Mathematics as well as an MBA from the National University of Singapore. Michael is also an ACTA certified trainer

Jeremy Poon, MBA

Principal Consultant

Jeremy has more than 30 years of commercial and professional experience, which include leadership roles across many sectors and geographies, such as telecommunication, hospitality MICE and government across Asia Pacific.

Prior to joining FYT, he led the application of the MVNO license with IDA for a large multinational company and worked with command, control & communication solutions.

Jeremy consults and leads engagements to help clients build analytics capabilities in their organizations. He brings his broad & extensive experience and makes the topic come to life with realistic examples and practical applications.

Jeremy has a Bachelor’s degree in Electrical &Electronics from the National University of Singapore as well as a Certified Associated from the Institute for Human Resource Professionals.