Excel Case Studies: Sales Analysis with Pivot Tables, Charts
4.0 (195 ratings)
Course Ratings are calculated from individual students’ ratings and a variety of other signals, like age of rating and reliability, to ensure that they reflect course quality fairly and accurately.
1,240 students enrolled

Excel Case Studies: Sales Analysis with Pivot Tables, Charts

Learn by doing! Save time! Real case studies in sales analysis to learn functions IF, VLOOKUP, pivot tables and charts.
4.0 (195 ratings)
Course Ratings are calculated from individual students’ ratings and a variety of other signals, like age of rating and reliability, to ensure that they reflect course quality fairly and accurately.
1,240 students enrolled
Created by Jeff Knowlton
Last updated 5/2017
English [Auto]
Current price: $20.99 Original price: $29.99 Discount: 30% off
5 hours left at this price!
30-Day Money-Back Guarantee
This course includes
  • 3 hours on-demand video
  • 89 downloadable resources
  • Full lifetime access
  • Access on mobile and TV
  • Certificate of Completion
Training 5 or more people?

Get your team access to 4,000+ top Udemy courses anytime, anywhere.

Try Udemy for Business
What you'll learn
  • Starting with a large set of unformatted data, summarize and analyze the data with Excel to find the important "nuggets" of information, and explain the results with clarity.
  • Specifically, prepare a sales and margin report for business, starting with basic invoice information, summarizing results by product line and by region.
  • Make complex pivot tables and display the results visually, with charts and conditional formatting to make a customer scorecard.
  • Use the Excel functions =if, =vlookup, =countif, =sumif, and make nested if statements.
  • Make an Excel data model to connect several Excel tables.
  • Basic familiarity with Excel is assumed. Students should be able to enter data and formulas. The examples are very easy to follow.
  • You'll need a copy of Excel that you can use, preferably Excel 2013 or Excel 2016 for Windows. Excel 2010 will work as well.

Another Excel Course?!? How is this one different? This course is not just a list of Excel functions. Yes, it does teach the critical Excel functions like IF, VLOOKUP, and pivot tables and charts, but it does so through an extended case study example of sales reporting and analysis. So you'll learn WHY these functions are important, and how to use them to answer real business questions. You'll learn them by seeing them used in action to answer questions, like "Who is the top customer?" and "What are the top-selling products?" How do I know? I'm a senior executive, and a hiring manager, at a high-tech manufacturing company.

In this course, you are a business analyst, given the task of analyzing the sales results for a small company, to answer questions about sales and profitability. The lessons show the Excel functions in detail, highlighting each step. I provide the scripts for each lesson, along with the Excel file used, both the starting point and finishing point. I provide everything I can to make this course a success for you!

The course starts with a basic set of raw data. First I show you how to format the raw data, adding product lines, customer regions, and product cost, using IF and VLOOKUP. Then I show you how to summarize the data with advanced pivot table functions, filters, and slicers, to calculate sales and margin by product line by region. Next, I show you how to visually display the results with several different types of charts, and to make a color-coded customer scorecard. Finally, I show you how to write the up the results in a business report.

There is no more practical course on Excel and Business Analytics: this is exactly what business analysts do every day. I learned these Excel techniques over a period of years: you can learn them in a few days. Let's get started!

Who this course is for:
  • The course is designed for people starting their careers, looking for a jump-start. The examples in the case studies are taken directly from a business environment. This is how Excel is used today!
  • The course is designed for college students learning Excel in a business environment. In addition to Excel, you'll learn some business basics, like sales reporting.
  • The course is designed for people who may have been out of the office work environment, and who are looking to return. Excel is critical to success in many careers today.
Course content
Expand all 36 lectures 03:08:12
+ Course Introduction
3 lectures 08:11

I'm a senior executive at a high-tech manufacturing company, and a hiring manager. I've developed this course to teach students about critical analytical skills for business. In today's economy, employees who can take a large set of data, summarize it, determine the important results, and explain the results in writing, with appropriate tables and graphs, have a huge advantage. This course teaches those skills.

Preview 01:41
+ Basic Business Concepts
5 lectures 14:55

This lesson sets the stage for the case study which is at the heart of this course. It also introduces the business topics to be covered in more detail in this section: return credit memos, the calculation of profit margin, and reporting by groups.

Preview 03:28

The case study is about sales reporting. Sales are based on invoices and return credit memos. The first course defined an invoice. This course adds returns, and this lesson defines the important elements of a return credit memo. Remember, this course is about more than Excel: it also provides some definitions of key business terms.

Preview 03:36

Businesses sell products - but they must also buy (or make) those products. The difference between the sales price and the cost of the products is called the simple profit margin, or just margin. This lesson shows the specific calculations used, especially to calculate the margin percentage.

Preview 02:22
Margin Calculation
6 questions

Businesses group their products into product lines, to make it easier to understand trends in sales and profits. And they often group their customers into regions (for example by state, or by country), again, to make it easier to understand trends in sales and profits. This lesson shows conceptually how the grouping can be done, to combine data from multiple sources into one common data model.

