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.8 (18 ratings) Instead of using a simple lifetime average, Udemy calculates a
course's star rating by considering a number of different factors
such as the number of ratings, the age of ratings, and the
likelihood of fraudulent ratings.
731 students enrolled
$30
Take This Course
  • Lectures 36
  • Contents Video: 3 hours
    Other: 1 min
  • Skill Level All Levels
  • Languages English
  • Includes Lifetime access
    30 day money back guarantee!
    Available on iOS and Android
    Certificate of Completion
Wishlisted Wishlist

How taking a course works

Discover

Find online courses made by experts from around the world.

Learn

Take your courses with you and learn anywhere, anytime.

Master

Learn and practice real-world skills and achieve your goals.

About This Course

Published 10/2014 English

Course Description

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!

What are the requirements?

  • 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 for Windows. Excel 2010 will work as well.

What am I going to get from this course?

  • 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.

What is the target audience?

  • 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.

What you get with this course?

Not for you? No problem.
30 day money back guarantee.

Forever yours.
Lifetime access.

Learn on the go.
Desktop, iOS and Android.

Get rewarded.
Certificate of completion.

Curriculum

Section 1: Course Introduction
Course Introduction
Preview
03:01
01:41

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.

Course Map and Pre-Requisites
Preview
03:29
Section 2: Basic Business Concepts
03:28

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.

03:36

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.

02:22

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.

Margin Calculation
6 questions
05:29

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.

Downloading Practice Files, Homework, and Scripts
Preview
1 page
Section 3: Excel Functions
02:11

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

05:38

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.

07:54

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").

05:49

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.

03:59

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.

08:45

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.

09:40

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.

02:49

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.

Section 4: Pivot Tables
Introduction to Pivot Tables
04:37
Pivot Table Layouts and Totals
06:29
Filters and Slicers
Preview
07:03
Pivot Table Calculated Formulas
09:14
Pivot Table Relationships and Data Models
04:42
02:22

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.

07:52

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.

3 questions

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!

Section 5: Data Visualization
06:50

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.

07:13

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.

05:34

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.

07:45

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!

05:38

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.

Conditional Formatting
09:44
Section 6: Presenting the Results
Introduction to the Written Report
02:01
Page Setup
06:43
Print Functions
03:19
Some Print Examples
03:47
Copying Charts and Tables to Microsoft Word
04:27
The Written Report
06:54
Section 7: Course Overview
Course Overview and Additional Practice Session
05:07
10 questions

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.

Students Who Viewed This Course Also Viewed

  • Loading
  • Loading
  • Loading

Instructor Biography

Jeff Knowlton, Senior Executive. Data Scientist. Teacher.

I am a senior business executive, a data scientist, and a former university instructor. And I've been using Excel, and tools like it, for more than 30 years! 

In addition to my current role as VP Global Services, I've been a VP of Information Technology, a global project manager for the deployment of very large IT systems, a senior statistician/data scientist, a university instructor and a corporate trainer.

I bring a unique perspective to Excel and business analytics. I know how Excel is used in business, and I know what hiring managers are looking for. All of my courses are built on authentic case studies, using real data to show how Excel functions are used today in business.

I have two degrees from Stanford University, one in English with Honors in Humanities, and a graduate degree in Statistics (now called data science). I explain technical concepts in non-technical terms.

You can see more detail on my linked in page. Send me a connection request!

Ready to start learning?
Take This Course