Question

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 10.

Graph the above population data using the same steps as in Exercise 1. Add an exponential trendline, being sure to include the equation and R2 value in the chart. 11. 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. 12. In cell I1 enter the value 2008. 13. Drag your formula to cell I3. What does your model predict for the world population in 2008? 14. In a complete sentence, explain whether you believe this is realistic, and why. 15. Now let's use goal seek to calculate what year the population was 1 billion (1000 million). 16. Drag the formula from row 3 into cell J3. 17. Highlight cell J3 and click on tools then goal seek. 18. 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.” 19. According to your calculations, when was the world population 1 billion? 20. Looking at the actual population values in the table, how accurate do you think your calculation is?

Answer 1

10)

The scatter plot between Year and World population, is obtained in excel in following steps,

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

A C F G H 1930 1 Year 1000 1650 1800 1850 1900 1920 World Population (in 2 millions) 2170 200 545 924 1171 1635 1834

Step 2: Select the data values then INSERT > Recommended Charts > XY (Scatter) > OK.

FILE HOME INSERT PAGE LAYOUT FORMULAS DATA REVIEW VIEW DESIGN FORMAT Tables Illustrations Add- Links PivotChart Sparklines Filters Recommended ins Charts Change Chart Type Chi Recommended Charts All Charts Scatter wa Popletoe n eonh 1 2 World Population (in millions) 3 25 00 200 w 2000 5 1500 7 8 1000 9 500 10. 11 12 C 500 1000 1500 2000 13 A scatter chart is used to compare at least two sets of values o 14 data, Use it to show relationships between sets of values. 15 wrow m n NcO O

The Chart is obtained. The screenshot is shown below,

World Population (in millions) 2500 2000 1500 1000 500 C C 500 1000 1500 2000 2500

The trend line and R square value are added in following steps,

Step 1: Click on add Chart Element > Trendline > More Option > OK. The screenshot is shown below,

CHART ELEMENTS World Population (in millions) Axes 2500 Axis Titles Chart Title 2000 Data Labels Error Bars 1500 Gridlines Legend Trendline Linear Exponential 16 17 Linear Forecast 18 Two Period Moving Average 19 1000 1500 2000 2500 More Options... 20

Step 2: Select Tending option: Exponential and tick display equation on Chart and display R square value on Chart then OK. The screenshot are shown below,

Format Trendline RENDLINE OPTIONS TRENDLINE OPTIONS Exponential Linear Logarithmic

Backward 0.0 period Set Intercept 0.0 Display Equation on chart Display R-squared value on chart

The plot is shown below,

World Population (in millions) 2500 2000 y 15.587e.0024x R 0.9081 1500 1000 500 0 500 1000 1500 2000 2500

The trendline equation is,

Y = 15.587 X.0024X

The R square value is,

R2 0.908

11)

The population for each is predicted using the formula,

fix -15.587*EXP(0.0024 B1) B3 B A C D F G H 1 Year 1000 1650 1800 1850 1900 1920 1930 World Population (in 2 millions) Predicted 3 population 200 545 924 1171 1635 1834 2170 171.82 817,65 1171.97 1321.39 1489.86 1563.12 1601.09

12 and 13)

-15.587 EXP(0.0024*11) 12 A C D E P G 1 Year 1000 1650 1800 1850 1900 1920 1930 2008 World Population (in 2 millions) Predicted 3 population 200 545 924 1171 1635 1834 2170 171.82 817.65 1171.97 1321.39 1489.86 1563.12 1601.09 1930.70 CO

14)

The predicted population for year 2008 is 1930.70 million which is not realistic, since the population is increasing and the actual population of year 1930 had already 2170 millions which is much larger than the prediction of year 2008.

15)

Goal, Y = 1000 million,

16)

Drag the formula from row 3 into cell J3

17)

click on tools then goal seek.

REVIEW DATA VIEW neeraj sin... Flash Fill Remove Duplicates Data Validation Outlina Scenario Manager... Data Tools Goal Seek... Data Iable... J K 2008 9 1930.70 15.59

18)

J 2008 1733.882 1930.70 1000.00

19)

World population was 1000 billion in year 1733.

20)

The actual world population was 1000 billion in year between 1650 and 1800 which is somewhat accurate to our prediction.

(Using calculation

For Y = 1000 million,

In(T 15.587 Y 15.587 x e0.0024X x: 0.0024

In1000 15.587 1733 X 0.0024

X fLN(1000/15.587)/0.0024 K1 K N 1 Year 1733.883 World Population 2 (in millions) 1000