Excel Case Studies: Sales Analysis with Pivot Tables, Charts

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.

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.

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.

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.

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.

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

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.

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

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.

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.

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.

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.

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.

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.

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.

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!

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.

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.

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.

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!

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.

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.