interpret two-way tables for two categorical variables, visualize two-way tables using charts

Online tutoring services

Need help with this question or any other Business assignment help task? Click on the button below to to hire an expert cheap.

I will share the excel file my professor wants us to complete. It has 6 problems each with accompanying data sets used for the specific problem ie.(Problem #4, Data#4). I will also attach the professor's slides which can be used to help with what the answers to the questions should look like.

Get Help With a similar task to - interpret two-way tables for two categorical variables, visualize two-way tables using charts

Login to view and/or buy answers.. or post an answer
Additional Instructions:
Problem#4 Problem #4. Pivot Tables and Charts An online retailer recently ran a marketing experiment, called the “24-hour Flash Sale.” Data on all transactions made during a 24-hour period was collected. A total of 20,000 transactions were made on the day of the sale. A customer had to use a special discount code during checkout to take advantage of the Flash Sale. The special discount codes were emailed to a randomly selected group of customers. Transactions on the day of the experiment varied by the method of payment, whether the customer was emailed a discount code, whether the customer used the discount code, and the time elapsed since the customer made their last purchase prior to the Flash Sale. You are provided with the data for a random sample of transactions; see the accompanying data. Considering all the customers who used a discount code, did the majority use the EStore Card to make a payment? How about all the customers who did not use a discount code? To answer these questions: a) (10 points)Prepare a two-way table to display the percentage frequencies with respect to method of payment and use of discount code. Use Pivot Tables to prepare your table. b) (5 points) Visualize the information obtained in part (a) using charts. You can create bar charts to display the information. Data#4 Customer ID Method of Payment No. of Months Since Last Purchase Discount Code was Emailed Discount Code Used 553800 EStore Card 9 No No 555700 EStore Card 11 Yes Yes 558100 EStore Card 7 Yes Yes 561200 EStore Card 11 Yes Yes 562000 EStore Card 5 Yes No 562700 EStore Card 2 Yes No 588500 EStore Card 3 Yes Yes 596800 MasterCard 12 No No 603900 EStore Card 2 No Yes 607000 EStore Card 11 Yes No 609200 EStore Card 6 Yes Yes 612200 Visa 8 No No 620900 EStore Card 9 Yes Yes 631400 EStore Card 7 Yes Yes 640900 EStore Card 4 Yes No 643600 EStore Card 8 Yes Yes 651100 EStore Card 2 Yes Yes 653900 Visa 10 No Yes 677300 Visa 8 No Yes 678200 MasterCard 12 No Yes 678500 EStore Card 7 Yes Yes 679400 EStore Card 6 Yes Yes 679700 EStore Card 2 Yes Yes 684800 EStore Card 12 Yes Yes 687100 EStore Card 2 Yes Yes 687400 EStore Card 6 No Yes 692700 MasterCard 8 No Yes 695800 Visa 2 No Yes 695900 EStore Card 18 Yes Yes 701100 EStore Card 4 No Yes 702000 MasterCard 10 No Yes 706400 EStore Card 9 No Yes 708600 EStore Card 6 Yes Yes 714000 EStore Card 7 Yes Yes 720800 EStore Card 3 Yes Yes 721600 EStore Card 9 No Yes 729400 MasterCard 24 No Yes 730000 EStore Card 3 Yes Yes 734500 EStore Card 3 Yes Yes 738900 EStore Card 6 No Yes 747800 Discover 10 No Yes 750100 EStore Card 9 Yes Yes 752100 EStore Card 3 Yes Yes 756700 EStore Card 7 Yes Yes 756900 EStore Card 9 Yes Yes 764800 EStore Card 2 Yes Yes 768400 EStore Card 5 Yes Yes 769700 Visa 8 No Yes 777000 EStore Card 7 Yes No 777300 MasterCard 8 No No 778300 Visa 6 No No 782600 EStore Card 3 Yes No 783800 Discover 7 No No 784300 EStore Card 12 Yes Yes 784700 American Express 3 No No 796900 EStore Card 2 Yes No 799400 EStore Card 9 Yes Yes 800700 EStore Card 8 No No 809300 MasterCard 2 No No 810400 MasterCard 3 No Yes 816400 EStore Card 12 Yes Yes 819400 MasterCard 16 No No 827700 EStore Card 12 Yes Yes 828200 Visa 5 No No 828900 EStore Card 10 Yes Yes 832900 EStore Card 8 Yes Yes 845700 EStore Card 3 Yes Yes 854000 EStore Card 11 No Yes 856300 EStore Card 15 Yes Yes 857400 Visa 11 No No 862600 MasterCard 6 No No 865400 Discover 8 No Yes 887900 EStore Card 12 Yes Yes 894500 EStore Card 11 Yes Yes 901500 MasterCard 11 No No 902900 EStore Card 18 No Yes 910700 Visa 2 No No 911200 American Express 12 No No 911400 MasterCard 9 No Yes 919700 MasterCard 19 No No 923200 MasterCard 2 No Yes 924000 EStore Card 17 Yes Yes 926800 Discover 3 No No 929200 EStore Card 2 Yes No 930300 EStore Card 5 Yes Yes 930700 EStore Card 15 Yes No 940400 EStore Card 2 Yes No 944400 EStore Card 22 Yes Yes 946400 EStore Card 18 Yes Yes 946700 EStore Card 17 Yes No 947900 EStore Card 20 Yes Yes 952900 EStore Card 4 Yes Yes 955600 EStore Card 3 Yes Yes 955600 EStore Card 10 Yes No 956900 EStore Card 3 Yes No 959200 EStore Card 2 Yes Yes 961200 EStore Card 11 Yes Yes 962700 EStore Card 28 Yes No 962900 Visa 32 No No 985500 EStore Card 2 Yes No 985800 EStore Card 6 No Yes 985900 Discover 10 No Yes 986000 EStore Card 9 Yes Yes 986100 EStore Card 3 Yes Yes 986200 EStore Card 7 Yes Yes 986300 EStore Card 9 Yes Yes 986400 EStore Card 2 Yes Yes 986500 EStore Card 5 Yes Yes 986600 Visa 8 No Yes 986700 EStore Card 7 Yes No 986800 MasterCard 8 No No 986900 Visa 6 No No 987000 EStore Card 3 Yes No 987100 Discover 7 No No 987200 EStore Card 12 Yes Yes 987300 American Express 3 No No 987400 EStore Card 2 Yes No 987500 EStore Card 9 Yes Yes 987600 EStore Card 8 No No 987700 MasterCard 2 No No 987800 MasterCard 3 No Yes 987900 EStore Card 12 Yes Yes 988000 MasterCard 16 No No 988100 EStore Card 12 Yes Yes 988200 Visa 5 No No 988300 EStore Card 10 Yes Yes 988400 EStore Card 8 Yes Yes 988500 EStore Card 3 Yes Yes 988600 EStore Card 11 No Yes 988700 EStore Card 15 Yes Yes 988800 Visa 11 No No 988900 MasterCard 6 No No 989000 Discover 8 No Yes 989100 EStore Card 12 Yes Yes 989200 EStore Card 11 Yes Yes 989300 MasterCard 11 No No 989400 EStore Card 18 No Yes 989500 Visa 2 No No 989600 American Express 12 No No 989700 MasterCard 9 No Yes 989800 MasterCard 19 No No 989900 MasterCard 2 No Yes 990000 EStore Card 17 Yes Yes 990100 Discover 3 No No 990200 EStore Card 2 Yes No 990300 EStore Card 5 Yes Yes 990400 EStore Card 15 Yes No 990500 EStore Card 2 Yes No 990600 EStore Card 22 Yes Yes 990700 EStore Card 18 Yes Yes Problem#5 Problem #5. Pivot Tables and Charts Use the data provided in Problem#4. Of all the customers who made a purchase within the past 3 months, what percentage used an EStore Card? Is this percentage different for the customers who did not make purchase in the past 3 months? To answer these questions: a) (10 points) Prepare a two-way table for method of payment and time since last purchase. Use Pivot Tables to prepare your table. b) (5 points) Visualize the information obtained in part (a) using bar charts. Data#5 Customer ID Method of Payment No. of Months Since Last Purchase Discount Code was Emailed Discount Code Used 553800 EStore Card 9 No No 555700 EStore Card 11 Yes Yes 558100 EStore Card 7 Yes Yes 561200 EStore Card 11 Yes Yes 562000 EStore Card 5 Yes No 562700 EStore Card 2 Yes No 588500 EStore Card 3 Yes Yes 596800 MasterCard 12 No No 603900 EStore Card 2 No Yes 607000 EStore Card 11 Yes No 609200 EStore Card 6 Yes Yes 612200 Visa 8 No No 620900 EStore Card 9 Yes Yes 631400 EStore Card 7 Yes Yes 640900 EStore Card 4 Yes No 643600 EStore Card 8 Yes Yes 651100 EStore Card 2 Yes Yes 653900 Visa 10 No Yes 677300 Visa 8 No Yes 678200 MasterCard 12 No Yes 678500 EStore Card 7 Yes Yes 679400 EStore Card 6 Yes Yes 679700 EStore Card 2 Yes Yes 684800 EStore Card 12 Yes Yes 687100 EStore Card 2 Yes Yes 687400 EStore Card 6 No Yes 692700 MasterCard 8 No Yes 695800 Visa 2 No Yes 695900 EStore Card 18 Yes Yes 701100 EStore Card 4 No Yes 702000 MasterCard 10 No Yes 706400 EStore Card 9 No Yes 708600 EStore Card 6 Yes Yes 714000 EStore Card 7 Yes Yes 720800 EStore Card 3 Yes Yes 721600 EStore Card 9 No Yes 729400 MasterCard 24 No Yes 730000 EStore Card 3 Yes Yes 734500 EStore Card 3 Yes Yes 738900 EStore Card 6 No Yes 747800 Discover 10 No Yes 750100 EStore Card 9 Yes Yes 752100 EStore Card 3 Yes Yes 756700 EStore Card 7 Yes Yes 756900 EStore Card 9 Yes Yes 764800 EStore Card 2 Yes Yes 768400 EStore Card 5 Yes Yes 769700 Visa 8 No Yes 777000 EStore Card 7 Yes No 777300 MasterCard 8 No No 778300 Visa 6 No No 782600 EStore Card 3 Yes No 783800 Discover 7 No No 784300 EStore Card 12 Yes Yes 784700 American Express 3 No No 796900 EStore Card 2 Yes No 799400 EStore Card 9 Yes Yes 800700 EStore Card 8 No No 809300 MasterCard 2 No No 810400 MasterCard 3 No Yes 816400 EStore Card 12 Yes Yes 819400 MasterCard 16 No No 827700 EStore Card 12 Yes Yes 828200 Visa 5 No No 828900 EStore Card 10 Yes Yes 832900 EStore Card 8 Yes Yes 845700 EStore Card 3 Yes Yes 854000 EStore Card 11 No Yes 856300 EStore Card 15 Yes Yes 857400 Visa 11 No No 862600 MasterCard 6 No No 865400 Discover 8 No Yes 887900 EStore Card 12 Yes Yes 894500 EStore Card 11 Yes Yes 901500 MasterCard 11 No No 902900 EStore Card 18 No Yes 910700 Visa 2 No No 911200 American Express 12 No No 911400 MasterCard 9 No Yes 919700 MasterCard 19 No No 923200 MasterCard 2 No Yes 924000 EStore Card 17 Yes Yes 926800 Discover 3 No No 929200 EStore Card 2 Yes No 930300 EStore Card 5 Yes Yes 930700 EStore Card 15 Yes No 940400 EStore Card 2 Yes No 944400 EStore Card 22 Yes Yes 946400 EStore Card 18 Yes Yes 946700 EStore Card 17 Yes No 947900 EStore Card 20 Yes Yes 952900 EStore Card 4 Yes Yes 955600 EStore Card 3 Yes Yes 955600 EStore Card 10 Yes No 956900 EStore Card 3 Yes No 959200 EStore Card 2 Yes Yes 961200 EStore Card 11 Yes Yes 962700 EStore Card 28 Yes No 962900 Visa 32 No No 985500 EStore Card 2 Yes No 985800 EStore Card 6 No Yes 985900 Discover 10 No Yes 986000 EStore Card 9 Yes Yes 986100 EStore Card 3 Yes Yes 986200 EStore Card 7 Yes Yes 986300 EStore Card 9 Yes Yes 986400 EStore Card 2 Yes Yes 986500 EStore Card 5 Yes Yes 986600 Visa 8 No Yes 986700 EStore Card 7 Yes No 986800 MasterCard 8 No No 986900 Visa 6 No No 987000 EStore Card 3 Yes No 987100 Discover 7 No No 987200 EStore Card 12 Yes Yes 987300 American Express 3 No No 987400 EStore Card 2 Yes No 987500 EStore Card 9 Yes Yes 987600 EStore Card 8 No No 987700 MasterCard 2 No No 987800 MasterCard 3 No Yes 987900 EStore Card 12 Yes Yes 988000 MasterCard 16 No No 988100 EStore Card 12 Yes Yes 988200 Visa 5 No No 988300 EStore Card 10 Yes Yes 988400 EStore Card 8 Yes Yes 988500 EStore Card 3 Yes Yes 988600 EStore Card 11 No Yes 988700 EStore Card 15 Yes Yes 988800 Visa 11 No No 988900 MasterCard 6 No No 989000 Discover 8 No Yes 989100 EStore Card 12 Yes Yes 989200 EStore Card 11 Yes Yes 989300 MasterCard 11 No No 989400 EStore Card 18 No Yes 989500 Visa 2 No No 989600 American Express 12 No No 989700 MasterCard 9 No Yes 989800 MasterCard 19 No No 989900 MasterCard 2 No Yes 990000 EStore Card 17 Yes Yes 990100 Discover 3 No No 990200 EStore Card 2 Yes No 990300 EStore Card 5 Yes Yes 990400 EStore Card 15 Yes No 990500 EStore Card 2 Yes No 990600 EStore Card 22 Yes Yes 990700 EStore Card 18 Yes Yes Problem#6 Problem #6. Pivot Tables and Charts Consider the online retailer data used in Problem#4. The data has been expanded to include the transaction value ($ sales value) and the age of each customer. See the accompanying data. On the average, did the customers who use a discount code spend more or less than the customers who did not use a discount code? To answer this question: a) (10 points) Prepare a table to display the average $ amount spent per customer in each category (i.e. those that use discount code vs. not). Use Pivot Tables. b) (5 points) Display the information obtained in part (a) as a bar chart. Data#6 Customer ID Method of Payment No. of Months Since Last Purchase Discount Code was Emailed Discount Code Used Transaction value ($ sales value) Age 553800 EStore Card 9 No No $ 64.00 27 555700 EStore Card 11 Yes Yes $ 54.00 27 558100 EStore Card 7 Yes Yes $ 49.00 27 561200 EStore Card 11 Yes Yes $ 51.00 27 562000 EStore Card 5 Yes No $ 41.00 27 562700 EStore Card 2 Yes No $ 27.00 27 588500 EStore Card 3 Yes Yes $ 64.00 28 596800 MasterCard 12 No No $ 39.00 28 603900 EStore Card 2 No Yes $ 43.00 28 607000 EStore Card 11 Yes No $ 42.00 30 609200 EStore Card 6 Yes Yes $ 50.00 30 612200 Visa 8 No No $ 32.00 30 620900 EStore Card 9 Yes Yes $ 98.00 31 631400 EStore Card 7 Yes Yes $ 44.00 33 640900 EStore Card 4 Yes No $ 115.00 33 643600 EStore Card 8 Yes Yes $ 80.00 33 651100 EStore Card 2 Yes Yes $ 76.00 33 653900 Visa 10 No Yes $ 27.00 33 677300 Visa 8 No Yes $ 108.00 34 678200 MasterCard 12 No Yes $ 74.00 34 678500 EStore Card 7 Yes Yes $ 133.00 34 679400 EStore Card 6 Yes Yes $ 123.00 36 679700 EStore Card 2 Yes Yes $ 154.00 36 684800 EStore Card 12 Yes Yes $ 44.00 36 687100 EStore Card 2 Yes Yes $ 124.00 36 687400 EStore Card 6 No Yes $ 33.00 37 692700 MasterCard 8 No Yes $ 87.00 37 695800 Visa 2 No Yes $ 75.00 37 695900 EStore Card 18 Yes Yes $ 126.00 39 701100 EStore Card 4 No Yes $ 123.00 39 702000 MasterCard 10 No Yes $ 131.00 39 706400 EStore Card 9 No Yes $ 27.00 40 708600 EStore Card 6 Yes Yes $ 127.00 40 714000 EStore Card 7 Yes Yes $ 67.00 42 720800 EStore Card 3 Yes Yes $ 92.00 43 721600 EStore Card 9 No Yes $ 69.00 43 729400 MasterCard 24 No Yes $ 29.00 43 730000 EStore Card 3 Yes Yes $ 104.00 46 734500 EStore Card 3 Yes Yes $ 112.00 46 738900 EStore Card 6 No Yes $ 154.00 46 747800 Discover 10 No Yes $ 133.00 48 750100 EStore Card 9 Yes Yes $ 134.00 48 752100 EStore Card 3 Yes Yes $ 21.00 48 756700 EStore Card 7 Yes Yes $ 175.00 49 756900 EStore Card 9 Yes Yes $ 141.00 49 764800 EStore Card 2 Yes Yes $ 144.00 49 768400 EStore Card 5 Yes Yes $ 128.00 51 769700 Visa 8 No Yes $ 79.00 52 777000 EStore Card 7 Yes No $ 81.00 52 777300 MasterCard 8 No No $ 92.00 52 778300 Visa 6 No No $ 64.00 52 782600 EStore Card 3 Yes No $ 161.00 52 783800 Discover 7 No No $ 108.00 52 784300 EStore Card 12 Yes Yes $ 419.00 52 784700 American Express 3 No No $ 202.00 54 796900 EStore Card 2 Yes No $ 99.00 54 799400 EStore Card 9 Yes Yes $ 348.00 54 800700 EStore Card 8 No No $ 157.00 54 809300 MasterCard 2 No No $ 168.00 54 810400 MasterCard 3 No Yes $ 63.00 54 816400 EStore Card 12 Yes Yes $ 41.00 54 819400 MasterCard 16 No No $ 118.00 54 827700 EStore Card 12 Yes Yes $ 170.00 54 828200 Visa 5 No No $ 137.00 54 828900 EStore Card 10 Yes Yes $ 70.00 55 832900 EStore Card 8 Yes Yes $ 128.00 55 845700 EStore Card 3 Yes Yes $ 676.00 55 854000 EStore Card 11 No Yes $ 82.00 57 856300 EStore Card 15 Yes Yes $ 213.00 58 857400 Visa 11 No No $ 44.00 58 862600 MasterCard 6 No No $ 24.00 58 865400 Discover 8 No Yes $ 58.00 58 887900 EStore Card 12 Yes Yes $ 84.00 58 894500 EStore Card 11 Yes Yes $ 299.00 60 901500 MasterCard 11 No No $ 91.00 60 902900 EStore Card 18 No Yes $ 54.00 60 910700 Visa 2 No No $ 332.00 60 911200 American Express 12 No No $ 81.00 60 911400 MasterCard 9 No Yes $ 193.00 60 919700 MasterCard 19 No No $ 306.00 61 923200 MasterCard 2 No Yes $ 112.00 61 924000 EStore Card 17 Yes Yes $ 324.00 63 926800 Discover 3 No No $ 111.00 63 929200 EStore Card 2 Yes No $ 216.00 63 930300 EStore Card 5 Yes Yes $ 675.00 63 930700 EStore Card 15 Yes No $ 55.00 64 940400 EStore Card 2 Yes No $ 612.00 64 944400 EStore Card 22 Yes Yes $ 27.00 64 946400 EStore Card 18 Yes Yes $ 123.00 64 946700 EStore Card 17 Yes No $ 50.00 64 947900 EStore Card 20 Yes Yes $ 41.00 66 952900 EStore Card 4 Yes Yes $ 160.00 66 955600 EStore Card 3 Yes Yes $ 29.00 66 955600 EStore Card 10 Yes No $ 225.00 66 956900 EStore Card 3 Yes No $ 170.00 67 959200 EStore Card 2 Yes Yes $ 67.00 69 961200 EStore Card 11 Yes Yes $ 30.00 70 962700 EStore Card 28 Yes No $ 132.00 72 962900 Visa 32 No No $ 185.00 72 985500 EStore Card 2 Yes No $ 81.00 75 985800 EStore Card 6 No Yes $ 178.00 52 985900 Discover 10 No Yes $ 190.00 52 986000 EStore Card 9 Yes Yes $ 131.00 52 986100 EStore Card 3 Yes Yes $ 93.00 52 986200 EStore Card 7 Yes Yes $ 791.00 52 986300 EStore Card 9 Yes Yes $ 145.00 54 986400 EStore Card 2 Yes Yes $ 74.00 54 986500 EStore Card 5 Yes Yes $ 288.00 54 986600 Visa 8 No Yes $ 211.00 54 986700 EStore Card 7 Yes No $ 343.00 54 986800 MasterCard 8 No No $ 48.00 54 986900 Visa 6 No No $ 201.00 54 987000 EStore Card 3 Yes No $ 176.00 54 987100 Discover 7 No No $ 100.00 54 987200 EStore Card 12 Yes Yes $ 272.00 54 987300 American Express 3 No No $ 73.00 55 987400 EStore Card 2 Yes No $ 52.00 55 987500 EStore Card 9 Yes Yes $ 51.00 55 987600 EStore Card 8 No No $ 203.00 57 987700 MasterCard 2 No No $ 109.00 58 987800 MasterCard 3 No Yes $ 195.00 58 987900 EStore Card 12 Yes Yes $ 54.00 58 988000 MasterCard 16 No No $ 153.00 58 988100 EStore Card 12 Yes Yes $ 233.00 58 988200 Visa 5 No No $ 187.00 60 988300 EStore Card 10 Yes Yes $ 62.00 60 988400 EStore Card 8 Yes Yes $ 352.00 60 988500 EStore Card 3 Yes Yes $ 35.00 60 988600 EStore Card 11 No Yes $ 129.00 60 988700 EStore Card 15 Yes Yes $ 54.00 60 988800 Visa 11 No No $ 114.00 61 988900 MasterCard 6 No No $ 190.00 61 989000 Discover 8 No Yes $ 233.00 63 989100 EStore Card 12 Yes Yes $ 183.00 63 989200 EStore Card 11 Yes Yes $ 228.00 63 989300 MasterCard 11 No No $ 464.00 63 989400 EStore Card 18 No Yes $ 102.00 64 989500 Visa 2 No No $ 477.00 64 989600 American Express 12 No No $ 34.00 64 989700 MasterCard 9 No Yes $ 317.00 64 989800 MasterCard 19 No No $ 69.00 64 989900 MasterCard 2 No Yes $ 325.00 66 990000 EStore Card 17 Yes Yes $ 43.00 66 990100 Discover 3 No No $ 411.00 66 990200 EStore Card 2 Yes No $ 275.00 66 990300 EStore Card 5 Yes Yes $ 260.00 67 990400 EStore Card 15 Yes No $ 22.00 69 990500 EStore Card 2 Yes No $ 338.00 70 990600 EStore Card 22 Yes Yes $ 300.00 72 990700 EStore Card 18 Yes Yes $ 83.00 72 Problem#7 Problem #7. Pivot Tables and Charts Use the data provided in Problem#6. Is the transaction value distribution similar for customers who use the discount code vs. not? To answer this question: a) (10 points) Prepare a table to display the statistical distribution of the $ amount spent for customers how used the discount code (vs. not). Use Pivot Tables so that the transaction value is grouped by increments of $25, starting at $0, and ending at $800. b) (5 points) Display information obtained in part (a) using bar charts. The horizontal axis in your chart should be the transaction value and the vertical axis should be the number of customers. You can present two bar charts (one for customers who used a discount code and one for those who did not) or you can display the information for these these categories of customers as a side-by-side bar chart. Data#7 Customer ID Method of Payment No. of Months Since Last Purchase Discount Code was Emailed Discount Code Used Transaction value ($ sales value) Age 553800 EStore Card 9 No No $ 64.00 27 555700 EStore Card 11 Yes Yes $ 54.00 27 558100 EStore Card 7 Yes Yes $ 49.00 27 561200 EStore Card 11 Yes Yes $ 51.00 27 562000 EStore Card 5 Yes No $ 41.00 27 562700 EStore Card 2 Yes No $ 27.00 27 588500 EStore Card 3 Yes Yes $ 64.00 28 596800 MasterCard 12 No No $ 39.00 28 603900 EStore Card 2 No Yes $ 43.00 28 607000 EStore Card 11 Yes No $ 42.00 30 609200 EStore Card 6 Yes Yes $ 50.00 30 612200 Visa 8 No No $ 32.00 30 620900 EStore Card 9 Yes Yes $ 98.00 31 631400 EStore Card 7 Yes Yes $ 44.00 33 640900 EStore Card 4 Yes No $ 115.00 33 643600 EStore Card 8 Yes Yes $ 80.00 33 651100 EStore Card 2 Yes Yes $ 76.00 33 653900 Visa 10 No Yes $ 27.00 33 677300 Visa 8 No Yes $ 108.00 34 678200 MasterCard 12 No Yes $ 74.00 34 678500 EStore Card 7 Yes Yes $ 133.00 34 679400 EStore Card 6 Yes Yes $ 123.00 36 679700 EStore Card 2 Yes Yes $ 154.00 36 684800 EStore Card 12 Yes Yes $ 44.00 36 687100 EStore Card 2 Yes Yes $ 124.00 36 687400 EStore Card 6 No Yes $ 33.00 37 692700 MasterCard 8 No Yes $ 87.00 37 695800 Visa 2 No Yes $ 75.00 37 695900 EStore Card 18 Yes Yes $ 126.00 39 701100 EStore Card 4 No Yes $ 123.00 39 702000 MasterCard 10 No Yes $ 131.00 39 706400 EStore Card 9 No Yes $ 27.00 40 708600 EStore Card 6 Yes Yes $ 127.00 40 714000 EStore Card 7 Yes Yes $ 67.00 42 720800 EStore Card 3 Yes Yes $ 92.00 43 721600 EStore Card 9 No Yes $ 69.00 43 729400 MasterCard 24 No Yes $ 29.00 43 730000 EStore Card 3 Yes Yes $ 104.00 46 734500 EStore Card 3 Yes Yes $ 112.00 46 738900 EStore Card 6 No Yes $ 154.00 46 747800 Discover 10 No Yes $ 133.00 48 750100 EStore Card 9 Yes Yes $ 134.00 48 752100 EStore Card 3 Yes Yes $ 21.00 48 756700 EStore Card 7 Yes Yes $ 175.00 49 756900 EStore Card 9 Yes Yes $ 141.00 49 764800 EStore Card 2 Yes Yes $ 144.00 49 768400 EStore Card 5 Yes Yes $ 128.00 51 769700 Visa 8 No Yes $ 79.00 52 777000 EStore Card 7 Yes No $ 81.00 52 777300 MasterCard 8 No No $ 92.00 52 778300 Visa 6 No No $ 64.00 52 782600 EStore Card 3 Yes No $ 161.00 52 783800 Discover 7 No No $ 108.00 52 784300 EStore Card 12 Yes Yes $ 419.00 52 784700 American Express 3 No No $ 202.00 54 796900 EStore Card 2 Yes No $ 99.00 54 799400 EStore Card 9 Yes Yes $ 348.00 54 800700 EStore Card 8 No No $ 157.00 54 809300 MasterCard 2 No No $ 168.00 54 810400 MasterCard 3 No Yes $ 63.00 54 816400 EStore Card 12 Yes Yes $ 41.00 54 819400 MasterCard 16 No No $ 118.00 54 827700 EStore Card 12 Yes Yes $ 170.00 54 828200 Visa 5 No No $ 137.00 54 828900 EStore Card 10 Yes Yes $ 70.00 55 832900 EStore Card 8 Yes Yes $ 128.00 55 845700 EStore Card 3 Yes Yes $ 676.00 55 854000 EStore Card 11 No Yes $ 82.00 57 856300 EStore Card 15 Yes Yes $ 213.00 58 857400 Visa 11 No No $ 44.00 58 862600 MasterCard 6 No No $ 24.00 58 865400 Discover 8 No Yes $ 58.00 58 887900 EStore Card 12 Yes Yes $ 84.00 58 894500 EStore Card 11 Yes Yes $ 299.00 60 901500 MasterCard 11 No No $ 91.00 60 902900 EStore Card 18 No Yes $ 54.00 60 910700 Visa 2 No No $ 332.00 60 911200 American Express 12 No No $ 81.00 60 911400 MasterCard 9 No Yes $ 193.00 60 919700 MasterCard 19 No No $ 306.00 61 923200 MasterCard 2 No Yes $ 112.00 61 924000 EStore Card 17 Yes Yes $ 324.00 63 926800 Discover 3 No No $ 111.00 63 929200 EStore Card 2 Yes No $ 216.00 63 930300 EStore Card 5 Yes Yes $ 675.00 63 930700 EStore Card 15 Yes No $ 55.00 64 940400 EStore Card 2 Yes No $ 612.00 64 944400 EStore Card 22 Yes Yes $ 27.00 64 946400 EStore Card 18 Yes Yes $ 123.00 64 946700 EStore Card 17 Yes No $ 50.00 64 947900 EStore Card 20 Yes Yes $ 41.00 66 952900 EStore Card 4 Yes Yes $ 160.00 66 955600 EStore Card 3 Yes Yes $ 29.00 66 955600 EStore Card 10 Yes No $ 225.00 66 956900 EStore Card 3 Yes No $ 170.00 67 959200 EStore Card 2 Yes Yes $ 67.00 69 961200 EStore Card 11 Yes Yes $ 30.00 70 962700 EStore Card 28 Yes No $ 132.00 72 962900 Visa 32 No No $ 185.00 72 985500 EStore Card 2 Yes No $ 81.00 75 985800 EStore Card 6 No Yes $ 178.00 52 985900 Discover 10 No Yes $ 190.00 52 986000 EStore Card 9 Yes Yes $ 131.00 52 986100 EStore Card 3 Yes Yes $ 93.00 52 986200 EStore Card 7 Yes Yes $ 791.00 52 986300 EStore Card 9 Yes Yes $ 145.00 54 986400 EStore Card 2 Yes Yes $ 74.00 54 986500 EStore Card 5 Yes Yes $ 288.00 54 986600 Visa 8 No Yes $ 211.00 54 986700 EStore Card 7 Yes No $ 343.00 54 986800 MasterCard 8 No No $ 48.00 54 986900 Visa 6 No No $ 201.00 54 987000 EStore Card 3 Yes No $ 176.00 54 987100 Discover 7 No No $ 100.00 54 987200 EStore Card 12 Yes Yes $ 272.00 54 987300 American Express 3 No No $ 73.00 55 987400 EStore Card 2 Yes No $ 52.00 55 987500 EStore Card 9 Yes Yes $ 51.00 55 987600 EStore Card 8 No No $ 203.00 57 987700 MasterCard 2 No No $ 109.00 58 987800 MasterCard 3 No Yes $ 195.00 58 987900 EStore Card 12 Yes Yes $ 54.00 58 988000 MasterCard 16 No No $ 153.00 58 988100 EStore Card 12 Yes Yes $ 233.00 58 988200 Visa 5 No No $ 187.00 60 988300 EStore Card 10 Yes Yes $ 62.00 60 988400 EStore Card 8 Yes Yes $ 352.00 60 988500 EStore Card 3 Yes Yes $ 35.00 60 988600 EStore Card 11 No Yes $ 129.00 60 988700 EStore Card 15 Yes Yes $ 54.00 60 988800 Visa 11 No No $ 114.00 61 988900 MasterCard 6 No No $ 190.00 61 989000 Discover 8 No Yes $ 233.00 63 989100 EStore Card 12 Yes Yes $ 183.00 63 989200 EStore Card 11 Yes Yes $ 228.00 63 989300 MasterCard 11 No No $ 464.00 63 989400 EStore Card 18 No Yes $ 102.00 64 989500 Visa 2 No No $ 477.00 64 989600 American Express 12 No No $ 34.00 64 989700 MasterCard 9 No Yes $ 317.00 64 989800 MasterCard 19 No No $ 69.00 64 989900 MasterCard 2 No Yes $ 325.00 66 990000 EStore Card 17 Yes Yes $ 43.00 66 990100 Discover 3 No No $ 411.00 66 990200 EStore Card 2 Yes No $ 275.00 66 990300 EStore Card 5 Yes Yes $ 260.00 67 990400 EStore Card 15 Yes No $ 22.00 69 990500 EStore Card 2 Yes No $ 338.00 70 990600 EStore Card 22 Yes Yes $ 300.00 72 990700 EStore Card 18 Yes Yes $ 83.00 72 Problem #8, #9, #10 Problem #8. Correlation (5 points) Use the data provided for Problem#6. What is the correlation between the transaction value and the customer’s age? Compute the correlation value and comment on what the correlation indicates about the relationship between age and transaction value. Problem #9. Scatter Plot (Scatter Diagram) (5 points) Use the data provided in Problem#6. Explore the relationship between the transaction value and the customer’s age by preparing a scatter plot (scatter diagram). Use the chart functions in Excel to prepare a scatter plot. Age should be on the horizontal axis. Transaction value should be on the vertical axis. Problem #10. Trend Line (5 points) Fit a trend line to the scatter plot prepared in Problem#9. Determine the equation and the coefficient of determination (R-squared or R2) of the trend line. Display the equation and the R-squared values on the scatter plot. Data #8, #9, #10 Customer ID Method of Payment No. of Months Since Last Purchase Discount Code was Emailed Discount Code Used Transaction value ($ sales value) Age 553800 EStore Card 9 No No $ 64.00 27 555700 EStore Card 11 Yes Yes $ 54.00 27 558100 EStore Card 7 Yes Yes $ 49.00 27 561200 EStore Card 11 Yes Yes $ 51.00 27 562000 EStore Card 5 Yes No $ 41.00 27 562700 EStore Card 2 Yes No $ 27.00 27 588500 EStore Card 3 Yes Yes $ 64.00 28 596800 MasterCard 12 No No $ 39.00 28 603900 EStore Card 2 No Yes $ 43.00 28 607000 EStore Card 11 Yes No $ 42.00 30 609200 EStore Card 6 Yes Yes $ 50.00 30 612200 Visa 8 No No $ 32.00 30 620900 EStore Card 9 Yes Yes $ 98.00 31 631400 EStore Card 7 Yes Yes $ 44.00 33 640900 EStore Card 4 Yes No $ 115.00 33 643600 EStore Card 8 Yes Yes $ 80.00 33 651100 EStore Card 2 Yes Yes $ 76.00 33 653900 Visa 10 No Yes $ 27.00 33 677300 Visa 8 No Yes $ 108.00 34 678200 MasterCard 12 No Yes $ 74.00 34 678500 EStore Card 7 Yes Yes $ 133.00 34 679400 EStore Card 6 Yes Yes $ 123.00 36 679700 EStore Card 2 Yes Yes $ 154.00 36 684800 EStore Card 12 Yes Yes $ 44.00 36 687100 EStore Card 2 Yes Yes $ 124.00 36 687400 EStore Card 6 No Yes $ 33.00 37 692700 MasterCard 8 No Yes $ 87.00 37 695800 Visa 2 No Yes $ 75.00 37 695900 EStore Card 18 Yes Yes $ 126.00 39 701100 EStore Card 4 No Yes $ 123.00 39 702000 MasterCard 10 No Yes $ 131.00 39 706400 EStore Card 9 No Yes $ 27.00 40 708600 EStore Card 6 Yes Yes $ 127.00 40 714000 EStore Card 7 Yes Yes $ 67.00 42 720800 EStore Card 3 Yes Yes $ 92.00 43 721600 EStore Card 9 No Yes $ 69.00 43 729400 MasterCard 24 No Yes $ 29.00 43 730000 EStore Card 3 Yes Yes $ 104.00 46 734500 EStore Card 3 Yes Yes $ 112.00 46 738900 EStore Card 6 No Yes $ 154.00 46 747800 Discover 10 No Yes $ 133.00 48 750100 EStore Card 9 Yes Yes $ 134.00 48 752100 EStore Card 3 Yes Yes $ 21.00 48 756700 EStore Card 7 Yes Yes $ 175.00 49 756900 EStore Card 9 Yes Yes $ 141.00 49 764800 EStore Card 2 Yes Yes $ 144.00 49 768400 EStore Card 5 Yes Yes $ 128.00 51 769700 Visa 8 No Yes $ 79.00 52 777000 EStore Card 7 Yes No $ 81.00 52 777300 MasterCard 8 No No $ 92.00 52 778300 Visa 6 No No $ 64.00 52 782600 EStore Card 3 Yes No $ 161.00 52 783800 Discover 7 No No $ 108.00 52 784300 EStore Card 12 Yes Yes $ 419.00 52 784700 American Express 3 No No $ 202.00 54 796900 EStore Card 2 Yes No $ 99.00 54 799400 EStore Card 9 Yes Yes $ 348.00 54 800700 EStore Card 8 No No $ 157.00 54 809300 MasterCard 2 No No $ 168.00 54 810400 MasterCard 3 No Yes $ 63.00 54 816400 EStore Card 12 Yes Yes $ 41.00 54 819400 MasterCard 16 No No $ 118.00 54 827700 EStore Card 12 Yes Yes $ 170.00 54 828200 Visa 5 No No $ 137.00 54 828900 EStore Card 10 Yes Yes $ 70.00 55 832900 EStore Card 8 Yes Yes $ 128.00 55 845700 EStore Card 3 Yes Yes $ 676.00 55 854000 EStore Card 11 No Yes $ 82.00 57 856300 EStore Card 15 Yes Yes $ 213.00 58 857400 Visa 11 No No $ 44.00 58 862600 MasterCard 6 No No $ 24.00 58 865400 Discover 8 No Yes $ 58.00 58 887900 EStore Card 12 Yes Yes $ 84.00 58 894500 EStore Card 11 Yes Yes $ 299.00 60 901500 MasterCard 11 No No $ 91.00 60 902900 EStore Card 18 No Yes $ 54.00 60 910700 Visa 2 No No $ 332.00 60 911200 American Express 12 No No $ 81.00 60 911400 MasterCard 9 No Yes $ 193.00 60 919700 MasterCard 19 No No $ 306.00 61 923200 MasterCard 2 No Yes $ 112.00 61 924000 EStore Card 17 Yes Yes $ 324.00 63 926800 Discover 3 No No $ 111.00 63 929200 EStore Card 2 Yes No $ 216.00 63 930300 EStore Card 5 Yes Yes $ 675.00 63 930700 EStore Card 15 Yes No $ 55.00 64 940400 EStore Card 2 Yes No $ 612.00 64 944400 EStore Card 22 Yes Yes $ 27.00 64 946400 EStore Card 18 Yes Yes $ 123.00 64 946700 EStore Card 17 Yes No $ 50.00 64 947900 EStore Card 20 Yes Yes $ 41.00 66 952900 EStore Card 4 Yes Yes $ 160.00 66 955600 EStore Card 3 Yes Yes $ 29.00 66 955600 EStore Card 10 Yes No $ 225.00 66 956900 EStore Card 3 Yes No $ 170.00 67 959200 EStore Card 2 Yes Yes $ 67.00 69 961200 EStore Card 11 Yes Yes $ 30.00 70 962700 EStore Card 28 Yes No $ 132.00 72 962900 Visa 32 No No $ 185.00 72 985500 EStore Card 2 Yes No $ 81.00 75 985800 EStore Card 6 No Yes $ 178.00 52 985900 Discover 10 No Yes $ 190.00 52 986000 EStore Card 9 Yes Yes $ 131.00 52 986100 EStore Card 3 Yes Yes $ 93.00 52 986200 EStore Card 7 Yes Yes $ 791.00 52 986300 EStore Card 9 Yes Yes $ 145.00 54 986400 EStore Card 2 Yes Yes $ 74.00 54 986500 EStore Card 5 Yes Yes $ 288.00 54 986600 Visa 8 No Yes $ 211.00 54 986700 EStore Card 7 Yes No $ 343.00 54 986800 MasterCard 8 No No $ 48.00 54 986900 Visa 6 No No $ 201.00 54 987000 EStore Card 3 Yes No $ 176.00 54 987100 Discover 7 No No $ 100.00 54 987200 EStore Card 12 Yes Yes $ 272.00 54 987300 American Express 3 No No $ 73.00 55 987400 EStore Card 2 Yes No $ 52.00 55 987500 EStore Card 9 Yes Yes $ 51.00 55 987600 EStore Card 8 No No $ 203.00 57 987700 MasterCard 2 No No $ 109.00 58 987800 MasterCard 3 No Yes $ 195.00 58 987900 EStore Card 12 Yes Yes $ 54.00 58 988000 MasterCard 16 No No $ 153.00 58 988100 EStore Card 12 Yes Yes $ 233.00 58 988200 Visa 5 No No $ 187.00 60 988300 EStore Card 10 Yes Yes $ 62.00 60 988400 EStore Card 8 Yes Yes $ 352.00 60 988500 EStore Card 3 Yes Yes $ 35.00 60 988600 EStore Card 11 No Yes $ 129.00 60 988700 EStore Card 15 Yes Yes $ 54.00 60 988800 Visa 11 No No $ 114.00 61 988900 MasterCard 6 No No $ 190.00 61 989000 Discover 8 No Yes $ 233.00 63 989100 EStore Card 12 Yes Yes $ 183.00 63 989200 EStore Card 11 Yes Yes $ 228.00 63 989300 MasterCard 11 No No $ 464.00 63 989400 EStore Card 18 No Yes $ 102.00 64 989500 Visa 2 No No $ 477.00 64 989600 American Express 12 No No $ 34.00 64 989700 MasterCard 9 No Yes $ 317.00 64 989800 MasterCard 19 No No $ 69.00 64 989900 MasterCard 2 No Yes $ 325.00 66 990000 EStore Card 17 Yes Yes $ 43.00 66 990100 Discover 3 No No $ 411.00 66 990200 EStore Card 2 Yes No $ 275.00 66 990300 EStore Card 5 Yes Yes $ 260.00 67 990400 EStore Card 15 Yes No $ 22.00 69 990500 EStore Card 2 Yes No $ 338.00 70 990600 EStore Card 22 Yes Yes $ 300.00 72 990700 EStore Card 18 Yes Yes $ 83.00 72
ITEC 210 DATA ANALYSIS FOR BUSINESS Exploring The Relationship Between Two Variables Prof. Itir KARAESMEN AYDIN 1 Outline and Learning Outcomes In this presentation, you will learn To interpret two-way tables that explore the relationship between two categorical variables To explore the relationship between a quantitative (numerical) and categorical variable using side-by-side charts and histograms To interpret scatter diagrams (plots) To interpret correlation between two numerical variables To interpret the trend line on a scatter diagram. NOTE: This presentation does not show you *how* the work is done on Excel. 2 Topic: Relationship Between Two Categorical Variables 3 Learning Objective After completing these examples, you will be able to interpret two-way tables for two categorical variables visualize two-way tables using charts answer business questions or provide business insights with the help of two-way tables. 4 Two-Way Tables Information displayed in each cell of the table Frequency or % frequency by column (Variable-1) Frequency or % frequency by row (Variable-2) Frequency or % frequency by grand total 5 TABLE Categories of Variable 1 Cat-1 Cat-2 Cat-3 Cat-4 … Categories of Variable 2 Cat-A Cat-B … Excel Exercise #4 Use the data from Exercise#2. 6 #4 (cont’d) Considering all the customers who used a discount code, did the majority use the EStore Card to make a payment? How about all the customers who did not use a discount code? To answer these questions: Prepare a two-way table to display the percentage frequencies with respect to method of payment and use of discount code. Visualize the data using charts. 7 #4 (cont’d) Two-way table prepared using Pivot Tables Frequencies are displayed below 8 #4 (cont’d) Two-way table prepared using Pivot Tables Frequencies as percentages of the Grand Total are displayed below 9 #4 (cont’d) Two-way table prepared using Pivot Tables Frequencies as percentages of the column totals (use of discount code) are displayed below 10 #4 (cont’d) Side-by-side bar chart created via Pivot Charts We display the percentage frequency for method of payment considering all the customers who used a discount code (vs. not used). 11 #4 (cont’d) Q: Considering the customers who used a discount code, did the majority use the EStore Card to make a payment? How about the customers who did not use a discount code? A: Of all the customers who used a discount code, 80% used the EStore Card. Of all the customers who did not use a discount code, 50% used the EStore Card. 12 Excel Exercise #5 Use the data in the previous exercise. Of all the customers who made a purchase within the past 3 months, what percentage used an EStore Card? Is this percentage different for the customers who did not make purchase in the past 3 months? To answer these questions: Prepare a two-way table for method of payment and time since last purchase. Visualize the data using bar charts. 13 #5 (cont’d) Two-way table prepared using Pivot Tables Frequencies as percentages of the Grand Total are displayed below 14 #5 (cont’d) Two-way table prepared using Pivot Tables Frequencies as percentages of the column total (time since last payment) are displayed below 15 #5 (cont’d) Side-by-side bar chart created via Pivot Charts Displays percentage frequency of method of payment by customers who made a purchase in the past 3 months (vs. not) 16 #5 (cont’d) Q: Of all the customers who made a purchase within the past 3 months, what percentage used an EStore Card? Is this percentage different for the customers who did not make purchase in the past 3 months? A: Of all the customers who made a purchase within the past 3 months, 74% used the EStore card. Of all the customers who did not make a purchase within the past 3 months, 68% used the EStore card. 17 Summary Two-way tables and side-by-side bar charts make comparison across categories easy. However: Just because we observe that the percentage of customers who used the EStore Card is different for two distinct groups of customers (discount code users vs. not) does not mean this difference is statistically significant. Statistical tests (e.g. hypothesis tests) can be used to test for statistical significance of the differences. 18 Topic: Relationship Between A Categorical Variable and A Quantitative Variable 19 Learning Objective After completing these examples, you will be able to interpret tables that display information about a quantitative variable when data is grouped with respect to another categorical variable compare histograms of a quantitative variable for different categories answer business questions or provide business insights with the help of tables and charts. 20 Excel Exercise #6 Consider the online retailer data used in Exercises #2 to #5. The data has been expanded to include the transaction value ($ sales value) and the age of each customer. 21 #6 (cont’d) On the average, did the customers who use a discount code spend more or less than the customers who did not use a discount code? To answer this question: Prepare a table to display the average $ amount spent per customer in each category (i.e. those that use discount code vs. not). Visualize the data using bar charts. 22 #6 (cont’d) Pivot Table that displays the average $ sales value for customers who used a discount code vs. not 23 Rows: Use of discount code Column: Transaction value Average transaction value #6 (cont’d) A bar chart created via Pivot Charts 24 #6 (cont’d) Q: On the average, did the customers who use a discount code spend more or less than the customers who did not use a discount code? A: The average amount spent by customers who used a discount code ($113) is lower than the average amount spent by those who did not use a discount code ($120). Note: This difference may or may not statistically significant. 25 Excel Exercise #7 Use the data provided for Exercise#6. Is the transaction value distribution similar for customers who use the discount code vs. not? To answer this question: Prepare a table to display the statistical distribution of the $ amount spent for customers how used the discount code (vs. not). Visualize the data using bar charts. 26 #7 (cont’d) Pivot Table that displays the no. of customers who used a discount code (vs. not), grouped by the amount spent by customer. Amount spent is grouped by $25 increments 27 #7 (cont’d) Side-by-side bar chart is created using Pivot Charts 28 #7 (cont’d) Histogram of amount spent for two separate categories, created using Pivot Charts 29 #7 (cont’d) Q: Is the transaction value distribution similar for customers who use the discount code vs. not? A: Both distributions are skewed to the right; there are a few customers who spent >$350 regardless of the discount code. The bin ranges $50-$74 and $75-$99 have the highest frequency for both groups of customers. The number of customers who did not use a discount code declines sharply beyond $99. In contrast, there is a sizable portion of customers who used a discount code that spent more than $99. 30 Summary We can compare quantitative data across categories by Creating tables Creating side-by-side charts Creating separate histograms for each category Pivot tables and pivot charts are quick ways to organize data 31 Topic: Relationship Between Two Quantitative Variables 32 Learning Objective After completing these examples, you will be able to interpret correlation between two quantitative variables interpret scatter plots (diagrams) interpret trend lines on scatter plots. 33 Review: Correlation Correlation: measures the strength of the linear relationship between two quantitative variables Correlation takes values between -1 and +1. 34 Correlation = -1 Correlation = 0 Correlation = +1 Types of Relationships Y X Y X Y Y X X Linear relationships Curvilinear relationships Types of Linear Relationships Y X Y X Strong Y Y X X Weak Y X Y X No relationship Review: Correlation If correlation value is close to 0, does this mean there is no apparent relationship between the variables? Example: Correlation between X and Y variables is 0.000 for the data below. 37 Review: Correlation If correlation value is close to +1 (or -1), does this mean the relationship between the variables is perfectly linear? Example: Correlation is +0.90 for the data below. 38 Excel Exercise #8 Use the data provided for Exercise#6. What is the correlation between the amount spent by a customer and the customer’s age? Use the CORREL function in Excel. Format: CORREL(Range1, Range2) Range1: cell range for the amount spent Range2: cell range for the age of customer 39 #8 (cont’d) Correlation between the amount spent by a customer and the customer’s age is +0.667. There is a positive relationship between these two variables. 40 #8 (cont’d) Wrong interpretation: “The more people spend, the older they get.” This statement says that spending more at the retail shop is causing people to age. Better interpretation: “The older customers in this data set tend to spend more and the younger customers tend to spend less.” 41 #8 (cont’d) Possible reasons for a positive relationship between age and transaction value: Older people have higher income and have higher discretionary budget to spend. The retailer sells products that are demanded more by older people (e.g. medical devices). 42 Summary Zero correlation does not mean one variable is not related to another. Correlation is not causation. When X and Y are correlated, the changes in Y may not be “caused” or “induced” by changes in X but by outside factors that are related to both variables. CORREL function in Excel computes the correlation. 43 Excel Exercise #9 Use the data provided for Exercise#8. Explore the relationship between the amount spent by a customer and the customer’s age by preparing a scatter plot (scatter diagram). Use the chart functions in Excel to prepare a scatter plot. Age should be on the horizontal axis. Transaction value should be on the vertical axis. 44 #9 (cont’d) Scatter plot of Transaction Value and Age 45 #9 (cont’d) Observations Older customers tend to spend more. Customers who are 35 or younger spent between $50 and $100. Only the customers who are older than 35 spent more than $100. The transactions with the highest $ value were made by customers between the ages of 35 and 45. The relationship between age and transaction value is positive and linear for customers 35 or younger. The relationship between age and transaction value does not appear to be linear for customers 35 and older. 46 Summary A scatter plot can be used to explore the relationship between two quantitative variables. Trends and patterns can be detected visually on a scatter plot. The scatter plot in the Excel chart function plots two quantitative variables. 47 Trend Line Trend line is a straight line It is displayed on the scatter plot The trend line equation is Y = b0 + b1 X where X: variable displayed on the horizontal axis of the scatter plot b0: intercept of the line (the value Y takes when X=0). b1: slope of the line (i.e., every 1 unit change in X, results in b units of change in Y); slope can be positive or negative. Y: variable displayed on the vertical axis of the scatter plot 48 Fitting a Trend Line to the Data 49 A B C Which of these lines “fits best” to the data? Determining the “best fit” Choose the slope and intercept so “the distance between the line and the points” on the scatter plot are minimized. The trend line is the straight line that minimizes the sum of the squares of error. Error is the distance from a point on the scatter plot to the trend line. 50 Goodness of Fit of the Trend Line Coefficient of determination, also known as the R-squared (R2), is a measure of goodness of fit of the trend line. R-squared (R2) takes values less than or equal to 1. In general, the higher the value, the better the fit. It is OK to have low R-squared values. R-squared is NOT equal to the correlation between X and Y. 51 Excel Exercise #10 Fit a trend line to the scatter plot prepared in Exercise#9. Determine the equation and the coefficient of determination (R2) of the trend line. 52 #10 (cont’d) Trend line fitted to the scatter plot of age and transaction value 53 #10 (cont’d) Trend line equation (algebraically): Y = -91.677 + 6.3326 X Trend line equation (using variable names): Transaction Value = -91.677 + 6.3326 Age Interpretation: With every 1 year increase in the age of a customer, the amount they spend increases by $6.3326. 54 #10 (cont’d) R2 = 0.44 Interpretation: 44% of the variability in transaction value can be explained by the fitted trend line; 56% is unexplained by the trend line and is due to other factors. 55 Summary This presentation covered Exploring relationship between categorical variables using Pivot Tables and Pivot Charts. Exploring relationship between a quantitative and a categorical variable using Pivot Tables and Pivot Charts. Exploring the relationship between two quantitative variables using correlation, scatter plots, and trend lines. NOTE: This presentation did not show you *how* the work is done on Excel. 56 Count of Discount Code UsedColumn Labels Row LabelsYesNoGrand Total American Express22 Discover224 EStore Card531770 MasterCard7714 Visa4610 Grand Total6634100 Count of Discount Code UsedColumn Labels Row LabelsYesNoGrand Total American Express0.00%2.00%2.00% Discover2.00%2.00%4.00% EStore Card53.00%17.00%70.00% MasterCard7.00%7.00%14.00% Visa4.00%6.00%10.00% Grand Total66.00%34.00%100.00% Count of Discount Code UsedColumn Labels Row LabelsYesNoGrand Total American Express0.00%5.88%2.00% Discover3.03%5.88%4.00% EStore Card80.30%50.00%70.00% MasterCard10.61%20.59%14.00% Visa6.06%17.65%10.00% Grand Total100.00%100.00%100.00% 0.00% 10.00% 20.00% 30.00% 40.00% 50.00% 60.00% 70.00% 80.00% 90.00% % o f C u s t o m e r s Method of Payment Percentage Frequency Distribution by Method of Payment given the Discount Code Use Yes No 050100150200250300350400450020406080100120140YXScatter Plot 050100150200250020406080100120140YXScatter Plot 01002003004005006007008009001000020040060080010001200YXScatter Plot 050001000015000200002500030000020040060080010001200YX 050100150200250300350400450500-15-10-5051015YXScatter Plot

Related Questions

Similar orders to interpret two-way tables for two categorical variables, visualize two-way tables using charts
26
Views
0
Answers
Criminal Law Make up Midterm Dan was a passenger in Vics car. While driving in a desolate mountain area

Criminal Law

Make up Midterm

Dan was a passenger in Vics car. While driving in a desolate mountain area, Vic stopped and offered Dan a hallucinogenic drug. Dan refused, but Vic said if Dan wished to stay in the car, he would have to join Vic in using the drug. Fearing that he would be abandoned in freezing temperatures many miles from the nearest town, Dan decided to ingest the drug.

Dan started to hallucinated. While under the influence of the drug, Dan killed Vic, left the body beside the road, and drove Vics car to the nearest town. On his drive back into town he ran over Ped, who was standing on the side of the road because his car had broken down. Ped died from internal bleeding, and would have survived if Dan would have stopped after hitting him and rendering aid.

After several days Dan was arrested by police officers who had discovered Vics and Peds body on the side of the road. Dan has no recall of the events between the time he ingested the drug and his arrest.

Dan has been charged with murder of Vic, and the murder of Ped. Evidence of the above facts has been introduced by the prosecution. At trial, Dan testified about the event...

62
Views
0
Answers
University of Phoenix BUSINESS IT 210 1)Internet advertising typically is one-way and impersonal. Which of the

