Part 1 (50 points) KIOSK 1 2 3 4 5 6 7 8 9 10 Table 1: Shell Petrol Sale by Kiosk 2016 SHELL SALE CARD (RM (RM ADVISETISEMENT 000) 000) (RM 000) 150 15.00 140 210 13.50 150 140 16.50 50 190 14.50 190 130 17.00 90 160 16.00 60 200 13.00 140 150 18.00 110 210 12.00 200 190 15.00 150 INCOME (RM 000) 19.00 17.50 14.00 21.00 15.50 14.50 21.50 18.00 18.50 20.00 WHEN TO USE MULTIPLE LINEAR REGRESSION ANALYSIS? To answer this question, we refer to a hypothetical Case Study. In the following example, the study is on the sale of petrol at kiosks in Kuala Lumpur. YOUR TASKS AS THE MARKETING MANAGER ARE SUMMARIZED AS FOLLOWS: 1. Using Microsoft Excel, perform regression analysis from the data in the Table. 2. Develop the function in linear form of demand model for the petrol sale for the kiosks. 3. Test whether the coefficients of the variables used in the model are statistically significant. 4. Determine the percentage of the variation of output that is ‘explained’ by the regression equation. 5. Based on the findings, what advise should the Manager propose to the top management to increase the petrol sale at all kiosks in Kuala Lumpur? USING EXCEL TO PERFORM MULTIPLE REGRESSION ANALYSIS The steps are as follows: HOW TO INTERPRET THE RESULT OF THE ESTIMATION OF REGRESSION MODEL? To answer this question, let us refer to the 5 tasks a marketing manager normally performs. TASK 1: Using Microsoft Excel, perform regression analysis from the data in the Table. TASK 2: Develop the function in linear form of demand model for the petrol sale for the kiosks. TASK 3: Test whether the coefficients of the variables used in the model are statistically significant. This is the most crucial task in regression analysis as measure of the accuracy of estimation is needed to test the statistical significance of the estimated regression coefficients of each variable. In addition, the regression results are based on samples and we need to determine how true that the results are truly reflective of the population. In conducting the test, Correlation Analysis Techniques is used, namely R-Square, F-Statistics (F-Test), tstatistic (or t-test), P-value and Confidence Intervals. i. R-SQUARE (COEFFICIENT OF DETERMINATION) R-Square measures the proportion of variation in dependent variable that is explained in the regression line (independent variables). It is computed as the ratio of the sum of squared errors from the regression (SSRegression) to the total sum of squared errors (SSTotal). The value of R-Square ranges from 0 to 1. The closer R-Square is to one, the better the regression equation; i.e., the greater the explanatory of the regression equation. Low value of R-Square indicates the absence of some important variables from the model. ii. F-STATISTICS (F-RATIO) F-statistic is used to test the hypothesis that all the independence variables (X 1, X2, ….…. Xn) explain the variation in the dependence variable (Y). Null hypothesis = Ho: All βi = 0 (rejected) Alternative hypothesis = Ha: At least one βI ≠ 0 (cannot be rejected) In other words, we are testing whether at least one of the explanatory variables contributes information for the prediction of Y. The greater the F-value the better the overall fit of the regression line. ii. T-STATISTIC (OR T-TEST) t-statistic is also used to test the hypothesis that all independence variables (X 1, X2, ….. Xn) explain the variation in the dependence variable (Y). Null hypothesis = Ho : All βi = 0 Alternative hypothesis = Ha : At least one βI ≠ 0 In other words, we are testing whether at least one of the explanatory variables contributes to the explanation for the prediction of Y. The rule of thumb: if the absolute value of t-statistic is greater or equal to 2, than the parameter estimate is statistically different from zero iii. P-VALUE P-value is associated with a test statistic, i.e. to test the hypothesis of relationship between dependent and independent variables. The smaller the P value, the more strongly the test rejects the null hypothesis, and thus, to accept the alternative hypothesis. A p-value of .05 or 5% confidence level implies that 95% of the observed variables are strongly correlated. iv. CONFIDENCE INTERVALS A term used in measuring the probability that a parameter will fall between two set values. In other words, a confidence interval is the probability that a value will fall between an upper and lower bound of a probability distribution. The confidence interval can take any number of probabilities, with the most common being 95% or 99%. SUMMARY TASK 3: RESULT OF CORRELATION ANALYSIS The summary of Task 3 is summarized as follows: F value. The F value is 8.234 So we reject the null hypothesis and conclude that the independence variables are useful in explaining of petrol sale with (1- 0.015) = 98.5 % confidence. t-value. The t-value for variable SHELL CARD is -2.593 (i.e. greater than 2 in absolute value). Hence, we can conclude that SHELL CARD is statistically significance in explaining the sale of petrol. The inference is that SHELL should focus on SHELL CARD than the advertisement or disposable income to increase the petrol sale. P-value. From the Table, the P-value for the estimated coefficient of SHELL CARD is 0.04104. This means, there is only 4.1 in 100 chance that the true coefficient of price is actually 0. The coefficient of SHELL CARD implies that it is statistically significant at (1- 0.04104 = 0.9589) or 96%. Thus, the estimated coefficient is highly significant Confidence intervals. The Table indicate that the upper and lower bound of the 95% confidence intervals. As the coefficients of advertisement, price and income are lying between the upper and lower boundary of 95% confidence level, these coefficients are significant TASK 4: DETERMINE THE PERCENTAGE OF THE VARIATION OF OUTPUT THAT IS ‘EXPLAINED’ BY THE REGRESSION EQUATION The answer to this question is normally referred to the value of R-Square. It measures the proportion of variation in dependent variable that is explained in the regression line (independent variables). From the Table above, the sum of squared errors is 6605.61 and the total sum of squared errors is 8210. Thus, the R-square is: R-Square = 6605.61 / 8210 = 0.8045 This means the estimated demand equation (the regression line) explains 80% of the total variation in petrol sales across the sample of the 10 kiosks. TASK 5: BASED ON THE FINDINGS, WHAT ADVISE SHOULD THE MANAGER PROPOSE TO THE TOP MANAGEMENT? Deliverables: 1. 2. 3. 4. Submit your recommendation to me in a memo. Your workbook with the output on a separate worksheet (name your worksheet with appropriate name) You should name your all files submitted with the following convention: YourLastNameA# --A# is the assignment number i.e WrightA4 (last name is Wright) Note: If you do not know what a memo is “Google it” Part 2 (50 points) Use Excel to develop a linear regression equation to predict number of games lost for a baseball team based on rainy days and payroll using the following data set. Use the equation to answer the questions listed below. Year Games Lost Rainy Days Payroll (000’s) 1993 25 26 175 1994 20 30 178 1995 10 3 240 1996 15 6 235 1997 22 17 180 1998 12 10 241 1999 25 22 173 2000 8 2 255 2001 4 2 267 2002 28 38 160 2003 29 34 147 What is the regression equation? According to the R2 and adjusted R2, is the line of the regression equation a good fit to the data? According to the t statistic, is either independent variable significant? According to the F statistic, is the entire equation significant? How many wins would you expect with 15 rainy days and a payroll of 220? Deliverables: 1. 2. 3. Submit your recommendation to me in a memo. Your workbook with the output on a separate worksheet (name your worksheet with appropriate name) YourLastNameA# --A# is the assignment number i.e WrightA4 (last name is Wright) 
Purchase answer to see full attachment