Using EXCEL, not SPSS please: (show formulas)

The research firm LL Research collected data from 200 client businesses. They want to determine how the businesses compare among four variables:

2015 Profit in millions of dollars

2016 Profit in millions of dollars

2015-2016 Two-Year Change in Daily Average Customer Visits

Two-Year Average Number of Employees

Data collected for the sample of 200 businesses is contained in the file namedBusinesses,linked at the bottom of the page. Use all 200 data points.

Managerial Report

Prepare a report (see Module folder) using the numerical methods of descriptive statistics presented in this module to learn how each of the variables contributes to the success of a client business. Be sure to include the following three items in your report.

1. Find descriptive sample statistics (mean, median, two quartiles Q1 and Q3, minimum, maximum, range, sample standard deviation, and coefficient of variation) for each of the four variables along with an explanation of what the descriptive statistics tell us about the client businesses.
2. Compute the percent change in profit from 2015 to 2016 for each business. Then use the z-score to determine which businesses were outliers with respect to percent change in profit.
3. Compute the sample correlation coefficient, showing the relationship between percent change in profit and each of the other two variables (2015-2016 Two-Year Change in Daily Average Customer Visits and Two-Year Average Number of Employees). Explain what the correlation coefficients tell us about the three pairs of relationships. Use tables, charts, or graphs to support your conclusions.

