Homework 2: Using the given pivot table provided 8. Create a pivot table to show all clients by region, ownership and annual income with the sum of their total purchases and the average number of their purchases. Then answer following questions (Note: you will have to do and show more table): calculations in the pivot (a) Which region has the renters with the highest annual income and the largest average (b) Which region has the owners with the lowest annual income and the smallest (c) Which region and ownership (Rent or Own) have the clients whose sum of total number of purchases. average number of purchases. purchases is closest to the median of the sum of the total purchases for all regions and ownerships? (d) In which regions do renters have a higher percentage of the sum of total purchases than owners? You must show all work and highlight each question and answer in your spreadsheet or you will lose points. You may put the answers on the right of your pivot table. Hints to assist you with the assignments above: 8a. You need to compare the same "highest annual income" category in each region and determine which region and owners has the largest average number of purchases. No calculation needed, just a visual determination. 8b. You need to compare the same "lowest annual income" category in each region and determine which region and owners has the smallest average number of purchases. No calculation needed, just a visual determination 8c. Steps: 1. Calculate the median for the sum of the total purchases for all region and ownerships. This means that you will have 8 values for the arguments in your median calculation (Two from each region for the total purchases). The median colculation would have only one answer 2. Compare each of the 8 values from 1 (mathematically) to see which one is the closest to the median 8d. Steps: within a region. The sum of the percentage within a region must equal 100. You will have 8 values (2 per region) 1. Calc ulate the percentage of the sum of the total purchases for each owner and renter 2. Compare to see which region and own/rent answers the question REGION-ANNUAL INCOME-Data 13 Sum of TOTAL PURCHASES 35 Sum of TOTAL PURCHASES Average of NUM PURCHASES 191 830 Average of NUM PURCHASES 21548 053355556 1232 5 363636364 Average of NUM PURCHASES 5588235294 100 Sum of TOTAL PURCHASES Average of NUM PURCHASES East Sum of TOTAL PURCHASES East Average of NUM PURCHASES 5793 5 43902439 539 571428571 5545454545 595 10233 13 Sum of TOTAL PURCH 35 Sum of TOTAL PURCHASES 75 Sum of TOTAL PURCHASES North Average of NUM PURCHASES erage of NUM PURCHASES age of NUM PURCHASES Average of NUM PURCHASES 5 285714206 5 878787879 5 61224488 5 862068968 142857143 100 Sum of TOTAL PURCHASES 5 346153846 5 9130434781092 591 823529412 North Average of NUM PURCHASE 841269841 South 13 Sum of TOTAL PURCHASES 6.416666687 936 6 789473684 2216 6 433333333 Average of NUM PURCHASES 35 Sum of TOTAL PURCHASES 7 142857143 verage of NUM PURCHASES 75 Sum of TOTAL PURCHASES Average of NUM PURCHASES 910 5.923076923 529411765 5.133333333 5 526315789 5.928571429 100 Sum of TOTAL PURCHASES Average of NUM PURCHASES South Sum of TOTAL PURCHASES South Average of NUM PURCHASES 3326 5 803921569 6 053829787 13 Sum of TOTAL PURCHASES Average of NUM PURCHASES 35 Sum of TOTAL PURCHASES Average of NUM PURCHASES 75 Sum of TOTAL PURCHASES Average of NUM PURCHASES 100 Sum of TOTAL PURCHASES Average of NUM PURCHASES West 4 333333333 5 375 2709 5.375 3259 5.758097551 1576 1649 66 6.083333333 West Average of NUM PURCHASES 5.74137931 Total Average of NUM PURCHASES 480916035 15082 5691991786 Homework 2: Using the given pivot table provided 8. Create a pivot table to show all clients by region, ownership and annual income with the sum of their total purchases and the average number of their purchases. Then answer following questions (Note: you will have to do and show more table): calculations in the pivot (a) Which region has the renters with the highest annual income and the largest average (b) Which region has the owners with the lowest annual income and the smallest (c) Which region and ownership (Rent or Own) have the clients whose sum of total number of purchases. average number of purchases. purchases is closest to the median of the sum of the total purchases for all regions and ownerships? (d) In which regions do renters have a higher percentage of the sum of total purchases than owners? You must show all work and highlight each question and answer in your spreadsheet or you will lose points. You may put the answers on the right of your pivot table. Hints to assist you with the assignments above: 8a. You need to compare the same "highest annual income" category in each region and determine which region and owners has the largest average number of purchases. No calculation needed, just a visual determination. 8b. You need to compare the same "lowest annual income" category in each region and determine which region and owners has the smallest average number of purchases. No calculation needed, just a visual determination 8c. Steps: 1. Calculate the median for the sum of the total purchases for all region and ownerships. This means that you will have 8 values for the arguments in your median calculation (Two from each region for the total purchases). The median colculation would have only one answer 2. Compare each of the 8 values from 1 (mathematically) to see which one is the closest to the median 8d. Steps: within a region. The sum of the percentage within a region must equal 100. You will have 8 values (2 per region) 1. Calc ulate the percentage of the sum of the total purchases for each owner and renter 2. Compare to see which region and own/rent answers the question REGION-ANNUAL INCOME-Data 13 Sum of TOTAL PURCHASES 35 Sum of TOTAL PURCHASES Average of NUM PURCHASES 191 830 Average of NUM PURCHASES 21548 053355556 1232 5 363636364 Average of NUM PURCHASES 5588235294 100 Sum of TOTAL PURCHASES Average of NUM PURCHASES East Sum of TOTAL PURCHASES East Average of NUM PURCHASES 5793 5 43902439 539 571428571 5545454545 595 10233 13 Sum of TOTAL PURCH 35 Sum of TOTAL PURCHASES 75 Sum of TOTAL PURCHASES North Average of NUM PURCHASES erage of NUM PURCHASES age of NUM PURCHASES Average of NUM PURCHASES 5 285714206 5 878787879 5 61224488 5 862068968 142857143 100 Sum of TOTAL PURCHASES 5 346153846 5 9130434781092 591 823529412 North Average of NUM PURCHASE 841269841 South 13 Sum of TOTAL PURCHASES 6.416666687 936 6 789473684 2216 6 433333333 Average of NUM PURCHASES 35 Sum of TOTAL PURCHASES 7 142857143 verage of NUM PURCHASES 75 Sum of TOTAL PURCHASES Average of NUM PURCHASES 910 5.923076923 529411765 5.133333333 5 526315789 5.928571429 100 Sum of TOTAL PURCHASES Average of NUM PURCHASES South Sum of TOTAL PURCHASES South Average of NUM PURCHASES 3326 5 803921569 6 053829787 13 Sum of TOTAL PURCHASES Average of NUM PURCHASES 35 Sum of TOTAL PURCHASES Average of NUM PURCHASES 75 Sum of TOTAL PURCHASES Average of NUM PURCHASES 100 Sum of TOTAL PURCHASES Average of NUM PURCHASES West 4 333333333 5 375 2709 5.375 3259 5.758097551 1576 1649 66 6.083333333 West Average of NUM PURCHASES 5.74137931 Total Average of NUM PURCHASES 480916035 15082 5691991786