# Technological Forecasting by Excel by yurtgc548

VIEWS: 12 PAGES: 32

• pg 1
```									Other Growth Curves and
Correlation

Henry C. Co
Technology and Operations Management,
California Polytechnic and State University
http://www.csupomona.edu/~hco/MoT/03USKWh.xls

We will use data on U.S.
electric power
consumption from 1945
through 1965 to forecast
consumption for 1970.

We will illustrate the use
of exponential trends
through what is actually a
‘postdiction’ rather than a
"prediction.”

Other Growth Curves and Correlation   2
Exponential Curve –
Using Excel Function GROWTH
Function GROWTH
   Calculates predicted exponential
growth by using existing data.
   Using known x- and y-values, returns
predicted y-values corresponding to a
series of new x-values.
   The GROWTH worksheet function can
also fit an exponential curve to
existing x-values and y-values.

Other Growth Curves and Correlation   4
Other Growth Curves and Correlation   5
GROWTH is an array formula.
After entering the formula C2=GROWTH(B2:B22,A2:A22),
select the range C2:C22 (i.e., starting with the formula cell).
Press F2, and then press CTRL+SHIFT+ENTER.

Other Growth Curves and Correlation                  6
Enter the formula C23=GROWTH(B2:B22,A2:A22,A23:A45)

Other Growth Curves and Correlation        7
Select the range
C23:C45 (i.e., starting
with the formula cell).
Press F2, and then
press
CTRL+SHIFT+ENTER.

Other Growth Curves and Correlation   8
Inserting Chart
Select the range A1:C45.
Click ‘Insert,’ select charts (scatter).

Other Growth Curves and Correlation   9
Modify chart type, location of legend, etc.

Other Growth Curves and Correlation   10
Exponential Curve –
Using Trend line
Select the range A1:B22.
Click ‘Insert,’ select charts (scatter).

Other Growth Curves and Correlation   12
Move the cursor to any point on the graph, and right-click.

Other Growth Curves and Correlation              13
Move the cursor to any point
on the graph, and right-click.

Select Exponential.
Forecast Forward 34 periods
(through 1989).

Display Equation and R-
squared value on chart

Other Growth Curves and Correlation   14
Other Growth Curves and Correlation   15
Use the trend-line formula for
post-diction:
y = 7E-59e0.0753x.

C2 = 7E-59*EXP(0.0753*A2);
Copy and Paste.

Other Growth Curves and Correlation   16
Select the range A1:B22.
Click ‘Insert,’ select charts (scatter).
Modify chart, legend, etc.

Other Growth Curves and Correlation   17
Try other trend-lines:
Polynomial, etc.

The easiest way to do this is to make a copy of the
worksheet for the Exponential trend-line. Move the cursor
to the trend-line, right-click, and choose a different
trend-line.
Polynomial Trend-Line

Other Growth Curves and Correlation   19
Moving Average

Other Growth Curves and Correlation   20
Exponential Curve –
Using Linear Regression
   The exponential growth curve
assumes that the growth is dy  k  y
dt
reached. This is expressed
k t
mathematically as: y  y0  e
   where k is the constant of proportionality.
   Solving this differential equation, we
have:
   By taking logarithms on both sides,
ln y = Y = ln y0 + kt.

Other Growth Curves and Correlation   22
loge Transformation
Enter the formula for cell C2=LN(B2).

This is the formula for the
transformed data (log base e).

Copy and paste the formula for cell C2
to C3:C22.

Y (column C) is linear with respect to Year
(Column A)

Other Growth Curves and Correlation                         23
The Intercept and the Slope
   Cell H6 is the value of the intercept of the linear
regression line.
 Excel function Intercept calculates the point at which
a line will intersect the y-axis by using existing x-
values and y-values.
 The Syntax is INTERCEPT(known_y's,known_x's)
   H6=INTERCEPT(C2:C22,A2:A22)

   Cell H7 is the value of the slope of the linear
regression line.
 Returns the slope of the linear regression line
through data points in known_y's and known_x's.
 The Syntax is SLOPE(known_y's,known_x's)

 H7=SLOPE(C2:C22,A2:A22)

Other Growth Curves and Correlation        24
Regression Equation
Enter the formula for cell
D2=\$H\$6+\$H\$7*A2

This is the formula for the
(transformed)predicted U.S.
electric power consumption.

Copy and paste onto D3:D45.

Other Growth Curves and Correlation   25
EXP() Transformation
Enter the formula for cell E2
=EXP(D2) .
This is the predicted U.S.
electric power consumption in
million MW-h.
Copy and paste onto E3:E45.

Forecast for 1970 is 1,780,009 million
KWh, 4% above the actual million KWh.

Forecast for 1975 is 2,593,658 million

Other Growth Curves and Correlation                             26
Correlation
   Patents often serve as leading indicators of technological change.
Japanese patenting activity grew dramatically between 1965 and
the mid-1970s. This patenting activity reflected development of
new technology that was responsible for the growth of the
camera market. It also gained a larger share of this market for
the Japanese camera firms, starting about 1970. Consider the
following data on Japanese applications for U.S. patents on
camera, and the Japanese market share on imported 35-mm
camera:

Other Growth Curves and Correlation                28
Function ‘CORREL’
   Click cell F4. Click
fx. Choose
Statistical and
CORREL as follows:

   Enter the data as
shown:

Other Growth Curves and Correlation   29
Correlation Coefficient
   The correlation coefficient will be pasted onto Cell F4.

Other Growth Curves and Correlation       30
   Is there a time lag (or lead) between patenting
activity and market share? Consider the following
analysis:

Other Growth Curves and Correlation       31
Final Worksheets
   Final
worksheet:

   The above
analysis was
replicated for
the Japanese
Watch Patent
Application.
What can
you conclude
from the
completed
analysis?

Other Growth Curves and Correlation   32

```
To top