Business Intelligence at Super Skateboard Builders (SSB), Inc

Business Intelligence at Super Skateboard Builders (SSB), Inc.

Background1

Super Skateboard Builders (SSB), Inc. was founded in 1997 by John “Z-boy” Boeve, the current president of the company, with the help of a small trust John received from his maternal grandmother, a woman who motivated John to go to college and to continue to pursue his skateboarding passion. She marveled at John’s skateboarding finesse and encouraged him to find some way to earn a reasonable living by capitalizing on his passion for the sport. John used the money from the trust to buy the necessary shop and office equipment (storage bins, an assembly table, desks, etc.) and lease a small building that would adequately house a skateboard assembly operation.

Based on your thorough analysis of the cost and benefits of implementing an ERP system, John and his team decided to move forward with a full scale implementation of SAP. Implementation lasted approximately 12 months and went smoothly for a project of such size, complexity, and scope.

The Problem

SSB has been using the new ERP system for approximately 12 months and they are thrilled with the initial gains in productivity and overall efficiencies. Business processes have been formally defined and are routinely being followed, communication between departments has improved, delivery times have decreased, sales have increased due to better information about products in inventory, and overall employee morale has improved.

Now that the system configuration has stabilized, John would like to start taking advantage of the data being collected by SAP during the processing of transactions. Specifically, he would like to conduct a detailed analysis of SSBs (1) customers, (2) products, and (3) salespeople and thinks the data residing in SAP will provide valuable insights. John and SSB have requested your assistance with analyzing the data to identifypatterns and trends and make recommendations that can be used to improve the overall performance of SSB.

Your Task

SSB would like for you to perform a detailed analysis of data containedin SAP using Access and Excel. Accordingly, the SAP database administrator has created the attached data mart for you to use. The data mart is in a small subset of the overall data contained in SAP and has been sent to you as an Access database. The database contains sales orders for July, August and September of 2010 along with employee, customer and product information. To analyze the data, you will need to understand relational databases and create queries to find and extract the data of interest.

Creating Queries using SQL

SSB would like for you to generate several queries using the SQL “Query Design” feature in Access. The queries are designed to provide an overview of the data stored in the data mart and techniques for answering specific questions about customers, products, and employees.

Query 1 – Employee List

Design a query to list all employees in the organization. The query should display the following information: employee ID, employee first name, employee last name, employee address, employee salary, department name, and region name. After executing the query and verifying the query works correctly, save the query as “Query1”.

Query 2 – Sales Representative List

Design a query to list all sales people in the organization. The query should display the following information for the sales people in the organization: employee ID, sales rep first name, sales rep last name, sales rep address, sales rep salary, sales rep department name, sales rep region name. After executing the query and verifying the query works correctly, save the query as “Query2”.

Query 3 – Customer List

Designa query to list allcustomersin alphabetical order by customer last name. The query should display the following information: customer ID, customer first name, customer last name, and customer birth date. After executing the query and verifying the query works correctly, save the query as “Query3”.

Query 4 – Sales Orders in July

Design a query to list all sales orders for the month of July sorted in ascending order by purchase order date. The query should display the following information: PO date, order ID, product ID, product description, quantity sold, and unit price. After executing the query and verifying the query works correctly, save the query as “Query4”.

Query 5 – Finding a Sales Order

Construct a query to list all the products purchased by Jason Witten on July 14th, 2010. The query should display the following information: PO Date, order ID, customer ID, customer first name, customer last name, product ID, product description, quantity sold, and unit price. After executing the query and verifying the query works correctly, save the query as “Query5”.

Query 6 – Sales Orders for Export to Excel

Create a query to listall sales orders in ascending order by PO Date. The query should contain the following information: PO date, order ID, customer first name, customer last name, product ID, product description, quantity sold, unit price, sales rep first name, sales rep last name, and sales rep region name. After executing the query and verifying the query works correctly, save the query as “Query6”.

Creating Pivot Tables using Excel

Pivot tables 1 through 4 require you to export the result set of query 6 to a Microsoft Excel Workbook. To export the query 6 result set, run query 6 by double clicking on it and export the results to Excel using the “Export to Excel” button on the “External Data” tab of Access. Save the exported data as “SSB Pivot Tables.xlsx”. The pivot tables described below should be created from the data you export and all pivot tables shouldbe contained in the same workbook (i.e. SSB Pivot Tables.xlsx”) listed as separate tabs.

Pivot Table 1 – Customer Sales by Product

SSB would like to analyze the purchase patterns of customers. Specifically, SSB would like to know how much money each customer spent on various products. Create a pivot table to show the total sales dollar amount for each customerin descending order from high to low. The pivot table should list purchases by product type so that the columns display the amount of each product purchased along with the total amount each customer purchased. Which customer purchased the greatest dollar amount of products for the quarter? How much money did he or she spend? What product did he or she spend the most money on? What recommendations would you make to management based on this report? Save the pivot table as worksheet “Customers”. (Hint: Customers should be displayed in rows and products should be displayed in columns. You should create a new column in the raw data set to compute the total sales amount prior to creating the pivot table. In addition, you should create a new column in the raw data set to display the customer’s full name).

Pivot Table 2– Products by Region

SSB would like to analyze the sales of products by geographic region. Create a pivot table to show the total sales dollar amount of each product in order from high to low with the product with the greatest amount listed first. The pivot table should list purchases by region name so that the column display the dollar amount of each product purchased by region name along with the total amount of each product sold. Which product had the highest sales dollar amount for the quarter? How much did the east region sell? What recommendations would you make to management based on this report? Save the pivot table as worksheet “ProductsByRegion”.

Pivot Table 3 – Sales by Rep

SSB would like to analyze the sales of products by sales representative. Create a pivot chart to show the total sales by sales representative from high to low with the person selling the greatest dollar amount listed first. The pivot table should list sales representatives’ names as rows and total amount sold as the only column and display the corresponding bar chart. Who sold the greatestdollar amount for the quarter? Who sold the least? What recommendations would you make to management based on this report? Save the pivot table as worksheet “Sales by Rep”.

Pivot Table 4 – Product by Month

SSB would like to analyze the sales of products over time. Create a pivot chart to show the total sales by month for entry level skateboards, helmets, shoes, and T-shirts. The pivot chart should display a bar chart for each month (e.g. July, August, September) with the dollar amount sold for each of the four products for each month. Did sales of skateboards increase or decrease between August and September? What recommendations would you make to management based on this report? Save the pivot table as worksheet “Products by Month”. (Hint: Create a new column in the raw data set to compute the month prior to creating the pivot table.)

The Deliverables

Part 1 – The Access Database

Please turn in a copy of your Access database containing queries 1 through 6.

Part 2 – The Excel Workbook

Please attach a copy of your Excel workbook containing pivot tables 1 through 4. Answers to the discussion questions for each pivot table should be included directly in the Excel workbook.