Docstoc

Technological Forecasting by Excel

Document Sample
Technological Forecasting by Excel Powered By Docstoc
					Other Growth Curves and
Correlation


Henry C. Co
Technology and Operations Management,
California Polytechnic and State University
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.”




             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.
Choose Add Trendline.




             Other Growth Curves and Correlation              13
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




                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
    proportional to the value already
    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
                                         KWh, about 29% higher. Why?

             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
Time Lag (Lead)
   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

				
DOCUMENT INFO
Shared By:
Categories:
Tags:
Stats:
views:12
posted:3/12/2012
language:English
pages:32