Excel Case Studies: Sales Analysis with Pivot Tables, Charts
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.
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 or Excel 2016 for Windows. Excel 2010 will work as well.
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!
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
- Preview03:01
- Preview01:41
- Preview03:29
Instructor
![Jeff Knowlton](data:image/svg+xml,%3Csvg xmlns="http://www.w3.org/2000/svg" viewBox="0 0 64 64"%3E%3C/svg%3E){kind=avatar}
- 4.6 Instructor Rating
- 18,409 Reviews
- 117,117 Students
- 5 Courses
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 Information Business Services, I've been 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 can help you prepare for interviews, and excel in your first job as a business analyst.
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!