Bibitor LLC* Inventory Analysis Case Study Phase 3 *Note1: Bibitor,

Bibitor LLC* Inventory Analysis Case Study Phase 3 *Note1: Bibitor, LLC is a fictitious company based on data created by the HUB of Analytics Education @ www.hubae.org.

Introduction and Case Overview PwC 2

Objectives This case study is designed to enhance your understanding and knowledge of data analytics, including leveraging analytics to make business decisions. By completing this case, you 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 PwC 3

Case Format You should plan to work in teams of 4 to 5 students to complete the case. This will give you the opportunity to share the learning, knowledge, and experience. This case will be delivered in three phases. Each phase includes an instructional component to learn how to manage the data within the tool and case study exercises utilizing the skills. - 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. - Phase 2: Introduction to Data Discovery and Visualization Using tables created in Phase 1, load data into Tableau to perform analysis then create and interpret visualizations. - Phase 3: Introduction to Statistical Analysis Analyze one set of independent/dependent variables from each of the two case studies and create a tableau worksheet that uses linear regression to provide some insight into the data. PwC 4

Case Materials All the materials required to complete the case can be found at ((LOCATION)). PwC 5

Case Background Bibitor, LLC* is 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. *Bibitor, LLC is a fictitious company based on data created by the HUB of Analytics Education @ www.hubae.org. The HUB of Analytics Education materials are owned by Northeastern University. PricewaterhouseCoopers LLP is not responsible for any errors or 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 LLP, or its partners, principals, employees, or agents, be liable to you or anyone else for any decision made or action taken in reliance on the information in the HUB of Analytics Education materials or for any consequential, special or similar damages, even if advised of the possibility of such damages. PwC 6

Getting Started – Phase 3 Pre-work Review background information on Linear Regression: - https ://www.hackerearth.com/practice/machine-learning/machine-learning-algorithms/begin ners-guide-regression-analysis-plot-interpretations/tutorial/ Review Basic Concepts - Single-variable Linear Regression (Definition): a statistical method that allows for the evaluation of the relationship between two continuous variables, one predictor (input) variable –‘x’, and one response (output) variable—‘y’. PwC 7

Phase 3 Pre-work The key function is the same as the standard form of an algebraic linear equation: f(x) b a(x) y Where x is the independent (predictor) variable, y, or f(x), is the dependent (response) variable, b is a constant, and a is the slope of the line. Purpose: we are applying this function in order to predict the outcome of a future event/input. The function approximates the line of ‘best fit’ given the relationship of two variables based upon pre-existing/historical data. As such, in reference to the following example graph, we would expect that new events with input value 4, would result in an output value of 12.5. PwC 8

Phase 3 Pre-work Source: https:// www.google.com/search?q basic linear equation graph&rlz 1C1GGRV enUS753US753&source lnms&tb m isch&sa X&ved 0ahUKEwjf29uDvJPVAhUKfiYKHQBvANIQ AUICigB&biw 1533&bih 761#tbm isch&q basic fitting line graph&imgrc PG9RR9w1ofQR6M PwC 9

Phase 3 Pre-work Deriving the Slope of the line of best fit: - Numerator: for each known data point, calculate the sum of the variances – where point is each known event, and is the calculated mean of the independent variable values, and the is the calculated mean of the dependent variable values. - Denominator: for each known independent variable value, calculate the sum of squares of variance – where is each known independent variable value and is the calculated mean. Note: If you want to learn how to process this work in Tableau, refer to Tableau Help Anayze box BuildIt-Yourself Exercises Build A Line Chart. PwC 10

Phase 3 Pre-work Deriving the Constant of the line of best fit: - Where a is slope, is the calculated mean of the dependent variable values, and is the calculated mean of the independent variable values. PwC 11

Phase 3: Introduction to Statistical Analysis PwC 12

Statistical Analysis Now that you can extract and format data and utilize visualization tools, the steps in this phase will demonstrate how to further use these tools to evaluate the relationships between two continuous variables. PwC 13

Review of Pre-Work and Case Results PwC 14

Practice Ex. 1: Using SQL to calculate the Slope and the Constant for the line of best fit. In your class SQLite database, table ‘VendorInvoicesDec’, we would like to further evaluate the relationship between quantity billed, and the related freight costs. We are hypothesizing that the larger the quantity billed, the higher the freight costs. Using SQLite, calculate the Slope and Constant for the ‘best fit’ line equation for these two variables. Slope 0.04858 Constant 69.93 Note: You can also create a Trend Line in Tableau. For guidance: Tableau Help Design Views and Analyze Data Work with Time Trend Lines PwC 15

Using Regression in Tableau Note from Tableau Help: - "To add trend lines to a view, both axes must contain a field that can be interpreted as a number. - For example, you cannot add a trend line to a view that has the Product Category dimension, which contains strings, on the Columns shelf and the Profit measure on the Rows shelf. - However, you can add a trend line to a view of sales over time because both sales and time can be interpreted as numeric values." PwC 16

Case Studies PwC 17

Instructions Analyze one set of independent/dependent variables from each of the two case studies on the next 2 slides and create a tableau worksheet that uses linear regression to provide some insight into the data. When selecting your independent/dependent variables, determine your rationale to support the notion that the independent variable precedes or affects the dependent variable and not the other way around. Document your explanation you observe in these data trends. PwC 18

Case 1 (for reference from phase 2) Executive leadership is looking for dashboard reporting that would be useful to help identify and monitor vendor activity in order to focus efforts strategically on key supplier relationships. Create (1) an aggregate table that includes all critical vendor billings and their associated purchasing activity Create (2) separate tables to store key information, such as ‘top 10 vendors’ by quantity purchased. Name all tables you create ‘c1 Prep [table name]’ PwC 19

Case 2 (for reference from phase 2) You notice that there is inventory that is purchased but sits on the shelf for a long period of time rather than being sold. As such, there is an opportunity to add value to the procurement process by identifying trends in the timing inventory purchased versus its corresponding sale. Consider key data points around purchase prices, seasonality, and vendor information in creating an aggregate table for future visual & statistical analysis. Name the table you create ‘c2 Prep [table name]’. PwC 20

Thank You.