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.
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
Goto Developer Web Site for more information
| Additional Information: Portfolio Optimization | |
|
Require Excel 97 or greater. $18.00 |
|