Computation Section
Subunit Forecasting
 - Investments

One application of forecasting is in equity stock investment analysis. Here we construct a worksheet for the analysis of Cisco Systems common stock. The goal is to automatically make buy and sell decisions on the basis of the time series of daily closing prices for shares of the stock.

The decision process uses the ratio of two moving averages a slow (long period) moving average and a fast (short period) moving average. We select the denominator of the ratio as a moving average of 20 days, about four 5-day weeks. The numerator is the moving average of some smaller number of periods. When the fast moving average moves above the slow moving average, the ratio is greater than 1 and we give the signal to buy. The buying price is the next day's closing price. When the fast moving average falls below the slow moving average, the ratio is less than 1 and we give the signal to sell. The selling price is the next day's closing cost.

To set up the worksheet we chose the Compare menu item. In the dialog below we choose to show two moving averages. We choose not to provide a forecast, because the method uses only the two current moving averages. The data will be the closing prices. The extra data column is for the observation date. The extra results columns are for analysis and book keeping.


The top of the Cisco worksheet is shown below. We have entered information for 125 trading days ranging in dates from March 20, 2003 to September 15, 2003, an interval of about 6 months. For the illustration the fast moving average has only 2 periods. The first 20 days are used for a history.

Cells L2 and L3 hold the initial wealth and the number of shares per transaction. No transaction costs are charged and there is no return for funds not invested.

The analysis computes the ratios of the two moving averages in column J. Whenever this ratio passes from a number less than or equal to 1, this means that the mean price for the two day period is greater than the long run average. This indicates that the market may be going up and we choose to buy. The initial entry in J29 is set to 1, and since the ratio is more than 1 in J30, the stock is purchased on the first day.

Columns J through Q hold formulas that compute various quantities. The formulas return *** when the results are not numeric. Column K is 1 when stock is owned and 0 of not. Column L holds the buy/sell action. Column M holds the number of shares owned. Column N shows the change in capital caused by a buy or sell. Column O holds the amount of the capital that is not invested, and column P shows the value of the stocks owned. Column Q is the sum of O and P and is the investor's wealth. The goal is to maximize the ending wealth.

Several transactions occur for days 8 through 74, but we show rows near the bottom of the forecast below. September 16 is the last observed date. On day 77 the fast moving average falls below the slow moving average and the ratio falls below 1. This is a sell signal and the 500 shares are sold at the next day's price. Day 87 gives a signal to buy and the stock is purchased on the next day. The pair of transactions on day 77 and 87 actually generate a net loss. There is no guarantee of success. The analysis comes to an end on day 105 with the investor having a total wealth of $12,680.


We present this example to show the kind of analysis that is possible using forecasting methods, not to suggest that this is a good way to decide on investments.



Return to Top

tree roots

Operations Management / Industrial Engineering
by Paul A. Jensen
Copyright 2004 - All rights reserved