How to...

• Overview
• An Important Principle
• Importing Analysts' Stock Rankings into BIRR
• Converting Stock Rankings into the BIRR Format
• Creating a New Optimized Portfolio
• Controlling Sector or Style Weightings
• Avoiding Very Small Holdings in an Optimized Portfolio
• Optimizing an Existing Portfolio
• Handling Missing Stocks
• Using the Buy-Sell Feature
• Using the Extended Database
• The BIRR Advantage

One of the most frequent uses of the BIRR Risks and Returns Analyzer® is to develop a portfolio that optimizes some desired attribute while at the same time ensuring that multiple other measures of risk exposure (and possibly style/sector weightings) lie within acceptable ranges. A simple objective would be to create a portfolio having a risk profile comparable to that of the S&P 500 while investing in stocks recommended by your firm's analysts. More complex goals are also possible.

What follows is a set of step-by-step instructions explaining how to perform optimizations for a few illustrative examples. In these examples we assume that the target risk profile is that of the S&P 500, but any desired benchmark can be substituted. For more information on BIRR's approach to risk analysis and control, see "The BIRR Approach to Controlling for Economy-Wide Surprises."

We assume here that you're at least somewhat familiar with how to use the BIRR Risks and Returns Analyzer software. If you're not a regular user, however, you may find it helpful to keep in mind the following:

• To make a selection from a menu, either highlight the desired choice with the up and down arrows and then press the Enter key, or type the first letter of the menu entry.

• To back out of a given menu, data entry screen, report, or graph, press the Esc key.

• For general help on keyboard functions, press the F1 key. For keys specific to the current screen, see the prompt line at the bottom of the screen.

An Important Principle

The BIRR Risks and Returns Analyzer is designed to serve as a tool to assist your decision-making, not substitute for your own judgment and expertise. Using the optimizer is often an iterative process of designing a set of optimization objectives, examining the resulting optimized portfolio, revising the objectives in light of that analysis, and so on, until you've achieved the results that you want.

Importing Analysts' Stock Rankings into BIRR

Problem: Import a text file of analyst stock rankings into BIRR as a user-defined variable.

BIRR uses a simple format for a file containing values for a user-defined variable, such as stock rankings. It must be an ordinary ASCII text file (the kind of file used by simple text editors such as MS-DOS Edit and Windows Notepad). There must be one line per stock, and each line must start with a ticker symbol or CUSIP number followed by a numerical value. The value must be separated from the stock identifier by one or more spaces or tabs. Anything to the right of the value on the line is ignored. The lines should not be wider than 80 characters (the width of a DOS screen.)

You do not need to list all stocks in the BIRR database, only those for which you wish to import a value. User variable values for stocks not listed in the file will not be affected.

For example, you could load a user variable from a file such as this:

If you used this file to load a user variable, BIRR would update the selected user variable for the stocks with the tickers ABC, DEF, and GHI, and they would receive the values 1, 2, and 3, respectively. Everything to the right of the first two columns would be ignored, and no other stocks would be affected.

If your file is already in the correct format, importing it as a user variable is very simple. (If it is not in the correct format, see the next example.) From the BIRR Main Menu, select Utilities, then User Variables, and finally Load User Variables. Select the user variable you wish to use (such as User Variable 1) and then enter the full name of the file containing the rankings.

The program will load the file and inform you of the number of stocks updated. You will then have an opportunity to give the variable a meaningful name.

See pages S.6-7 and S.55-58 in the "Manual Supplement" part of the BIRR User's Manual for more information on importing and using user variables.

Converting Stock Rankings into the BIRR Format

Problem: Convert a text file or an Excel worksheet to a format BIRR can use. If you have rankings in a different file format or in a different kind of file (such as a worksheet), it is usually fairly simple to convert it to the format required by BIRR. The following example assumes you're using Microsoft Excel® and that you have a text file containing data lined up in columns, one line per stock. One column (not necessarily the first) holds ticker symbols or CUSIP numbers, and another column holds ranking codes. For sake of illustration, let's say the rankings are coded S for sell, H for hold, B for buy, and AB for aggressive buy. Other columns, if any, hold other information of no immediate interest to us here.

The first step is to import this file into Excel. To do so, start Excel and either select File, Open from the menu or click on the open-folder toolbar button. Be sure to change the file type in the Open dialog box to "All Files (*.*)". Switch to the directory containing the file you want to import, select that file, and click Open (or simply double-click the file name) to open it.

