Fluent Essays

In this assignment, you will be assessed based on the following outcome: GB513-3: Predict business results by using quantitative methods. Make sure to use the Unit 4 Assignment template to turn in your answers. This assignment requires you to use Excel in all three questions. Make sure you explain your answers and provide the regression output tables for Questions 1 and 2 as you are showing work in the template (see attached). You still need to submit the Excel file you used to generate your answers, in addition to the report in Word. Failure to submit the Excel file will result in a 20 point deduction.Question 1 Shown below are rental and leasing revenue figures for office machinery and equipment in the United States over a 7-year period according to the U.S. Census Bureau. Use this data and the regression tool in the data analysis tool pack to run a linear regression. Based on the formula you get from the regression output, answer the following questions: a)What is the forecast for the rental and leasing revenue for the year 2011? b)How confident are you in this forecast? Explain your answer by citing the relevant metrics. Do not just put down a number without explanation.Year Rental and Leasing ($ millions) 2004 5,8602005 6,6322006 6,5432007 5,9522008 5,7322009 5,4232010 4,589Question 2 Suppose a researcher gathered survey data from 19 employees and asked the employees to rate their job satisfaction on a scale from 0 to 100 (with 100 being perfectly satisfied). Suppose the following data represent the results of this survey. Assume that relationship with their supervisor is rated on a scale from 0 to 50 (0 represents a poor relationship and 50 represents an excellent relationship); overall quality of the work environment is rated on a scale from 0 to 100 (0 represents poor work environment and 100 represents an excellent work environment); and opportunities for advancement is rated on a scale from 0 to 100 (0 represents no opportunities and 100 represents excellent opportunities). Answer the following questions: a)What is the regression formula based on the results from your regression? b)How reliable do you think the estimates will be based on this formula? Explain your answer by citing the relevant metrics. Do not just put down a number without explanation.c)Are there any variables that do not appear to be good predictors of job satisfaction? How can you tell? d)If a new employee reports that her relationship with her supervisor is 40, rates her opportunities for advancement to be at 30, finds the quality of the work environment to be at 75, and works 60 hours per week, what would you expect her job satisfaction score to be? Job satisfaction Relationship with supervisor Opportunities for advancement Overall quality of work environment Total hours worked per week 55 27 42 50 52 20 35 28 60 60 85 40 7 45 42 65 35 48 65 53 45 29 32 40 58 70 42 41 50 48 35 22 18 75 55 60 34 32 40 50 95 40 48 45 40 65 33 11 60 38 85 38 33 55 47 10 5 21 50 62 75 37 42 45 43 80 37 46 40 42 50 31 48 60 46 90 42 30 55 38 75 36 39 70 43 45 20 22 40 42 65 32 12 55 53 Question 3 Investment analysts generally believe the interest rate on bonds is related to the prime interest rate for loans. Run a simple linear regression using the data analysis tool pack. Do you think the bond rate can be predicted by the prime interest rate? Justify your answer using the relevant metrics. Prime interest rateBond rate0.050.280.120.380.090.220.0150.140.0040.050.110.440.060.280.020.105 Directions for submitting your Assignment Make sure to use the Unit 4 Assignment template from Course Documents when you turn in your answers. Submit your assignment to the Unit 4 Dropbox.In all assignments, your answers must be justified by referencing the appropriate figures, metrics and charts. Your work is evaluated on this justification that demonstrates your understanding of the concepts. Just putting down the correct response without justification is not sufficient. Grading Rubric Your assignment will be graded based on the following breakdown. Unit 4 AssignmentCriteriaPoints PossiblePoints EarnedQuestion 1a2011 forecast for rental and leasing revenue is accurate. 6 Question 1bAppropriate analysis of the reliability of forecast based on this formula. 6 Question 2aCorrect regression formula. 5 Question 2bAppropriate analysis of the reliability of estimates based on this formula. 5 Question 2cAppropriate analysis of whether there are any variables that do not appear to be good predictors. 5 Question 2dCorrect expected job satisfaction score. 5 Question 3aConstructed a scatter graph and included a regression line. 6 Question 3bAppropriate analysis of whether a bond rate could be predicted by the prime interest rate. 7 Properly completed assignment template. 5 Total50 u4_assignment_template.docx ch_13.pptx ch._12.pptx Unformatted Attachment Preview Unit 4 Dropbox Assignment Answers by (Insert your name here) In the summary tables below, insert only the answers. You will show work after the summary section. Question 1 answers 1a: forecast 1b: reliability interpretation Question 2 answers 2a: formula 2b: reliability interpretation 2c: variables 2d: expected score Question 3 answers How well can the bond rate be predicted by the prime? Justify your answer using the relevant metrics. Paste scatter graph here: Work Show all your work for the three questions below. Make sure to also attach your Excel file to avoid losing points. Question 1 Show your work here. Question 2 Show your work here. Question 3 Show your work here. Business Statistics Tenth Edition Ken Black Chapter 13 Multiple Regression Analysis Copyright ©2020 John Wiley & Sons, Inc. Learning Objectives 1. Explain how, by extending the simple regression model to a multiple regression model with two independent variables, it is possible to determine the multiple regression equation for any number of unknowns. 2. Examine significance tests of both the overall regression model and the regression coefficients. 3. Calculate the residual, standard error of the estimate, coefficient of multiple determination, and adjusted coefficient of multiple determination of a regression model. 4. Use a computer to find and interpret multiple regression outputs. Copyright ©2020 John Wiley & Sons, Inc. 2 13.1 The Multiple Regression Model (1 of 8) Probabilistic Multiple Regression Model y =  0 + 1 x1 +  2 x2 + 3 x3 + . . . +  k xk +  where y = the value of the dependent variable β0 = the regression constant β1 = the partial regression coefficient for independent variable 1 β2 = the partial regression coefficient for independent variable 2 β3 = the partial regression coefficient for independent variable 3 βk = the partial regression coefficient for independent variable k k = the number of independent variables • In multiple regression models, y is sometimes referred to as the response variable • The partial regression coefficient of an independent variable, βi, represents the increase that will occur in the value of y from a one-unit increase in that independent variable if all other variables are held constant Copyright ©2020 John Wiley & Sons, Inc. 3 13.1 The Multiple Regression Model (2 of 8) • The partial regression coefficients and the regression constant of a multiple regression model are population values and are unknown Estimating y with Sample Information yˆ = b0 + b1 x1 + b2 x2 + b3 x3 + where + bk xk ŷ = the predicted value of y b0 = the estimate of the regression constant b1 = the estimate of regression coefficient 1 b2 = the estimate of regression coefficient 2 b3 = the estimate of regression coefficient 3 bk = the estimate of regression coefficient k k = the number of independent variables Copyright ©2020 John Wiley & Sons, Inc. 4 13.1 The Multiple Regression Model (3 of 8) Multiple Regression Model with Two Independent Variables (First-Order) • The simplest multiple regression model is one constructed with two independent variables, where the highest power of either variable is 1 (first-order regression model) y =  0 + 1 x1 +  2 x2 +  ŷ = b0 + b1 x1 + b2 x2 Left graph shows data; right graph shows the response plane, the 3D regression line Copyright ©2020 John Wiley & Sons, Inc. 5 13.1 The Multiple Regression Model (4 of 8) Determining the Multiple Regression Equation • As in simple regression, use least squares analysis, with the objective of minimizing the sum of squares of error for the model • After using calculus methods to solve the three equations for three unknowns, 𝑏0 , 𝑏1 , 𝑏2 , the following equations are found: b0 n + b1x1 + b2 x2 = y b0 x1 + b1x12 + b2 x1 x2 = x1 y b0 x2 + b1x1 x2 + b2 x2 2 = x2 y • The number of equations depends on the number of independent variables o A regression model with six independent variables will generate seven simultaneous equations with seven unknowns • The process of solving these equations by hand is tedious and time-consuming, so computers are virtually always used Copyright ©2020 John Wiley & Sons, Inc. 6 13.1 The Multiple Regression Model (5 of 8) A Multiple Regression Model A real-estate study was conducted in a small Louisiana city to determine what variables are related to the market price of a home • Suppose the analyst wants to develop a regression model to predict the market price of a home by two variables, “total number of square feet in the house” and “the age of the house” • Can use Excel or Minitab to find results TABLE 13.1: Real-Estate Data Market Price ($1,000) y Total Number of Square Feet x1 Age of House (Years) x2 63.0 1605 35 65.1 2489 45 69.9 1553 20 76.8 2404 32 73.9 1884 25 77.9 1558 14 74.9 1748 8 78.0 3105 10 79.0 1682 28 83.4 2470 30 Copyright ©2020 John Wiley & Sons, Inc. 7 13.1 The Multiple Regression Model (6 of 8) TABLE 13.1: Real-Estate Data Market Price ($1,000) y Total Number of Square Feet x1 Age of House (Years) x2 79.5 1820 2 83.9 2143 6 79.7 2121 14 84.5 2485 9 96.0 2300 19 109.5 2714 4 102.5 2463 5 121.0 3076 7 104.9 3048 3 128.0 3267 6 129.0 3069 10 117.9 4765 11 140.0 4540 8 Copyright ©2020 John Wiley & Sons, Inc. 8 13.1 The Multiple Regression Model (7 of 8) A Multiple Regression Model • From the Minitab output, the regression equation is: yˆ = 57.4 + .0177 x1 − .666 x2 Copyright ©2020 John Wiley & Sons, Inc. 9 13.1 The Multiple Regression Model (8 of 8) • The regression constant, 57.4, is the y-intercept o Here the y-intercept is meaningless; a house cannot contain 0 square feet or have an age of 0 o Since the data is in thousands, a house with 0 square feet and 0 age would have a value of $57,400 • The coefficient of 𝑥1 (total number of square feet in the house) is .0177 o All other variables held constant, the addition of 1 square foot of space in the house results in a predicted increase of $17.70 in the price of the home • The coefficient of x2 (age) is −.666 o If the total number of square feet in the house is kept constant, a one-unit increase in the age of the house (1 year) will result in −.666 ⋅ ($1,000) = −$666, a predicted $666 drop in the price • Can be used to predict price o For a 12-year-old house with 2,500 square feet, predicted price is yˆ = 57.4 + .0177 ( 2500 ) − .666 (12 ) = 93.658, or $93, 658 Copyright ©2020 John Wiley & Sons, Inc. 10 13.2 Significance Tests of the Regression Model and Its Coefficients (1 of 5) Testing the Overall Model For multiple regression, testing whether the independent variables are predictors of the dependent variable is an F test with the hypotheses: H 0 : 1 =  2 = 3 = = k = 0 H𝑎: at least one of the regression coefficients is ≠ 0 • If we fail to reject the null hypothesis, we are stating that the regression model has no significant predictability for the dependent variable • A rejection of the null hypothesis indicates that at least one of the independent variables is adding significant predictability for y Copyright ©2020 John Wiley & Sons, Inc. 11 13.2 Significance Tests of the Regression Model and Its Coefficients (2 of 5) Testing the Overall Model The ANOVA table from the Minitab output gives F values • The F value is 28.63 • Because p = .000, the F value is significant at = .001 • The null hypothesis is rejected, indicating there is at least one significant predictor of house price in this analysis Copyright ©2020 John Wiley & Sons, Inc. 12 13.2 Significance Tests of the Regression Model and Its Coefficients (3 of 5) SS reg The F value is calculated using: F= where MS reg MSerr = df reg SSerr df err SSR k = SSE n − k −1 MS = mean square SS = sum of squares df = degrees of freedom k = number of independent variables n = number of observations • The regression degrees of freedom is the number of independent variables, k o The real estate example uses two independent variables, so k = 2 • The error degrees of freedom is sample size minus the number of regression coefficients minus the regression constant, n − k − 1 o For the real estate example, N = 23, so error df = 23 − 2 − 1 = 20. Copyright ©2020 John Wiley & Sons, Inc. 13 13.2 Significance Tests of the Regression Model and Its Coefficients (4 of 5) Significance Tests of the Regression Coefficients Individual significance tests can be computed for each regression coefficient using a t test • For a model with two independent variables, there are two t tests: H0: β1 = 0 Ha: β1 ≠ 0 Variable H0: β2 = 0 Ha: β2 ≠ 0 T P Square feet 5.63 .000 Age −2.92 .008 • t values come from the computer output • At α = 0, the null hypothesis is rejected for both variables because the probabilities (p) associated with their t values are less than .05 • The degrees of freedom for each of these individual tests of regression coefficients are n − k − 1 Copyright ©2020 John Wiley & Sons, Inc. 14 13.2 Significance Tests of the Regression Model and Its Coefficients (5 of 5) Significance Tests of the Regression Coefficients • In this case, degrees of freedom are 23−2−1 =20 • 𝑡.025, 20 = ±2.086 • Notice from the t ratios that if this critical table t value had been used as the hypothesis test criterion instead of the p-value method, results would have been the same • If the t ratios for any predictor variables are not significant (fail to reject the null hypothesis), the analyst might decide to drop that variable(s) from the analysis as a nonsignificant predictor(s) • Other factors can enter into this decision o Model building is discussed further in Chapter 14 Copyright ©2020 John Wiley & Sons, Inc. 15 13.3 Residuals, Standard Error of the Estimate, and R2 (1 of 8) Residuals • The residual, or error, of the regression model is the difference between the y value and the predicted value, ŷ TABLE 13.2: Residual for the Real Estate • Found in the same way as for a simple regression model o Enter the value for each independent variable into the multiple regression equation; solve for the predicted value o Then find the difference between the predicted value and actual value Regression Model y yˆ y − yˆ 63.0 62.499 .501 65.1 71.485 −6.385 69.9 71.568 −1.668 76.8 78.639 −1.839 73.9 74.097 −.197 77.9 75.653 2.247 74.9 83.012 −8.112 78.0 105.699 −27.699 79.0 68.523 10.477 Copyright ©2020 John Wiley & Sons, Inc. 16 13.3 Residuals, Standard Error of the Estimate, and R2 (2 of 8) TABLE 13.2: Residual for the Real Estate Regression Model, y 83.4 79.5 83.9 79.7 84.5 96.0 109.5 102.5 121.0 104.9 128.0 129.0 117.9 140.0 Copyright ©2020 John Wiley & Sons, Inc. yˆ y − yˆ 81.139 2.261 88.282 −8.782 91.335 −7.435 85.618 −5.918 95.391 −10891 85.456 10.544 102.774 6.726 97.665 4.835 107.183 13.817 109.352 −4.452 111.230 16.770 105.061 23.939 134.415 −16.515 132.430 7.570 17 13.3 Residuals, Standard Error of the Estimate, and R2 (3 of 8) SSE and Standard Error of the Estimate • A single statistic that can represent the error in a regression analysis, is the 2 sum of squares of error (SSE),  ( y − yˆ ) o Can be calculated or found from the ANOVA table • The SSE can be used to find the standard error of the estimate: s = SSE n − k −1 Copyright ©2020 John Wiley & Sons, Inc. 18 13.3 Residuals, Standard Error of the Estimate, and R2 (4 of 8) • Residuals are used to test the assumptions of the regression model or to find outliers o In this case, in the top right graph of the residuals, the residual variance seems to increase in the right half of the plot, indicating potential heteroscedasticity Copyright ©2020 John Wiley & Sons, Inc. 19 13.3 Residuals, Standard Error of the Estimate, and R2 (5 of 8) SSE and Standard Error of the Estimate • For the real estate example, s = SSE = n − k −1 2861 = 11.96 23 − 2 − 1 • Also part of computer output • Can be used to check normality or find confidence intervals • If the standard error is very large, the analyst may wish to change or discard the model Copyright ©2020 John Wiley & Sons, Inc. 20 13.3 Residuals, Standard Error of the Estimate, and R2 (6 of 8) Coefficient of Multiple Determination (R2) • The coefficient of multiple determination ( R 2 ) is analogous to the coefficient of determination (r2) used in simple regression R 2 represents the proportion of variation of the dependent variable, y, accounted for by the independent variables in the regression model R2 = SSR SSE 2861.0 = 1− = 1− = .741 SS yy SS yy 11050.7 Copyright ©2020 John Wiley & Sons, Inc. 21 13.3 Residuals, Standard Error of the Estimate, and R2 (7 of 8) Adjusted R2 • As additional independent variables are added to a regression model, the value of R2 cannot decrease and will usually increase o Additional independent variables may add no significant information to the regression model, yet R2 increases • The adjusted R2 takes into consideration both the additional information each new independent variable brings to the regression model and the changed degrees of freedom SSE Adjusted R 2 = 1 − n − k − 1 SS yy n −1 Copyright ©2020 John Wiley & Sons, Inc. 22 13.3 Residuals, Standard Error of the Estimate, and R2 (8 of 8) Adjusted R2 • For the real estate example, the adjusted R2 can be calculated from the ANOVA table or found in the computer output  2861/ 20  Adj.R 2 = 1 −  = 1 − .285 = .715  11050.7 / 22  • Slightly lower than the unadjusted value of .741 o Difference between the values increases as nonsignificant independent variable are added Copyright ©2020 John Wiley & Sons, Inc. 23 13.4 Interpreting Multiple Regression Computer Output Copyright ©2020 John Wiley & Sons, Inc. 24 Copyright Copyright © 2020 John Wiley & Sons, Inc. All rights reserved. Reproduction or translation of this work beyond that permitted in Section 117 of the 1976 United States Act without the express written permission of the copyright owner is unlawful. Request for further information should be addressed to the Permissions Department, John Wiley & Sons, Inc. The purchaser may make back-up copies for his/her own use only and not for distribution or resale. The Publisher assumes no responsibility for errors, omissions, or damages, caused by the use of these programs or from the use of the information contained herein. Copyright ©2020 John Wiley & Sons, Inc. 25 Business Statistics Tenth Edition Ken Black Chapter 12 Simple Regression Analysis and Correlation Copyright ©2020 John Wiley & Sons, Inc. Learning Objectives (1 of 2) 1. Calculate the Pearson product-moment correlation coefficient to determine if there is a correlation between two variables. 2. Explain what regression analysis is and the concepts of independent and dependent variables. 3. Calculate the slope and y-intercept of the least squares equation of a regression line and from those, determine the equation of the regression line. 4. Calculate the residuals of a regression line and from those determine the fit of the model, locate outliers, and test the assumptions of the regression model. 5. Calculate the standard error of the estimate using the sum of squares of error, and use the standard error of the estimate to determine the fit of the model. Copyright ©2020 John Wiley & Sons, Inc. 2 Learning Objectives (2 of 2) 6. Calculate the coefficient of determination to measure the fit for regression models, and relate it to the coefficient of correlation. 7. Use the t and F tests to test hypotheses for both the slope of the regression model and the overall regression model. 8. Calculate confidence intervals to estimate the conditional mean of the dependent variable and prediction intervals to estimate a single value of the dependent variable. 9. Determine the equation of the trend line to forecast outcomes for time periods in the future, using alternate coding for time periods if necessary. 10. Use a computer to develop a regression analysis, and interpret the output that is associated with it. Copyright ©2020 John Wiley & Sons, Inc. 3 12.1 Correlation (1 of 4) Correlation: a measure of the degree of relatedness of variables • Do the stocks of two airlines rise and fall in any related manner? • How strong is the correlation between the producer price index and the unemployment rate? • Are sales related to population density? Pearson Product-Moment Correlation Coefficient (  x )(  y ) xy − r=  ( x − x )( y − y ) ( x − x ) ( y − y ) 2 2 =   ( x)  x 2 −   n  n 2 2  y)  (  2   y −   n    • Measure of the linear relationship between variables: o r = 0 means that there is no linear relationship between the variables o r = +1 means that there is perfect positive correlation o r = −1 means that there is perfect negative correlation Copyright ©2020 John Wiley & Sons, Inc. 4 12.1 Correlation (2 of 4) Copyright ©2020 John Wiley & Sons, Inc. 5 12.1 Correlation (3 of 4) TABLE 12.2: Computation of r for the Economics Example Example: What is the measure of correlation between the interest rate of federal funds and the commodities futures index? • • • It does not matter in calculation which variable is x or y For this example, r = .815, which shows a high degree of correlation between the fed funds rate and the futures index over this 12-day period Correlation does not imply causation Interest Rate x Futures Index y x2 y2 xy 1 7.43 221 55.205 48,841 1,642.03 2 7.48 222 55.950 49,284 1,660.56 3 8.00 226 64.000 51,076 1,808.00 4 7.75 225 60.063 50,625 1,743.75 5 7.60 224 57.760 50,176 1,702.40 6 7.63 223 58.217 49,729 1,701.49 7 7.68 223 58.982 49,729 1,712.64 8 7.67 226 58.829 51,076 1,733.42 9 7.59 226 57.608 51,076 1,715.34 10 8.07 235 65.125 55,225 1,896.45 11 8.03 233 64.481 54,289 1,870.99 12 8.00 241 64.000 58,081 1,928.00 Σx = 92.93 Σy = 2,725 Σx2 = 720.220 Σy2 = 619,207 Σxy = 21,115.07 Day r= (92.93)(27.25) 12 = .815 2  (92.93)   (2725) 2  (720.22) − 12  (619.207) − 12     (21,115.07) − Copyright ©2020 John Wiley & Sons, Inc. 6 12.1 Correlation (4 of 4) Excel or Minitab can be used to calculate the correlation coefficient • Minitab also gives the p-value for significance of the correlation coefficient Copyright ©2020 John Wiley & Sons, Inc. 7 12.2 Introduction to Simple Regression Analysis (1 of 3) Regression analysis: the process of constructing a mathematical model or function that can be used to predict or determine one variable by another variable or other variables • The most elementary regression model is called simple regression or bivariate regression • Two variables; one variable is predicted by another variable o Dependent variable: the variable to be predicted; designated as y o Independent variable: the predictor, or explanatory, variable; designated as x • In simple regression analysis, only a straight-line relationship between two variables is examined Copyright ©2020 John Wiley & Sons, Inc. 8 12.2 Introduction to Simple Regression Analysis (2 of 3) Example: Can the cost of flying a commercial airliner be predicted using regression analysis? TABLE 12.3: Airline Cost Data Number of Passengers COST ($1,000) 61 4.280 63 4.080 67 4.420 69 4.170 70 4.480 74 4.300 76 4.820 81 4.700 86 5.110 91 5.130 95 5.640 97 5.560 • Many variables are related to cost • One possible variable is number of passengers, which is related to th ... Purchase answer to see full attachment