University of Phoenix

BUSINESS IT 210

  1. Internet advertising typically is one-way and impersonal.
  2. Which of the following electronic payments is ideal for micropayments?
  3. The primary characteristic of auctions is that prices are determined dynamically by competitive bidding.
  4. Clicks-and-mortar organizations conduct some business in the physical world, yet their primary business involves e-commerce activities.
  5. When two companies fight over a domain name, the company that has been in the business longer and has already purchased the domain name is considered the owner of the domain.
  6. When organizations request for bids for contracts on their own Web site for procurement, such business is classified as a(n)
  7. In general, the e-commerce Web sites of click-and-mortar companies operate only during the business hours of their brick-and-mortar stores.
  8. Cybersquatting refers to the tampering of existing Web sites and selling unrelated products.
...
134
Views
0
Answers
Formulating Functions in a pre-made incomplete excel sheet
Hi there, This is my first time submitting a question here. As I am extremely busy with multiple deadlines from several courses. I overlooked this one particular assignment have don't have enough time to complete it. The assignment consists of basic Excel functions such as VLOOKUP, COUNTIF, SUMIF. If you have the time to complete this within my desired completion date. I would greatly appreciate it. P.S. The Microsoft Word doc is a guideline on what to do. The Excel sheet is where you must complete the work....
155
Views
0
Answers
Peregrine Standardized Test
I have a peregrine standardized test coming up and I would need a B+ or better, a little info on the test: 1. It's not proctored or over webcam 2. Multiple choice and True/False 3. Exam assesses knowledge in 12 foundational business areas " Accounting, Finance, Business Integration and Strategy, Economics (Macro and Micro) etc. 4. The exam will take 60-90 minutes to complete Once the job start's I can send over the link for the test. Thank you...