Excel Case Studies for Business Students
4.6 (47 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.
1,233 students enrolled
Wishlisted Wishlist

Please confirm that you want to add Excel Case Studies for Business Students to your Wishlist.

Add to Wishlist

Excel Case Studies for Business Students

Learn Excel functions & business analytics via authentic case studies. A QuickStart tutorial is included for beginners!
4.6 (47 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.
1,233 students enrolled
Created by Jeff Knowlton
Last updated 7/2015
English
Learn Fest Sale
Current price: $10 Original price: $45 Discount: 78% off
1 day left at this price!
30-Day Money-Back Guarantee
Includes:
  • 4.5 hours on-demand video
  • 7 Supplemental Resources
  • Full lifetime access
  • Access on mobile and TV
  • Certificate of Completion
What Will I Learn?
  • Analyze business data with Excel. Starting from text data, students will be able to import the data, use Excel functions to clean the data, preparing it for analysis, summarize the data with pivot tables, visually display the results with charts, and present the results in a written report.
View Curriculum
Requirements
  • A copy of Excel. Excel 2013 is preferred, but Excel 2010 works, too.
  • Basic Excel skills like data entry and formatting are assumed, but I provide an optional "QuickStart" Tutorial if your skills are a little rusty.
  • This is not an Advanced course - no macros or scientific functions are covered.
Description

Excel has become the primary analytical tool of the business world. But this is not just an Excel course: this course is about business analytics. It’s about turning business data into valuable information, using Excel as the tool.

This course is designed for college students taking business courses. While giving a guest lecture at the University of Oregon, I learned that both students and faculty were not happy with the existing business courses that taught Excel. The courses were too focused on the mechanics of Excel, on the commands and menu paths. And they used contrived, artificial examples.

Improve your grades in business classes. Be better prepared for interviews.

So I designed this course around authentic case studies from my experience as a senior business executive. The first case study is based on sales reporting and analysis. Students are given all of the sales information for a small company for an entire year. Through applying Excel functions like VLOOKUP and IF, the data is prepared for analysis. Then using pivot tables, the data is summarized and analyzed. The results are displayed visually with charts. Finally, the results are explained in writing. And students learn some “Business Basics” along the way, about invoicing, credit memos, and sales analysis.

Students learn Excel while answering real business questions.

The second case study is based on service repair and analysis. Students are given text files with the repair history of two different service sub-contractors (more than 50,000 records). The data must be imported, cleaned, and harmonized, using Excel text, date, and logical functions. Finally, the data can be analyzed with pivot tables and the results displayed visually: Which sub-contractor repairs products more quickly?

These are the skills employers are looking for!

The course begins with an optional “Quickstart” Tutorial for beginners, or for those students who haven’t used Excel in awhile, to refresh their knowledge.

The University of Oregon uses these case studies in two of their information management courses. And my other Udemy courses are recommended by the Oregon Business Education Association.

This course covers these topics in Excel:

  • Formatting Data
  • Cleaning "Dirty Data" with Functions: Text, Date, and Logical Functions
  • Filtering and Sorting Data with Table Functions
  • Analyzing Data with Pivot Tables
  • Visualizing the Results with Charts and Conditional Formatting

Content and Overview

In five hours of content including 50+ lessons, this course covers the important Excel functions to analyze business data. Most lessons have a practice session. I provide two Excel files for each practice session: the starting point and the finish point. I also provide the written script for most lessons.

Upon completion, students will be able to analyze large sets of business data. Students will be able to import large text files with tens of thousands of records, use Excel functions to clean data, use logical functions to build complex expressions, use the VLOOKUP function to work with cross-reference tables, analyze the data with pivot tables, display the results visually with charts, and explain the results in writing, and with clarity.

And you’ll know more than 80% of the casual Excel users in business today!

Let’s get started!

Who is the target audience?
  • This course is designed for business students, to assist them in their existing courses and to prepare them for upper division courses
  • Others looking to advance their careers through improved Excel skills will find the course helpful as well. You'll know more than 80% of casual business Excel users when you finish this course.
Students Who Viewed This Course Also Viewed
Curriculum For This Course
Expand All 55 Lectures Collapse All 55 Lectures 05:04:12
+
Introduction
1 Lecture 06:03

This lesson provides a detailed overview of the case studies, including the business context and the Excel functions to be covered.

It also provides some information on how to get the most out of this course.

Preview 06:03
+
Quickstart Tutorial: Get Up to Speed in 25 Minutes!
7 Lectures 28:20

This lesson introduces the Quickstart Tutorial: only 25 minutes of videos to get up to speed! The tutorial is for beginners, or for those students who would like to refresh their knowledge of Excel before beginning the more involved case studies later in the course.

Preview 01:43

Begin with the basics! This lecture introduces Excel worksheet structure, formulas, and data formatting, via an example of the sales from a flower shop.

Preview 05:11

This lesson continues with the simple flower shop case study. It introduces the simple filter and sort functions, auto-sum to sum a column of data, or to count the number of entries in a column. It introduces insertion and deletion of columns and rows.

Quickstart - Filtering and Sorting
04:55

In this lesson, I move on to a more realistic example: sales reporting for a small company. The case study lists all of the sales for an entire year. Using IF and VLOOKUP functions, the data is first prepared for analysis. The Excel NAME function is introduced as well.

Preview 03:52

In this lesson, an Excel table is applied to the data. The table is used to filter and sort the data, remove duplicate data records, and summarize the data with a pivot table. Some simple pivot table functions are introduced.

Quickstart - Analyzing the Data with Pivot Tables
06:31

Make trend charts, column charts, and fancy 3D column charts, to visually display the results of your analysis.

Quickstart - Visualizing the Data with Pivot Charts
04:31

The "Quickstart" tutorial is complete. You're now ready for the more authentic and detailed case studies to follow!

Quickstart: Summary and Next Steps!
01:37
+
Sales Reporting Case Study with Pivot Tables and Charts
28 Lectures 02:21:46

This lesson introduces the case study and the topics to be covered in the "Business Basics" section. The case study is based on a printing company, selling impact printers to car companies and finance companies. You must use Excel functions to analyze the business data, to determine the sales and margin (profit) by product line by region, and identify any trends in the data.

Preview 03:32

Invoices and credit memos form the foundation of sales reporting. Businesses send invoices to their customers to collect the money they're owed. The list of invoice information is compiled into tables, for later analysis. This lecture defines the essential elements of an invoice, and how the data is mapped into Excel.

Preview 08:26

Business are not just interested in their sales revenue, but in their profits. This lesson shows how to calculate the profit margin and profit margin percentage (that is, the percentage of profit compared to the sales amount).

Business Basics - Defining and Calculating the Profit Margin
02:03

Businesses usually have many, many customers and even more products. To simplify the analysis and understand the business, the customers and products are grouped, into customer regions and product lines.

This lesson explains the most common grouping techniques used in business today, and also explains the techniques required to analyze the data in groups.

Business Basics - Reporting by Groups, like Product Line and Region
03:47

This lesson introduces the Excel functions which will be used to prepare the data. Data preparation is the first step in the analysis.

Preview 02:03

This lesson reviews some simple formulas and formatting techniques. In particular, we'll calculate the sales revenue as the product of the quantity and the unit price.

Preview 05:43

The IF function is one of the most commonly used Excel functions in business analytics. This lesson introduces it conceptually first, and then provides two examples of its use: once against a text value and once against a numeric value.

The Excel function is used to update the sales revenue based on the type of document (Invoice or Return Credit Memo) and to create a new field for the "Value Category", either a free product or a charged product.

Preparing the Data - The IF Function
06:56

This lesson introduces nested IF functions, that is, one IF function inside another IF function. The two IF functions introduced in the last lesson will to revised to add a third value for "Value Category": Free, Low Price and High Price Items, and to introduce some error-checking into the calculated field for Sales Revenue.

Preparing the Data - Nested IF Functions
05:52

This lesson introduces the Excel functions for Tables and Names. Tables are a collection of useful functions for list data, providing a means to format, filter, sort, and summarize the data. Excel Names are a way to label, or name, cells or tables, so that they are easy to understand and easy to reference in formulas.

Preparing the Data - Tables and Names
03:52

The VLOOKUP function is used to find, or lookup, a value in a separate list or table, and bring it back into the main table. It's often used when there's a main table of data (like all of the invoices), and a second table with related information (like a customer table with secondary information about the customer, for example the customer region or customer type). The VLOOKUP function retrieves the secondary information about the customer, and adds it to the main table.

Preparing the Data - The VLOOKUP Function
08:47

VLOOKUP functions can be a little tricky. This lesson walks you through several types of common errors, and shows you how to fix them (and prevent them!). The lesson also shows you how to trouble-shoot spreadsheet problems in general.

Preview 09:33

This lesson reviews the Excel functions used to prepare the data for analysis.

Preparing the Data - Review of Excel Functions
02:35

This lesson introduces the Excel pivot table. It begins with a conceptual definition of the pivot table, and then walks you through step by step how to summarize a list of data in an Excel table with an Excel pivot table.

Preview 06:41

This lesson presents several different layouts for a pivot table: the compact layout, the outline layout, and the tabular layout. It also presents techniques for controlling the sub-totals and grand totals. Together, these controls provide powerful tools to format the pivot table.

Analyzing the Data - Pivot Table Layouts, Sub-totals and Totals
06:36

This lesson presents two different techniques for filtering the data in a pivot table: the traditional filters and the slicers. The filters take up less space on the screen, while the filters are more intuitive and easier to use, and provide an excellent way to visually explore the data, especially when used with pivot charts, as shown in a later lesson.

Analyzing the Data - Pivot Table Filters, Slicers and Timelines
07:16

This lesson describes how to create new, calculated fields directly in a pivot table. This functionality is needed to calculate the margin percentage, and it's also very useful for weighted averages.

With the margin and margin percentage, we now have the information needed to make the make pivot tables for Cowboy Business Machines: a pivot table showing the sales, margin, and margin percentage by customer region, and another by product line. We find that South Carolina has some negative margins (losses) for accessories, and I show you how to double-click to investigate the issue in detail.

Analyzing the Data - Calculated Fields in Pivot Tables
08:57

This lesson describes how to link several Excel tables into a data model, so that multiple tables can be analyzed together. In some cases, it can eliminate the need for using the VLOOKUP function. But not all pivot table functions are supported with data models: in particular, calculated fields are not supported, and we need them to calculate the margin percentage.

Analyzing the Data - Pivot Table Relationships & Data Models
04:40

This lesson provides some simple tips and tricks to trouble-shoot common problems with pivot tables.

Preview 07:52

This lesson introduces some key concepts for making charts from pivot tables. Charts are useful for better understanding the data in the pivot tables, and for explaining results. A picture is worth a thousand words!

Preview 06:44

This lesson introduces two different kinds of charts for identifying the important characteristics: the pie chart and the pareto chart.

Data Visualization - Pie Charts and Pareto Charts
07:26

This lesson presents 3D column charts. In most cases, 3D charts are to be avoided, but there is one specific case in which it can be useful, namely charting one key figure (like sales) by two attributes (like product line and region). We make a chart showing the sales by product line by region, and another chart showing the count of the "value category" (free, low, high) by the product line.

Data Visualization - 3D Column Charts
05:44

In this lesson, I show you how to chart two different values on one chart, with dual axes. We make a chart showing the sales revenue on one axis and the margin percentage on another axis, by month. The chart is combined with slicers for product line and region, to create a cool, interactive tool for data discovery.

Data Visualization - Some Advanced Charting Techniques
07:58

This lesson provides a brief introduction to conditional formatting, another way to visually display data (in addition to the standard way, charts). Conditional formatting is useful to highlight issues with larger lists of data, like lists of customers and product lines, color-coding both the good and the bad data records.

Data Visualization - Conditional Formatting
08:43

This lesson presents the Excel functions for managing the Page Layout, the first step in printing information from your spreadsheet. Topics include updating the layout from portrait to landscape, scaling the output to fit on one page, adding headers and footers, and repeating the first row at the top of each printed page.

The Written Report - Excel Page Layout
3 pages

This lesson presents the Excel functions for actually printing elements of the spreadsheet to paper. Examples include the list of invoice data, a chart by itself, and the pivot chart and table together, using the function to control print area.

The Written Report - Printing from Excel
5 pages

This lesson presents several options for copying charts and tables from Excel and pasting them in MS Word. There are some important tips and tricks to know!

Copying Tables and Charts from Excel to Word
4 pages

This lesson presents some tips for writing a business report to explain the results of an analysis. It includes the important topics to include, and some general hints for improving your writing.

The Written Report - Topics to Include
7 pages

Try it Yourself! Practice File from Start to Finish!
2 pages
+
Service Repair Case Study, Using Excel Text, Date, and Logical Functions
19 Lectures 01:41:03

This lesson introduces the case study. Cowboy Business Machines has been asked by their customers to provide a repair service. They do not have the staff to do it themselves, so they've decided to find a sub-contractor to repair the product. They evaluate two sub-contractors based on the repair turn-around time (how long it takes to repair the printers). The two sub-contractors provide all of the repair history data from 2014 in text files. As the business analyst, you must first import the data into Excel, then prepare the data for analysis, before the analysis can be conducted. Unfortunately, the data is "dirty", which is very common in business today. The data must be cleaned using Excel date, text, and logical functions.

Preview 05:13

This lesson describes the business context of the service repair process, starting from the base document, the service RMA (Repair Material Authorization). It describes how the data from an RMA is mapped into an Excel file for analysis.

Preview 03:08

This lesson describes the key steps for importing a text file into Excel, using the "Text Import Wizard".

Preparing the Data - An Introduction to Importing Text Files into Excel
04:28

We import a tab-delimited text file into Excel, to start the analysis of the data from the first sub-contractor.

Preparing the Data - Importing Delimited Text Files
04:54

We import a fixed width text file into Excel, to start the analysis of the data from the second sub-contractor.

Preparing the Data - Importing Fixed Width Text Files
03:36

This lesson introduces text strings, and shows how to work with them using text functions.

Preparing the Data - An Introduction to Text Functions
02:05

In this lesson, we parse the text data from the two sub-contractors to make new fields in the data sets. In particular, we use the Excel functions LEFT, RIGHT, and MID. We also use the text function VALUE to convert a text string to a numeric value.

Preparing the Data - Using Text Functions to Parse Data and Make New Fields
05:23

I introduce several other text functions, including the concatenate function (CONCAT, and also done with the & symbol directly), LEN to calculate the length of a text string, and TRIM to remove spaces. I show you how to format numbers as text with many different TEXT formatting methods. Finally, I show you the Text to Columns wizard to separate text strings into multiple fields.

Preview 09:32

The goal of this case study is to calculate the turn-around time, that is, the number of working days between the start date and the ship date of the repair. To start with, it's important to understand how Excel makes calculations with dates.

Preparing the Data - An Introduction to Date Functions
02:19

In this lesson, I show you how to use the function NETWORKDAYS to calculate the number of work days (excluding weekends) between the start date and the ship date. I show you how to build dates using the DATE function.

Preparing the Data - Using Date Functions to Make Calculations
05:43

In this lesson, I show you how make many other calculations with dates, starting with date functions to parse a date, like the YEAR function, MONTH function, WEEK function, and DATE function. I show you two ways to calculate the day of the week (WEEKDAY and TEXT(<>,"dddd")). I show you how to easily calculate the number of years as a decimal / fraction, YEARFRAC, between two dates, a very handy function when working with contracts.

Preparing the Data - Parsing Dates and Advanced Date Functions
09:53

This lesson introduces logical functions, truth tables, and provides some examples of their use. Note that the most important logical function, IF, was presented in an earlier case study on sales reporting and analysis.

Preparing the Data - An Introduction to Logical Functions
05:35

This lesson introduces the OR function. It's used to identify the data records to include in the analysis. For the case study, the analysis should include only those records which are printers OR the serial number begins with the letter "Q".

Preparing the Data - The OR Function
06:58

This lesson presents the AND function, and introduces complex logical functions, that is, how to combine two or more logical functions together, generally to create a new field based on complex conditions.

Preparing the Data - The AND Function; Compound Logical Expressions
07:22

The data has finally been cleaned. We have all of the fields we need. There are two more steps: the data is still in two different files, one for each of the sub-contractors. The data must be consolidated into one file. I show you some tips and tricks for reducing the chance for error. Second, there are still some differences in the field for location/region. We need to use the VLOOKUP function one more time to harmonize the codes for the location.

Analyzing the Data - Consolidating the Files and Normalizing the Data
09:17

In this lesson, the data is placed into an Excel table. The duplicate data rows are deleted.

And finally, the data can be analyzed with a pivot table, to summarize the results. We calculate the average turn-around time, for the two sub-contractors, for relevant products (printers, or products whose serial numbers begin with the letter "Q", in the southern region, excluding NPFs).

Analyzing the Data - Removing Duplicates & Summarizing with Pivot Tables
07:39

The results from the analysis are displayed in a simple column chart.

Data Visualization - Pivot Charts
07:58

This lesson provides a sample, written report. For more information about printing, page layout, and the topics to include in a written report, please see the last few lessons in the case study on sales reporting.

The Written Report: Explaining the Results in Writing and with Clarity
3 pages

Try it Yourself! Practice File from Start to Finish!
3 pages

This quiz is based on the data from the service repair practice file. The questions are based on the pivot table that you made at the end, on the sum of the field "Failures" and on the average of the field "Turn Around Time".

Be sure to regularly clear the filters in the pivot table!

Service Repair Practice File Quiz
19 questions
About the Instructor
Jeff Knowlton
4.5 Average rating
394 Reviews
4,418 Students
5 Courses
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!