Question: 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

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

Step by Step Solution

There are 3 Steps involved in it

1 Expert Approved Answer
Step: 1 Unlock blur-text-image
Question Has Been Solved by an Expert!

Get step-by-step solutions from verified subject matter experts

Step: 2 Unlock
Step: 3 Unlock

Students Have Also Explored These Related Mathematics Questions!

Q:

Read Chapters 1,2,4,7 and Write a 800 - 1000 word Reflection Paper Grading : ?Thoughtfulness?Reactions,personal experiences,criticisms, etc. ?Application to your futureprofessional(and personal)life...

Q:

I'm an undergrad accounting student in an introduction to forensic accounting course.I need help getting started on a final project for this class over a fictitious company called the Grand Teton...

Q:

The resulting bar chart shows that when HMK is the AR Clerk and FKL is the Cash Receipts Clerk, CT is the GL Accounting Clerk for $226,851 of current AR balances. However, there are $25,352 of...

Q:

P L U G - I N T3 Problem Solving Using Excel 2010 LEARN IN G OUT C O ME S 1. 2. 3. 4. 5. Describe how to create and sort a list using Excel. Explain why you would use conditional formatting using...

Q:

BATCH 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67...

Q:

I have this Accounts assignment that needs to be done. Need help on that Statement of individual work Individual work required This project is designed to assure that all students, individually, have...

Q:

Hi, I'm having an issue with this Simnet project and I was wondering if anyone could help me. There is only one issue: Step 3a, in the instruction pdf that I have attached. When my project is graded...

Q:

43 AaBbCcDc AaBbc AaBbCc) AaBbCcDd AaBbCcDd Emphasis Heading 1 Heading 2 11 Normal 1 Normal ... Paragraph Styles Instructions Points Possible exercise begins on page 343 of your text. Start Excel....

Q:

The resulting bar chart shows that when HMK is the AR Clerk and FKL is the Cash Receipts Clerk, CT is the GL Accounting Clerk for $226,851 of current AR balances. However, there are $25,352 of...

Q:

The resulting bar chart shows that when HMK is the AR Clerk and FKL is the Cash Receipts Clerk, CT is the GL Accounting Clerk for $226,851 of current AR balances. However, there are $25,352 of...

Q:

Refer to the facts in Exercises 11-39 and 11-40. In Exercise 11-39, Douglas manufactures four grades of lubricant, W-10, W-20, W-30, and W-40, from a joint process. Additional information follows: In...

Q:

A stock has a beta of 0.75 and the standard deviation of its returns is 25%. The market return is 15% and the risk free rate is 4%. 1. what is the expected return for stock 2. what are the expected...

Q:

Find the equation o f the plane that passes through the points ( 1 , 2 , 3 ) , ( 0 , - 2 , 6 ) , and ( - 1 , - 2 , 0 ) . Make sure you submit a clear, complete, and detailed solution. I will take...

Q:

The homogeneous rectangular plate weighs 40 lb and is supported in the vertical plane by the light parallel links shown. If a couple M = 80 lb-ft is applied to the end of link AB with the system...

Q:

Catherine and Ana wanted to know if student athletes (students on at least one varsity team) have faster reaction times than non-athletes. They took separate random samples of 33 athletes and 30...

Q:

Given the unity feedback system of Figure P6.3 with find the following: a. The range of K that keeps the system stable b. The value of K that makes the system oscillate c. The frequency of...

Q:

Repeat Problem 33 for Data From Problem 33: Given the unity feedback system of Figure P6.3 with find the following: a. The range of K that keeps the system stable b. The value of K that makes the...

Q:

a. Sketch the graph of y = |3 + 5x|, showing the coordinates of the points where your graph meets the coordinate axes. b. Solve the equation |3 + 5x| = 2.

Q:

Design a minimum-mass symmetric three-bar truss (the area of member 1 and that of member 3 are the same) to support a load P, as was shown in Fig. 2.9. The following notation may be used: Pu = P cos...

Q:

Economic predictions. An investment newsletter makes general predictions about the economy to help their clients make sound investment decisions. a) Recently they said that because the stock market...

Q:

Airline safety. Even though commercial airlines have excellent safety records, in the weeks following a crash, airlines often report a drop in the number of passengers, probably because people are...

Q:

Fire insurance. Insurance companies collect annual payments from homeowners in exchange for paying to rebuild houses that burn down. a) Why should you be reluctant to accept a $3000 payment from your...