Preview 05:29
+ Excel Functions
8 lectures 46:45

It's time to dive into Excel! This lesson provides a preview of the Excel functions to be introduced in this section.

Introduction to Excel Functions

You are the data / business analyst! In this lesson, I provide the Excel file with the raw data to be analyzed, and we get started with answering the questions about sales and profitability for Cowboy Printers. The data must first be formatted, and some simple calculations made for sales revenue.

Review of Formatting

In this lesson, I introduce the "if function" first with some diagrams to explain it conceptually, and then we use it to create two new fields in the spreadsheet, one to show the value of a product ("Free" or "Charge"), and the other to calculate the sales revenue based on the document type ("Invoice" or "Return").

Preview 07:54

Sometimes just one "if" statement isn't enough. The if statements need to be combined, one with another, to calculate the values needed. In this lesson, we'll update the if statements made in the last lesson, nesting one within another. Nested if statements are also very helpful in finding errors in the worksheets.

Nested if Functions

Excel tables convert a simple list of data into a powerful data application, adding features like filtering, sorting, and totaling. And the tables can be "named", so that they are easily referenced in other parts of the worksheet. Tables and Names are advanced features which make spreadsheets easier to use and understand.

Tables and Names

The product line data is in one Excel table, and must be added to the main invoice table. The vlookup function is our tool to make the link, to look up the data in one table and add it to another table. Starting with conceptual diagrams, and working step by step, I show you how to use the vlookup function to add the product line, customer type, customer region, and unit cost from the master data tables into the main invoice table.

The vlookup Function

Vlookup functions can be tricky, especially at first. This lesson shows how to find and correct many common problems with vlookup functions. The troubleshooting hints from this lesson can be applied to many other types of problems and errors encountered with different Excel functions as well.

Troubleshooting vlookup Functions

This lesson provides a quick review of the Excel functions covered in this section. The review will help you "lock in" what you've learned.

Now that the data is formatted, and the additional fields for product line, region, and cost have been added to the main invoice data file, it's time to start the detailed analysis with pivot tables. We'll get started in the next lesson.

Review of Excel Functions
+ Pivot Tables
7 lectures 42:19
Introduction to Pivot Tables
Pivot Table Layouts and Totals
Pivot Table Calculated Formulas
Pivot Table Relationships and Data Models

This lesson reviews the topics presented in this section on new features and functions of pivot tables. The review will help you better remember what you've learned. And there's a quiz after this lesson. Though not mandatory, the quiz will help ensure that you're learning the material, and help you "lock in" what you've learned.

In the next section, we move on to Data Visualization, that is, charts and conditional formatting.

Review of Pivot Tables

This lesson presents some tips and tricks for addressing common errors that occur with pivot tables. It focuses on the use of the "Refresh" function, used after the base table has been updated. It also presents how to manage filters, which can remain inadvertently applied.

Preview 07:52

For this quiz, please use one of the Excel files from the lessons in this section to create a pivot table showing the total margin % by product line by customer type (I recommend the "Answer File" from Lesson 4.4). The questions that follow in the quiz will reference this pivot table. Good luck!

Pivot Tables
3 questions
+ Data Visualization
6 lectures 42:44

The pivot tables list the numbers and results. But numbers can't always tell the full story. Pictures add context, texture and power to the results. This section provides two ways to visualize the data, the standard Excel charts, and conditional formatting.

Preview 06:50

Create a classic pie chart, showing which region is responsible for most of the profits and the causes for the returns. This lesson also provides some alternatives to the classic pie chart which research shows is easier to interpret.

Pie Charts

Graphically display one of the key results: sales by product line by region. This is a classic chart made by most businesses on a regular basis, at least monthly.

3D Column Charts

In this lesson, I show you how to create a dynamic data discovery tool combining slicers with charts. Instantly reveal patterns in the data with just a few clicks!

Advanced Charting Features

Up to now, all of the charts have been based directly on pivot tables. It's easy and powerful. There are some charts which do not lend themselves to pivot table data, and must be made directly. In this lesson, I show you how to make a scatter chart. The chart graphs profitability by sales, identifying which customers show the most opportunity (higher profit, with low sales), which are the best (high profit and high sales), and which are the dogs.

Alternative Methods for Charts, and Scatter Plots
Conditional Formatting
+ Presenting the Results
6 lectures 27:11
Introduction to the Written Report
Page Setup
Print Functions
Some Print Examples
Copying Charts and Tables to Microsoft Word
The Written Report
+ Course Overview
1 lecture 05:07
Course Overview and Additional Practice Session

This quiz is based on the practice file, "Homework Lesson 7.1 Starting Point". Format the data, calculate the sales revenue, margin, and margin percentage, and make pivot tables and charts as described in the script for the lesson. The questions will be based on the pivot tables and charts.

Additional Practice Material - Final Quiz
10 questions