Posted: February 19th, 2015

Coursework Assignment

Paper, Order, or Assignment Requirements

Economics 515

EXCEL Assignment 1

100 points

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 assignment is due on
February 26, 2015 at 11:59PM.  You are to e-mail the file to [email protected] by that time.  The response should contain an Excel file with multiple sheets with work areas clearly labeled (sheet tabs?) and a Word document summarizing your answers.  I will go over in class exactly what is expected in the summary document.

1. Find the mean, median and mode of all variables in the data.
2. Find the variance and standard deviation of all variables in the data.
3. Find the covariance and correlation between all of the numeric variables in the data.
4. 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.
5. Do a linear regression of sales on the numeric variables and present the results.
6. 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?
7. 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?
8. 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:
   1. What is the median of this distribution? What is it in the data?
   2. What percentage of the drivers drives over 59 mph theoretically? What percentage is it in the data?
   3. What percentage of drivers drives under 74 mph theoretically? What percentage is it in the data?
   4. What percentage of drivers drive between 62 and 69 mph theoretically? What percentage is it in the data?
   5. What is the 90^th percentile of the distribution theoretically? What is it in the data?
   6. What is the 25^th percentile of the distribution theoretically? What is it in the data?
   7. Graph this density function along with the data.
9. 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:
   1. What percentage of drivers drives over 75 mph theoretically? What is it in the data?
   2. What percentage of drivers drives under 55 mph theoretically? What is it in the data?
   3. What percentage of drivers drives between 60 and 70 mph theoretically? What is it in the data?
   4. What percentage of drivers drives between 55 and 75 mph theoretically? What is it in the data?
   5. What is the 90^th percentile speed theoretically? What is it in the data?
   6. What is the 95^th percentile speed theoretically? What is it in the data?
   7. 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:
    1. What is the probability of less than 20 accidents during the year theoretically? What is it in the data?
    2. What is the probability of no more than 75 accidents during the year theoretically? What is it in the data?
    3. What is the probability of less than 125 accidents during the year theoretically? What is it in the data?
    4. What is the probability of no more than 150 accidents during the year theoretically? What is it in the data?
    5. What is the probability of more than 40 accidents during the year theoretically? What is it in the data?
    6. What is the probability of at least 90 accidents during the year theoretically? What is it in the data?
    7. What is the probability of more than 140 accidents during the year theoretically? What is it in the data?
    8. What is the probability of at least 40 and no more than 70 accidents during the year theoretically? What is it in the data?
    9. What is the probability of more than 60 and less than 120 accidents during the year theoretically? What is it in the data?
    10. Graph this density function along with the data.