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.

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 2008.
4. Drag your formula to cell I3. What does your model predict for the world population in 2008?
Important 5. In a complete sentence, explain whether you believe this is realistic, and why. ( I need a specific answer. )
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?

@ Please, show your all works and upload an Excel sheet.

Thanks.

Answer 1

1)

The regression analysis is done in excel by following steps

Step 1: Write the data values in excel. The screenshot is shown below,

Step 2: Select the year and population column then INSERT > Recommended Charts > Scatter with Straight Lines and Markers > OK.

The chart is obtained. the screenshot is shown below,

Step 3: To add a Trend line, regression equation value in the plot.

Click on Add Chart element> Trendline > More options>OK. The screenshot is shown below,

Now tick the box for Exponential,  Display Equation on Chart and Display R-squared value on chart. The screenshot is shown below,

The chart is obtained. The screenshot is shown below,

The exponential regression equation is,

The R square value is,

2)

The regression model is defined as,

From the regression analysis,

a	15.5872
b	0.0024

The population values are predicted by putting the year X value in the model. The screenshot is shown below,

3 & 4)

5)

The regression analysis is used to predict the value of the dependent variable within the range of the independent variable for which the regression model is originally developed. Hence the prediction is not realistic as we can do not know whether the trend is the same outside the range of the independent variable.

6 to 8)

the goto DATA > Data Tools > What-If Analysis > Goal Seek > OK.

9)

10)

The result is obtained. The screenshot is shown below,

The world population was 1000 million in the year 1737.

11)

If plot the scatter plot we can observe that the prediction for the population is overestimated