trying to do this with excel

CASE PROBLEM 2: PREDICTING WINNINGS FOR NASCAR DRIVERS ...................................................... Matt Kenseth won the 2012 Daytona 500, the most important race of the NASCAR season. His win was no surprise because for the 201 1 season he finished fourth in the point standings with 2330 points, behind Tony Stewart (2403 points). Carl Edwards (2403 points). and Kevin Harvick (2345 points). In 201 1 he earned $6, 183,580 by winning three Poles (fastest driver in qualifying). winning three races, finishing in the top five 12 times, and finishing in the top ten 20 times. NASCAR's point system in 2011 allocated 43 points to the driver who finished first, 42 points to the driver who finished second, and so on down to I point for the driver who finished in the 43rd position. In addition any driver who led a lap received I bonus point. the driver who led the most laps received an additional bonus point, and the race winner was awarded 3 bonus points. But the maximum number of points a driver could earn in any race was 48. Table 15.8 shows data for the 201 1 season for the top 35 drivers (NASCAR website). Managerial Report 1. Suppose you wanted to predict Winnings ($) using only the number of poles won (Poles). the number of wins (Wins), the number of top five finishes (Top 5), or the num- ber of top ten finishes (Top 10). Which of these four variables provides the best single predictor of winnings? 2. Develop an estimated regression equation that can be used to predict Winnings ($) given the number of poles won (Poles), the number of wins (Wins), the number of top fiveTABLE 15.8 NASCAR Results for the 2011 Season Driver Points Poles Wins Top 5 Top 10 Winnings ($) Tony Stewart 2403 5 9 19 6,529,870 Carl Edwards 2403 19 26 8,485,990 WOW Kevin Harvick 2345 9 19 6,197,140 Matt Kenseth 2330 12 20 6,183,580 Brad Keselowski 2319 10 14 5,087,740 Jimmie Johnson 2304 21 6,296,360 Dale Earnhardt Jr. 2290 12 4,163,690 Jeff Gordon 2287 18 5,912,830 Denny Hamlin 2284 14 5,401,190 Ryan Newman 2284 17 5,303,020 Kurt Busch 2262 16 5,936,470 Kyle Busch 2246 18 6,161,020 Clint Bowyer 1047 16 5,633,950 Kasey Kahne 1041 15 4,775,160 A. J. Allmendinger 1013 10 4,825,560 Greg Biffle 997 10 4,318,050 Paul Menard 947 8 3,853,690 Martin Truex Jr. 937 12 3,955,560 Marcos Ambrose 936 12 4,750,390 Jeff Burton 935 5 3,807,780 TOONNNNOOSOWONO Juan Montoya 932 5,020,780 Mark Martin 930 10 3,830,910 David Ragan 906 4,203,460 902 090909090-60-060-0-0 0 - - Joey Logano 3,856,010 Brian Vickers 846 4,301,880 Regan Smith 820 4,579,860 Jamie McMurray 795 4,794,770 David Reutimann 757 4,374,770 Bobby Labonte 670 4,505,450 David Gilliland 572 3,878,390 Casey Mears 541 2,838,320 Dave Blaney 508 0000000 3,229,210 Andy Lally 398 2,868,220 Robby Gordon 268 2,271,890 J. J. Yeley 192 2,559,500 finishes (Top 5), and the number of top ten (Top 10) finishes. Test for individual signifi- cance and discuss your findings and conclusions. 3. Create two new independent variables: Top 2-5 and Top 6-10. Top 2-5 represents the number of times the driver finished between second and fifth place and Top 6-10 represents the number of times the driver finished between sixth and tenth place. Develop an estimated regression equation that can be used to predict Winnings ($) using Poles, Wins, Top 2-5, and Top 6-10. Test for individual significance and discuss your findings and conclusions. 4. Based upon the results of your analysis, what estimated regression equation would you recommend using to predict Winnings ($)? Provide an interpretation of the estimated regression coefficients for this equation