1. Read: Data Analysis: What, How, and Why to Do Data Analysis for Your Organization 2. Download this Salary Comparison Workbook & and review the worksheet(s) to answer the complete the following. On worksheet one we see a comparison of the same employees and what their earnings would have been over 12 pay periods. Using Microsoft Word... 1. Give at least one descriptive analysis of the data. 2. By inserting formulas as needed to help you draw conclusions and explain what you did to come to the conclusion. (For example: When summing the gross salaries for each section we see in this Option one would be... ... but if we change the base salary to...) Develop at least three conclusions using diagnostic analysis. 3. Offer at least one predictive analysis statement. 4. Give a prescriptive analysis which includes why. -Save and submit your Word document.Callbri (Body) v 11 A A E de Wrap Text Paste B I U HV CAV BEE Merge & Center v $ ~ % 9 78 Conditional Format Cell Sort & Find & Ideas Sensitivity Formatting as Table Styles Format v Filter Select A1 x v fx A B C D E F G H K L M N O OPTION 1: Salary + Static Commission Quarterly 2 Employee 4/2/2 4/9/2 4/16/21 4/23/21 4/30/21 5/7/21 5/14/21 5/21/21 $/28/21 6/4/21 6/11/21 6/18/21 Total Sales Commission Gross Salary John 53240 71477 79857 55525 12465 25167 50306 62545 34315 75290 96573 622,764.00 S 31,138 20 $ 35,938.20 James 35887 72898 98718 43823 85514 37839 17907 15894 52931 67756 70595 677,827.00 $ 33,891.35 $ 38,691.35 Karer $2284 19262 $4702 85055 26001 9279 82341 28471 8914 47892 10739 $ 588,692.00 $ 29,434.60 $ 34,234.60 David 56003 40030 79150 49855 79715 8986 41537 93991 35019 9207 37276 52758 $ 747,267 00 $ 37,363.95 $ 42,163.35 Sheryl 80789 24272 32788 70068 69732 5469 14013 21873 25577 40898 69740 28395 5 532,837.00 5 26,641 85 $ 31,441.85 Bud 61542 97010 59210 10046 41106 63961 72861 7459 53764 46672 80151 63710 $ 724,628.00 $ 36,231.40 $ 41,031 40 9 Total Sales S 339,745.00 5 330,949.00 $ 404,425.00 $ 314,372.00 5 314,533.00 S 364,318.00 $ 278,965.00 $ 298,898.00 $ 230,077.00 $ 411,839,00 $ 402,227.00 $ 203,667.00 10 11 12 Quarterly Commission 5% 13 Weekly Salary $400.00 14 15 16 17 18 OPTION 2: Salary + Dynamic Commission Quarterly 19 Employee 4/2/21 4/9/21 4/16/21 4/23/21 4/30/21 5/7/21 5/14/21 5/21/21 5/28/21 6/4/21 6/11/21 6/18/21 Total Sales Commission Gross Salary 20 John 53240 77477 79857 55525 12465 25167 50306 62545 $4319 75290 36573 O S 622, 764.00 $ 12.455.28 $ 17,255.28 21 James 35887 72898 9871 43823 85514 37835 17907 45894 52931 67756 10595 S 677,827.00 $ 33,891.35 $ 38,691.35 2.2 Kare 52284 19262 5470 85055 23 26001 92796 82341 28471 8914 17892 10739 $ 588,692.00 $ 5,886.92 5 10,686.92 David 56003 40030 79150 49855 79715 8986 41537 93991 35015 92074 37276 52758 $ 747,267.00 $ 67,254.03 $ 72,054.03 24 Sheryl 80789 24272 3278 70068 69732 54697 14013 21873 5577 4089 69740 28395 $ 532,837.00 $ 5,328.37 $ 10,128.37 25 Bud 61547 97010 59210 10046 41106 63961 72861 74595 53764 46672 80151 63710 $ 724,628.00 5 36,231.40 $ 41,031.40 26 Total Sales 339,745.00 $ 330,949.00 S 404,425.00 $ 314,372.00 $ 27 314,533.00 $ 364,318,00 $ 278 965.00 S 298,898.00 $ 230,077.00 $ 411,839.00 5 402.227.00 S 203,667.00 28 29 Quarterly Sales Commissions 30 Over $725,000 0.09 31 $675,000-5724,999 0.05 32 $600,00 -$674,000 0.02 33 Under $600,000 0.01 34 5 Weekly Salary 400,00 36 37 38
