Software : Business : Finance : Portfolio Optimization

The Portfolio Optimization template calculates the optimal capital weightings for a basket of investments that gives the highest return for the least risk. The unique design of the template enables it to be applied to either financial instrument or business portfolios. The ability to apply optimisation analysis to a portfolio of businesses represents an excellent framework for driving capital allocation, investment, and divestment decisions.

 

The Portfolio Optimization template by Excel Business Tools



Key features of the Portfolio Optimization template include:

FAQ

What version of Excel do I need?
All templates are compatible with Microsoft Excel 97 or above.

How can I open the download files?
The download files are "zipped" to to minimise the file size. You will need some extraction software like Winzip to open them on your computer once they are downloaded.

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.

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.

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.

Why can't I see the VBA code?
The VBA code has been protected to ensure the robustness of operation and for intellectual property purposes.

Can other users open the models that I have completed?
When other users open the model for the first time on another computer, they are presented with the 30-day trial version. After 30 days, if they have not registered the model, they will still be able to view completed models, but not run processes. In this way, the templates can be freely distributed to clients and/or interested parties to view results.

I want to customize a model for my own use or to distribute it to multiple users in my organization. What are my options?
Upon purchasing the products, you will receive the password to unlock cells, sheets, and the workbook. Since much of the functionality depends on the original workbook structure, modifications made are at the sole risk of the user. Site licenses are available on purchase so that the model can be registered for an unlimited number of users within the same organization.

An updated version of a model is available on your site. How can I upgrade my older registered version?
You can simply download the new version and it will work as a registered version on the same computers that you have your older version on.

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 optimisation can be applied at a variety of different levels within an organisation reconciling up to the total organisation. Such levels typically include business units making up the entire organisation, 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 optimisation analysis to a portfolio of businesses represents an excellent framework for driving capital allocation, investment, and divestment decisions.

The results are slightly different each time I run the optimization process with the same data. Why is this?
The optimisation process utilises 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 minimise this difference at the expense of processing time.

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.

My input data is in percentage returns. How do I convert it to price data so that the model will work?
The model assumes that the input data is in price or dollar return values for each product or business, and calculates the returns and covariances automatically. If your data is already represented in percentage returns, you can convert it to price data using an index. This can be done in a separate spreadsheet and copied into the model or entered directly in the Input sheet with the formula referencing to your return data. To do this, start by inputting 100 for the first observation for each product. Then for each subsequent observation use the formula of the previous cell multplied by (1 + the percentage return for that period).  If this is the first observation for the first product, the formula would look like =D7*(1+[percentage return]). Assuming that the [percentage return] is a relative link to a cell with the return data in a column, this formula can then be copied down for the rest of the observations. Finally, to force the current portfolio weightings to calculate correctly, you then make the current number of units (in The Current Units row above) for each product or business equal the total value held in that product or business divided by the final observation's index price.

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.

Why does the model "hang" 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. Even though the program appears to "hang" the model will eventually run to the end. The time it takes will depend on you available RAM and processor speed. 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.

Secure Order

Download

Additional Information: Portfolio Optimization

Require Excel 97 or greater.   $18.00

Buy Online


Home

Excel Spreadsheets