Stand-Alone Allocation Tool Brief Overview - Version 2005.09:  

• Define your asset classes (up to 21) on the “Define Portfolio, Asset Classes, and Allocation” worksheet. Name the model portfolios and indicate asset class allocation percentages for each. When you enter the asset classes you wish to use in the “define Classes.Models” worksheet start at the top and fill in every row as you go down leaving any empty asset class rows at the bottom of the worksheet. Determine which model portfolio matches the client’s situation (based on your suitability interview). Select applicable model portfolio button on the “Master Allocation” worksheet.
• Enter all clients’ current investment holdings by asset class on the “Investment Holdings Detail” worksheet in columns c through h. One line per holding (Up to 500). In the case where a client owns a variable annuity contract, you should enter separate entries in the investment holdings detailed worksheet for each separate sub account held in the annuity (a 401K with multiple funds – is another example of where multiple entries is appropriate). The sort buttons are available to sort the data alphabetically by asset class or by account type. Asset classes should be entered into the investment holdings detail worksheet to match (spelling, spaces, etc.) those asset classes entered on the asset class list on the “Define Classes.Models” worksheet. To facilitate this a pull-down menu arrow appears on the right side of each of the asset class cells in column C. This pull-down menu contains identical matches of the asset class names you have entered on the “Define Classes.Models” worksheet. Simply select the correct asset class name in the pull down menu… This will insure your entry matches those class names entered on the “Define Classes.Models” worksheet. Excel will not allow you to enter new data that does not match the asset classes listed on the “Define Classes.Models” worksheet. However, if after entering data on the detailed worksheet you then modify an asset class name on the “Define Classes.Models” worksheet you will need to adjust all corresponding asset class names on the Detail worksheet to match. If some of the asset class names on the Detail sheet to not match the master asset list on the “Define Classes.Models” worksheet the file will be out of balance and a warning message will be visible at the top of the Detail worksheet. To the extent that the investor will need to purchase new holdings (that are not currently in the portfolio) as part of the effort to balance the allocation, enter those new positions in the “Investment Holding Detail” worksheet as well (columns c through h) as you would enter existing holding, however, in the “Holdings Existing Value” column (G) you will enter a value of zero. Then you will show the amount of the proposed buy for the new position in column H.

• Visually compare the current portfolio with the model portfolio on the “Master Allocation Balancing” worksheet. Determine the appropriate “buys” and “sells” needed to move client toward the model portfolio. This decision may be influenced by client “feelings” about specific investment holdings, investment holdings performance, as well as tax considerations for each holding.
• Manually enter recommended “Buys” and “Sells” on the “Investment Holdings Detail” worksheet. Buys are entered as positive numbers and sells are entered as negative numbers.
• See how the resulting portfolio compares to the Model Portfolio on the “Master Allocation Balancing” worksheet. There is also a summary of this comparison at the top of the Investment Holding Detail worksheet to the right of the main section for your convenience.
• Return to the Detail and adjust “buys” and/or “Sells” accordingly to move to the model portfolio or as close as is acceptable. On the Detail holdings page click and drag to highlight desired print range. Select File>Print Area>Set Print Area to set print area appropriately. Recommended buys and sells are totaled by asset class and are summarized on the Master Allocation worksheet.
• Check the graphs on the Master Allocation worksheet. If labels are not arranged properly – do a slow double click to select the appropriate label and drag the label to a more favorable position.

Additional Information:

To in sure the column sort macros are functioning properly be sure to set the macro security level in Excel to medium…To do so, select Tools>Macro>Security>Medium. Close and reopen the file.

IF I DO NOT USE ALL 21 OF THE ASSET CLASSES, HOW DO I ELIMINATE THE ZERO VALUES IN THE GRAPHS ON THE MASTER ALLOCATION SPREADSHEET? In the Master Allocation worksheet scroll half way down such that you can see the bottom of the data grid and the top of the graphs. Select one of the graphs by clicking in the white area just outside the asset class labels. When you select the graph in this way you should see blue and purple outlines from around the source data in the top section of the spreadsheet. You should notice a little square in the bottom right corner of the blue and purple data source outlines. Move your cursor over the little square. The cursor will be transformed into a “plus sign”. Click the little square and drag the outlines upward such that the outlines only surround the active data rows in the grid – omitting rows that do not contain asset class and associated data. This process will eliminate the empty lines in the worksheet from the graph data source and it will therefore remove the zero values from the graph. Repeat this process for the other graph as well. If at some point you choose to use more rows in the spreadsheet, it will be necessary to move the graph data source outlines back to their original position