The MakeupSales.xlsx file gives sales data for a small makeup company. Each row lists the salesperson, product sold, location of the sale, units sold, and revenue generated. Use this file to perform the following exercises:

a. Create a combination chart involving a column and line graph with a secondary axis to chart the unit sales and dollar sales by location (east, Midwest, south, west). (Hint: first you have to use pivot table to get the sales by location. Then, create combination chart on that new table).

b. Use check boxes to set up a chart in which the user can choose which series are charted for Products eye liner, foundation, lip gloss, lipstick, mascara. (Hint: first you have to use pivot table to get the sales dollars by product. Then, create checkboxes, reference table, and chart on that new table).

c. Report descriptive statistics and correlation tables for product sales by each sales person (Hint: Summarize the total revenue sold by each person of each product using Pivot table - you did this in the assignment #1. Then, compute descriptive statistics and correlations using data analysis tool)

NOTE: I CANNOT ATTACH SPREADSHEET AS IT IS 1892 ROWS LONG

Please explain to me HOW I would set up the pivot table in order to make the combination chart/check boxes.

I attached a screenshot of the first few lines of the excel doc for context.

Where do I put everything on here in order to make the proper combination chart?

A B D E F G H 1 J Transaction_number Date Salesperson 1 Betsy 2 Hallagan 3 Ashley 4 Hallagan 5 Zaret 6 Colleen 7 Cristina 8 Colleen 9 Ashley 10 Betsy 11 Ashley 12 Ashley 13 Emilee 14 Hallagan 15 Zaret 16 Emilee 17 Colleen 18 Jen Product Units 4/1/04 lip gloss 3/10/04 foundation 2/25/05 lipstick 5/22/06 lip gloss 6/17/04 lip gloss 11/27/05 eyeliner 3/21/04 eyeliner 12/17/06 lip gloss 7/5/06 eye liner 8/7/06 lip gloss 11/29/04 mascara 11/18/04 lip gloss 8/31/05 lip gloss 1/1/05 eye liner 9/20/06 foundation 4/12/04 mascara 4/30/06 mascara 8/31/05 lip gloss 10/27/04 eye liner Dollars Location 45 137.2046 south 50 152.0073 midwest 9 28.71948 midwest 55 167.0753 west 43 130.6029 midwest 58 175.991 midwest 8 25.80069 midwest 72 217.8397 midwest 75 226.6423 south 24 73.50234 east 43 130.8354 east 23 71.03437 west 49 149.5928 west 18 56.472 south -8 -21.993 east 45 137.3904 east 66 199.6543 south 88 265.1876 midwest 78 236.147 south 19 Jen Pivot Table Fields FIELD NAME Q Search fields Transaction_number Salesperson DOOOO Date Product Units Filters III Columns E Rows Values