MBA 6350 - Week 1 Case Study #1 Using Pivot Tables-10HR DEADLINE

1

MBA 6350 - Week 1 Case Study #1

Using Pivot Tables

(Version 8-20-2020)

This assignment is intended to test your knowledge of how to use Pivot Tables in Excel to summarize and analyze da a in differen ie . You will also need to write-up an Executive Summary of your findings of your analysis of the data. Watch the following Two Videos on Pivot Tables for this assignment: How to Create Pivot Tables in Excel Pivot Table Value Field Settings

Use the Excel Sales Transaction Data file included in the Canvas Assignment: Cust ID - Unique ID for each customer Region Sales Region of the country where the sale was made Payment Method - Method of payment Transaction Code - Unique transaction code for each transaction Purchase Source - How purchase was made Purchase Amount - Amount of purchase in USD $ Product Type Type of Product purchased Time of Purchase - Time of Day of purchase in 24 Hour Format

I. PIVOT TABLES: Create the following 4 Pivot Tables in 4 separate worksheet tabs:

1. Pivot Table 1: SUM of P cha e Amo n b Region 2. Pivot Table 2: COUNT of C ome ID b Region 3. Pivot Table 3: SUM of P cha e Amo n b Pa men Me hod AND Region . 4. Pivot Table 4: COUNT of Pa men Me hod b Region AND P od c T e

and add a FILTER on P cha e So ce e o j elec he WEB P cha e

Please use appropriate formatting (numeric, currency, etc.) of the data and ensure that the Column and Row Labels are correctly displayed. You can use your discretion to de e mine hich field o lace in Ro Col mn and o ho ld e e imen o ee hich gi e he be o e all i al e e en a ion of he da a You may add in

colors, cell outlines, fonts, shading, and o he de ign elemen to better display your Pivot Tables in a professional and business-appropriate manner.

II. EXECUTIVE SUMMARY:

2

Next, use Excel Pivot Tables to EXPLORE the full Sales Transaction dataset that has been provided to you. You do not need to submit any additional Pivot Tables beyond the 4 listed above, but you should use the power of Excel Pivot Tables to help analyze the full Sales Transaction data set. After you have completed your analysis of the data, i e an Executive Summary that contains your findings and analysis based on the 4 Pivot Tables you performed as well as your additional analysis of the data on your own. The Executive Summary ho ld be a minimum of 2 pages and a maximum of 5 pages long (double-spaced). It should contain a mix of Text, select Tables, and select Graphs of an appropriate type and format, and be written as a professional business document, such as one being presented to a client or manager. It should include the following elements:

1. An introductory paragraph summarizing the purpose of the analysis. 2. A section (2 or more paragraphs) describing how the following variables impact

the Sales Data based on your pivot table analysis: a. Region b. Payment Method c. Purchase Source d. Product Types

3. A concluding paragraph summarizing what you believe are the most important and key findings resulting from your analysis of the data, as well as any areas that may warrant further analysis or research.

Submit a single Excel Workbook with worksheet tabs named as outlined above containing the 4 Required Pivot Tables. Name and submit your Excel file in Canvas using the following naming convention: lastname-firstname-MBA6350- Case1.xlsx

Submit a single Word Document con aining o E ec i e S mma a o lined above. Ensure it follows the requirements listed and answers all the questions in parts 1 through 3. Name and submit your Word file in Canvas using the following naming convention: lastname-firstname-MBA6350-Case1.doc