Please answer all the steps from the instructions for a like please!
AutoSave OFF NU OO Y019_Excel_Ch06_Prepare_PartB_Sales_Analysis_Instructions - Saved to my Mac Review View RCM Grammarly Tell me Home Insert Draw Design Layout References Mailings Share Comments y ASDCORE Arial v 14 A A Aa A BI U X. * AOA IA AaBbCcD Heating E AaBhCcDdE ABECODE Hawin Nams Paste Emphis Dictatc Sensitivity Editor Styles Pane Open Grammarly YO19_Excel_Ch06_Prepare_PartB_Sales_Analysis Project Description: Aleata Hecdott, manager of the Red Bluff Pro Shop, would like to develop a marketing strategy for increasing pro shop patronage. She has requested data about the pro shop sales over the past several years. She needs to be able to work with the data to understand the current patronage, such as where the patrons were from what kind of items they purchased, how much money they spent, and so forth. Exploring the data is key in determining the marketing strategy because it helps her learn about customer preferences. Afler analyzing the data, aleela will present her ideas to the board of directors Steps to Perform: Step Instructions Points Possible 1 0 This exercise begins on page 343 of your lexL Slart Excel. Download and open the file named Excol_Chog_Propare_SalesAnalysis.xlsx. Grader has automatically added your last name to the beginning of the filename. Save the file to the location where you are storing your filas 2 1.5 Database functions allow for the user to specify criteria in one or more fields to explore the dala with ease. When this is done, all the criteria must be evaluated to TRUE for the record to be included in the calculation. Using a table for the Excel Database allows you to add new records to the database easily and any database functions used on the table will automatically update On the SalesData worksheet, convert the plain data set to an Excel table. Name the Excel table, Sales Data and then create a named range Sales Database for all of the data in the table, including the column Headings. Copy the column headings from the SalesData table and paste them on the Database Totals Worksheel, starting in cell A1 to setup the criteria area of for use in the Database funcions. used in the databa worksheel, in call B5, type NetRevenue for the field name that will be , , , In cells B7:B11, use the appropriate Database function to calculate the sun, average, count max and min of the NetRevenue field using the range A1:L2 as the criteria. Once all database functions have been created, use the criteria area to limit the calculations to those records with transaction dates after 11/15/2022 and with Apple Pay as the payment method. Finally, change the field being used in the calculations from NetRevenue to TotalDiscounts . Page 1 of 3 1116 words 02 English (United States Focus + 130% AutoSave OFF NOO Home Insert Draw Design Layout References Share Comments Y019_Excel_Ch06_Prepare_PartB_Sales_Analysis_Instructions - Saved to my Mac Mailings Review View RCM Grammarly Tell me vvv EE 1 ASOC.DE AaBCCD AarhCdE ABE Copco Hearing Hawny y Arial v 14 A A A A B ! | x 40 x x ADA E IA Paste Empi Norms Dictate Sensitivity Editor Styles Pane Open Grammarly SU UULILI Possible 3 0.7 Excel's Recommended Pivot Tables feature alows you to easily explare data from many different perspectives with just a few clicks. Once created, they can be easily modified to improve readability and even changed to further explore the data. Using the data on the Sales Data worksheed, use the Recarmenced PivatTable button an the Insertab to create the average of Cabbag by Quantity PrivatTabla. if using a Mac the Recommended Pivottable automaticaly created will need to be modified before maving forward. In the Pivot Table Fields are olck to deselect Transate, NetBewegue and EMP-ID. In the PivalTable Fields are, drag Quantity to Ruws, Clubblebu? lo columns, and Coabisc to Values Right click the Caribiss fed in the Values area, select Field Settings, and change the Summarize by function to Average ........................... Configure the PivoTable Options so that error values are shown as 0 In call A4, replace Row Labak with Quantity Sold An incel 3, replace Colim Labe's with I Discount? Remove the Casalec. Tiekd from the Values area and replace it with the TotalDiscouatarield. Rename the worsheet to be Total Discount 4 1.3 A PivoTable is an interactive table that extracts, argarizes, and summarizes source data. PivoTables are used for data analysis and looking for trends and pattems for decision-making purposes. The frst step in creating a PivoTable is to select the data to be used and the location where it is to be created Use the data in the Sales Data Excel Table to create a ProtTable on a new worksheet. Name The new workshant Pratamatula 5 1.5 Sasing how the Nat Revenue breaks doran nto various groups can be easily done with PivoTables Create 9 Pivot Table that cisplays the NetBeece values with the Taosete. Tield grouped into Years, Quarters, and Months as the Ruws and the payment tye as the courts. Use the Cut Mater? field as the report fiter and only show the data for club members 1 PivoTables can be made more user-friendly and provide additional insights into your data though various PivoTaulut configuration upons. Create a Total Net Revenue Custom Name for the Sum of NetBewegue feid and format the fed as Acourting with 2 decimal places. In cell A4, replace Row Labak with Quarters by Year and in call B3, replace Column Label with Payment Type Change the Pivot Table so that it shows the Tatal Nat Revenue as of Grand Total. Apply the White, Pivot Style Light 23 to the Pivottable. Page 2 of 3 1116 words IE English (United States) Focus E - + 120%. AutoSave OFF NOO Home Insert Draw Design Layout References Share Comments YO19_Excel_Ch06_Prepare_PartB_Sales_Analysis_Instructions - Saved to my Mac Mailings Review View RCM Grammarly Tell me vvv 21 ASCODE AaBbCcD AaBhCcDdE ABECODE - = = Heating Hawin? y Arial v 14 A A A A BI U X. * ADA E IA Paste mphis Norms Dictatc Styles Pane Sensitivity Editor Open Grammarly Step Instructions Points Possible 7 Slicers added 10 a PivotTable make it easy to apply various filters to the data. 1 2 2 Insert an EMP-ID slicer to the Pivottable. Position the slicer so that the top-left corner is in cell G3 and then drag the bottom edge to adjust the height so that the extra while space is no konger visible. Do not drag it so far that you see a scroll bar on the right side. Modify the Header Caption of the slicer to bx Employee and apply the White, Slicer Style Other 2. Use the slicer so that only the records for EMP-00024 are showing As new transactions are recorded in the SalesData table. the Pivot Table can be easily refreshed to incorporate the new records into the analysis Add the following data to row 25 of the SalesDala table: Transio POOD121 TransDate 01/01/2023 EMP-ID EMP-00024 Itemid T822248 Payment Type Cash Quantity 3 Goosa Revenue 98.85 CubMerker? Yes Coupon Cash Disc NotRows.us 98.85 TotalDiscounts D Refresh the PotTable so that the new transaction is included in the Analysis and then clear all PivotTable filters. Create a drill-down of the December, 2022 Apple Pay transactions onto a new worksheet and name the worksheel ArpleRay Transactions, 9 ElvatChaca.can add a visual component to your analysis with options to filter specific records. Create a PivotChart based on the data in the Sales Data table. Start the analysis on a new worksheet, renamed to be RexsBy Payment Type The PivolChart shouki be a Pie Chart that shows the proportion of Ne! Revenue from each Payment Type , Use Years as the Filter so that you can see the data for any given year, Move the Pie Chart to its own worksheet named Roxeaway.TypePlusChart Edit the chart title to be proportion of Revenue by Payment Type Use the Years Filter to only show transactions from 2022. 10 Save and close Excel_Choc_Prepare_SalesAnalysis.xlsx. Exit Excel Submit the file as directed. Total Points 10 Page 3 of 3 1116 words LE English (United States) Focus + 130% AutoSave OFF A OO -- Toss_Excel_Ch06_Prepare_SalesAnalysis Home Insert Draw Page Layout Formulas Data Review View Tell me Share Comments Times New Roman v 18 v P A y a Wraa Text Insert y General v X [G AYO 0 Analyze Data Paste = = = - - Merge & Center $ % Delete Format Conditional Format Cell Formatting as Table Styles Sensitvity Sort Filter v y Find & Select BIU A x fx Pro Shop Sales Database A1 T A B D E F H 1 1 1 M N 0 P P Q R 5 U w Pro Shop Sales Database 3 Transio 1 P000100 S PO30101 6 POD0202 7 P000103 8 PO30104 9 PO00105 10 P000105 11 P030107 12 PO90209 13 P030109 14 00:10 15 POO011 16 P000112 17 P00:13 18 PO00.14 19 P030115 20 PO30:15 21 FO00217 22 PO30118 23 PO30:19 24 PO00120 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 10 41 42 43 TransDate EMP-ID 10/3/22 EMP-30076 103/22 EMPODOBE 10/2/22 EMP-00024 10/5/22 EMP-30015 10/6/22 EM 00025 10/12/22 LMP-9007G 10/16/22 EMP-3003B 10/22/22 EM 20024 10/25/22 EMP-90015 11/2/22 EMP-00026 11/7/22 EMP 30076 11/10/22 EMP 30038 11/13/22 EMP-00024 11/23/22 EMP 30015 12/13/22 EMP 30026 12/14/22 EMP-00076 Art 12/16,22 EMPODOS 12/12/22 EMP-00024 12/24/22 EMP-90015 12/29/22 EMP 30025 12/31/22 LMP-90076 Item ID C881P23 T822248 F232P37 C583K2S F232727 +375P67 X740029 7981011 C994P23 X487P18 R483724 RA X349R3S F83319 R239157 C583K2S F375P67 X497P4 18.33K10 F232837 X49748 PaymentType Ouantity GrossRevenue Club Member? Coupon CashDisc NetRevenue TotalDiscounts 3 5 171.00 N 15% 56 S 136 83 S 34.20 Cash 2 S 65.90 No $ 55.99 $ Apple Pay 1 S 129.00 Y S 119.97 $ 9.03 Apple Pay 34 177.00 No S 177.00 S Credit 3 $ 387.00 No 15% 56 $ 309.60 $ 77.40 Cash 3 S 46.35 Yes 15% $ 36.15 20.20 Apple Pay 15 453.00 Yes S 421 29 5 31.71 Credit 1 S 12.00 No $ 12.03 $ Cash 2 $ 124.00 No 54 S 108.30 S 5.70 Credit 3 5 1,257.00 No 10% 5 1,131 30 S 125.70 Apple Pay 1 S 2.00 No 10% $ 1.83 $ 0.20 wa Card 1 $ 2.00 Y 13.5 S 1.56 $ 0.44 Credit 35 119.85 No 5% 5 142.35 S 7.49 Apple Pay 3 S 17.85 No 5% $ 16.95 $ 0.89 . Card 2 $ 194.00 No $ 164.99 $ 29.10 Card 15 59.00 Ne S 59.00 5 Cash 3 S 45.35 No $ 46.35 S Apple Pay 2 $ 938.00 YS $ 779.34 $ 58.66 Apple Pay 45 23.80 No 5 23.80 S Card 1$ 129.00 Yes 5% $ 113.52 $ 15.48 Apple Pay 2 $ 938.00 No S 838.03 S 1506 Sales Dala Dalabase Tolals + Ready ### - 100% AutoSave FFA OFCO - Toss_Excel_Ch06_Prepare_SalesAnalysis Home Insert Draw Page Layout Formulas Data Review View Tell me Share Comments v 11 Calibrl (Cody Insert v v P A y a Wras Text General X LE v HD 47 Paste B 1 U A = = = Merge & Center Delete Format Find & Conditional Format Cell Formatting as Table Styles Y Analyze Sensitvity Sort Filter v v Select Data A1 + x fx A B c D E F G H 1 K M N 0 P 1 2 3 4 5 Field 2 Sum 8 Average 9 Count 10 Max 11 Min 12 13 14 15 16 17 18 19 20 2: 22 23 24 25 26 27 28 23 30 32 33 34. 35 36 37 38 35 40 11 42 43 14 Sales Dalas Database Totals + Ready E- + 100%
