      Download Forecasting - Portfolio  An example for the Portfolio option involves an investor with several types of investments. Each investment has a market value that varies in time and the number of units owned by the investor. The investor wants to keep track of the values over time and the total value of the portfolio. She also wants to forecast future values. To create a portfolio forecast, choose the Portfolio option from the menu. The dialog box shown below is displayed. For the example we enter only two data columns (number of investments) so the example worksheet can be displayed here. The number of data columns is limited only by the number of columns on the Excel worksheet. The number of columns used by a single investment depends on the forecasting method chosen. The remainder of the dialog accepts the time horizon, history, extra columns and forecasting method. We have chosen to simulate the data for the example. The worksheet for the example is below. After the titles in column A, columns B and C hold the unit values of the investments. If the investments were stocks, the values would be the stock price per share. Columns D holds the moving average forecast of the unit value of Investment 1 for t periods beyond the data period. The number of periods specified in cell C3 is 2 for the example however this number can be changed. All components of the portfolio have the same forecast time. Formulas in E3 and H3 link these values to the contents of C3. Column F holds the forecast error for the first investment. Columns H and I hold the forecast and forecast error for Investment 2. Columns J and K hold the number of units owned of the two investments. These numbers are under the control of the investor. Column L holds the total portfolio value for each period. It is the sum over the investments of the product of the unit values and the numbers of units owned. Column M holds the forecasted value of the portfolio. It is the sum over the investments of the product of the forecasted value and the number of units owned. The last column is the error in the forecasts. The last few rows have no data indicating that the last observation is in period 7. As time passes data will be added and forecasts revised. The change option can be used to add more rows or move up time.   Operations Management / Industrial Engineering
Internet
by Paul A. Jensen
Copyright 2004 - All rights reserved    