Portfolio Optimization 1.0 - User Guide and FAQ

Frequently Asked Questions - Portfolio Optimization

1. Why can't I see all of the cell formulas?
   The worksheets are protected to ensure the robustness of operation. However, critical formulas are outlined in the embedded help prompts.
   
   
2. When I open the file, it asks me if I want to enable macros. What should I do?
   You need to click "Yes" to accept macros in order to run the tools.
   
   
3. When I open the file, it doesn't ask me if I want to enable macros. Therefore, the macros are disabled. What should I do?
   In this case you need to set your macro security settings to Medium. You can do this by opening a blank workbook and choosing Tools >> Options >> Security >> Macro Security, and selecting the Medium level. You should now be presented with the option to enable macros on opening the template file.
   
   
4. I thought that portfolio optimization was for financial instruments. How can I use it for my business?
   A business is simply a collection of investments. Portfolio optimization can be applied at a variety of different levels within an organization reconciling up to the total organization. Such levels typically include business units making up the entire organization, products and services making up business units, and so on. It is important that profitability for each business or product can be identified by attributing costs and revenues that otherwise be recorded at an aggregated level. Methods such as Activity Based Costing can assist in accomplishing this. The ability to apply optimization analysis to a portfolio of businesses represents an excellent framework for driving capital allocation, investment, and divestment decisions.
   
   
5. The results are slightly different each time I run the optimization process with the same data. Why is this?
   The optimization process utilizes random portfolio weightings to select the most optimal one. For this reason results are likely to be slightly different each time the model is run. Increasing the number of iterations in the CoVar sheet will minimize this difference at the expense of processing time.
   
   
6. Should I just change my portfolio as the model tells me?
   The model results should be used as a guide to making decisions about the make up of your portfolio. It is important to remember that the results are based on historical input data that may not be reflective of future circumstances. Further criteria to be considered should be the ability and constraints to change weightings, and the cost of transactions.
   
   
7. I have differing time periods for my input data. Will the model still work?
   The model requires for each product or business data to be based on the same time period and frequency. This is to ensure that return and volatility parameters are not biased by missing or zero values.
   
   
8. Why does the model take so long when I have a large number of products?
   The problem is due to the large number of iterations that the model uses for a large number of products in the portfolio. The time it takes will depend on the processor speed of your computer. You can, however, reduce the number of iterations to a more suitable level for your requirements by altering the formula in cell "E4" of the "CoVar" sheet. For example, altering the formula to "=MIN((G4^2)*100,1000)" will limit the number of iterations to 1,000; thus speeding up the optimization process.