|User Guide Navigation:|
Business Spreadsheets User Guides: Project Planning and Management Multiple Regression Analysis and Forecasting Investment and Business Valuation Real Options Valuation Portfolio Optimization Portfolio Performance Tracking Customer Invoicing
Portfolio Optimization Help Topics: Setting Up the Inputs Running the Optimization Process Analyzing and Utilizing the Results Applications to Business Portfolios
Upon running the optimization process you will be taken directly to the 'Results' sheet.
Portfolio Optimization Results
The optimization results show the current portfolio on the left and the optimized portfolio on the right. For each of these, a pie chart displays the product or business weightings. Underneath this, the results of the Monte Carlo simulation show the return distribution of returns for the portfolio. The axis for each portfolio's return distribution graph is set to the same scale, to illustrate the optimized portfolio's enhanced return/risk attributes. Beneath these graphs is the portfolio's return, standard deviation and resulting Sharpe or Sortino ratio, the probability of achieving the target return and the Omega ratio. Benchmark data is displayed in between the current and optimized results. It should be noted that these results are in the frequency specified by the historical price data in the 'Input' sheet. If, for example, the historical price data is specified in months, these results are also based on months. To convert monthly results to annual equivalents, the return should be multiplied by 12 and the standard deviation should be multiplied by the square root of 12 (multiplying by the square root of time is required to convert standard deviation data).
Below this analysis is a table listing each product or business with current structure, theoretical change and optimal structure specified in percentage weightings and number of units. The theoretical change columns indicate the acquisition and liquidation actions required to arrive at the optimal portfolio. These are labeled as theoretical as they are based on historical data, and the relationships between products that drive this cannot be guaranteed to be replicated in the future. They do, nevertheless, provide valuable information on how the products or businesses behave as a portfolio and can assist in making acquisition and liquation decisions. The number of units corresponding to each product or business in the current portfolio is displayed here from the Input sheet or calculated from the 'Input' and 'Correl' sheets if the weightings have been modified. The current portfolio unit numbers can be modified here in order to simulate different buying and selling scenarios.
Key optimized result sets can be loaded directly from the drop down menu between the weighting pie charts. Alternatively, specific result sets that meet a desired risk return profile can be identified and loaded from the data to the right of the results holding all points around the frontier. Possible portfolio weighting sets can be ranked by return, risk (standard deviation), and optimization ratios in order to identify a particular profile. By selecting the desired row and clicking the Load Selected button at the top right will load the result set into the results with accompanying probability analysis.
Technical Analysis Results
The Technical Analysis section of the results shows charts of the total return achieved by the current and optimized portfolios and the efficient frontier tangent and test portfolio set. Beneath these two charts is the results of the technical analysis displaying back tested returns over the observation time period as well as signals and screening results for the last observation period.
Technical indicator constant parameters can be optimized in the 'Tech' sheet and the technical analysis updated by using the 'Run Technical Analysis' button at the top to update the overall portfolio technical analysis and back tested results.
The Indicators section of the technical analysis shows the back tested total return for the optimized portfolio by following the Buy and Sell signals under each indicator. Additionally the number of investments with Buy and Sell signals is displayed relating the indicator analysis for the last period in the observation data. This information is also shown as a simple average to give an overall indication of the technical analysis impact.
The Back Test Return section of the technical analysis compares the back tested total return that would have been achieved by either holding each investment (do nothing) or trading on the Buy and Sell signals. These returns are computed for both the Current and Optimal portfolios and the value added for trading signals is calculated for both.
The technical analysis results for each investment comprises of three sections:
Technical Indicator Analysis
The 'Tech' sheet allows the back testing, period constant optimization and calculation of return gains from following trading signals from five technical analysis indicators. Technical Indicators employed for the analysis are:
Investment titles are available in the drop down menu to load and run technical analysis at the investment level. The ‘Current Portfolio Index’ and ‘Optimized Portfolio Index’ options at the top represent the portfolio as a total value index for purposes of analyzed trends on the funds and aggregated investment products.
Technical Indicator Test Results: The Test Results display whether or not the loaded investment data passes technical analysis tests for last date of the observation period and any Buy or sell signals that eventuated from that date. For Long portfolios a screening Pass means that the last recorded signal was a Buy signal and Fail indicates the last signal was a Sell. These results can be interpreted and utilized under a variety of methods. Common approaches include accepting investments with at least three Pass screenings or Pass screening tests for the indicators that have recorded a value added total return above a certain threshold based on the back testing results.
Back Tested Signals: The Back Tested Signals report the total return that would have been attained by following the Buy and Sell signals derived from the technical analysis and their difference from the Hold (do nothing) strategy. The average and range are also displayed to give an overview of the combined effect of back testing on the investment. Valued added results can be optimized by obtaining the optimal period constants for each of the indicators by using the 'Optimize Constants' tool to the right.
Technical Indicator Charting: The Charting presents a drop down menu to select the technical indicator to visualize the analysis. The first chart displays the input data with any technical indicator that is relevant to the same scale as well as Buy and Sell signals of the observation period. The second chart displays any supplementary technical indicator data to show cross over and trend analysis relevant to signal generation. The third chart displays the back tested total return from Hold (do nothing) strategy and signal trading strategy.
Indicator Constant Optimization: Each technical indicator is dependent on the period constant parameters defined in the blue input cells above each of the indicator time series data. These can be manually adjusted for test alternative back tested trading scenarios. The constant parameters can also be automatically optimized to produce the highest back tested return under each indicator. Optimization can be applied to either the 'Current Data' all 'All Input Data'. Optimizing constant parameters for current data finds the best parameters for the currently loaded investment only and is useful determining the optimal trading strategy for each investment individually. Optimizing constant parameters for all data find one set of parameters that, when applied to all investments, results in the best performance for the entire portfolio. Optimizing for all data will likely result in a lower total return than optimizing for each investment, but with the benefit of having only one set of parameters to monitor on an ongoing trading strategy. The effect of all data optimization on the portfolio can be ascertained by the 'Run Technical Analysis' button in the 'Results' sheet.