In this project, analyze cosmetic product sales data and use what-if analysis to determine the commission potential and sales goal. Skills needed: Converting Data into Tables (Skill 5.11) Adding Total Rows to Tables (Skill 5.13) Removing Duplicate Rows from Tables (Skill 5.14) Filtering Data (Skill 5.16) Sorting Data (Skill 5.15) Inserting a Line Chart (Skill 5.3) Filtering Chart Data (Skill 5.9) Resizing and Moving Charts (Skill 5.4) Filtering Table Data with Slicers (Skill 5.17) Creating PivotTables Using Recommended PivotTables (Skill 5.19) Creating a PivotChart from a PivotTable (Skill 5.20) Showing and Hiding Chart Elements (Skill 5.6) Inserting Sparklines (Skill 5.10) Analyzing Data with Data Tables (Skill 5.21) Analyzing Data with Goal Seek (Skill 5.22) 3a.) Convert the cosmetic product sales data in the ProductSales worksheet into a table. Use the Red, Table Style Medium 10 table style. It is the third option in the second row in the Medium section of the gallery. 3b.) Add a Total row to the table to display the total for the Sale Total column. 3c.) In the Total row, display the average for the No. of Units and the Price Per Unit columns. 3d.) Delete any rows in the table that have duplicate data in all the columns. There are four. 4.) Filter to show just the sales for the Maryland region. 5.) Sort the table by order date with the newest orders first. 6a.) Insert a line chart to show the total sale amount for each order by order date. 6b.) Create a line chart using just the data from the Order Date and Sale Total columns. Be sure to include the header row when selecting the data for the chart. 6c.) There was an ordering glitch on August 31 that caused a spike in sales. Apply a filter to the chart to hide orders from that date. Notice the effect on the chart. 6d.) If necessary, move the chart so it does not cover the table data. 7.) Add a slicer to the table so you can filter by product name. Use the slicer to display data for the Fantastic Lash Mascara product only. Notice the effect on the chart. 8a.) Make a PivotTable from the data in the ProductSales worksheet. Use the Sum of Price Per Unit by Region recommended PivotTable. 8b.) Modify the PivotTable so the Price Per Unit data are averaged, not totaled. 8c.) Add the Product field to the PivotTable. It should appear in the Rows section below the Region field. 8d.) Format all the values in the PivotTable using the Accounting Number Format. 9a.) Make a clustered bar PivotChart based on the PivotTable data. Use the first recommended bar chart type. 9b.) Hide the chart title and legend. 9c.) If necessary, move the PivotChart on the worksheet so it does not cover the PivotChart data. 10.) Go to the By Region worksheet and add Line Sparklines in F3:F7 for data in columns B3:E7. 11a) On the By Region worksheet, create a doughnut chart to display the sales by region for the Bodacious Body Oil product. 11b) Display the data labels as data callouts. 11c) Hide the chart legend. 11d) If necessary, move the chart so it does not cover the table data. 12.) You have been told that you will receive a commission between 5 and 10 percent. On the Commission sheet, make a one-variable data table using cells A4:B14 to determine how much that commission may be based on $15,000 in sales. The column input cell is A4. 13.) You have a $5,000 balance on your credit card and would like to pay it all off with your commissions. Use Goal Seek to determine the amount you must sell (cell G5) in order for cell G3 (your commission) to equal $5,000 so you can fully pay off your credit card. Accept the Goal Seek solution.