B C D E 1 Assignment 2 A . % Conditional Formatting v Observation Y X Format as Table v Cells Editing Sensitivity Analyze FIRM IMPRESS ADEXP Paste Font Alignment Number Cell Styles v Data 32.1 50.1 99.6 74.1 G25 X V fx 11.7 19.3 21.9 22.9 B C D E F G H M N O P Q R S 8 60.8 82.4 Assignment z 5 6 00 V O UT A W N H 9 78.6 40.1 w 10 92.4 185.9 Name 11 50.7 26.9 UT 12 21.4 20.4 Theory: IMPRESS = F(ADEXP) [# of impressions (in millions) are a function of advertising expenditures] Linear: IMPRESSi = Bo + B1 ADEXP, + & 13 40.1 166.2 8 14 11 10.8 27 9 In the following tab: 12 10.4 45.6 10 1 Provide a formula (not using any Excel "built-in" fomula except =SUM and =AVERAGE) to calculate the estimated OLS slope and intercept coefficients. 16 13 88.9 154.9 11 Hint: you might want to create additional columns so that you can compute parts of beta hat 1 before putting them together in cell C27; use absolute cell addresses in formulas where necessary to copy to other cells. 14 12 12 3 Calculate SST, SSR and SSE (or TSS, RSS, ESS, from my videos). 13 Hint: you might want to add a few more columns to the table 15 29.2 49.7 14 4 Calculate the coefficient of determination (R'). 16 38 26.9 15 5 Use the built in Regression (Anaylsis Toolpack) package in Excel to check your answers - place it starting in cell B36. 17 10 5.7 16 In this tab: 18 12.3 7.6 17 6 Interpret beta hat 1, beta hat 0, and R" here in cells C18 through C20 19 23.4 9.2 18 beta hat 1: answer 19 beta hat 0: answer 20 71.1 32.4 20 R2: answer 21 25 22 26 23 beta hat 1 24 27 answer 25 28 beta hat 0 answer 29 30 SST answer 31 SSR answer 32 SSE answer 33 R2 answer 34 35 A2 Instructions A2 Work & Answers