Other Growth Curves and Correlation

1
Other Growth Curves and Correlation

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

2
Download data from 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.
3
Exponential Curve Using Excel Function GROWTH
4
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.

5
(No Transcript)
6
GROWTH is an array formula.
After entering the formula C2GROWTH(B2B22,A2A22
), select the range C2C22 (i.e., starting with
the formula cell). Press F2, and then press
CTRLSHIFTENTER.
7
Enter the formula C23GROWTH(B2B22,A2A22,A23A45
)
8
Select the range C23C45 (i.e., starting with the
formula cell). Press F2, and then press
CTRLSHIFTENTER.
9
Inserting Chart
Select the range A1C45. Click Insert, select
charts (scatter).
10
Modify chart type, location of legend, etc.
11
Exponential Curve Using Trend line
12
Select the range A1B22. Click Insert, select
charts (scatter).
13
Move the cursor to any point on the graph, and
right-click. Choose Add Trendline.
14
Move the cursor to any point on the graph, and
right-click. Choose Add Trendline. Select
Exponential. Forecast Forward 34 periods (through
1989). Display Equation and R-squared value on
chart
15
(No Transcript)
16
Use the trend-line formula for post-diction y
7E-59e0.0753x. C2 7E-59EXP(0.0753A2) Copy
and Paste.
17
Select the range A1B22. Click Insert, select
charts (scatter). Modify chart, legend, etc.
18
Try other trend-linesPolynomial, 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.

19
Polynomial Trend-Line
20
Moving Average
21
Exponential Curve Using Linear Regression
22

• The exponential growth curve assumes that the
  growth is proportional to the value already
  reached. This is expressed mathematically as
• 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.

23
loge Transformation
Enter the formula for cell C2LN(B2). This is
the formula for the transformed data (log base
e). Copy and paste the formula for cell C2 to
C3C22. Y (column C) is linear with respect to
Year (Column A)
24
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)
• H6INTERCEPT(C2C22,A2A22)
• 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)
• H7SLOPE(C2C22,A2A22)

25
Regression Equation
Enter the formula for cell D2H6H7A2 This
is the formula for the (transformed)predicted
U.S. electric power consumption. Copy and paste
onto D3D45.
26
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 E3E45.
Forecast for 1970 is 1,780,009 million KWh, 4
above the actual million KWh. Forecast for 1975
is 2,593,658 million KWh, about 29 higher. Why?
27
Correlation
28

• 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

29
Function CORREL

• Click cell F4. Click fx. Choose Statistical and
  CORREL as follows
• Enter the data as shown

30
Correlation Coefficient

• The correlation coefficient will be pasted onto
  Cell F4.

31
Time Lag (Lead)

• Is there a time lag (or lead) between patenting
  activity and market share? Consider the following
  analysis

32
Final Worksheets

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