Since this is a text file, Excel will import it, in the process converting its contents into an Excel worksheet and giving you control over the process. The details depend on the version of Excel you're using and on the format of the file, but as a rule it is not difficult. (If you encounter a problem, contact BIRR or Microsoft for assistance.) Note that all you need to do at this stage is to get each column of data in the ASCII file into a separate column in the worksheet. If there is a great deal of extraneous information, you may wish to omit the columns other than the two of interest.

Once you have the file in Excel, the next step is to delete any extraneous rows, such as headings or blank lines. The Edit, Delete choice from the menu will allow you to delete entire rows. You can also delete any remaining unneeded columns if you like, but this is not necessary unless the rows are very long (wider than the screen).

You are now ready to convert the information in the spreadsheet to BIRR's format. A good way to start is to add two new columns at the left of the spreadsheet. To do this, mark the first two columns (drag the cursor over the A and B column headings) and select Insert, Columns from the menu. All existing columns will shift to the right and two new, empty columns will appear.

In cell A1, insert an equal sign and a reference to the cell holding the ticker or CUSIP for the first stock (for example, "=C1"). In cell B1, insert a formula to convert the analyst ranking for that stock to a numeric value. Here is one possible formula that assumes the ranking is in cell D1:

Translated into English, this formula means: "If cell D1 contains 'AB', make the result 4; else if D1 contains 'B', make the result 2; else if D1 contains 'H' make the result 1; else if D1 contains 'S' make the result 0; otherwise make the result 'NA'." See the Excel Help system for more information on the IF spreadsheet function.

Many other mappings from letter rankings to numerical ones could be used; the one given here is merely for illustration. It's up to you to determine a set of numerical equivalents that are appropriate for your ranking system.

In some cases you may have multiple rankings from separate analysts. By means of a formula you can combine these rankings into one, even implementing complex rules taking into account how much the analysts disagree among themselves about a given stock. See Excel's documentation for more information about formulas and functions, or contact BIRR for assistance.

Once you have the desired formulas in cells A1 and B1, you can copy them into the appropriate range of cells below. By dragging the mouse, mark the cells you want to copy from (A1 and B1) and press Ctrl-C, then mark the range of cells to copy into (A2 and B2 through as many rows as there are stocks) and press Ctrl-V.

You can now choose File, Save As to save the spreadsheet, taking care to make sure that the file type is either "Text (Tab delimited) (*.txt)" or "Formatted Text (Space delimited) (*.prn)".

When finished, exit Excel. (If you do not exit Excel, or at least close the worksheet you have been using, no other program—including BIRR—will be able to access the file you have just created.)

You can now import the file into BIRR as in the previous example.

Creating a New Optimized Portfolio

Problem: Use your analysts' rankings to generate an optimized portfolio of 50 to 60 stocks with a risk profile close to that of the S&P 500.

Note: If you wish to include stocks from the BIRR extended database in your optimization, see "Related Topic: Using the extended database" on page 12 of this document.

1. From the Main Menu, choose Optimize Portfolio and then Create New Optimization Objectives. This will bring you to the following menu:
   Optimization Objectives Menu
   
   Number of Stocks, Amount to Invest
   Selection Universe
   Excluded Stocks
   Attribute Filter Rules
   Tracking Objectives
   Forecast Scenario
   Planning Horizon
   Overview

2. You can enter your optimization objectives in any sequence you find convenient. Choose Overview at any time to see a listing of your specifications so far. As with any BIRR report, you can print the Overview or export it to a text file by pressing the F2 key.

3. Choose Number of Stocks, Amount to Invest. You'll be asked for a target number of stocks and a target dollar value. Because we'll be using linear programming, the actual number of stocks in the optimized portfolio will be determined by the linear constraints, including maximum weight constraints, that you enter elsewhere, as described below. The number of stocks you enter here will only serve as a comment or reminder. The portfolio's value will be as close as possible to the dollar value you enter here, allowing for rounding to a whole number of shares.

4. Choose Selection Universe to specify the group of stocks from which you wish to build your portfolio. Only stocks found in the selection universe will be candidates for inclusion in the optimized portfolio. You can select one of the pre-defined selection universes such as the entire BIRR database or the stocks in the S&P 500, or you can designate any portfolio that you have already created. For purposes of our example, you may wish to select the BIRR database as the selection universe.

