Question

Excel Lab 2: Regression and Goal Seek

In this lab, you will use Excel to determine the equation of the model which best fits a set of ordered pairs obtained from data sets. You will enter data, graph the data, find the equation for the regression model, and then use that equation to make predictions for the dependent variable. You will use the goal seek to make predictions for the independent variable. Then you will consider how accurate your predictions are.

@ Part 1 The more a person weighs, the more calories they burn every minute when they are walking. The following table gives the number of calories a person burns per minute of walking as a function of their weight in pounds. Weight (lbs) 100 120 150 170 200 220 Calories burned per minute 2.7 3.2 4.0 4.6 5.4 5.9 1. Enter the data into columns A and B on your spreadsheet, in rows 3-9. 2. Highlight your data and create a scatter plot (the first type, with no lines). 3. Make sure your graph includes a title and that both axes are labeled. 4. Right-click on any data point and choose add trendline from the menu. 5. Select linear and check the boxes for display equation and show R2 (if you have Excel 2003 you will have to click on the options tab to see the last two checkboxes). 6. Based on your equation from step 5, state the slope. Explain in a complete sentence what the slope means in the context of the problem. 7. In the cell, C3 type the equation from step 5. 8. Type the equation in Excel format in cell C4 so that Excel calculates the number of Calories burned per minute by a 100 lb person. Drag this formula to cell C9. 9. Compare the actual Calories burned in column B to the predicted calories burned in column C. How accurate do you think your model is?

@Part 2 Type the following table, which has historical world population data, into sheet 2 in rows 1 and 2. Year 1000 1650 1800 1850 1900 1920 1930 World population, in millions 200 545 924 1171 1635 1834 2170 1. Graph the above population data using the same steps as in Part 1. Add an exponential trendline, being sure to include the equation and R2 value in the chart. 2. In row 3 calculate the values for population predicted for each of the years by your exponential model (as you did in step 9 of exercise 1). You will need to use Excel’s “=exp()” function for the value of “e”, the base of your exponential model. The complete exponent must be included in parentheses as the input for the exp function. Note: Do not use “^” in the exp function. 3. In the cell, I1 enter the value of 2008. 4. Drag your formula to cell I3. What does your model predict for the world population in 2008? 5. In a complete sentence, explain whether you believe this is realistic, and why. 6. Now let's use goal seek to calculate what year the population was 1 billion (1000 million). 7. Drag the formula from row 3 into cell J3. 8. Highlight cell J3 and click on Data, then What-If-Analysis, then Goal Seek. 9. You want Excel to fill in the value in cell J1 so that the formula in J3 results in 1000. So, in the pop-up box tell it to “set cell J3 to value 1000 by changing cell J1.” 10. According to your calculations, when was the world population 1 billion? 11. Looking at the actual population values in the table, how accurate do you think your calculation is?

Answer 1

Slope Interpretation:

If the year increases by 1 year, we predict the population in year will increase by approximately 0.027.

When compare the Actual Value and Predicted Value where the difference is very low. its means our model predict the value very good.

Our model predict the value it is not good. where we can see there is big difference between actual and predict value.