Creating a pivot-table for the Excel Marketing Ethics Case:

Open the Excel Data File, create the headings requested, compute & sort for information requested use Videos posted, then highlight the entire table:

Scenario: You are part of the top management team at a large car manufacturing company. The recent year has been good for your company as the profit has been improving greatly in the past few years and the team has decided to embark on a growth path. The team has decided on the following options:

Introduce the next generation model for one of the car models.

Increase marketing on low sales area.

To arrive at the best answer to this important decision, top management has asked the information resource department to collect aggregate sales from the past three years showing year, market, car type, unit sold in each, and average unit profit (use Winter 2023 Excel Assessment.xlsx for details of the data collected).

Now that you have the data, it is important for the top management team to determine the following:

1 - The Total Profit for each year. 2 - The Total Profit for each major market 3 - The Total Profit for each car type

However, Total Profit is not part of the data provided by your information resource department. Hence your team will have to compute this data.

Assignment:

Part 1: based on the excel data provided:

o Make Total Profits column and compute What formula did you use? _________

o "C" column combines geographic location and market, separate these by filtering this data:

H column label "Geographic";

I column label "Market"

Filter the information for these new created columns (note the formula used for this

step)

Part 2: with the new filtered information, make a Pivot table that will compute the following:

o The Total Profit for each year. o The Total Profit for each major market. o The Total Profit for each car type. o Once you make the Pivot Table

