Application assignments require solving problems from the textbook. Most of the problems require that you use Excel QM, however some chapters will require you to use the Solver Add-In for Excel (regression) or simply type in formulas and make calculations. The answers must be submitted to D2L in a Microsoft Word or Microsoft Excel file. You must state your answers within a complete sentence so that your understanding of applying the results of the computations can be observed. You should also include the work for your computation; this will assist in applying partial credit if your answers are not correct. 1. 4-22 The following data give the selling price, square footage, number of bedrooms, and age of houses that have sold in a neighborhood in the past 6 months. Develop three regression models to predict the selling price based upon each of the other factors individually. Which of these is best? SELLING PRICE ($) SQUARE FOOTAGE BEDROOMS AGE (YEARS) 84,000 1,670 2 30 79,000 1,339 2 25 91,500 1,712 3 30 120,000 1,840 3 40 127,500 2,300 3 18 132,500 2,234 3 30 145,000 2,311 3 19 164,000 2,377 3 7 155,000 2,736 4 10 168,000 2,500 3 1 172,500 2,500 4 3 174,000 2,479 3 3 175,000 2,400 3 1 177,500 3,124 4 0 184,000 2,500 3 2 195,500 4,062 4 10 195,000 2,854 3 3 2. 4-23 Use the data in Problem 4-22 and develop a regression model to predict selling price based on the square footage and number of bedrooms. Use this to predict the selling price of a 2,000-square-foot house with three bedrooms. Compare this model with the models in Problem 4-22. Should the number of bedrooms be included in the model? Why or why not? 1. 4-31 The number of victories (W), earned run average (ERA), runs scored (R), batting average (AVG), and on-base percentage (OBP) for each team in the American League in the 2012 season are provided in the following table. The ERA is one measure of the effectiveness of the pitching staff, and a lower number is better. The other statistics are measures of the effectiveness of the hitters, and a higher number is better for each of these. TEAM W ERA R AVG OBP Baltimore Orioles 93 3.90 712 0.247 0.311 Boston Red Sox 69 4.70 734 0.260 0.315 Chicago White Sox 85 4.02 748 0.255 0.318 Cleveland Indians 68 4.78 667 0.251 0.324 Detroit Tigers 88 3.75 726 0.268 0.335 Kansas City Royals 72 4.30 676 0.265 0.317 Los Angeles Angels 89 4.02 767 0.274 0.332 Minnesota Twins 66 4.77 701 0.260 0.325 New York Yankees 95 3.85 804 0.265 0.337 Oakland Athletics 94 3.48 713 0.238 0.310 Seattle Mariners 75 3.76 619 0.234 0.296 TEAM W ERA R AVG OBP Tampa Bay Rays 90 3.19 697 0.240 0.317 Texas Rangers 93 3.99 808 0.273 0.334 Toronto Blue Jays 73 4.64 716 0.245 0.309 a. Develop a regression model that could be used to predict the number of b. c. d. e. victories based on the ERA. Develop a regression model that could be used to predict the number of victories based on the runs scored. Develop a regression model that could be used to predict the number of victories based on the batting average. Develop a regression model that could be used to predict the number of victories based on the on-base percentage. Which of the four models is better for predicting the number of victories? f. Find the best multiple regression model to predict the number of wins. Use any combination of the variables to find the best model. **Question 20 is required however 17 is needed to answer it** 2. 5-17 Data collected on the yearly demand for 50-pound bags of fertilizer at Wallace Garden Supply are shown in the following table. Develop a 3-year moving average to forecast sales. Then estimate demand again with a weighted moving average in which sales in the most recent year are given a weight of 2 and sales in the other 2 years are each given a weight of 1. Which method do you think is better? YEAR DEMAND FOR FERTILIZER (1,000s OF BAGS) 1 4 2 6 3 4 4 5 5 10 6 8 7 7 8 9 9 12 10 14 11 15 1. 5-20 Use exponential smoothing with a smoothing constant of 0.3 to forecast the demand for fertilizer given in Problem 5-17. Assume that last period’s forecast for year 1 is 5,000 bags to begin the procedure. Would you prefer to use the exponential smoothing model or the weighted average model developed in Problem 5-17? Explain your answer. 2. 5-21 A college student has just completed her junior year. The following table summarizes her grade-point average (GPA) for each of the past nine quarters: Year Semester GPA Freshman Fall 2.4 Winter 2.9 Spring 3.1 Fall 3.2 Winter 3.0 Spring 2.9 Fall 2.8 Winter 3.6 Spring 3.2 Sophomore Junior a. Forecast the student’s GPA for the fall semester of her senior year by using a three-period moving average. b. Forecast the student’s GPA for the fall semester of her senior year by using exponential smoothing with α=0.2α=0.2. c. Which of the two methods provides a more accurate forecast? Justify your answer Exponential Smoothing In this tutorial, we will examine how to use exponential smoothing to create a forecast. Data collected on the yearly demand for 50-pound bags of fertilizer at Wallace Garden Supply are shown in the following table: Data Table Period Year 1 Year 2 Year 3 Year 4 Year 5 Year 6 Year 7 Year 8 Year 9 Year 10 Year 11 Demand for Fertilizer (1,000s bags) 4 6 4 5 10 8 7 9 12 14 15 Use exponential smoothing to forecast sales. To do this, open Excel QM, click on the Excel QM tab  Alphabetical  Forecasting  Exponential Smoothing. A Spreadsheet Initialization window will appear. In the Number of (past) periods of data box, enter 11, since the above data table shows 11 periods. For Name for period, type Year. Uncheck the box next to Graph. Click OK. Now, enter 0.3 (smoothing constant) for Alpha, and in the Demand column of the spreadsheet, enter the demand information from the data table above and the average is already calculated. You can see that the forecast for the next period is 11.7689607. Click here if you would like to download and view the completed sample spreadsheet. This concludes the tutorial on calculating averages using exponential smoothing. Moving Average and Weighted Moving Average Calculations In this tutorial, we will be covering moving average and weighted moving average calculations. Data collected on the yearly demand for 50-pound bags of fertilizer at Wallace Garden Supply are shown in the following table. Data Table Period Year 1 Year 2 Year 3 Year 4 Year 5 Year 6 Year 7 Year 8 Year 9 Year 10 Year 11 Demand for Fertilizer (1,000s bags) 4 6 4 5 10 8 7 9 12 14 15 Develop a three year moving average to forecast sales. Then estimate demand again with a weighted moving average in which the sales in the most recent year are given a weight of 2, and sales in the other two years are given a weight of 1. Which method do you think is best? To solve this, open Excel QM, click on the Excel QM tab  Alphabetical  Forecasting  Moving Average & Naive. A Spreadsheet Initialization window will appear. In the Number of (past) periods of data box, enter 11, since the above data table shows 11 periods. For Name for period, type Year and the Number of periods to average is 3. Uncheck the box next to Graph. Click OK. Now, in the Demand column of the spreadsheet, enter the demand information from the data table above and the moving average is already calculated. You can see from the output that our forecast for the next period is simply 13.6667. You can also calculate this by hand by taking the average of the three prior periods (12, 14, 15). Click here if you would like to download and view the completed sample spreadsheet. For calculating a weighted moving average the process is similar, but instead of selecting Moving Average & Naive from the forecasting menu, select Weighted Moving Average. A Spreadsheet Initialization window will appear. Like before, in the Number of (past) periods of data box, enter 11; for Name for period, type Year and the Number of periods to average is 3. Uncheck the box next to Graph. Click OK. Again, in the Demand column of the spreadsheet, enter the demand information from the data table above; in the Weights column of the spreadsheet, put in weights of 1, 1, and 2 going down from the top. You can see that the forecast for the next period is 14. Click here if you would like to download and view the completed sample spreadsheet. The weighted moving average tends to be more precise since you can weigh the more recent periods a little heavier than the older periods. This concludes the tutorial on calculating moving averages and weighted moving averages. Simple Linear and Multiple Regression In this tutorial, we will be covering the basics of linear regression, doing both simple and multiple regression models. The following data gives us the selling price, square footage, number of bedrooms, and age of house (in years) that have sold in a neighborhood in the past six months. Selling Price 64000 59000 61500 79000 87500 92500 95000 113000 115000 138000 142500 144000 145000 147500 144000 155500 165000 Square Footage 1670 1339 1712 1840 2300 2234 2311 2377 2736 2500 2500 2479 2400 3124 2500 4062 2854 Bedrooms 2 2 3 3 3 3 3 3 4 3 4 3 3 4 3 4 3 Age 30 25 30 40 18 30 19 7 10 1 3 3 1 0 2 10 3 We need to develop three simple regression models to predict the selling price based on each of the individual factors and determine which one is the best model. Next, we will develop a model to predict the selling price of a house based on the square footage, number of bedrooms, and age and will discuss if all three variables should be included and if it is a better model than just the three simple regression models. To use Excel for regression, we do not want to use the Excel QM module, but rather will be using the data analysis add-in. To check and be sure that it is activated, go to File  Options  Add-ins. An Excel Options window will appear as shown here. Under Active Application Add-ins be sure that Analysis ToolPak is there. If not, click the “Go” button at the bottom of the window next to “Manage Excel Add-Ins” and simply tick the box next to Analysis ToolPak and Analysis ToolPak VBA then click OK. Once you have the Add-ins in place, you are ready to get started. 1. Enter or copy the data from the table above into a blank Excel spreadsheet as shown here. 2. Click on Data Data Analysis and, in the Data Analysis pop-up window, scroll down and select Regression and click OK. 3. Click in the box for Input Y Range and this is going to be our dependent variable, or in this case, the selling price, so highlight cells A3-A20. 4. Our first independent variable will be square footage, so click in the box for Input X Range and select cells B3-B20. Be sure that the box is ticked next to Labels and select the Output Range as F3. 5. Click OK. This will put the regression output next to our data table. Repeat steps 2-5, but select C3-C20 for the number of bedrooms and put the Output Range as F23, then, repeat steps 2-5 again but select D3-D20 for Age, and put the Output Range as F43. You should now have all three simple regression models. Click here to download the completed sample spreadsheet so you can compare it to yours. The key parts of this output are as follows (using the square footage example): Under the “Regression Statistics”  Multiple R – the correlation coefficient – notes the strength of the relationship – in this case, 0.80358 – a pretty strong positive relationship.  R squared – the amount of variability in the dependent variable explained by the independent variable(s). In this case, 0.6457 – again, a pretty strong number – almost 65% of the variability in purchase price is explained by square footage.  Adjusted R squared – this is when you have more than one independent variable and have adjusted the R squared value for the number of independent variables. Use this when looking at a multiple regression model. Under the ANOVA Tables  Significance F – this tests the significance of the overall model. We look for this to be less than 0.05. If it is less than 0.05, we can reject the null hypothesis and determine that the model is statistically valid. In this case, it’s 0.000102, so we have a valid model.  Intercept Coefficient – this is the intercept for our line if we were to plot it out. With X as zero, this is where the line crosses the Y axis. Here its 2367. So a house with zero square feet will sell for $2,367.  X Coefficient – this is the coefficient for our independent variable for the linear equation. It is the slope of our line or the amount that our dependent variable changes for every $1 change in our independent variable. For every increase in square footage by one, our price will change by this amount, or $46.6.  X P-Value – this tests the significance of the variable. We look for this to be less than 0.05. If it less than 0.05, we can reject the null hypothesis and determine that the variable is statistically significant. It’s 0.000102, so we have a significant variable. Running a multiple regression is the same as a simple regression, the only difference being that we will select all three independent variables as our ‘X variables’ – our Input Y Range is A3-A20 while our Input X Range is now B3-D20. Again, be sure to tick the box for Labels and this time select New Worksheet Ply as your Output option. Click here to download the completed sample spreadsheet so you can compare it to yours. If we look at those statistics for all three simple models and our multiple regression model, we get the following: Model Square Footage Bedrooms Age Multiple model: SF Bedrooms Age Significance Multiple R (model/variable) 0.000102 .803581 R-Square .645743 Adjusted R Square .622126 0.010206 0.00000295 0.00000212 .017955 .811196 .000162 .365053 .776757 .886137 .322723 .761874 .859861 .604196 .881338 .941348 Comparing the three simple models, we can see that the model using age as the predictor of price is the best. It has the highest Multiple R (i.e., strongest relationship) and highest R-Square (explains most of the variability in the dependent variable). Looking at the multiple model, this is even better. Both Multiple R and R-Square are higher, even when adjusting for the number of dependent variables. What is interesting here is that the number of bedrooms is not significant in this model, so that should not be included in the final model. This concludes the tutorial on both simple and multiple regression models.
Purchase answer to see full attachment