____________________________________________________________________________________ BUSI 410 FINAL EXAM - Fall 2013 The regular exam length is 3 hours. This exam consists of three short cases: Case Robertson Express Bus Dean Blackburn's Boat MSFT Pag es 3-7 Poin ts 40 8-11 30 1216 Tota l Gra de 30 100 Answer the questions after each case. Show all of your work on the exam paper. The cases/questions are not ordered in difficulty. If you are stuck, try the next question! This exam is open book, open notes, and open laptop. However, no communication of any kind is allowedverbal, written, or electronic. Disable your laptop's wifi/internet connectivity. Turn off and stow away your cell phone. Name_________________________________________ Please Print Section (circle one): 2:00 9:30 11:00 12:30 Page 1 of 18 Honor Code Pledge: I will neither give nor receive unauthorized aid during this exam. Signature______________________________________ ****** DO NOT TURN TO NEXT PAGE UNTIL YOU ARE ASKED TO ****** Page 2 of 18 Robertson Express Bus The Triangle Transit Authority (TTA) operates the Robertson Express Bus to offer transit services for students to commute between UNC and Duke. The buses are scheduled to run between Franklin St. and Duke Chapel every 30 minutes on weekdays from 7:30AM to 11:30PM. There are two routes: one via US 15-501, which is always used, and another via Erwin Rd, which is only used as a detour during peak hours. In each semester, TTA collects a sample of transit times of the Robertson Express Bus to evaluate its on-time performance. TTA randomly selected 40 days this Fall. On each of the 40 days, a TTA staff member boards the bus during off-peak hours and records the transit time. The sorted (short to long) off-peak transit times are listed in Column B in the table below. Then in the afternoon during peak hours, two TTA staff members simultaneously board two buses, one taking the 15-501 route and one taking the Erwin Rd route, and record the transit times. The original (unsorted) peak transit times are listed in Columns C and D, where the two transit times in the same row are taken on the same day, at the same time. Fortunately, none of these transit times were affected by rare events such as basketball games or traffic accidents, therefore you can assume that the transit times in the sample vary due to many minor random factors, rather than a few dominant major factors. You are hired as a consultant to answer the following questions by analyzing the data. 1 2 3 ... 27 28 29 A B Count Off-Peak, via 15-501 1 18.84 2 19.06 ... ... 26 24.00 27 24.31 28 25.08 C D Peak, via 15-501 Peak, via Erwin Rd 52.60 27.17 23.79 30.30 ... ... 29.20 27.15 35.01 41.53 42.03 34.59 Page 3 of 18 ... 40 41 42 43 44 ... 39 40 ... 26.15 28.20 ... 27.11 31.93 ... 29.23 31.18 AVERAGE STDEV.S 24.96 3.14 33.66 8.65 27.94 6.76 1. To test whether during peak hours the Erwin route is faster than the 15-501 route, an analyst used the Excel formula T.TEST(C2:C41, D2:D41, ____, ____), where the last two parameters were curiously missing. What values would you use for these two parameters? Please justify. (4pts) 2. TTA wants to advertise on their pamphlet that even during peak hours, the average transit time from UNC to Duke using the route via Erwin Rd is less than 30 minutes. Please perform a statistical test to see if you can provide evidence to support this claim. Clearly state your null and alternative hypotheses, show all necessary calculations, and state your conclusion in non-technical terms. Use significance level 0.05. (8pts) Page 4 of 18 3. Please construct the exact 99% confidence interval of the average transit time from UNC to Duke during off-peak hours. (6pts) 4. Please construct an approximate 95% confidence interval of how much shorter the average transit time is during off-peak hours, compared with the average transit time during peak hours, using the route via 15-501. (6pts) Page 5 of 18 5. TTA states a goal that at least 50% of the time, one can travel by the Robertson Express Bus from UNC to Duke during off-peak hours in less than 25 minutes. a. In your sample, what percentage of the trips during offpeak hours took less than 25 minutes? (1pts) b. Based on the sample, do you have 95% confidence that TTA has achieved its goal that at least 50% of the time during off-peak hours, the transit time from UNC to Duke is less than 25 minutes? Please justify. (Hint: the answer is related to 5.a.)(4pts) c. What is your highest confidence that TTA has achieved this goal? Please justify. (Hint: the answer does not have to be perfectly accurate; just state your best estimate and provide reasoning.) (2pts) Page 6 of 18 d. In your sample, approximately what percentage of the trips during peak hours via 15-501 took less than 25 minutes? Please explain how you make the estimate. (Hint: the peak-hour transit times are not ordered.) (3pts) 6. When TTA introduced the detour route via Erwin Rd, its claim was that during peak hours, the Erwin Rd route will be on average at least 5 minutes shorter compared to the regular 15-501 route. Please show how you would statistically test this claim in the table using any Excel formulae as you need, and explain how to interpret your test results below. Please use significance level 0.05. (6pts) Page 7 of 18 1 2 C D Peak, Peak, via via Erwin 15-501 Rd 52.60 27.17 ... ... 31.93 F F ... 4 1 4 2 4 3 4 4 4 5 4 6 E 31.18 Page 8 of 18 Dean Blackburn's Boat As the Fall semester winds down, Dean Blackburn has begun thinking about Spring semester and realizes that the last day of class will be on April 24th, with BSBA graduation scheduled for May 11th. Once he has finished passing out diplomas, Dean Blackburn realizes it would be a great time to enjoy boating on Jordan Lake. The only problem is he doesn't have a boat. Undeterred, Dean Blackburn turns to Craigslist to find out how much it will cost to buy a used boat for his summer enjoyment. He wants to figure out what influences the price of a used boat, and uses the data from Craigslist to create a regression model. Dean Blackburn found 30 random used boat ads on Craigslist and collected each boat's price in dollars, as well as the following information that he believes drives the boat's price: Length - The length of the boat in feet BoatYear - The year the boat was manufactured Horsepower - The horsepower of the motor MotorYear - The year the motor was manufactured The data is partially shown below: Price Length 5500 15 5000 16 6800 17 8000 17 4800 17 6000 18 11000 18 BoatYear 1985 1973 1995 1973 1985 1972 2004 Horsepowe r 70 115 85 115 60 150 115 MotorYea r 1985 1987 1995 2008 1998 1987 2004 Running a regression with Price as the dependent variable and all four drivers as the independent variables generates the following regression output: Regression Statistics R Square 0.646379977 Standard Error 2598.149102 Observations 30 Significance F 0.00024251 Coefficients Intercept -618261.4315 Length -200.3754799 P-value 1.77301E-05 0.721626646 Lower 95% -859066.6825 -1345.660595 Upper 95% -377456.1805 944.9096353 Page 9 of 18 BoatYear Horsepower MotorYear 172.9662049 63.14328237 140.6256062 0.000213099 0.038162317 0.021848828 90.64147544 3.729958256 22.194102 255.2909343 122.5566065 259.0571103 1. Present the regression result in standard format. Use significance level 0.05. (6pts) 2. The p-value of 0.021849 on MotorYear is the p-value for a particular hypothesis test. What are the null and alternative hypotheses of that test? (2pts) 3. In case-specific terms (e.g., \"used boat price\" instead of \"dependent variable\"), please explain the coefficient on BoatYear, 173 (rounded). (3pts) Page 10 of 18 4. The negative coefficient on Length confuses Dean Blackburn. He thinks he must have made a mistake when jotting down the numbers. Do you have other possible explanations why the coefficient on Length is negative? (3pts) Just to be on the safe side, Dean Blackburn decides to remove the Length column from the model and run a new regression, which generates the following output: Regression Statistics R Square 0.644543445 Standard Error 2554.301985 Observations 30 Coefficients Intercept -612607.5857 BoatYear 170.060089 Horsepower 53.99814054 MotorYear 139.44693 P-value 1.24254E-05 0.00015602 0.000461253 0.020363886 Lower 95% -846750.8188 90.94357029 26.28521578 23.42892626 Upper 95% -378464.3526 249.1766078 81.7110653 255.4649337 5. What can you say about whether the original model is significantly better at explaining the variation in used boat prices? Please provide statistical evidence that supports your statement. (6pts) Page 11 of 18 6. Dean Blackburn loves a beautiful boat made in 1990, with a 90 horsepower motor that was manufactured in 2000. The seller does not list a price but instead asks the buyer to make a price offer. (Questions are on the next page.) a. Based on the new regression model (without the Length driver), create an exact 40% prediction interval for the price this boat would be listed for. (4pts) b. If Dean Blackburn offers the upper limit of the prediction interval that you calculated in 6.a., what is the probability that this offer will be less than the price this boat would be listed for on Craigslist? (2pts) Page 12 of 18 c. Dean Blackburn notices that this seller once shortly posted an ad for the exact same boat on Craigslist 3 years ago, again without a price tag. He wonders how much higher the listing price for such a boat would have been 3 years ago than now. What is your best estimate of the difference in this boat's would-be listing prices between 3 years ago and now? You can ignore inflation. (4pts) Page 13 of 18 MSFT (The professor cannot be held responsible for losses due to investment following this problem's analysis.) Kenny starts his first day as a trader intern at a hedge fund. In their first week, interns explore strategies with simulated trading. Kenny has set his mind on analytics-assisted mid- to long-term investments in IT stocks, and he picks Microsoft (MSFT) to test his strategy. From Yahoo Finance, Kenny first collects MSFT's stock prices on a quarterly basis between Mar 2005 and Dec 2013. He then thinks what financial data to collect that may be used to forecast MSFT's future movement. According to his strategy, he needs to forecast at least 2 quarters (6 months) into the future. He first picks Microsoft's strategic partner, Intel (INTC). In the past decade, Microsoft's throne has been challenged by another giant Google (GOOG), which Kenny also collects. Finally, he collects the S&P500 index (SP) as a proxy for the status of the US economy. All stock prices are adjusted for dividends and splits. You can ignore inflation. The chart below shows part of the data. Kenny follows the procedure that he learned in BUSI410 to perform his analysis. He first created lagged copies of the drivers, where the lags are represented in the labels; for example, q-2 means a lag of 2 quarters. Below is an example of a lagged driver. Month INTC q-2 6/1/2007 17.36 9/1/2007 17.21 12/1/2007 16.53 3/1/2008 20.09 6/1/2008 22.21 Page 14 of 18 1. What is INTC's price on 12/1/2007? (Hint: it is not $16.53). (2pts) 2. Kenny follows the procedure he learned in BUSI410 to remove the top few rows that contain incomplete data, and uses correlation to pick his first lagged driver: GOOG q-3. He then continues as described below. Please read the description carefully. \"After the regression of MSFT on GOOG q-3, Kenny sees in the residual plot below that the residuals are not independent over time. This is definitive evidence that there is auto-correlation in the residuals, which means he needs to add another driver to the model. GOOG q-3 Residual Plot 5 3 Residuals 1 -1180 -3 280 380 480 580 680 780 880 980 1080 -5 GOOG q-3 In Cell C39, Kenny types in the formula: '=CORREL(B2:B34,C2:C34)', and drags the dot at the cell's lower right corner all the way through cell H39. The calculated correlations are shown below. Kenny remembers that he should pick the highest correlation, therefore his choice of the second driver is SP q-2.\" Page 15 of 18 In the space below, list out all the mistakes you can find in the above procedure, and for each mistake, briefly explain what he should have done. You don't have to fill up all the space, and don't assume there are 3, 5, or 15 mistakes just because of the points. (15pts) 3. Microsoft's Windows operating system has gone through several major versions: Windows XP, released in 9/2001; Windows Vista, released in 3/2006; Windows 7, released in 9/2009; and Windows 8, released in 9/2012. Kenny created three indicator variables A, B and C to capture this information in his model as in the chart on the right. Part of the regression output containing these indicator variables is provided below. Month A B C 3/1/2005 0 0 1 6/1/2005 0 0 1 9/1/2005 0 0 1 12/1/2005 0 0 1 3/1/2006 1 0 0 6/1/2006 1 0 0 9/1/2006 1 0 0 12/1/2006 1 0 0 3/1/2007 1 0 0 6/1/2007 1 0 0 9/4/2007 1 0 0 12/3/2007 1 0 0 3/3/2008 1 0 0 6/2/2008 1 0 0 9/2/2008 1 0 0 12/1/2008 1 0 0 3/2/2009 1 0 0 6/1/2009 1 0 0 9/1/2009 0 1 0 12/1/2009 0 1 0 3/1/2010 0 1 0 6/1/2010 0 1 0 9/1/2010 0 1 0 12/1/2010 0 1 0 3/1/2011 0 1 0 6/1/2011 0 1 0 9/1/2011 0 1 0 12/1/2011 0 1 0 3/1/2012 0 1 0 6/1/2012 0 1 0 9/4/2012 0 0 0 12/3/2012 0 0 0 Page 16 of 18 3/1/2013 0 0 0 6/3/2013 0 0 0 9/3/2013 0 0 0 12/2/2013 0 0 0 a. What information is carried by indicator variable C? (2pts) b. Please interpret the meaning of Lower 95% and Upper 95% for C, 2.7831 and 3.6017. (3pts) c. Consider the following statement: On average, MSFT's stock price is _______ (e.g., \"$5 higher\") when Microsoft's most advanced operating system is Windows Vista, compared with when Microsoft's most advanced operating system is Windows 7. What would you put in the blank? Please answer below, including any necessary calculations. (3pts) Page 17 of 18 d. Finally, Kenny is satisfied with a regression model for MSFT stock price consisting of the following drivers: GOOG q-3, SP q-2, A and C (where A and C are indicator variables as defined above). As the last step of the analysis, Kenny wants to understand the following question: In this model, what percentage of variation in MSFT is explained by the variation in the driver SP q-2? Please verbally describe what procedure you would perform in order to answer this question. No calculation is needed. (5pts) Page 18 of 18