1. 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 points - Insert 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 points - Create 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 (\$). \begin{tabular}{|c|c|c|c|c|c|c|c|c|c|} \hline Jraer \# Customer & Type & Customer IV & Date IVlocel & Quantity & Price/Unit & Iotal Amount & & Customer & Customer ID \\ \hline 1 Maui Guitar Center & Acoustic-Electric & 323 & 1/1/2022 AE100 & 10 & $149.00 & $1,490.00 & & Ace Guitar World & \\ \hline 2 Westwood Music Academy & Acoustic-Electric & 229 & 1/1/2022 AE115 & 3 & $209.00 & $627.00 & & Earle's Guitar Emporium & \\ \hline 3 Maui Guitar Center & Acoustic-Electric & 323 & 1/1/2022 AE110 & 10 & $179.00 & $1,790.00 & & Firefly Music and Arts & \\ \hline 4 Maui Guitar Center & Acoustic-Electric & 323 & 1/1/2022 AE120 & 10 & $309.00 & $3,090.00 & & Jackson Music School & \\ \hline 5 Jackson Music School & Electric & 301 & 1/10/2022 E100 & 13 & $79.00 & $1,027.00 & & Maui Guitar Center & \\ \hline 6 Jackson Music School & Electric & 301 & 1/10/2022 E110 & 12 & $209.00 & $2,508.00 & & Patriot's Hill Music & \\ \hline 7 Jackson Music School & Acoustic & 301 & 1/10/2022A110 & 6 & $119.00 & $714.00 & & Starz Music Center & \\ \hline 8 Firefly Music and Arts & Acoustic & 250 & 1/15/2022A100 & 4 & $99.00 & $396.00 & & Westwood Music Academy & \\ \hline 9 Ace Guitar World & Acoustic-Electric & 160 & 1/15/2022 AE115 & 5 & $209.00 & $1,045.00 & & & \\ \hline 10 Westwood Music Academy & Acoustic-Electric & 229 & 2/15/2022 AE115 & 3 & $209.00 & $627.00 & & & \\ \hline 11 Starz Music Center & Acoustic & 170 & 2/15/2022A100 & 10 & $99.00 & $990.00 & & & \\ \hline 12 Firefly Music and Arts & Acoustic & 250 & 3/15/2022 A100 & 4 & $99.00 & $396.00 & & MEASURES OF CENTRAL T & TENDENCY \\ \hline 13 Ace Guitar World & Electric & 160 & 3/15/2022 E105 & 10 & $149.00 & $1,490.00 & & Mean & \\ \hline 14 Ace Guitar World & Acoustic-Electric & 160 & 4/1/2022 AE110 & 4 & $179.00 & $716.00 & & Median & \\ \hline 15 Starz Music Center & Electric & 170 & 4/14/2022 E100 & 10 & $79.00 & $790.00 & & Mode & \\ \hline 16 Earle's Guitar Emporium & Acoustic-Electric & 151 & 5/1/2022 AE120 & 7 & $309.00 & $2,163.00 & & & \\ \hline 17 Westwood Music Academy & Acoustic & 229 & 5/15/2022 A100 & 5 & $99.00 & $495.00 & & & \\ \hline 18 Starz Music Center & Acoustic-Electric & 170 & 5/15/2022 AE115 & 3 & $209.00 & $627.00 & & & \\ \hline 19 Earle's Guitar Emporium & Acoustic-Electric & 151 & 6/1/2022 AE115 & 3 & $209.00 & $627.00 & & & \\ \hline 20 Firefly Music and Arts & Acoustic & 250 & 6/15/2022 A100 & 3 & $99.00 & $297.00 & & & \\ \hline 21 Starz Music Center & Acoustic-Electric & 170 & 7/1/2022 AE110 & 9 & $179.00 & $1,611.00 & & & \\ \hline 22 Maui Guitar Center & Acoustic & 323 & 7/5/2022 A110 & 10 & $119.00 & $1,190.00 & & & \\ \hline 23 Maui Guitar Center & Electric & 323 & 7/5/2022 E105 & 12 & $149.00 & $1,788.00 & & & \\ \hline 24 Maui Guitar Center & Acoustic & 323 & 7/5/2022 A115 & 3 & $209.00 & $627.00 & & & \\ \hline 25 Ace Guitar World & Acoustic-Electric & 160 & 7/5/2022 AE100 & 12 & $149.00 & $1,788.00 & & & \\ \hline 26 Starz Music Center & Electric & 170 & 8/1/2022 E100 & 15 & $79.00 & $1,185.00 & & & \\ \hline 27 Patriot's Hill Music & Acoustic & 422 & 8/1/2022 A105 & 5 & $149.00 & $745.00 & & & \\ \hline 28 Patriot's Hill Music & Electric & 422 & 8/1/2022 E100 & 10 & $79.00 & $790.00 & & & \\ \hline 29 Patriot's Hill Music & Acoustic-Electric & 422 & 8/1/2022 AE110 & 10 & $179.00 & $1,790.00 & & & \\ \hline 30 Ace Guitar World & Electric & 160 & 8/5/2022 E110 & 3 & $209.00 & $627.00 & & & \\ \hline 31 Jackson Music School & Acoustic & 301 & 8/10/2022 A110 & 6 & $119.00 & $714.00 & & & \\ \hline 32 Jackson Music School & Acoustic-Electric & 301 & 8/10/2022 AE100 & 15 & $149.00 & $2,235.00 & & & \\ \hline 33 Jackson Music School & Electric & 301 & 8/10/2022 E110 & 4 & $209.00 & $836.00 & & & \\ \hline 34 Earle's Guitar Emporium & Acoustic-Electric & 151 & 8/13/2022 AE115 & 5 & $209.00 & $1,045.00 & & & \\ \hline 35 Westwood Music Academy & Acoustic-Electric & 229 & 8/15/2022 AE115 & 3 & $209.00 & $627.00 & & & \\ \hline 36 Firefly Music and Arts & Acoustic & 250 & 8/15/2022 A100 & 4 & $99.00 & $396.00 & & & \\ \hline 37 Earle's Guitar Emporium & Acoustic & 151 & 9/1/2022A120 & 3 & $249.00 & $747.00 & & & \\ \hline 38 Earle's Guitar Emporium & Electric & 151 & 9/13/2022 E105 & 8 & $149.00 & $1,192.00 & & & \\ \hline 39 Ace Guitar World & Electric & 160 & 10/1/2022 E110 & 5 & $209.00 & $1,045.00 & & & \\ \hline 40 Earle's Guitar Emporium & Electric & 151 & 10/13/2022 E110 & 5 & $209.00 & $1,045.00 & & & \\ \hline 41 Starz Music Center & Acoustic & 170 & 10/15/2022 A150 & 2 & $409.00 & $818.00 & & & \\ \hline 42 Earle's Guitar Emporium & Acoustic & 151 & 11/13/2022 A110 & 5 & $119.00 & $595.00 & & & \\ \hline \end{tabular} 1. 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 points - Insert 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 points - Create 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 (\$). \begin{tabular}{|c|c|c|c|c|c|c|c|c|c|} \hline Jraer \# Customer & Type & Customer IV & Date IVlocel & Quantity & Price/Unit & Iotal Amount & & Customer & Customer ID \\ \hline 1 Maui Guitar Center & Acoustic-Electric & 323 & 1/1/2022 AE100 & 10 & $149.00 & $1,490.00 & & Ace Guitar World & \\ \hline 2 Westwood Music Academy & Acoustic-Electric & 229 & 1/1/2022 AE115 & 3 & $209.00 & $627.00 & & Earle's Guitar Emporium & \\ \hline 3 Maui Guitar Center & Acoustic-Electric & 323 & 1/1/2022 AE110 & 10 & $179.00 & $1,790.00 & & Firefly Music and Arts & \\ \hline 4 Maui Guitar Center & Acoustic-Electric & 323 & 1/1/2022 AE120 & 10 & $309.00 & $3,090.00 & & Jackson Music School & \\ \hline 5 Jackson Music School & Electric & 301 & 1/10/2022 E100 & 13 & $79.00 & $1,027.00 & & Maui Guitar Center & \\ \hline 6 Jackson Music School & Electric & 301 & 1/10/2022 E110 & 12 & $209.00 & $2,508.00 & & Patriot's Hill Music & \\ \hline 7 Jackson Music School & Acoustic & 301 & 1/10/2022A110 & 6 & $119.00 & $714.00 & & Starz Music Center & \\ \hline 8 Firefly Music and Arts & Acoustic & 250 & 1/15/2022A100 & 4 & $99.00 & $396.00 & & Westwood Music Academy & \\ \hline 9 Ace Guitar World & Acoustic-Electric & 160 & 1/15/2022 AE115 & 5 & $209.00 & $1,045.00 & & & \\ \hline 10 Westwood Music Academy & Acoustic-Electric & 229 & 2/15/2022 AE115 & 3 & $209.00 & $627.00 & & & \\ \hline 11 Starz Music Center & Acoustic & 170 & 2/15/2022A100 & 10 & $99.00 & $990.00 & & & \\ \hline 12 Firefly Music and Arts & Acoustic & 250 & 3/15/2022 A100 & 4 & $99.00 & $396.00 & & MEASURES OF CENTRAL T & TENDENCY \\ \hline 13 Ace Guitar World & Electric & 160 & 3/15/2022 E105 & 10 & $149.00 & $1,490.00 & & Mean & \\ \hline 14 Ace Guitar World & Acoustic-Electric & 160 & 4/1/2022 AE110 & 4 & $179.00 & $716.00 & & Median & \\ \hline 15 Starz Music Center & Electric & 170 & 4/14/2022 E100 & 10 & $79.00 & $790.00 & & Mode & \\ \hline 16 Earle's Guitar Emporium & Acoustic-Electric & 151 & 5/1/2022 AE120 & 7 & $309.00 & $2,163.00 & & & \\ \hline 17 Westwood Music Academy & Acoustic & 229 & 5/15/2022 A100 & 5 & $99.00 & $495.00 & & & \\ \hline 18 Starz Music Center & Acoustic-Electric & 170 & 5/15/2022 AE115 & 3 & $209.00 & $627.00 & & & \\ \hline 19 Earle's Guitar Emporium & Acoustic-Electric & 151 & 6/1/2022 AE115 & 3 & $209.00 & $627.00 & & & \\ \hline 20 Firefly Music and Arts & Acoustic & 250 & 6/15/2022 A100 & 3 & $99.00 & $297.00 & & & \\ \hline 21 Starz Music Center & Acoustic-Electric & 170 & 7/1/2022 AE110 & 9 & $179.00 & $1,611.00 & & & \\ \hline 22 Maui Guitar Center & Acoustic & 323 & 7/5/2022 A110 & 10 & $119.00 & $1,190.00 & & & \\ \hline 23 Maui Guitar Center & Electric & 323 & 7/5/2022 E105 & 12 & $149.00 & $1,788.00 & & & \\ \hline 24 Maui Guitar Center & Acoustic & 323 & 7/5/2022 A115 & 3 & $209.00 & $627.00 & & & \\ \hline 25 Ace Guitar World & Acoustic-Electric & 160 & 7/5/2022 AE100 & 12 & $149.00 & $1,788.00 & & & \\ \hline 26 Starz Music Center & Electric & 170 & 8/1/2022 E100 & 15 & $79.00 & $1,185.00 & & & \\ \hline 27 Patriot's Hill Music & Acoustic & 422 & 8/1/2022 A105 & 5 & $149.00 & $745.00 & & & \\ \hline 28 Patriot's Hill Music & Electric & 422 & 8/1/2022 E100 & 10 & $79.00 & $790.00 & & & \\ \hline 29 Patriot's Hill Music & Acoustic-Electric & 422 & 8/1/2022 AE110 & 10 & $179.00 & $1,790.00 & & & \\ \hline 30 Ace Guitar World & Electric & 160 & 8/5/2022 E110 & 3 & $209.00 & $627.00 & & & \\ \hline 31 Jackson Music School & Acoustic & 301 & 8/10/2022 A110 & 6 & $119.00 & $714.00 & & & \\ \hline 32 Jackson Music School & Acoustic-Electric & 301 & 8/10/2022 AE100 & 15 & $149.00 & $2,235.00 & & & \\ \hline 33 Jackson Music School & Electric & 301 & 8/10/2022 E110 & 4 & $209.00 & $836.00 & & & \\ \hline 34 Earle's Guitar Emporium & Acoustic-Electric & 151 & 8/13/2022 AE115 & 5 & $209.00 & $1,045.00 & & & \\ \hline 35 Westwood Music Academy & Acoustic-Electric & 229 & 8/15/2022 AE115 & 3 & $209.00 & $627.00 & & & \\ \hline 36 Firefly Music and Arts & Acoustic & 250 & 8/15/2022 A100 & 4 & $99.00 & $396.00 & & & \\ \hline 37 Earle's Guitar Emporium & Acoustic & 151 & 9/1/2022A120 & 3 & $249.00 & $747.00 & & & \\ \hline 38 Earle's Guitar Emporium & Electric & 151 & 9/13/2022 E105 & 8 & $149.00 & $1,192.00 & & & \\ \hline 39 Ace Guitar World & Electric & 160 & 10/1/2022 E110 & 5 & $209.00 & $1,045.00 & & & \\ \hline 40 Earle's Guitar Emporium & Electric & 151 & 10/13/2022 E110 & 5 & $209.00 & $1,045.00 & & & \\ \hline 41 Starz Music Center & Acoustic & 170 & 10/15/2022 A150 & 2 & $409.00 & $818.00 & & & \\ \hline 42 Earle's Guitar Emporium & Acoustic & 151 & 11/13/2022 A110 & 5 & $119.00 & $595.00 & & & \\ \hline \end{tabular}