5. Choose Attribute Filter Rules if you wish to omit stocks based on one or more of a set of measures. Suppose that a ranking of 0 or lower in User Variable 1 signifies a stock that should be sold. If you want to exclude all such stocks from the optimized portfolio, apply a filter to User Variable 1, giving it a minimum value of 0.5. (In general, if you want to exclude a stock with 0 but allow a stock with 1, it's good practice to set the minimum value in between.)

6. You would choose Excluded Stocks if you wished to omit one or more specific stocks (specified individually or via a portfolio) from consideration for some reason. You would choose Forecast Scenario and Planning Horizon if you were attempting to optimize future returns on the basis of your own forecast of economic conditions. All these menu entries can be ignored for purposes of this example.

7. Choose Tracking Objectives, and from that menu choose Use Linear Programming. (The other entries on the Tracking Objectives menu are useful in certain specific situations, but for the great majority of users, linear programming is the best option.) This will bring you to the following menu:
   Linear Programming Menu
   Risk Exposure Limits
   Objective Function
   Maximum Weight Multipliers
   Additional Maximum Weight Control
   Portfolio of Minimum Holdings
   Use of Non-stock Assets
   Sector Weighting Constraints

   Choose Risk Exposure Limits and from the resulting menu choose Forecasted Macroeconomic Exposures. (The option to use historical exposures is provided mainly for completeness. In most cases the forecasted exposures will give more satisfactory results. Note, however, that the non-linear nature of the forecasted exposures may sometimes result in the optimized portfolio's being slightly outside the constraints you specify. In such cases you can, if you like, re-run the optimization with somewhat tighter constraints.) This brings you to the constraints entry screen (see next page).

   Enter minimum and maximum values for the risk exposure constraints. Enter an asterisk (*) when you want no limit to apply. Note that BIRR provides you with a default set of constraints to use as a starting point, based on the risk exposure profile of your current comparison asset.

   You can use the following keys when editing the risk exposure constraints:

   F3	switches the units between actual values and the number of standard deviations from the comparison asset.
   F6	enables you to change to a different comparison asset.
   F8	allows you to reset the minimum and maximum values to one of a number of possible defaults based on the comparison asset.
   F9	permits you to enter a multiplier to adjust the degree of "tightness" around the comparison asset for all the constraints at the same time. For example, a multiplier of 0.5 adjusts the limits to be only half as far away from the comparison asset as they had been.
   F10	displays a distribution graph of the measure whose minimum or maximum is currently highlighted.
   Tab	moves between columns.
   Esc	signifies that you're done.

   BIRR Risks and Returns Analyzer® BIRR Portfolio Analysis, Inc.

   October 1993 .

   Linear Programming Risk Exposure Constraints
   Enter minimum and maximum values. An asterisk (*) indicates no limit. Comparison Asset: S&P 500
   LIMIT: ACTUAL VALUES	MINIMUM	COMPARISON	MAXIMUM
   Forecasted Confidence Risk	-4.48306	0.53910	5.56126
   Forecasted Time Horizon Risk	0.24732	0.65614	1.06496
   Forecasted Inflation Risk	-0.18821	-0.00634	0.17554
   Forecasted Business Cycle Risk	-2.93706	1.06730	5.07166
   Forecasted Market Timing Risk	0.84645	1.00000	1.15355
   Sec 1: Agr, Mining, Oil, & Constr	-0.10598	0.00000	0.10598
   Sec 2: Light Industry	-0.22166	-0.00000	0.22166
   Sec 3: Manufacturing	-0.22158	0.00000	0.22158
   Sec 4: Trans, Commun, & Utilities	-0.20451	0.00000	0.20451
   Sec 5: Merchandising	-0.17148	0.00000	0.17148
   Sec 6: Finance	-0.14165	0.00000	0.14165
   Sec 7: Services, Health Care	-0.19235	-0.00000	0.19235
   BIRR Alpha (%/yr)	*	0.00000	*

   F3 Units F6 Comparison F8 Preset F9 Adjust F10 Graph Tab Column Esc Done

Controlling Sector or Style Weightings

Problem: Set minimum and maximum weights to control sector or style bias in your optimized portfolio.

In addition to, or instead of, controlling for exposures to the risks presented by economy-wide surprises, you can set upper and lower limits on investments in various categories of stocks, based on any categorization you like. This topic is discussed in detail in the BIRR document How to Examine and Control Sector and Style Weightings.

Avoiding Very Small Holdings in an Optimized Portfolio

Problem: Require that no holding be less than some percentage (0.5 percent, for example) of the total portfolio investment.

Sometimes a few stocks in an optimized portfolio will be held in trivially small amounts. While such a portfolio is mathematically optimal (subject to the specified constraints and objective function), for practical reasons it may not be desirable to deal with very small holdings. Fortunately, such small investments rarely have a significant effect on the risk exposure characteristics or performance of the portfolio.

To see if you have any small holdings, view the portfolio's stock contents. (From the Main Menu, select View or Form Portfolio, then View or Modify Existing Portfolio and select the optimized portfolio of interest from the list. With the red selection bar over the Common Stocks line of the portfolio summary screen, press Enter to view the common stock holdings.) Press F10 and choose Dollar Value of Holdings from the Sort Criterion Menu to sort by investment amount. You can then press the End key to jump to the end of the list and the stocks with the lowest holdings in the portfolio.

(Note that you can use the F3 key to toggle the investment column between dollars and shares held. The sort value column will remain unchanged.)

You can easily delete all the stocks with very small holdings by placing the red selection bar over the first of them and pressing F8. Extend the selection using the up- and down-arrow keys and then press Enter to delete the highlighted range of stocks.

Alternatively, rather than deleting all stocks below your investment cutoff level, you might prefer to delete only the smaller holdings and round others up to a reasonable minimum investment.

These adjustments will, of course, modify the total value of the portfolio. You can readjust it toward your target portfolio size by escaping back to the portfolio summary screen and pressing Alt-T to set a new total. All holdings will be adjusted up or down proportionately to make the total portfolio as close as possible to the value you enter, allowing for rounding to a whole number of shares. You can then save the revised portfolio and view it under Analyze Assets in order to verify that the adjustments did not produce an undesirable risk profile.

Optimizing an Existing Portfolio

Problem: Ensure that the optimizer includes a core portion of an existing portfolio in its solution.

As a practical matter, you will probably not want to turn over an entire existing portfolio in order to create a new one. To prevent this from happening, BIRR allows you to designate a portfolio of current holdings that must be included in the optimized portfolio. That is, the optimized portfolio will contain all the stocks in the minimum-holdings portfolio, and it will contain at least as many shares of each of those stocks as are held in the minimum-holdings portfolio.

(There is only one exception: If you're attempting to maximize or minimize a user variable, all stocks in the optimized portfolio must have a defined value, not NA, for that user variable. If any stocks in the minimum holdings portfolio have NA for that user variable, those stocks will be omitted from the optimization, and the program will alert you to this fact. You can easily check the user variable values for the stocks in a portfolio by sorting the portfolio's contents by that user variable. Stocks with NA will sort to the end of the list.)

To create a minimum-holdings portfolio, start with the portfolio you currently own. (You can load a portfolio from an ASCII file in much the same way as you load user variables. From the Main Menu, select View or Form Portfolio, then Load Tickers or CUSIPs from File.) While looking at the portfolio on the summary screen (the one you see when you first load an existing portfolio), you can scale the portfolio's holdings by pressing Alt-T and entering a new total value. For example, you could reduce a million-dollar portfolio to 75 percent of its value by pressing Alt-T and entering 750,000. (The value of the resulting portfolio may not exactly equal the amount you entered because holdings are automatically adjusted to hold a whole number of shares in each component stock.) You can then save this portfolio under a new name so that you don't lose the original portfolio representing your actual current holdings.

Before saving the portfolio, however, you may then want to manually adjust the holdings of the stocks further, based on your own knowledge of the stocks in question. That is, instead of requiring the new portfolio to hold exactly 75 percent of each of the current portfolio's stocks, you may prefer for it to hold 100 percent of the current investment in stocks you like and no minimum holding in those you don't.

You can sort the portfolio by the rankings you imported to help in deciding which stocks to favor and which to discard. (While viewing the portfolio's common stocks, press the F10 key to get to the Sort Criterion Menu.) You may also want to sort the portfolio based on risk exposures so that you can take those into account as well when specifying these minimum holdings. Including a large investment in a stock with a risk profile very different from that of your target profile may limit what the optimizer can do.

Once you have created a minimum holdings portfolio that meets your requirements, you can use it in your optimization objectives. From the Optimization Objectives Menu, choose Tracking Objectives, Use Linear Programming, and Portfolio of Minimum Holdings, then select the minimum holdings portfolio you created.

Note that the larger the dollar-value of the minimum holdings portfolio as a fraction of the total target portfolio size, the less freedom there is for the optimizer to meet your other objectives. In order to achieve a feasible solution, it may be necessary to relax one or more risk exposure constraints or to start with a smaller minimum holdings portfolio (and therefore to accept more turnover), or to manually modify the minimum holdings portfolio to have a smaller investment in stocks far removed from the risk exposure profile you're seeking.

Handling Missing Stocks

Problem: Create and use a surrogate asset to take the place of a stock not in the database.

Occasionally you may encounter a stock not found in the BIRR database, even after including the extended database (see Related Topic: Using the Extended Database. The most general way to deal with this problem is to create a separate user portfolio to serve as a surrogate asset.

To create this portfolio, select View or Form Portfolio from the Main Menu, then Create New Portfolio. This portfolio should contain the asset or set of assets that (based on your knowledge of the missing stock) you would expect to most likely mimic the returns behavior of the stock for which you're creating a substitute. This could be a set of similar stocks, an industry index, or whatever you deem appropriate.

(Alternatively, if you have a historical return series of at least 72 months for the stock in question, you could create a user portfolio by importing the return series. In this case, you would select Import Portfolio Returns from the View or Form Portfolio Menu.)

When you save this portfolio, you might want to use the company name for the portfolio description and the company's ticker symbol or CUSIP number for its name.

Once you have created your surrogate-asset portfolio, you can set the value of any user variables (such as analyst ranking). Invoke the Utilities Menu (by selecting Utilities from the Main Menu or pressing Alt-U within Analyze Asset) and then choose User Variables and then Adjust Primary Asset User Variables. Enter the value or values needed, then press the Esc key and confirm (by typing Y) that you wish to make the changes permanent.

To include this surrogate-asset portfolio asset in the selection universe of an optimization, you must do the following:

1. Create a portfolio that contains the surrogate-asset portfolio plus all the stocks (and possibly other surrogate-asset portfolios) that you would like to serve as candidate assets for the optimization. (Note: A portfolio's holdings can include other portfolios.)

2. Make this portfolio the selection universe for the optimization.

3. Enable the optimization to include non-stock assets. See pages S.34-35 in the "Manual Supplement" part of the User's Manual for more information.

Using the Buy-Sell Feature

Problem: Generate the transactions needed to change an existing portfolio into one created by the optimizer.

The BIRR buy-sell feature can be used to generate a list of the buy and sell orders needed to turn an existing portfolio into a target portfolio, typically one generated by the optimization feature. In the process it can limit turnover (based on a number of different definitions of "turnover") and take into account available funds and transaction costs.

Note: The buy-sell feature cannot be used with non-stock assets. If your portfolio contains assets other than stocks, you must handle those assets separately.

To use this feature, you need to provide the following:

• The target portfolio (typically generated using the optimization feature, as described in previous examples)

• The starting portfolio (representing your current holdings), if any

• Available additional funds to expend (not including the value of stocks in the starting portfolio)

• Turnover limitation, if any

Using the Extended Database

By default, BIRR excludes stocks with fewer than 72 months of continuous returns history. If you wish to include such stocks, you should select Utilities from the Main Menu and then Control Use of Extended Database. You can allow the inclusion of stocks with as many as 48 months of incomplete returns data (that is, having as few as 24 months of unbroken returns history).

For more information on extended database stocks, see pages S.60-61 in the "Manual Supplement" part of the User's Manual.

The BIRR Advantage

In summary, the BIRR Risks and Returns Analyzer's optimizer allows you to control your portfolio's exposures to economy-wide surprises while at the same time maximizing your analysts' rankings of individual stocks. By giving you control over the optimization process and allowing you to experiment with different approaches, BIRR's flexible approach gives you the power to combine your own portfolio management skills with state-of-the-art risk control.

Copyright © 1996 by BIRR Portfolio Analysis, Inc.