Description
Obtain the workbook: Guitar_Sales_Data.xlsx. Download Guitar_Sales_Data.xlsx.Use Microsoft Excel to complete the following questions:Formulas (Show each formula in the blue shaded area next to the data in your Excel file) – 1 point Calculate new data using the VLOOKUP function. In cell L4, enter a formula to match Customers IDs with customers. Use the VLOOKUP function to obtain the Customer ID that corresponds to a particular customer’s name (reference the table). Use the data in cells (B4:D53) in your calculation. Copy the formula in cell L4 through to cell L11. Calculate the average (mean) price per unit. Calculate the median price per unit. Calculate the mode for quantity sold. Note: Format all dollar amounts in currency ($).2. Add a new sheet (labeled “PivotChart”) and insert a new PivotTable (2 fields) using the data from the Guitar Sales sheet. – 2 pointsInsert a Chart of your choice. Choose only 2 fields to add to this report. You choose appropriate dimension(s) and measure(s), and choose your own unique layout, colors, and design. Ensure that your own Chart is relevant, provides insight about the data and that you will be able to answer questions about your data in the written portion in the next section. Note: Format all dollar amounts in currency ($).3. Create a PivotTable on a new sheet (labeled “PivotTable”) using the data from the Guitar Sales sheet to display summary data. – 3 pointsCreate value fields for Quantity and Total Amount. Display measures as total quantity sold and total amount received for sales (sum).Create row dimensions for Customer (primary) and Date (secondary, by month). Create a column dimension for Type.Add a basic filter for Model.Insert a Slicer for Months.Insert a Timeline for Date.Use “Format as Table” to format the PivotTable using colors of your choice. (Several PivotTable styles are available to choose from). Note: In a Pivot Table report, the “dimensions” are the descriptive attributes used for labeling, grouping, and aggregating the data – for example, Type and Date. The “measures” are the numeric data points that can be used in mathematical operations; for example, sales, quantity, etc. Format all dollar amounts in currency ($).Write one or two paragraphs (minimum 150 words) to describe your analysis for Precision Instrument Manufacturing’s 2016 Guitar Sales. What is being accomplished by your own Chart and your PivotTable? What conclusions can you reach in your Charts and PivotChart analysis? What insights do they provide about the data? What insight did the PivotTable and your own Chart provide about total sales, models, dates, types of guitars, customers, etc.? – 3 pointsPrecision Instrument Manufacturing
2016 Guitar Sales
Order #
Customer
1 Maui Guitar Center
2 Westwood Music Academy
3 Maui Guitar Center
4 Maui Guitar Center
5 Jackson Music School
6 Jackson Music School
7 Jackson Music School
8 Firefly Music and Arts
9 Ace Guitar World
10 Westwood Music Academy
11 Starz Music Center
12 Firefly Music and Arts
13 Ace Guitar World
14 Ace Guitar World
15 Starz Music Center
16 Earle’s Guitar Emporium
17 Westwood Music Academy
18 Starz Music Center
19 Earle’s Guitar Emporium
20 Firefly Music and Arts
21 Starz Music Center
22 Maui Guitar Center
23 Maui Guitar Center
24 Maui Guitar Center
25 Ace Guitar World
26 Starz Music Center
27 Patriot’s Hill Music
28 Patriot’s Hill Music
29 Patriot’s Hill Music
30 Ace Guitar World
31 Jackson Music School
32 Jackson Music School
33 Jackson Music School
34 Earle’s Guitar Emporium
35 Westwood Music Academy
36 Firefly Music and Arts
37 Earle’s Guitar Emporium
38 Earle’s Guitar Emporium
39 Ace Guitar World
40 Earle’s Guitar Emporium
41 Starz Music Center
42 Earle’s Guitar Emporium
43 Ace Guitar World
Type
Acoustic-Electric
Acoustic-Electric
Acoustic-Electric
Acoustic-Electric
Electric
Electric
Acoustic
Acoustic
Acoustic-Electric
Acoustic-Electric
Acoustic
Acoustic
Electric
Acoustic-Electric
Electric
Acoustic-Electric
Acoustic
Acoustic-Electric
Acoustic-Electric
Acoustic
Acoustic-Electric
Acoustic
Electric
Acoustic
Acoustic-Electric
Electric
Acoustic
Electric
Acoustic-Electric
Electric
Acoustic
Acoustic-Electric
Electric
Acoustic-Electric
Acoustic-Electric
Acoustic
Acoustic
Electric
Electric
Electric
Acoustic
Acoustic
Acoustic-Electric
Customer ID
Date
323
229
323
323
301
301
301
250
160
229
170
250
160
160
170
151
229
170
151
250
170
323
323
323
160
170
422
422
422
160
301
301
301
151
229
250
151
151
160
151
170
151
160
1/1/2016
1/1/2016
1/1/2016
1/1/2016
1/10/2016
1/10/2016
1/10/2016
1/15/2016
1/15/2016
2/15/2016
2/15/2016
3/15/2016
3/15/2016
4/1/2016
4/14/2016
5/1/2016
5/15/2016
5/15/2016
6/1/2016
6/15/2016
7/1/2016
7/5/2016
7/5/2016
7/5/2016
7/5/2016
8/1/2016
8/1/2016
8/1/2016
8/1/2016
8/5/2016
8/10/2016
8/10/2016
8/10/2016
8/13/2016
8/15/2016
8/15/2016
9/1/2016
9/13/2016
10/1/2016
10/13/2016
10/15/2016
11/13/2016
11/15/2016
44 Patriot’s Hill Music
45 Patriot’s Hill Music
46 Patriot’s Hill Music
47 Westwood Music Academy
48 Firefly Music and Arts
49 Patriot’s Hill Music
50 Maui Guitar Center
Electric
Acoustic-Electric
Acoustic
Acoustic-Electric
Acoustic-Electric
Acoustic-Electric
Acoustic-Electric
422
422
422
229
250
422
323
12/1/2016
12/1/2016
12/1/2016
12/15/2016
12/15/2016
12/1/2016
1/1/2016
Manufacturing
Sales
Model
AE100
AE115
AE110
AE120
E100
E110
A110
A100
AE115
AE115
A100
A100
E105
AE110
E100
AE120
A100
AE115
AE115
A100
AE110
A110
E105
A115
AE100
E100
A105
E100
AE110
E110
A110
AE100
E110
AE115
AE115
A100
A120
E105
E110
E110
A150
A110
AE100
Quantity
10
3
10
10
13
12
6
4
5
3
10
4
10
4
10
7
5
3
3
3
9
10
12
3
12
15
5
10
10
3
6
15
4
5
3
4
3
8
5
5
2
5
3
Price/Unit
Total Amount
$149,00
$209,00
$179,00
$309,00
$79,00
$209,00
$119,00
$99,00
$209,00
$209,00
$99,00
$99,00
$149,00
$179,00
$79,00
$309,00
$99,00
$209,00
$209,00
$99,00
$179,00
$119,00
$149,00
$209,00
$149,00
$79,00
$149,00
$79,00
$179,00
$209,00
$119,00
$149,00
$209,00
$209,00
$209,00
$99,00
$249,00
$149,00
$209,00
$209,00
$409,00
$119,00
$149,00
$1.490,00
$627,00
$1.790,00
$3.090,00
$1.027,00
$2.508,00
$714,00
$396,00
$1.045,00
$627,00
$990,00
$396,00
$1.490,00
$716,00
$790,00
$2.163,00
$495,00
$627,00
$627,00
$297,00
$1.611,00
$1.190,00
$1.788,00
$627,00
$1.788,00
$1.185,00
$745,00
$790,00
$1.790,00
$627,00
$714,00
$2.235,00
$836,00
$1.045,00
$627,00
$396,00
$747,00
$1.192,00
$1.045,00
$1.045,00
$818,00
$595,00
$447,00
E100
AE110
A105
AE115
AE100
AE110
AE110
10
10
5
3
5
10
11
$79,00
$179,00
$149,00
$209,00
$149,00
$179,00
$179,00
$790,00
$1.790,00
$745,00
$627,00
$745,00
$1.790,00
$1.969,00
Formulas:
VLOOKUP
Customer
Customer ID
Ace Guitar World
Earle’s Guitar Emporium
Firefly Music and Arts
Jackson Music School
Maui Guitar Center
Patriot’s Hill Music
Starz Music Center
Westwood Music Academy
MEASURES OF CENTRAL TENDENCY
Mean
Median
Mode
Purchase answer to see full
attachment
