I would like help completing the attached assignment.
MIDTERM EXAM You will need to use Excel to provide solutions to several of the answers. Please show all formulas that are used and show sufficient work so that I can follow how an answer was derived. Failure to provide the formulas and sufficient work may result in no credit being given for the problem. Where necessary, you may use excel to calculate your results, please save excel results and copy and paste the excel output into this exam or into a word document. Question 1 Open the dataset “Monet_STAT.xls.” The variables in the data set are: Price = Sale Price in $ (million) (deflated to 2010 dollars) Height = Height (inches), Width = Width (inches), Signed = Dummy variable = 1 if signed by the artist or not, 0 if not, The population: There are a total of 430 sales of this artist’s paintings. You have access to this entire set of sales. Your goal is to obtain a random sample from this Population of 430 paintings USING the instructions in part (a). a. Obtain a SIMPLE RANDOM sample of size 30 from this population of 430 paintings. To do so, follow these steps: First create (insert) a column to the left of the variable PRICE. In this column type in the formula =RAND(). This formula creates a random number between 0 and 1. Copy this formula down all 430 observations. Next copy all these random numbers into another new, empty column, where you paste the number NOT the formula. Call this new variable SORTER. Next, select all your data (all 430 observations AND the first row of Variable labels AND all columns of variables). Under DATA click sort. In the dialogue box sort by SORTER, in ascending order. Finally, pick the top (first 30) observations. Congratulations! You have now collected your first random sample! [Please save this sample data – I will ask you to use it later in the course.] b. Run a regression to determine how sale price is affected by the size of the painting and whether it was signed by Monet. (Be careful how you measure size.) c. Interpret the coefficient of size and the coefficient of signed. d. Provide a graphical illustration of the relationship between price, size, and signed using your answers to (b) e. Estimate or predict the sale price for the first observation in your data. f. Calculate the residual for the observation in (e). Explain why there is a residual in a way that someone who hasn’t studied statistics can understand? g. Suppose that a new (previously unknown) painting of Monet’s was recently discovered in your aunt’s attic. Your aunt tells you that it is 40” (ht) by 20” (width). Estimate the sale price of this painting, both if the painting were signed and not signed. h. What is your R-squared? What does this tell you about how well your regression fits the data? Explain this in a sentence in a way that can be understood by someone who doesn’t know statistics. i. Write down a short paragraph (5 sentences MAX) summarizing all your salient findings in (a) – (h) in a way that someone at Christie’s or Sotheby’s (two large art auction houses) could understand and use to price paintings. Here you are to use the feedback I gave you on how to write short reports of statistical findings we discussed in class. Question 2 Your goal in this question is to use the time series tools to forecast the sales volume of a stock. Go to: https://finance.yahoo.com and pick a stock (you may need to identify the stock’s abbreviation on the NASDAQ or DOW; for e.g. Microsoft is MSFT). Enter it into the quote look up search function. Then under historical data, download weekly data of that stock for approximately 1 year (so you should have about 52 observations, although I’m not too picky about this number). Focus on the variable “Volume” and “date.” STOCK I would like to use: Uber Technologies, Inc. (UBER) a.) Determine the best MOVING AVERAGE model to forecast stock prices. That is, use either the MSE (or MAD) to determine the validity of your model and find a model that best captures your data. (3) b.) Use your model to predict your chosen stock’s price going forward one week past your data’s range (3) c.) Use excel to produce a graph with both the forecasted Volume as well as the actual data (3) d.) Summarize your findings two or three sentences. (3) Question 3 The dataset “satisfaction_2019_STAT.xls” contains data on customer satisfaction responses from surveying an airline’s customers. The airline wants you to analyze this data to help it improves its customer satisfaction levels. Use the data to answer the following questions, which will in turn help determine how the airline should improve customer satisfaction. a.) What is the probability that a randomly selected customer is “satisfied” or “neutral/dissatisfied?” (2) b.) Suppose you know that a customer is “loyal,” are they more likely to be “satisfied” or “neutral/dissatisfied?” (3) c.) Suppose you know that a customer is a “business” traveler, are they more likely to be “satisfied” or “neutral/dissatisfied?” (3) d.) Use your answers to the previous questions to determine whether the events “satisfied” and “business” traveler dependent or independent events? Similarly, are the events “satisfied” and “loyal” dependent or independent events? (3) e.) Use your answers to the previous questions to help the airline determine how it should improve satisfaction. I.e. should it target business travelers or loyal customers? (2) Data Passenger IDSatisfactionCustomer TypeType of Travel 13450neutral or dissatisfiedLoyal CustomerPersonal Travel 48993satisfiedLoyal CustomerBusiness travel 85565satisfiedLoyal CustomerBusiness travel 73483neutral or dissatisfiedLoyal CustomerPersonal Travel 112879satisfiedLoyal CustomerBusiness travel 4205neutral or dissatisfieddisloyal CustomerBusiness travel 10951neutral or dissatisfiedLoyal CustomerBusiness travel 88333neutral or dissatisfieddisloyal CustomerBusiness travel 89018neutral or dissatisfiedLoyal CustomerBusiness travel 99565satisfiedLoyal CustomerBusiness travel 50646neutral or dissatisfiedLoyal CustomerPersonal Travel 60665neutral or dissatisfiedLoyal CustomerPersonal Travel 39412satisfiedLoyal CustomerBusiness travel 55910neutral or dissatisfiedLoyal CustomerPersonal Travel 43163neutral or dissatisfiedLoyal CustomerBusiness travel 68959satisfieddisloyal CustomerBusiness travel 127192neutral or dissatisfiedLoyal CustomerBusiness travel 77509satisfieddisloyal CustomerBusiness travel 121433neutral or dissatisfiedLoyal CustomerBusiness travel 109180satisfiedLoyal CustomerBusiness travel 100643neutral or dissatisfiedLoyal CustomerPersonal Travel 39126satisfiedLoyal CustomerBusiness travel 9007satisfiedLoyal CustomerBusiness travel 15446neutral or dissatisfiedLoyal CustomerBusiness travel 109515neutral or dissatisfiedLoyal CustomerBusiness travel 127198satisfiedLoyal CustomerBusiness travel 20877satisfiedLoyal CustomerBusiness travel 36664neutral or dissatisfiedLoyal CustomerPersonal Travel 99470neutral or dissatisfiedLoyal CustomerBusiness travel 37139satisfiedLoyal CustomerBusiness travel 107829neutral or dissatisfiedLoyal CustomerPersonal Travel 18021satisfiedLoyal CustomerBusiness travel 35767satisfiedLoyal CustomerBusiness travel 88510satisfiedLoyal CustomerBusiness travel 34095neutral or dissatisfieddisloyal CustomerBusiness travel 49214satisfiedLoyal CustomerBusiness travel 94053satisfiedLoyal CustomerBusiness travel 73161satisfiedLoyal CustomerBusiness travel 107223neutral or dissatisfiedLoyal CustomerBusiness travel 101332satisfieddisloyal CustomerBusiness travel 51966satisfiedLoyal CustomerBusiness travel 27945neutral or dissatisfiedLoyal CustomerBusiness travel 38414satisfiedLoyal CustomerBusiness travel 84844neutral or dissatisfiedLoyal CustomerBusiness travel 34088neutral or dissatisfiedLoyal CustomerPersonal Travel 120886neutral or dissatisfiedLoyal CustomerBusiness travel 22188neutral or dissatisfieddisloyal CustomerBusiness travel 4673neutral or dissatisfieddisloyal CustomerBusiness travel 24247satisfiedLoyal CustomerPersonal Travel 25388neutral or dissatisfieddisloyal CustomerBusiness travel 56184neutral or dissatisfiedLoyal CustomerPersonal Travel 90307neutral or dissatisfiedLoyal CustomerPersonal Travel 127491neutral or dissatisfiedLoyal CustomerPersonal Travel 27769neutral or dissatisfieddisloyal CustomerBusiness travel 57676satisfiedLoyal CustomerBusiness travel 40233satisfiedLoyal CustomerBusiness travel 16331neutral or dissatisfiedLoyal CustomerPersonal Travel 16607neutral or dissatisfieddisloyal CustomerBusiness travel 100544satisfiedLoyal CustomerBusiness travel 40902neutral or dissatisfiedLoyal CustomerPersonal Travel 8813neutral or dissatisfieddisloyal CustomerBusiness travel 116199neutral or dissatisfiedLoyal CustomerPersonal Travel 78504neutral or dissatisfieddisloyal CustomerBusiness travel 52968neutral or dissatisfiedLoyal CustomerPersonal Travel 53184neutral or dissatisfiedLoyal CustomerBusiness travel 16278neutral or dissatisfiedLoyal CustomerPersonal Travel 66447neutral or dissatisfieddisloyal CustomerBusiness travel 76487satisfiedLoyal CustomerBusiness travel 39091satisfiedLoyal CustomerBusiness travel 110687neutral or dissatisfiedLoyal CustomerPersonal Travel 87722satisfiedLoyal CustomerBusiness travel 35835satisfiedLoyal CustomerBusiness travel 67775neutral or dissatisfieddisloyal CustomerBusiness travel 34926neutral or dissatisfiedLoyal CustomerBusiness travel 29314neutral or dissatisfiedLoyal CustomerPersonal Travel 59819neutral or dissatisfiedLoyal CustomerPersonal Travel 123115neutral or dissatisfieddisloyal CustomerBusiness travel 123313satisfieddisloyal CustomerBusiness travel 74990neutral or dissatisfiedLoyal CustomerPersonal Travel 5209satisfiedLoyal CustomerBusiness travel 127383neutral or dissatisfiedLoyal CustomerBusiness travel 112342neutral or dissatisfieddisloyal CustomerBusiness travel 1714satisfiedLoyal CustomerBusiness travel 99149satisfiedLoyal CustomerBusiness travel 64834satisfiedLoyal CustomerBusiness travel 98458neutral or dissatisfiedLoyal CustomerBusiness travel 35102satisfiedLoyal CustomerBusiness travel 27674neutral or dissatisfieddisloyal CustomerBusiness travel 46736neutral or dissatisfiedLoyal CustomerPersonal Travel 95095neutral or dissatisfiedLoyal CustomerPersonal Travel 93881satisfiedLoyal CustomerBusiness travel 61921neutral or dissatisfieddisloyal CustomerBusiness travel 30392neutral or dissatisfiedLoyal CustomerPersonal Travel 62792neutral or dissatisfiedLoyal CustomerPersonal Travel 53352satisfiedLoyal CustomerBusiness travel 34184satisfiedLoyal CustomerBusiness travel 47273satisfiedLoyal CustomerBusiness travel 9450neutral or dissatisfiedLoyal CustomerPersonal Travel 51763neutral or dissatisfiedLoyal CustomerPersonal Travel 39630satisfiedLoyal CustomerBusiness travel 28563satisfiedLoyal CustomerBusiness travel 25001neutral or dissatisfiedLoyal CustomerPersonal Travel 22609satisfiedLoyal CustomerBusiness travel 51386neutral or dissatisfiedLoyal CustomerPersonal Travel 25960neutral or dissatisfiedLoyal CustomerBusiness travel 108603neutral or dissatisfiedLoyal CustomerBusiness travel 40080neutral or dissatisfiedLoyal CustomerPersonal Travel 15678neutral or dissatisfieddisloyal CustomerBusiness travel 67853neutral or dissatisfiedLoyal CustomerPersonal Travel 71503satisfiedLoyal CustomerBusiness travel 102940neutral or dissatisfiedLoyal CustomerPersonal Travel 1862satisfiedLoyal CustomerBusiness travel 89643neutral or dissatisfiedLoyal CustomerBusiness travel 100413neutral or dissatisfiedLoyal CustomerBusiness travel 27638satisfiedLoyal CustomerBusiness travel 82364neutral or dissatisfiedLoyal CustomerPersonal Travel 124291satisfiedLoyal CustomerBusiness travel 48192satisfiedLoyal CustomerBusiness travel 38071neutral or dissatisfieddisloyal CustomerBusiness travel 121393neutral or dissatisfiedLoyal CustomerBusiness travel 52437neutral or dissatisfiedLoyal CustomerPersonal Travel 110303satisfiedLoyal CustomerBusiness travel 68709neutral or dissatisfiedLoyal CustomerPersonal Travel 84230neutral or dissatisfiedLoyal CustomerPersonal Travel 11607neutral or dissatisfiedLoyal CustomerPersonal Travel 77815neutral or dissatisfiedLoyal CustomerPersonal Travel 89330satisfiedLoyal CustomerBusiness travel 112117satisfieddisloyal CustomerBusiness travel 50087satisfiedLoyal CustomerBusiness travel 4170neutral or dissatisfiedLoyal CustomerPersonal Travel 115845satisfiedLoyal CustomerBusiness travel 84382neutral or dissatisfiedLoyal CustomerBusiness travel 31544satisfiedLoyal CustomerPersonal Travel 38712satisfiedLoyal CustomerPersonal Travel 61299neutral or dissatisfiedLoyal CustomerPersonal Travel 104758neutral or dissatisfiedLoyal CustomerPersonal Travel 6113satisfiedLoyal CustomerBusiness travel 100527neutral or dissatisfiedLoyal CustomerPersonal Travel 22882neutral or dissatisfiedLoyal CustomerPersonal Travel 116465neutral or dissatisfiedLoyal CustomerPersonal Travel 19540satisfiedLoyal CustomerBusiness travel 77501satisfiedLoyal CustomerPersonal Travel 94644neutral or dissatisfiedLoyal CustomerBusiness travel 81406neutral or dissatisfiedLoyal CustomerPersonal Travel 24713satisfiedLoyal CustomerBusiness travel 73509neutral or dissatisfiedLoyal CustomerBusiness travel 50040neutral or dissatisfieddisloyal CustomerBusiness travel 78552neutral or dissatisfiedLoyal CustomerBusiness travel 106183neutral or dissatisfiedLoyal CustomerPersonal Travel 19386neutral or dissatisfiedLoyal CustomerPersonal Travel 81576satisfiedLoyal CustomerBusiness travel 57610satisfiedLoyal CustomerPersonal Travel 24345neutral or dissatisfiedLoyal CustomerPersonal Travel 129629satisfieddisloyal CustomerBusiness travel 114438satisfiedLoyal CustomerBusiness travel 122355satisfiedLoyal CustomerBusiness travel 123695neutral or dissatisfiedLoyal CustomerPersonal Travel 86535neutral or dissatisfiedLoyal CustomerPersonal Travel 15297neutral or dissatisfieddisloyal CustomerBusiness travel 3713neutral or dissatisfiedLoyal CustomerPersonal Travel 22459neutral or dissatisfiedLoyal CustomerPersonal Travel 33957neutral or dissatisfiedLoyal CustomerBusiness travel 23118satisfiedLoyal CustomerBusiness travel 127095neutral or dissatisfieddisloyal CustomerBusiness travel 81259satisfiedLoyal CustomerBusiness travel 74429satisfiedLoyal CustomerBusiness travel 46379neutral or dissatisfiedLoyal CustomerBusiness travel 80194satisfiedLoyal CustomerPersonal Travel 31016neutral or dissatisfiedLoyal CustomerPersonal Travel 58478satisfiedLoyal CustomerBusiness travel 118809neutral or dissatisfiedLoyal CustomerPersonal Travel 120938satisfiedLoyal CustomerBusiness travel 13865satisfiedLoyal CustomerBusiness travel 45411satisfiedLoyal CustomerBusiness travel 26877neutral or dissatisfiedLoyal CustomerBusiness travel 97418satisfieddisloyal CustomerBusiness travel 114166satisfiedLoyal CustomerBusiness travel 93017neutral or dissatisfieddisloyal CustomerBusiness travel 116525neutral or dissatisfiedLoyal CustomerPersonal Travel 47430neutral or dissatisfiedLoyal CustomerBusiness travel 113771neutral or dissatisfiedLoyal CustomerPersonal Travel 113209satisfieddisloyal CustomerBusiness travel 5146neutral or dissatisfiedLoyal CustomerBusiness travel 43570neutral or dissatisfieddisloyal CustomerBusiness travel 121429neutral or dissatisfiedLoyal CustomerBusiness travel 50918satisfiedLoyal CustomerPersonal Travel 126800satisfiedLoyal CustomerBusiness travel 121889neutral or dissatisfiedLoyal CustomerPersonal Travel 67986neutral or dissatisfiedLoyal CustomerBusiness travel 24482neutral or dissatisfieddisloyal CustomerBusiness travel 47666satisfieddisloyal CustomerBusiness travel 66087satisfieddisloyal CustomerBusiness travel 99014satisfiedLoyal CustomerPersonal Travel 74564neutral or dissatisfiedLoyal CustomerBusiness travel 11482neutral or dissatisfiedLoyal CustomerPersonal Travel 43686neutral or dissatisfieddisloyal CustomerBusiness travel 3367satisfiedLoyal CustomerBusiness travel 58100neutral or dissatisfiedLoyal CustomerPersonal Travel 124798neutral or dissatisfiedLoyal CustomerBusiness travel 15146neutral or dissatisfieddisloyal CustomerBusiness travel 40621neutral or dissatisfiedLoyal CustomerPersonal Travel 56665satisfiedLoyal CustomerPersonal Travel 96223neutral or dissatisfiedLoyal CustomerBusiness travel 26803neutral or dissatisfieddisloyal CustomerBusiness travel 111719neutral or dissatisfiedLoyal CustomerPersonal Travel 185neutral or dissatisfiedLoyal CustomerBusiness travel 52595neutral or dissatisfiedLoyal CustomerBusiness travel 79716neutral or dissatisfiedLoyal CustomerPersonal Travel 17380neutral or dissatisfiedLoyal CustomerBusiness travel 115180neutral or dissatisfieddisloyal CustomerBusiness travel 57236satisfiedLoyal CustomerBusiness travel 127521satisfieddisloyal CustomerBusiness travel 19642neutral or dissatisfiedLoyal CustomerPersonal Travel 24435satisfiedLoyal CustomerBusiness travel 76322neutral or dissatisfiedLoyal CustomerPersonal Travel 114797satisfiedLoyal CustomerBusiness travel 32765neutral or dissatisfieddisloyal CustomerBusiness travel 77477neutral or dissatisfieddisloyal CustomerBusiness travel 29993neutral or dissatisfiedLoyal CustomerBusiness travel 54106satisfiedLoyal CustomerBusiness travel 103152neutral or dissatisfieddisloyal CustomerBusiness travel 35817satisfiedLoyal CustomerBusiness travel 33940neutral or dissatisfieddisloyal CustomerBusiness travel 68329satisfiedLoyal CustomerPersonal Travel 61573satisfiedLoyal CustomerBusiness travel 31374neutral or dissatisfiedLoyal CustomerPersonal Travel 29724neutral or dissatisfiedLoyal CustomerPersonal Travel 101139satisfiedLoyal CustomerBusiness travel 102263satisfiedLoyal CustomerBusiness travel 86189neutral or dissatisfiedLoyal CustomerBusiness travel 74773neutral or dissatisfiedLoyal CustomerPersonal Travel 26468neutral or dissatisfiedLoyal CustomerPersonal