|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
Once the inputs have been established, the optimization process can be run by clicking the Optimize Portfolio button. This is a 2 step process which runs a convergence folding algorithm to identify out limits of the efficient frontier and Monte Carlo simulation to perform probability analysis. During this process, the program enters data into the 'Correl' sheet which displays a matrix of correlation statistics between each of the investment products or businesses.
The correlation matrix represents the correlation coefficients between the investment products or business cash flows. Covariance is calculated from this and the individual product or business standard deviations during the optimization process. All information within the correlation matrix represents long positions of the investment information. Short position calculations are adjusted accordingly in the optimization process to reflect both return and contributed risk of short positions. All of the inputs in this sheet can be modified in order to test different scenarios of portfolio dynamics and relationships. Attention should be made to ensure that the current portfolio weightings sum to 1 when manually modifying the correlation matrix.
When modifying returns and standard deviations manually, the ‘Calc’ button can be used to update the starting portfolio return, standard deviation and ratio values for optimization.