Pearl Curve on Excel

1
Pearl Curve on Excel

• Henry C. Co
• Technology and Operations Management,
• California Polytechnic and State University

2

• The revised (log base 10) Pearl Curve equations
  is
• This leads to the linear equation
• A postdiction
• U.S. household with cable TV
• Use data from 1952 through 1978 to derive a
  growth model.
• Extrapolation from 1979 to 1988.
• Compare the extrapolation with the known data.

3
Column A t Time (year 1952 through
1988) Column B y Household With Cable TV
(1952-88). Suppose we have decided to use the
data from 1952 1978 to fit the Pearl Curve, and
to use the data from 1979-1988 to validate our
model.
4
The first equation shown here is the Pearl Curve,
where y is a non-linear function of t. L is the
upper limit of y. A and B are constants. We use
the second equation to estimate the values of A
and B, through linear regression. The second
equation shows the transformed variable Y as a
function of variable y and L. Y is a linear
function of t.
5

• Transforming the variable y to Y, such that Y is
  a linear function of t.

6
Click on cell C3. Here we will enter the Excel
formula for y/(L-y), for year 1952 (row 3). Note
that y is in cell B3 and L is in cell B1. First,
enter the sign. Then either enter B3 as
shown, or click on cell B3. Type the division
sign /, followed by the open parenthesis (. L
is in cell B1. Enter B1 as shown, or simply
click on cell B1.
7
Since we are going to copy and paste this
formula, we must make sure that reference to L
stays in cell B1 as we pasted the formula. In
Excel, this means changing B1 to B1. A simpler
way to do this is to hit function key F4 (on the
first row of your keyboard).
8
Now complete the formula for y/(L-y), as shown.
9
Before you go any further, make sure that you
have the result shown in cell C3. Now, move the
cursor to the lower right hand corner of cell C3
(it becomes a sign), click and drag all the
way down to row 29 (year 1978).
10
Before you go any further, make sure that you
have the results shown in column C.
11
Now we are ready to enter the formula for the
transformed variable Y (cell D3 corresponds to Y
for year 1952). The Excel function LOG10(C3)
returns the log base 10 value of cell C3.
12
Before you go any further, make sure that you
have the result shown in cell D3.
13
Now, move the cursor to the lower right hand
corner of cell D3 (it becomes a sign), click
and drag all the way down to row 29 (year 1978).
Before you go any further, make sure that you
have the results shown in column D.
14
Graphing

• Let us graph the variables y and Y.
• Theoretically, variable y is non-linear (S curve)
  with respect to t variable Y is linear with
  respect to t.

15
Click Insert, choose Chart.
16
From the pull-down menu, choose XY (Scatter) as
shown.
17
Click the Series tab to continue.
18
Click Add to add a series.
19
We will first name the series, and then tell
Excel where the x- and y- values are.
20
Enter the name of the series as shown.
21
Next, click on the X-Values field, then click on
cell A3 and drag all the way down to row 29 (cell
A29).
22
Before continuing any further, make sure that you
see the same results, as shown.
23
Next, click on the Y-Values field, and drag to
highlight the entire field.
24
Click on cell B3 and drag all the way down to row
29 (cell B29).
25
Click Add to add the second series.
26
Enter the name of the series as shown.
27
Next, click on the X-Values field, then click on
cell A3 and drag all the way down to row 29 (cell
A29).
28
Next, click on the Y-Values field, and drag to
highlight the entire field.
29
Click on cell D3 and drag all the way down to row
29 (cell D29). Then click Next.
30
Before continuing any further, make sure that you
see the same results, as shown. Click the
Legend tab.
31
Choose to place the Legend at the bottom, as
shown. Click Next.
32
Choose to place chart as new sheet. Enter the
name of the chart, as shown.
33
Before continuing any further, make sure that you
see the same results, as shown. Lets add a
trend line to the series named Linearized.
34
Move the cursor to any point on the series named
Linearized.
35
Right click, you should see the pull-down menu as
shown. Choose from the menu, Add Trendline.
36
Be default, Excel will be adding a Linear
trendline. Click OK.
37
Click the Options tab and check the Display
equation on chart and Display R-squared value
on chart options. Click OK.
38
Before continuing any further, make sure that you
see the same results, as shown. Notice that the
trendline appears to be a good fit, with R2 value
of 0.9514.
39
Linear Regression

• The preceding slide shows the trendline equation
  to b e
• y 0.0804x - 159.61, with R2 0.9514.
• The Intercept 159.61, and the slope is 0.0804
• We can also find the Intercept and Slope
  directly, using Excel functions INTERCEPT and
  SLOPE.

40
Click on cell H6.
41
Click the Insert tab, and choose Function.
42
From the category field of the pull-down menu,
choose Statistical.
43
Choose function INTERCEPT.
44
Click on the Known ys field.
45
Click on cell D3 and drag all the way down to row
29 (cell D29).
46
Click on the Known xs field.
47
Click on cell B3 and drag all the way down to row
29 (cell B29). Click OK.
48
Before continuing any further, make sure that you
see the same result, as shown.
49
Click on cell H7.
50
Click the Insert tab, and choose Function.
51
Choose function SLOPE.
52
Click on the Known ys field. Enter
D3D29. Click on the Known xs field. Enter
A3A29
53
Before continuing any further, make sure that you
see the same results, as shown.
54
A negative of the intercept. Click H22, enter
the formula as shown.
55
B the slope. Click H23, enter the formula as
shown.
56
We are going to use the formula for y shown here
to compute the points on the S-curve.
57
On cell E3, enter the formula for the point on
the S-curve corresponding to t 1952.
Here L is in cell B1, A is in cell H22, and B is
in cell H23. Click cell E3. In order to copy and
paste the formula, cells B1, H6 and H7 must be
anchored.
58
(No Transcript)
59
(No Transcript)
60
The symbol for exponentiation is on the
second row of your keyboard, above the number 6.
61
Before continuing any further, make sure that you
see the same results, as shown.
62
Now, move the cursor to the lower right hand
corner of cell E3 (it becomes a sign), click
and drag all the way down to row 39 (year 1988).
63
To extend the trendline through year 2005,
highlight by dragging cell A38 and cell A39, as
shown.
64
Move the cursor to the lower right-hand corner of
cell A39, and drag it down, as shown.
65
Move the cursor to the lower right-hand corner of
cell E39, and drag it down to row 56.
66
Before continuing any further, make sure that you
see the same results, as shown.
67
Click on the tab C to choose the entire column
C, as shown.
68
Click the Format tab, choose Column, then
choose Hide to hide the entire column C.
69
Click on the tab D to choose the entire column
D, as shown.
70
Click the Format tab, choose Column, then
choose Hide to hide the entire column D.
71
Click and drag to highlight the cells, as shown.
72
Click Insert, choose Chart.
73
From the pull-down menu, choose XY (Scatter) as
shown.
74
Click the Series tab.
75
Name the time series.
76
Name the first time series, as shown.
77
Name the next time series, as shown.
78
Choose the Bottom button to place the legend at
the bottom.
79
Save the chart as a new sheet, name it Pearl
Curve.
80
(No Transcript)
81
Error Statistics

• How accurate was our Pearl Curve forecast?
• We will compute the mean squared error (MSE) for
  the periods covering model fitting, and the
  periods for validation.

82
Label column F Error, as shown.
83
The forecast error actual y predicted y. For
1952, the error (cell F3) B3-E3.
84
Move the cursor to the lower right-hand corner of
cell F3, and drag down to row 39 (year 1988).
85
We use Excel function SUMSQ to compute the sum of
squared errors. We use function COUNT to keep
track of the number of observations.
86
(No Transcript)
87
The Mean Squared Error (MSE) (Sum of Squared
Errors)/(n-1), where n number of observations.
We use Excel function COUNT to keep track of the
number of observations n I27H27/(COUNT(F3F29)-
1) I28H28/(COUNT(F30F39)-1)