Year	Area	Car Type	Unit Sold	Average Unit Profit						Area	Location
2014	South-Suburb	SUV/MiniVan	5032	1249.80						Central-Metro	Central
2014	West Coast-Suburb	Van/Trucks	1668	1982.70						Central-Rural/Country	Central
2014	Central-Metro	Sedan/Compact	7988	670.50						Central-Suburb	Central
2014	Central-Suburb	SUV/MiniVan	5062	1222.30						Northeast-Metro	Northeast
2014	Northeast-Rural/Country	SUV/MiniVan	3353	1244.50						Northeast-Rural/Country	Northeast
2014	West Coast-Suburb	Sedan/Compact	7313	693.90						Northeast-Suburb	Northeast
2014	West Coast-Metro	Van/Trucks	1167	1532.90						South-Metro	South
2014	Central-Metro	SUV/MiniVan	4154	1098.50						South-Rural/Country	South
2014	Central-Rural/Country	SUV/MiniVan	3390	1242.30						South-Suburb	South
2014	Northeast-Suburb	Sedan/Compact	7357	681.10						West Coast-Metro	West Coast
2014	West Coast-Rural/Country	Van/Trucks	2387	2197.00						West Coast-Rural/Country	West Coast
2014	South-Suburb	Sedan/Compact	7328	677.30						West Coast-Suburb	West Coast
2014	West Coast-Metro	SUV/MiniVan	4172	1096.00
2014	Northeast-Metro	SUV/MiniVan	4171	1097.50
2014	South-Suburb	Van/Trucks	1561	1965.90
2014	Central-Rural/Country	Sedan/Compact	4006	632.20
2014	Central-Metro	Van/Trucks	1184	1544.00
2014	South-Metro	Sedan/Compact	7999	662.10
2014	South-Metro	SUV/MiniVan	4221	1114.70
2014	South-Rural/Country	Van/Trucks	2430	2207.90
2014	West Coast-Metro	Sedan/Compact	7995	654.60
2014	South-Metro	Van/Trucks	1183	1540.20
2014	Northeast-Metro	Van/Trucks	1196	1526.00
2014	Northeast-Rural/Country	Van/Trucks	2396	2211.60
2014	Northeast-Rural/Country	Sedan/Compact	3990	620.30
2014	Northeast-Metro	Sedan/Compact	8029	659.00
2014	Central-Rural/Country	Van/Trucks	2415	2186.10
2014	West Coast-Rural/Country	Sedan/Compact	4017	637.20
2014	Central-Suburb	Sedan/Compact	7357	690.00
2014	West Coast-Suburb	SUV/MiniVan	5039	1241.10
2014	Northeast-Suburb	Van/Trucks	1524	1966.90
2014	South-Rural/Country	SUV/MiniVan	3370	1233.20
2014	Northeast-Suburb	SUV/MiniVan	5005	1225.40
2014	Central-Suburb	Van/Trucks	1514	1978.10
2014	South-Rural/Country	Sedan/Compact	3982	621.60
2014	West Coast-Rural/Country	SUV/MiniVan	3368	1247.40
2015	Northeast-Metro	Van/Trucks	1349	1607.80
2015	West Coast-Suburb	Van/Trucks	1681	2071.60
2015	Central-Rural/Country	Sedan/Compact	4235	663.00
2015	West Coast-Rural/Country	Van/Trucks	2611	2305.00
2015	Northeast-Rural/Country	SUV/MiniVan	4026	1370.40
2015	West Coast-Rural/Country	Sedan/Compact	4211	659.60
2015	South-Rural/Country	Van/Trucks	2674	2303.30
2015	South-Suburb	SUV/MiniVan	6022	1370.00
2015	Central-Rural/Country	SUV/MiniVan	4012	1368.50
2015	South-Metro	Sedan/Compact	8505	679.10
2015	South-Suburb	Sedan/Compact	7805	693.60
2015	West Coast-Rural/Country	SUV/MiniVan	3955	1390.60
2015	Northeast-Suburb	Van/Trucks	1666	2057.00
2015	Northeast-Suburb	Sedan/Compact	7802	705.90
2015	Central-Rural/Country	Van/Trucks	2696	2294.50
2015	West Coast-Metro	Van/Trucks	1315	1614.50
2015	Central-Suburb	SUV/MiniVan	5969	1365.40
2015	West Coast-Metro	SUV/MiniVan	4957	1237.60
2015	Northeast-Metro	Sedan/Compact	8481	689.90
2015	Central-Metro	Sedan/Compact	8562	693.80
2015	Northeast-Suburb	SUV/MiniVan	6047	1377.80
2015	South-Rural/Country	SUV/MiniVan	3955	1377.70
2015	South-Metro	SUV/MiniVan	5024	1220.10
2015	Central-Suburb	Sedan/Compact	7830	719.20
2015	Northeast-Rural/Country	Van/Trucks	2617	2298.30
2015	Central-Metro	SUV/MiniVan	5006	1237.30
2015	West Coast-Suburb	SUV/MiniVan	6014	1554.80
2015	West Coast-Suburb	Sedan/Compact	7767	701.20
2015	Central-Suburb	Van/Trucks	1695	2060.20
2015	South-Rural/Country	Sedan/Compact	4291	658.70
2015	South-Suburb	Van/Trucks	1696	2058.80
2015	Northeast-Metro	SUV/MiniVan	5035	1236.90
2015	Northeast-Rural/Country	Sedan/Compact	4299	639.20
2015	Central-Metro	Van/Trucks	1314	1624.30
2015	South-Metro	Van/Trucks	1354	1607.10
2015	West Coast-Metro	Sedan/Compact	8514	689.00
2016	Northeast-Metro	Sedan/Compact	9111	718.30
2016	South-Metro	Sedan/Compact	9077	698.50
2016	West Coast-Metro	SUV/MiniVan	5827	1404.70
2016	South-Metro	SUV/MiniVan	5827	1405.70
2016	Central-Metro	Sedan/Compact	9065	724.50
2016	South-Suburb	SUV/MiniVan	7049	1571.60
2016	Northeast-Rural/Country	SUV/MiniVan	4696	1556.30
2016	South-Metro	Van/Trucks	1410	1676.50
2016	Central-Suburb	SUV/MiniVan	7034	1562.40
2016	South-Rural/Country	Sedan/Compact	4557	676.80
2016	West Coast-Rural/Country	Sedan/Compact	4536	672.90
2016	Central-Rural/Country	Sedan/Compact	4568	672.30
2016	Central-Rural/Country	Van/Trucks	2916	2407.80
2016	Northeast-Suburb	Sedan/Compact	8328	723.70
2016	Central-Suburb	Van/Trucks	1802	2148.80
2016	Northeast-Metro	Van/Trucks	1474	1670.30
2016	West Coast-Rural/Country	Van/Trucks	2861	2409.70
2016	Northeast-Rural/Country	Van/Trucks	2859	2386.30
2016	Northeast-Suburb	SUV/MiniVan	6990	1563.50
2016	South-Rural/Country	SUV/MiniVan	4685	1578.10
2016	Central-Metro	Van/Trucks	1447	1689.20
2016	West Coast-Suburb	Sedan/Compact	8331	726.20
2016	West Coast-Metro	Van/Trucks	1423	1685.80
2016	Northeast-Metro	SUV/MiniVan	5841	1402.60
2016	South-Suburb	Sedan/Compact	8383	739.00
2016	South-Suburb	Van/Trucks	1891	2173.90
2016	West Coast-Rural/Country	SUV/MiniVan	4682	1574.40
2016	Northeast-Suburb	Van/Trucks	1821	2148.90
2016	Central-Suburb	Sedan/Compact	8382	749.60
2016	West Coast-Suburb	Van/Trucks	1859	2169.00
2016	Northeast-Rural/Country	Sedan/Compact	4566	669.00
2016	Central-Metro	SUV/MiniVan	5839	1407.90
2016	West Coast-Metro	Sedan/Compact	9047	698.30
2016	West Coast-Suburb	SUV/MiniVan	6997	1571.00
2016	South-Rural/Country	Van/Trucks	2902	2411.40
2016	Central-Rural/Country	SUV/MiniVan	4624	1561.90

The following will pop-up: the range of the table will automatically pop up: Table range Sheetl!$B52:5G$44

Location: Sheetl!SK$2:SUS26