1. For the variables Shipping Costs and Customer Segment, produce a frequency table and appropriate chart to describe the data. Place both within the Word file. ( 6 marks)
2. Calculate and explain a 95% confidence interval for the average sales amount of orders for the home office customers only. Show Excel output and explanation in Word file. (6 marks)

3. It is often felt that the order priority of critical would have higher shipping costs than an order of low priority. Investigate this contention by carrying out an appropriate hypothesis test using Excel. State null and alternate hypotheses, and give Excel output to verify your conclusion. Assume equal variances. Place hypotheses, Excel output and conclusion in the Word File. (8 marks)

4. You will investigate the relationship between the sales in dollars and the order quantity. In excel you will undertake a full regression analysis,

a. including a scatter diagram,

b. an estimate of the linear regression model

c. stating the coefficients of correlation and determination and what they mean in relation to this case

d. a test of the hypothesis that there is no linear relationship between sales and the order quantity for an order. State hypotheses, values and conclusion.

5 Part B Data File Excel Tusharjoitely File Home Insert Draw Page Layout Formulas Data Review View Help Tell me what you want to do Share . Calibri ED - ' AutoSurm - - 11 ab Wrap Text General 47 O Fill Paste BIU > Merge & Center - %% .nan OU 7.0 Insert Delete Format Conditional Format as Cell Formatting Table Styles Styles Clear Sort & Find & Filter - Select Editing Clipboard Font 5 Alignment Nurnber 19 Cells A H6 & Consumer 1 L M N o R 5 T V A B C D E F G H H 1 1 Order IDOrder Prioritrder Quantity Sales Ship Modeshipping Cost Region Customer SegmenDaya te ship 2 16005 1 26 $172.33 1 $5.74 w 2 3 34660 0 36 $3,832.37 1 $5.81 W Home Olice 2 4 38146 1 45 $341.39 2 1 $6.16 w Small Business 4 S 20486 42 $163.98 1 $0.70 ET Corporate 1 5 35549 7 75 $174.03 1 $5.74 w Consumer 2 7 15519 2 49 $138.96 1 $0.70 E Small Business 2 B 18432 3 12 $76,42 3 $0.83 E Corporate 2. 9 52929 3 19 $59.94 1 $1.25 w Small Business 1 10 40643 23 $258.74 1 $5.72 E Home Office 2 11 31556 4 35 $269.25 $3.6% w Consumer 1 12 47459 0 10 $197.21 1 $1.19 F Hare Office 1 13 36679 4 28 $1,491.83 1 $8.99 E Corporate 3 14 46372 3 7 7 $375.57 1 $6.11 E Consumer 0 15 6413 2 41 $159.26 Su.So E Home Dffice 1 16 2790 3 8 $460.59 1 $8.99 W w Consumer 1 17 57350 3 33 $140.63 1 1 $5.48 W Small Business 1 18 48512 0 47 $143.26 3 $0.81 w Consumer 0 19 30566 1 34 $225.45 1 $9.54 w Small Business 2 20 42788 4 26 $241.92 $2.27 W Small Business 2 21 3250 3 38 $261.28 1 SR.74 E Corporate 2 2253443 35 $1,477.63 1 $1.62 w Consumer 1 23 11815 1 22 $351.27 1 $7.42 E Consumer 2 24 15428 4 50 $1,444.96 3 $4.00 w Small Business 1 25 42061 1 $32.49 1 $1.39 Small Business 2 2 23558 1 17 $579.71 2 $5.51 E Home Office 5 27 54215 3 14 $321.21 1 $1.99 w Small Business 2 28 12323 0 32 $513.74 1 $1.13 E Corporate 1 29 51008 3 20 $269.66 $4.59 E Consumer 2 30 35360 $41.06 $6.28 Corporate Data description Sheet1 Data 4 Windows Ink Workspace + 7296 16:42 Type here to search O ? ENG 16-11-2020 5 Part B Data File Excel Tusharjoitely File Home Insert Draw Page Layout Formulas Data Review View Help Tell me what you want to do Share . Calibri ED - 11 - ab Wrap Text General 47 0 Paste BIU A- + Merge & Center - %% .nan OU 7.0 Insert Delete Format Conditional Format as Cell Formatting Table Styles Styles AutoSum - Fill Sort & Find & Clear Filter - Select Editing Clipboard Font 5 Alignment Nurnber Cells A H6 X & Consumer K M N R s T V B 4 Z 2 2. 0 E 1 1 29 1 2 3 3 1 10 1 1 1 1 17 42 49 15 41 2S F $4.50 $5.03 $8.50 $5.52 $1.01 $9.20 $10.99 $4.93 $8.99 $4.30 $4.45 1 1 1 1 2 5 1 2 2 1 2 2 1 1 A 31 6413 32 3347 3351200 34 10010 35 2214 36 15205 37186 38 3955 39 43539 40 57799 41 1034 42 326 43 15623 44 1229 45 51650 4637891 47 3488 48 42081 49 45243 50 40134 51 16624 52 37198 53 1888 54 23315 55 38784 56 40964 57 56577 53 49479 59 30620 60 50657 61 SCAS 621 4 O 2 3 3 3 3 2 3 3 3 3 3 4 1 1 2 1 1 2 0 28 1/ 21 31 43 24 17 36 98 D $311.OR $270.03 $1,617.8 $767,34 $48.24 $1,53.51 $ $14,146.73 $71.61 $2,303.71 $12.343.07 $1.679,04 $100.95 $115.99 $253.17 $10.450.83 $163.36 $ $86.29 $164.34 $154.AS $9.73 $1,95943 $240.63 $131.27 $2,950,14 $298.12 $301.12 S7,497.05 55,615,40 $220.82 960.02 $127.50 1 1 1 1 2 1 1 G H E Corpcrate w Corporate W Small Business w Corscrate E Small Busines w Corporale w Home Ottice E Corporate w Consumer Consumer Consumer E Consume w Consumer E Home Office E Consumer E Small Business E Consumer E Small Business E Corpcrate w Small Business E Small Business E Consumer E Home Office E El Corporate Home Office E Corporate E E Small Business E Consumer E Corpcrate E CUI PUTNI E Corporate 1 2 2 3 5 2 1 1 1 1 1 1 1 49 31 5 $4.GR $1.39 $8.06 $6.60 $0.49 $5.0 $5.13 $4.72 $1.62 $8.53 $4.36 $11.20 $0.50 $8.37 $4.12 $6.30 $1.09 $1.93 $4.95 2 4 5 21 50 40 30 46 2 3 1 1 2 2 3 3 0 1 1 2 3 1 2 1 2 0 4 2 2 4 3 4 16 27 1 Data description Sheet1 Data + 4 Windows Ink Workspace + 70% 16:43 ka ENG 16-11-2020 Type here to search O ? 5 Part B Data File Excel Tusharjoitely File Home Insert Draw Page Layout Formulas Data Review View Help Tell me what you want to do Share . Calibri ED - ' AutoSurm - - 11 ab Wrap Text General 47 O Fill Paste BIU > Merge & Center - %% .nan OU 7.0 Insert Delete Format Conditional Format as Cell Formatting Table Styles Styles Clear Sort & Find & Filter - Select Editing Clipboard Font 5 Alignment Nurnber 19 Cells A H6 & Consumer 1 L M N o R 5 T V A B C D E F G H H 1 1 Order IDOrder Prioritrder Quantity Sales Ship Modeshipping Cost Region Customer SegmenDaya te ship 2 16005 1 26 $172.33 1 $5.74 w 2 3 34660 0 36 $3,832.37 1 $5.81 W Home Olice 2 4 38146 1 45 $341.39 2 1 $6.16 w Small Business 4 S 20486 42 $163.98 1 $0.70 ET Corporate 1 5 35549 7 75 $174.03 1 $5.74 w Consumer 2 7 15519 2 49 $138.96 1 $0.70 E Small Business 2 B 18432 3 12 $76,42 3 $0.83 E Corporate 2. 9 52929 3 19 $59.94 1 $1.25 w Small Business 1 10 40643 23 $258.74 1 $5.72 E Home Office 2 11 31556 4 35 $269.25 $3.6% w Consumer 1 12 47459 0 10 $197.21 1 $1.19 F Hare Office 1 13 36679 4 28 $1,491.83 1 $8.99 E Corporate 3 14 46372 3 7 7 $375.57 1 $6.11 E Consumer 0 15 6413 2 41 $159.26 Su.So E Home Dffice 1 16 2790 3 8 $460.59 1 $8.99 W w Consumer 1 17 57350 3 33 $140.63 1 1 $5.48 W Small Business 1 18 48512 0 47 $143.26 3 $0.81 w Consumer 0 19 30566 1 34 $225.45 1 $9.54 w Small Business 2 20 42788 4 26 $241.92 $2.27 W Small Business 2 21 3250 3 38 $261.28 1 SR.74 E Corporate 2 2253443 35 $1,477.63 1 $1.62 w Consumer 1 23 11815 1 22 $351.27 1 $7.42 E Consumer 2 24 15428 4 50 $1,444.96 3 $4.00 w Small Business 1 25 42061 1 $32.49 1 $1.39 Small Business 2 2 23558 1 17 $579.71 2 $5.51 E Home Office 5 27 54215 3 14 $321.21 1 $1.99 w Small Business 2 28 12323 0 32 $513.74 1 $1.13 E Corporate 1 29 51008 3 20 $269.66 $4.59 E Consumer 2 30 35360 $41.06 $6.28 Corporate Data description Sheet1 Data 4 Windows Ink Workspace + 7296 16:42 Type here to search O ? ENG 16-11-2020 5 Part B Data File Excel Tusharjoitely File Home Insert Draw Page Layout Formulas Data Review View Help Tell me what you want to do Share . Calibri ED - 11 - ab Wrap Text General 47 0 Paste BIU A- + Merge & Center - %% .nan OU 7.0 Insert Delete Format Conditional Format as Cell Formatting Table Styles Styles AutoSum - Fill Sort & Find & Clear Filter - Select Editing Clipboard Font 5 Alignment Nurnber Cells A H6 X & Consumer K M N R s T V B 4 Z 2 2. 0 E 1 1 29 1 2 3 3 1 10 1 1 1 1 17 42 49 15 41 2S F $4.50 $5.03 $8.50 $5.52 $1.01 $9.20 $10.99 $4.93 $8.99 $4.30 $4.45 1 1 1 1 2 5 1 2 2 1 2 2 1 1 A 31 6413 32 3347 3351200 34 10010 35 2214 36 15205 37186 38 3955 39 43539 40 57799 41 1034 42 326 43 15623 44 1229 45 51650 4637891 47 3488 48 42081 49 45243 50 40134 51 16624 52 37198 53 1888 54 23315 55 38784 56 40964 57 56577 53 49479 59 30620 60 50657 61 SCAS 621 4 O 2 3 3 3 3 2 3 3 3 3 3 4 1 1 2 1 1 2 0 28 1/ 21 31 43 24 17 36 98 D $311.OR $270.03 $1,617.8 $767,34 $48.24 $1,53.51 $ $14,146.73 $71.61 $2,303.71 $12.343.07 $1.679,04 $100.95 $115.99 $253.17 $10.450.83 $163.36 $ $86.29 $164.34 $154.AS $9.73 $1,95943 $240.63 $131.27 $2,950,14 $298.12 $301.12 S7,497.05 55,615,40 $220.82 960.02 $127.50 1 1 1 1 2 1 1 G H E Corpcrate w Corporate W Small Business w Corscrate E Small Busines w Corporale w Home Ottice E Corporate w Consumer Consumer Consumer E Consume w Consumer E Home Office E Consumer E Small Business E Consumer E Small Business E Corpcrate w Small Business E Small Business E Consumer E Home Office E El Corporate Home Office E Corporate E E Small Business E Consumer E Corpcrate E CUI PUTNI E Corporate 1 2 2 3 5 2 1 1 1 1 1 1 1 49 31 5 $4.GR $1.39 $8.06 $6.60 $0.49 $5.0 $5.13 $4.72 $1.62 $8.53 $4.36 $11.20 $0.50 $8.37 $4.12 $6.30 $1.09 $1.93 $4.95 2 4 5 21 50 40 30 46 2 3 1 1 2 2 3 3 0 1 1 2 3 1 2 1 2 0 4 2 2 4 3 4 16 27 1 Data description Sheet1 Data + 4 Windows Ink Workspace + 70% 16:43 ka ENG 16-11-2020 Type here to search O