Excel Case Studies: Sales Analysis with Pivot Tables, Charts
- 3 hours on-demand video
- 89 downloadable resources
- Full lifetime access
- Access on mobile and TV
- Certificate of Completion
Get your team access to 4,000+ top Udemy courses anytime, anywhere.
Try Udemy for Business- 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!
- 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.
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.
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.
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.