Excel Case Studies for Business Students

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.

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.

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

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.

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.

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.

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

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

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.

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.

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

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.

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

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.

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.

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.

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.

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.

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.

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

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.

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.

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.

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.

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.

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

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!

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

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.

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.

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.

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.

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.

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!

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.

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.

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.

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

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

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

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

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.

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.

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.

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.

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.

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.

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

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.

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.

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

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

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.

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!