Mathematics
EXCEL Assignment - stats class

Question Description

I’m studying for my Statistics class and don’t understand how to answer this. Can you help me study?

EXCEL Assignment

This assignment has you perform a variety of statistical analyses on hypothetical sales data.  You are to answer ALL questions and to do your own assignment. The response should contain an Excel file with multiple sheets with work areas clearly labeled (sheet tabs?) and a Word document summarizing your answers. 


Stats Project.xls

project.doc


Unformatted Attachment Preview

MONTH DATE DAY Temp MIN WTHR HOLIDAY January 1 Monday 36 25 0 1 January 2 Tuesday 32 25 0 0 January 3 Wednesday 35 20 0 0 January 4 Thursday 36 28 0 0 January 5 Friday 33 25 0.1 0 January 6 Saturday 39 31 0.03 0 January 7 Sunday 40 20 0 0 January 8 Monday 38 34 0.19 0 January 9 Tuesday 36 27 0 0 January 10 Wednesday 43 28 0 0 January 11 Thursday 54 33 0 0 January 12 Friday 43 33 0 0 January 13 Saturday 43 29 0 0 January 14 Sunday 41 30 0 0 January 15 Monday 43 38 0.06 0 January 16 Tuesday 47 37 0 0 January 17 Wednesday 44 36 0 0 January 18 Thursday 38 34 0.03 0 January 19 Friday 43 36 0.9 0 January 20 Saturday 40 32 0.2 0 January 21 Sunday 32 25 0.12 0 January 22 Monday 34 22 0 0 January 23 Tuesday 36 25 0 0 January 24 Wednesday 47 31 0 0 January 25 Thursday 40 30 0 0 January 26 Friday 39 26 0 0 January 27 Saturday 45 35 0 0 January 28 Sunday 42 29 0 0 January 29 Monday 39 29 0 0 January 30 Tuesday 55 35 0.7 0 January 31 Wednesday 55 36 0 0 February 1 Thursday 50 39 0 0 February 2 Friday 51 29 0 0 February 3 Saturday 34 26 0 0 February 4 Sunday 43 27 0 0 February 5 Monday 41 36 0.76 0 February 6 Tuesday 47 37 0 0 February 7 Wednesday 51 36 0 0 February 8 Thursday 49 34 0 0 February 9 Friday 69 53 0 0 February 10 Saturday 65 34 0 0 February 11 Sunday 36 27 0 0 February 12 Monday 36 26 0.12 0 February 13 Tuesday 52 33 0 0 February 14 Wednesday 51 42 0.09 1 February 15 Thursday 55 43 0.09 0 February 16 Friday 44 40 0.75 0 February 17 Saturday 44 26 0.01 0 February 18 Sunday 35 22 0 0 February 19 Monday 49 26 0 0 February 20 Tuesday 63 38 0 0 SALES 7895.69 3002.99 2623.12 4678.12 4125.25 4112.69 2888.33 1978.55 2415.69 2915.64 2487.12 3100.61 3265.75 3334 3621.54 3050.25 2999.5 2895.6 2232.22 2135.61 2494.44 2315.6 2222 2659.22 2555.5 2987.21 2641.3 3005.98 3106.29 2315.69 2858.41 4555.25 5100.32 4698.52 4715.63 2065.25 5145.69 5523.65 5896.52 4698.3 4987.6 3875.69 3162.25 4125.55 3758.25 3695.8 3987.4 4166.9 4789.5 3698.5 5169.2 February February February February February February February February March March March March March March March March March March March March March March March March March March March March March March March March March March March March March March March April April April April April April April April April April April April April 21 22 23 24 25 26 27 28 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 1 2 3 4 5 6 7 8 9 10 11 12 13 Wednesday Thursday Friday Saturday Sunday Monday Tuesday Wednesday Thursday Friday Saturday Sunday Monday Tuesday Wednesday Thursday Friday Saturday Sunday Monday Tuesday Wednesday Thursday Friday Saturday Sunday Monday Tuesday Wednesday Thursday Friday Saturday Sunday Monday Tuesday Wednesday Thursday Friday Saturday Sunday Monday Tuesday Wednesday Thursday Friday Saturday Sunday Monday Tuesday Wednesday Thursday Friday 53 28 44 37 55 54 58 45 47 56 58 44 41 39 50 44 46 46 61 50 61 58 53 49 50 50 54 52 48 54 59 59 46 39 42 51 44 52 49 49 54 59 59 61 69 68 61 88 69 54 69 76 28 21 23 30 34 40 35 35 31 40 41 35 28 25 36 33 35 32 34 35 39 45 41 43 42 36 37 39 42 42 53 38 24 30 26 31 40 43 42 44 44 39 46 43 59 45 45 53 54 50 51 62 0 0.16 0.39 0 0.12 0 0 0 0 0 0 0.43 0.12 0 0 0 0.04 0 0 0.33 0.15 0 0.23 0.03 0.08 0 0 0.11 1.45 0 0 0 0 0 0 0 1.23 0.51 0.03 0 0 0 0 0 0.04 0.02 0.01 0.49 0 0.57 0 0.05 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 5236.99 4215.3 2998.56 4156.25 3856.45 3652.89 3241.56 3351.26 5442.25 5122.31 4982.33 3325.69 3162.32 5123.02 4987.65 4869.23 3659.11 4200.69 5680.25 4956.25 4756.25 5489.36 5569.21 5478.65 5321.88 5987.88 4644.21 4987.55 2098.33 4885.2 4000.29 4125.25 3327.56 3998.75 3687.45 3987.44 2857.62 3029.75 4012.56 4315.29 4698.79 4965.25 4123.26 4852.65 5236.24 5369.25 4125.25 3219.25 4005.65 4758.31 4125.69 4555.21 April April April April April April April April April April April April April April April April April May May May May May May May May May May May May May May May May May May May May May May May May May May May May May May May June June June June 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 1 2 3 4 Saturday Sunday Monday Tuesday Wednesday Thursday Friday Saturday Sunday Monday Tuesday Wednesday Thursday Friday Saturday Sunday Monday Tuesday Wednesday Thursday Friday Saturday Sunday Monday Tuesday Wednesday Thursday Friday Saturday Sunday Monday Tuesday Wednesday Thursday Friday Saturday Sunday Monday Tuesday Wednesday Thursday Friday Saturday Sunday Monday Tuesday Wednesday Thursday Friday Saturday Sunday Monday 69 65 52 50 50 60 65 70 84 82 85 54 63 76 64 61 70 77 81 84 85 80 65 63 68 71 81 85 74 68 68 71 67 59 63 75 64 56 62 77 76 72 65 73 77 77 71 72 67 76 72 77 52 48 42 37 37 36 45 55 60 61 54 47 44 45 51 44 46 53 59 61 65 60 51 45 51 56 62 61 60 53 50 52 53 54 58 59 51 50 40 47 57 60 60 61 59 59 58 51 57 62 60 57 0 0.2 0.15 0.12 0.03 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0.07 0 0 0 0 0 0 0 0 0 0.12 0.32 0.61 1.8 0 0 0.27 1.57 0.61 0 0.02 0 0 0.81 0.07 0 0.02 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 1 0 0 0 0 0 0 0 0 0 0 0 1 1 1 1 0 0 0 0 0 0 0 5069.78 3625.33 3875.4 3698.21 3666.55 3261.59 6521 6987.25 6005.8 6998.35 7021.51 5629.87 4589.65 5412.25 4965.23 4785.62 5982.79 6598.21 7214.59 6874.29 6125.87 6658.29 6002.3 5444.29 5879.33 5231.55 6958.65 7008.29 7215.56 7546.02 6250.3 6012.12 5998.29 5621.5 5548.99 7529.65 5845.25 4216.55 3269.21 5985.32 6002.56 7985.65 7596.21 8000.98 8956.25 8021.65 6958.32 5876.25 4695.25 6021.3 6985.4 6666.25 June June June June June June June June June June June June June June June June June June June June June June June June June June July July July July July July July July July July July July July July July July July July July July July July July July July July 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 Tuesday Wednesday Thursday Friday Saturday Sunday Monday Tuesday Wednesday Thursday Friday Saturday Sunday Monday Tuesday Wednesday Thursday Friday Saturday Sunday Monday Tuesday Wednesday Thursday Friday Saturday Sunday Monday Tuesday Wednesday Thursday Friday Saturday Sunday Monday Tuesday Wednesday Thursday Friday Saturday Sunday Monday Tuesday Wednesday Thursday Friday Saturday Sunday Monday Tuesday Wednesday Thursday 82 80 77 82 83 84 87 90 90 88 85 80 89 85 89 89 87 86 78 81 84 87 93 94 89 95 91 74 79 87 86 80 84 83 90 94 86 83 83 84 87 91 94 78 81 83 84 84 90 94 89 90 65 66 62 64 64 65 67 74 73 74 74 73 71 71 70 68 74 71 69 64 69 69 74 86 78 77 72 60 61 70 68 63 64 67 73 72 72 66 67 67 69 71 74 72 70 66 66 68 71 75 71 71 0.03 0.04 0.95 0 0 0 0 0 0 0 0.09 1.25 0 0 1 0.61 0.03 0.11 0.08 0 0 0 0 0 0 0.15 0.33 0 0 0.31 0.07 0 0 0.36 0 0.23 0 0 0 0 0 0 0.07 0.35 0 0 0 0 0 0 0.02 0.08 0 0 0 0 0 0 0 0 0 0 0 0 1 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 1 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 7051.49 6985.52 6987.45 7415.25 8005.69 7456.21 7662.25 7221.56 7036.98 7645.21 7714.56 6200.35 7665.25 8651.29 6598.25 7236.25 7888.6 7398.25 7456.21 8562.33 8854.62 7895.25 7005.62 6958.25 7241.33 7795.6 8012.3 7215.25 8254.26 9956.21 9156.23 8975.6 9002.25 7569.35 7456.28 6895.25 7452.65 7325.8 7456.9 7863.5 7695.23 7224.25 7004.14 6998.5 7456.33 8659.25 8745.65 7569.23 9006.25 7995.99 7665.25 7569.25 July July July July July August August August August August August August August August August August August August August August August August August August August August August August August August August August August August August August September September September September September September September September September September September September September September September September 27 28 29 30 31 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 Friday 80 Saturday 80 Sunday 72 Monday 81 Tuesday 82 Wednesday 87 Thursday 85 Friday 88 Saturday 86 Sunday 90 Monday 97 Tuesday 99 Wednesday 99 Thursday 101 Friday 99 Saturday 88 Sunday 83 Monday 85 Tuesday 84 Wednesday 84 Thursday 87 Friday 88 Saturday 84 Sunday 81 Monday 87 Tuesday 84 Wednesday 88 Thursday 84 Friday 86 Saturday 83 Sunday 83 Monday 90 Tuesday 90 Wednesday 85 Thursday 85 Friday 87 Saturday 84 Sunday 76 Monday 79 Tuesday 86 Wednesday 80 Thursday 82 Friday 83 Saturday 84 Sunday 84 Monday 87 Tuesday 84 Wednesday 79 Thursday 87 Friday 73 Saturday 71 Sunday 76 66 67 62 66 69 68 63 68 74 75 78 79 82 81 77 74 74 70 72 70 71 74 74 74 71 69 71 72 71 71 69 72 74 73 72 70 68 60 64 70 67 62 64 68 70 72 66 65 64 58 53 56 0 0 0.43 0.01 0 0 0 0 0.02 0 0 0 0 0 0.64 0.34 0.33 0.61 0.02 0 0 0 0 0.23 0.2 0 0 0 0 0 0 0 0 0 0.19 0.66 0 0 0 0 0 0 0 0 0 0.03 0 0 0 0.34 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 1 1 1 1 0 0 0 0 0 0 0 0 0 0 0 0 0 9756.25 9875.65 7214.98 7456.25 7789.25 7649.8 7745.69 7415.21 7956.2 7415.62 8125.65 8569.3 8411.5 8456.23 7985.25 8213.3 7965.25 7415.23 7655.2 7325.69 7451.25 8002.5 8154.6 6398.2 6987.5 7569.23 8569.23 7460.25 8756.23 8452.36 8562.21 7225.6 7014.25 7984.21 7444.25 8541.23 9658.99 9658.8 9009.26 8569.23 8825.6 8741.56 8623.25 8941.56 8825.6 7698.52 5623.25 5612.59 4125.25 5621.25 4985.25 3126.98 September September September September September September September September September September September September September September October October October October October October October October October October October October October October October October October October October October October October October October October October October October October October October November November November November November November November 17 18 19 20 21 22 23 24 25 26 27 28 29 30 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 1 2 3 4 5 6 7 Monday Tuesday Wednesday Thursday Friday Saturday Sunday Monday Tuesday Wednesday Thursday Friday Saturday Sunday Monday Tuesday Wednesday Thursday Friday Saturday Sunday Monday Tuesday Wednesday Thursday Friday Saturday Sunday Monday Tuesday Wednesday Thursday Friday Saturday Sunday Monday Tuesday Wednesday Thursday Friday Saturday Sunday Monday Tuesday Wednesday Thursday Friday Saturday Sunday Monday Tuesday Wednesday 78 79 79 72 77 84 81 80 69 66 68 63 66 62 68 79 84 83 82 71 58 57 63 72 78 78 76 76 69 70 59 59 65 74 78 81 76 81 75 58 52 52 61 65 63 70 80 71 67 59 56 70 59 62 64 66 65 68 67 66 53 48 52 51 53 52 52 58 62 62 61 52 45 40 41 47 54 57 61 61 54 50 45 41 43 49 58 57 58 63 58 44 42 40 38 47 48 48 57 57 50 43 38 49 0 0 0 0.94 0 0 0 0.12 0.33 0 0 0 0 0.12 0.06 0 0 0 0 0.02 0 0 0 0 0 0 0 0.48 0 0.15 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0.02 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 1 0 0 0 0 0 0 0 3251.25 3369.5 4560.21 3265.69 5698.6 5021.9 4965.29 4789.76 4599.25 4421.2 3985.6 4652.25 4950.25 4215.23 4125.6 3692.5 2985.69 3165.25 3269.55 3339.25 2365.29 2998.5 3025.69 3012.5 2963.25 2995.62 3225.25 2569.21 3698.52 3144.78 3069.75 3014.5 4006.3 4125.26 4251.99 3358.6 3495.6 3789.5 3659.55 3258.4 4000.99 3259.6 3311.25 3165.87 3698.25 3256.99 3485.75 3325.6 2698.56 2998.41 2415.6 3165.25 November November November November November November November November November November November November November November November November November November November November November November November December December December December December December December December December December December December December December December December December December December December December December December December December December December December December 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 Thursday Friday Saturday Sunday Monday Tuesday Wednesday Thursday Friday Saturday Sunday Monday Tuesday Wednesday Thursday Friday Saturday Sunday Monday Tuesday Wednesday Thursday Friday Saturday Sunday Monday Tuesday Wednesday Thursday Friday Saturday Sunday Monday Tuesday Wednesday Thursday Friday Saturday Sunday Monday Tuesday Wednesday Thursday Friday Saturday Sunday Monday Tuesday Wednesday Thursday Friday Saturday 67 64 69 57 52 56 66 72 74 60 58 63 62 50 59 59 63 65 65 57 60 60 68 73 54 56 62 69 73 63 53 52 47 55 51 57 64 59 44 56 58 54 45 42 44 54 47 41 39 37 45 47 50 45 41 43 37 38 40 46 50 48 43 44 39 37 36 41 50 58 51 48 55 55 59 52 44 40 42 51 52 53 42 40 37 44 42 48 54 41 35 43 43 40 37 34 34 33 36 31 30 26 27 28 0 0 0 0 0 0 0 0 0 0 0 0 0.03 0 0 0 0 2.29 0 0 0 0.03 0.01 0 0 0 0 0 0 0 0.56 0.01 0.01 0.3 0 0.01 0.17 0 0 0.21 0.11 0 0 0 0 0.18 0.15 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 1 1 1 1 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 1 1 0 0 0 0 2013.98 3069.42 4012.8 2587.41 2365.4 2875.64 2991.25 2456.31 4051.29 5687.99 4985.32 2876.23 3313.69 3698.25 7756.25 3698.41 4789.62 1002.39 2136.25 2987.65 2035.69 2236.98 2442.56 3069.45 3625.87 2069.54 2987.56 2658.41 2778.46 2955.26 3102.69 2013.69 2269.85 2415.33 2311.85 2466.25 2013.69 2997.65 3055.64 4007.89 4211.5 3069.85 3165.29 3301.99 3698.25 4225.69 3215.25 3620.59 3012.99 3698.25 4561.25 5468.55 December December 30 Sunday 31 Monday Mean Meadian Mode Variance ST 30 34 23 23 0 0 0 1 6321.98 8956.25 66.3 51 0.0933 0.075411 5195.528 68 51 0 0 4789.62 84 74 0 0 4125.25 292 240 0.0692 0.054373 4463486 17.1 15.5 0.263 0.23318 2112.696 EXCEL Assignment 1 This assignment has you perform a variety of statistical analyses on hypothetical sales data. You are to answer ALL questions and to do your own assignment. The response should contain an Excel file with multiple sheets with work areas clearly labeled (sheet tabs?) and a Word document summarizing your answers. 1. 2. 3. 4. 5. 6. 7. 8. 9. Find the mean, median and mode of all variables in the data. Find the variance and standard deviation of all variables in the data. Find the covariance and correlation between all of the numeric variables in the data. If you wished to predict the value of sales using a regression, which variables would you expect to be the most important based on the results of 3. Do a linear regression of sales on the numeric variables and present the results. Find the mean and variance of the numeric variables for the months of November –March and separately for the months of April – October. Do you think that the season is important in determining sales? Why? Any other relationships you notice? Find the mean and variance of the numeric variables for Friday-Sunday and separately for Monday-Thursday. Do you think the day of the week is important in determining sales? Why? What are relationships do you notice by day of week? Suppose that driver speeds on a highway are uniformly distributed on the interval [46,83]. Create a sample of 2,500 drivers in excel that behave this way (be sure to lock these drivers after creation so they don’t continually change). Answer the following questions: a. What is the median of this distribution? What is it in the data? b. What percentage of the drivers drives over 59 mph theoretically? What percentage is it in the data? c. What percentage of drivers drives under 74 mph theoretically? What percentage is it in the data? d. What percentage of drivers drive between 62 and 69 mph theoretically? What percentage is it in the data? e. What is the 90th percentile of the distribution theoretically? What is it in the data? f. What is the 25th percentile of the distribution theoretically? What is it in the data? g. Graph this density function along with the data. Suppose that the driver speeds on a highway are normally distributed with mean 67.3 and variance 86.4. Create a sample of 2,500 drivers in excel that behave this way (be sure to lock these drivers as you did in 8.)Answer the following questions: a. What percentage of drivers drives over 75 mph theoretically? What is it in the data? b. What percentage of drivers drives under 55 mph theoretically? What is it in the data? c. What percentage of drivers drives between 60 and 70 mph theoretically? What is it in the data? d. What percentage of drivers drives between 55 and 75 mph theoretically? What is it in the data? e. What is the 90th percentile speed theoretically? What is it in the data? f. What is the 95th percentile speed theoretically? What is it in the data? g. Graph this density function of speeds from 50 to 100 along with the data. 10. Suppose the probability of an accident on a highway on any given weekday is 30%. We are looking at a year of data (260 weekdays). Create a string of 260 observations that follow this distribution in excel (as in last two problems be sure to lock in your data). Answer the following questions: a. What is the probability of less than 20 accidents during the year theoretically? What is it in the data? b. What is the probability of no more than 75 accidents during the year theoretically? What is it in the data? c. What is the probability of less than 125 accidents during the year theoretically? What is it in the data? d. What is the probability of no more than 150 accidents during the year theoretically? What is it in the data? e. What is the probability of more than 40 accidents during the year theoretically? What is it in the data? f. What is the probability of at least 90 accidents during the year theoretically? What is it in the data? g. What is the probability of more than 140 accidents during the year theoretically? What is it in the data? h. What is the probability of at least 40 and no more than 70 accidents during the year theoretically? What is it in the data? i. What is the probability of more than 60 and less than 120 accidents during the year theoretically? What is it in the data? j. Graph this density function along with the data. ...
Student has agreed that all tutoring, explanations, and answers provided by the tutor will be used to help in the learning process and in accordance with Studypool's honor code & terms of service.

This question has not been answered.

Create a free account to get help with this and any other question!