Inventory Analysis Case Study

Instructor Guide
__________________________________________________________________
Project Overview
PwC has built this case study to be used in university classrooms to enhance students’
understanding and knowledge of data analytics, including leveraging analytics to make business
decisions.
By completing this case study, students will obtain the following:

 An understanding of what data analytics and visualization are

 The ability to utilize analytic tools
 Comfort in leveraging data in making decisions
 Skills to select data components for analysis and illustrate results to highlight data
tracking and identify conditions for exception analysis or business advisory strategy

Recommended Audience
It is recommended that this project be introduced to students who have successfully completed
Intermediate Accounting. It could be easily embedded into an Accounting Information Systems
(AIS or CIS) course, a Data Analytics course or an Advanced Audit course.
It is recommended that the students work in small groups of 2-3 to complete the pre-work, in-
class activities, and case study questions. You can have larger groups but that will reduce the
level of hands-on engagement by all group members.

Case Background
Our case is based upon Bibitor, LLC*note1, a liquor store chain in the fictional state of Lincoln. It is
a major retailer with approximately 80 locations and total sales in excess of $450 million.
Bibitor has asked the team to complete due diligence on their wine and spirits business looking
at data for their beginning and ending inventory, purchases and sales for a 12 month period.

Case Format and Learning Objectives:

This case is designed to be delivered in three phases. Each phase includes an instructional
component to learn how to manage the data within the tool and then there are case study
exercises designed to apply the skills addressed.

 Phase 1: Introduction to Data Preparation

Load data exported from company into SQLite to put it into a format for analysis and
create tables which can be used by other analysis tools.

1
 Total Class and Teamwork time: Approximately 6 hours

 Phase 2: Introduction to Data Discovery and Visualization

Using tables created in Phase 1, load data into Tableau (or any other appropriate data
visualization too) to perform analysis then create and interpret visualizations.
Total Class and Teamwork time: Approximately 8 hours (primarily teamwork time)

 Phase 3: Introduction to Statistical Analysis

Analyze one set of independent/dependent variables from each of the two case studies
and create a Tableau (or any other appropriate data visualization too) worksheet that
uses linear regression to provide some insight into the data.
Total Class and Teamwork time: Approximately 6 hours

Software Requirements
In order to access all the files required for the case, you and the students will need access to a
data visualization tool (the case materials were created using Tableau and usage of the Tableau
Reader is free) and the SQLite browser.

Technical Skills Preparation

Faculty and students should complete tutorials in Tableau and SQLite. The HUB of Analytics
Educationnote1 includes some instructional videos for instructors regarding teaching with Tableau
which could be helpful. Additional internet Data Science learning is available at “Codecademy”.

Recommended Case Delivery

Following are some recommendations for delivering the case. The materials are designed to be
flexible so you can choose to follow these recommendations or deliver the materials in a format
that works best for your class.

 Throughout the case materials, Tableau is used as the data visualization tool. If you
decide to use a different tool, you will need to update the language and examples in the
case materials
 It may be helpful to utilize a Teaching Assistant or some other classroom support person
who is well versed in SQLite and data visualization tools to support the delivery of this
case. However, if you do not have access to this type of resource, the materials are
written step by step with answers provided so you should be able to follow it.
 Prior to introducing the case to your students, you will need to post the case files in the
delivery system that your school uses to make materials available to students. This will
include the data files, the PowerPoint slide decks which include step-by-step instructions
for references as well as the details for the assignments and the appendix files included
via hyperlink in this document which are referenced in the slides.
 To kick-off the case study, it is recommended that you devote time in the class period
prior to commencing with Phase 1 of the case to introduce the case and review the
learning objectives. There is some pre-work that the participants should complete before

2
 beginning the case activities. You can introduce the pre-work during this time. Note
that this pre-work is detailed in the PowerPoint
 Each of the three case phases includes:
o Introductory Information,
o Review of the previous case activities and pre-work,
o A teach component,
o In-class activities,
o Case study questions, and
o Pre-work for the next phase.

The PowerPoint presentations are designed to walk you through the content for each of
these components

 The pre-work and case study questions should be completed outside of class

Note: If you choose to focus solely on the Tableau exercises in Phase 2, the data table created in
Phase 1 is included in these materials as Appendix 4.

Case Materials
Please note that the case is based on a fictitious company, Bibitor LLC, and Bibitor’s
transactional data created by the HUB of Analytics Education *note1. The case materials include
files posted on PwC’s faculty website and include a PowerPoint deck for each phase (1, 2, & 3)
and data files. In addition, files for Appendix 1 – 7 are embedded below as hyperlinks and are
referenced throughout the PowerPoint slides. Instructors will need to post appropriate files in
the delivery system that your school uses to make materials available to students.

Additional Instructor Aids

In addition to the PowerPoint presentations, some Instructor Aids have been created to support
your delivery of the case. These files are included below as a hyperlink (Ctl+Click to follow link)
within the file title and are referenced in the Instructor Notes in the PowerPoint presentation.

3
Note that Critical Syntax examples which should be made available to the students, unless
otherwise indicated.

 Phase 1 Data Collection and Preparation (in SQLite) slide 25 Basic “Select” Syntax and
Features refers to Appendix1_Learner_SQL Joins.docx for more details on SQL joins.
 Phase 1 Data Collection and Preparation (in SQLite) slide 26 Basic “Select” Syntax and
Features refer to Appendix2_Section1_6i_Joins.txt for SQL scripts.

 Phase 2 Data Discovery and Visualization slide 11 Data Pre-processing refer to
Appendix3_Section2_1_SQLPrepTable.txt with SQLite scripts used to generate prep
table. (the six steps in this Appendix3 are critical to generate the volume of records
necessary to facilitate the visualization and analysis work)

 Phase 2 Data Discovery and Visualization slide 13 Link Visualization to Slide Source Data
refer to Appendix4_prep_Exercise1_AvgCost.xls for source prep table or
Appendix5_Sect2_Visual Example.twb to facilitate this topic.

 Phase 3 Introduction to Statistical Analysis slide 15 Practice Exercise 1 refer to

Appendix6_Section3_2_SQLPrepTable.txt for solution SQL scripts. (Note that this file
requires Tableau)

 Phase 3 Answer Key Examples of the Dashboard (for Instructor Use Only – Note that
these are potential answers. Your students may come up with different solutions. These
are included here as a guide only.)

*Note1: Bibitor, LLC is a fictitious company based on data created by the HUB of Analytics Education @
The HUB of Analytics Education materials are owned by Northeastern University.  PricewaterhouseCoopers LLP is not responsible for any errors or
www.hubae.org.
omissions in, or for the results obtained from the use of, the HUB of Analytics Education. The HUB of Analytics Education materials are provided "as is", with no guarantee of

completeness, accuracy, timeliness or of the results obtained from the use of this information, and without warranty of any kind. In no event will PricewaterhouseCoopers CI LLP, or

its partners, employees or agents, be liable to you or anyone else for any decision made or action taken in reliance on the information in this Site or for any consequential, special or

similar damages, even if advised of the possibility of such damages.