Number Business 2015 Profit 2016 Profit Two Year avg. change in daily no. of visits Two Year Average Number of Employees 1 B Phillips 4436 8108 37 100 2 Barr & Edwin Co. 7452 9542 82 71 3 Lacrosse Archery 2564 2125 6 41 4 Global Fleet Sales 4547 5219 40 56 5 ALLSTATE ATHLETIC SUPPLY 8028 6160 85 79 6 ELECTROPOWER UTILITY SALES 1313 5074 -7 31 7 Bergner's 6843 7211 71 97 8 BESLEY'S 4280 7775 34 54 9 Dave Ranney & Assoc 4721 9674 40 89 10 RDI Technologies 3603 4881 17 37 11 Rocky MTN HMO - Info. & Sales 2701 7172 7 63 12 Mid-South Sweeper Supply; Inc 3119 8327 12 85 13 WestLake Supply 4308 6516 36 43 14 Old Town Artisans Galleries 4550 2305 40 48 15 Client Services Guy 5267 8804 46 32 16 Bill Reed 8973 4747 93 86 17 Game Fair Ltd 7366 2629 80 96 18 Air Supply Systems; Inc 5905 7186 54 38 19 The Webmaster 3701 9466 20 44 20 First Supply Group; Inc 1180 7572 -8 26 21 Comfyco 6980 930 73 95 22 Telson; Inc 2117 2414 4 66 23 One Plus Two 1219 1108 -8 83 24 FlowTech Supply 2817 5635 9 91 25 Brewer Business Supply 3142 2651 14 44 26 King Plaza-Management Lsng 2533 3784 6 47 27 Sales Recruiting Specialist 7021 9522 75 62 28 Lenty Sales; Inc. 1654 1841 -3 21 29 Vipbrasil 7713 6069 83 74 30 Empire State Sales Inc 6244 1063 57 26 31 Sunday Sales 8838 2851 91 26 32 Spa City Silver 5777 3058 51 24 33 Cps 4504 6606 39 65 34 WSA Sales Co. Inc 1990 6252 -2 65 35 Sun State Sales 6541 5904 67 58 36 KWICK SUPPLY INC 4541 8953 39 67 37 Power Solutions Inc 5662 5125 50 71 38 Reece & Nichols Wyandotte 9286 1158 94 42 39 Harmony Ball 5414 2769 48 98 40 Eagle Flight Safety Products 5505 4364 49 66 41 Team Vehicle Sales 3444 4977 14 50 42 Papaya Clothing 3703 4059 20 39 43 Daniels & Fry Motor Company 5009 3286 44 29 44 Home Base Solutions 8229 9303 87 50 45 SAFsystems 4800 4999 42 69 46 Robert Rytter & Associates 8424 3258 89 79 47 Arbor Consulting 6488 1039 62 30 48 DEANGELO MACHINERY SALES 6287 5524 58 44 49 JDG Sales And Service 3844 7146 25 74 50 MOSS 6730 8194 71 72 51 Sonya C. Atwell 5695 7747 51 47 52 Sentry Network Service; Inc. 3579 6245 17 39 53 SUMMIT FINANCIAL GROUP 1737 2498 -3 57 54 Lenexa Office Supply 7001 4820 74 35 55 Cyberzone 6699 8272 69 38 56 Monarch Rep Sales Inc 8868 6316 92 21 57 Eddiechamberland 7114 7116 76 63 58 EXCEL PRODUCTS 5239 7248 45 70 59 Samco Sales 6541 2355 67 49 60 Lajobi Sales; Inc 4484 4466 38 74 61 ECS Anchor Supply 8655 3577 91 100 62 Tiger Sales 4278 7425 32 39 63 Auto Graphics Sales 9678 2829 97 39 64 Algin Technology LLC 4440 3301 38 51 65 Triple T Sales 3687 6882 19 89 66 Georges Miret Salon 8906 1063 93 35 67 NICHOLSON; WILLIAM 2991 3746 11 66 68 Sears Grand 992 8233 -10 57 69 Active Fitness And Aerobics 4106 9366 29 93 70 Crosstech 3527 9840 16 69 71 Druglaunch 8190 5866 86 58 72 Brian Sullivan Enterprises 6966 1888 72 56 73 PC SALES 9268 6999 94 77 74 Starr Gennett Foundation 6132 9766 55 62 75 KCI Sales 8134 9845 85 60 76 Mapleshade Lane E 2595 962 6 26 77 Rod Dow 3136 5738 14 95 78 CC Industrial Sales 4301 9571 36 20 79 Southwest Distributing; Inc. 5513 7564 49 54 80 Robert M. Wells 1541 2515 -5 83 81 United Sales & Services; Inc 8249 4299 88 25 82 NO. Power Plant 6137 5846 55 54 83 BREWARES INC 9936 5651 100 32 84 Closeouts Attic 6720 8230 69 40 85 AGEE Sales; Inc. 6253 8833 57 80 86 Jason Golden 5564 5855 50 94 87 Shepherd Sales 2178 6106 4 22 88 John C Weigand 6376 8755 60 32 89 Discount Silks 7664 9634 83 48 90 Ban-Koe Systems Inc. 5335 1975 47 96 91 Shamrock Computer 1124 7099 -9 95 92 Margaret Lucas 4869 9725 43 40 93 Shafer Valve 7368 2085 80 96 94 RTG INDUSTRIAL SUPPLY; INC 1553 4908 -4 64 95 American Supply Group 2185 8449 4 51 96 Mr. Spindle 2045 7917 1 36 97 Jones Services Supply Co. 6135 6843 55 79 98 Factory Direct Craft Supply 9943 1268 100 70 99 Metro Sandwiches & Salads 2306 5727 4 66 100 Global Industrial Sales 9843 5622 99 32 101 Fun Yards 5939 1321 54 48 102 Taylor Supply 6882 6040 72 95 103 Facility Robotics; Inc 2630 1653 6 63 104 Tele-Sales Consultants; Inc 1309 8385 -8 68 105 Hometown Paintball Supply Inc. 4131 6619 30 77 106 Bales Hay Sales 5335 4943 48 39 107 Zero systems 1588 8446 -3 93 108 Agm 9655 7249 97 65 109 Simcha Sales 4806 3536 42 71 110 Pauls Motor Sales And Service 6187 9957 56 100 111 Drillers Supply 7384 1685 81 35 112 Sharper Concepts; Inc 2728 1360 7 80 113 Pat Tomek 4812 1312 43 66 114 Mbh Sales And Service 9471 3405 96 52 115 Howard Sales Co.; Inc 1488 6142 -7 23 116 SELECT SALES; INC. 3246 3549 14 24 117 John Garesche 7462 5052 82 68 118 TOP SALES COMPANY; INC 8551 3427 89 67 119 On Angels Wings 2599 2740 6 23 120 WebStar Designs 7521 7907 82 40 121 Lone Pine Sales 7937 5991 85 50 122 Associated Supply Co 9202 7112 94 95 123 John Cody 7230 4503 79 92 124 Barton Mattress Sales 6399 3312 61 71 125 InternetNow 3675 9576 19 70 126 Corrosion Guard 6536 8512 63 40 127 Comet Website Consultants 2464 4501 5 26 128 MCMAHON; JOHN 4084 5773 28 59 129 Alan Warren 7540 5068 83 83 130 ROV Supply CO 2371 4610 4 49 131 Wilrock Sales Corp 6361 7557 60 75 132 Lowe Co Motion Inc. 3509 6616 16 80 133 Daley Plastering 7675 5536 83 86 134 Walter Drake Inc 8011 2665 85 68 135 PhilipsBusiness Solutions 8417 2808 88 88 136 Bacik Karpinski Associates Inc 7190 8807 77 41 137 Jeff Munchick Sales 5499 5077 49 23 138 GEO-THERMAL SALES; INC 6720 7404 70 99 139 Sprinkler Supply Company 7089 5822 60 81 140 Rol-Tru Putting Greens 8124 7440 77 83 141 Energy Mgmt Systems 8271 7978 84 85 142 B & D Sales 9050 8048 91 85 143 Tech-Tran Corp. 6312 3887 -5 72 144 Judy Rees 1360 1123 -60 61 145 Jenskates 2041 1606 -50 62 146 Custom Filter Supply; Inc 2329 1866 -50 63 147 Fiduciary Trust Co. Intl 9176 8072 95 86 148 The Office Supply Store; Inc. 9430 8961 97 87 149 Char Sakamoto Ishii Lum Ching 8001 6363 61 81 150 Otto Wolff U.S. Sales Corp 8068 7261 66 83 151 Bailey Slipper Shop 6432 4169 50 77 152 Megaidea; Inc 6608 4956 50 77 153 Jeff Branch Motor Sales 6866 4998 52 77 154 Aeropostale Inc 5316 1953 -50 64 155 High Tides 5758 3857 -9 66 156 Foley's Department Store 6964 5252 52 78 157 Gornet Technologies 9712 9608 98 92 158 Brinton Vet Supply 9957 9965 99 93 159 The Jones Shop 2599 2740 6 23 160 WOODSTOCK QUILT CO.; INC 8427 7816 89 79 161 KWB SALES 4228 2742 32 52 162 Efficient Lighting Sales Co. 7381 7066 81 37 163 Filene'S 3913 1176 27 71 164 Gemini Technical Sales 5827 9978 53 75 165 Walter Long 900 1584 21 97 166 Cloutier Property Management 9566 6887 97 78 167 ATMCo 7180 2327 76 67 168 Mark Maggiacoma 9973 8707 101 79 169 Global Granite 7608 8684 83 39 170 NuHS 5337 2539 48 26 171 JTC Express Office Supply 6751 6204 71 81 172 Inductive Technologies; LLC 7701 9342 83 41 173 Florida Villa Vacations Inc 1917 9450 -2 77 174 Misty Mountain Sales 5012 2294 44 29 175 Outpost 6640 4570 67 72 176 Dollar All 7879 5010 83 71 177 Clinical Laboratory Standard 7546 1229 83 20 178 AdultPaths.com 9627 3445 97 79 179 Ship Supply Of Florida; Inc. 4039 1385 27 68 180 M Hoffman Enterprises Inc. 3068 2896 11 45 181 Worldscale Association 9070 7770 94 34 182 Dream Weavers Network 9401 7552 95 53 183 Mr. Trademark Inc. 3754 4761 24 90 184 Jefferson Financial 3730 5878 21 78 185 P J P Sales 7900 6278 84 81 186 Mylon C. Jacobs Supply 2911 4628 11 86 187 Terry Smith 1569 2078 -3 60 188 Smith Supply 3337 5390 14 24 189 Complete Police Supply Limited 2342 5262 4 94 190 Design Build Concepts 8181 7472 86 34 191 Bon Voyage Travel 8702 3493 91 66 192 Diverse Technologies Corporation 9537 3047 97 59 193 Magic Dollar 3503 2386 16 65 194 Syracuse Castings Sales 8596 4174 91 81 195 McCormick Sales And Service 6196 2909 56 49 196 Zecharya Sales & Services;Inc 9167 1727 94 45 197 Marinette Co. Abstract & Land 6257 3575 57 78 198 BoKip Sales 4183 1880 30 85 199 Mark's Sales 7135 6928 76 39 200 Pete Reilly Consultants 4992 5412 43 55