For a large number of systems and estimates I would propose the following approach.
Categorize the systems so that optimization can be run for up to 100 systems in each category and then over the category index values in order to overcome the 100 limit for the optimization input.
Ascertain the available historical cash flow data for each system in order to determine the appropriate frequency (months, quarters etc.) for calculation of historical volatility.
Use the option to modify the correlation matrix for the optimization and then update the expected return data to reflect the estimated budgets that are available for each system.
Combine all individual category optimizations by running optimization on category groups and multiplying each system by the capital allocated to the parent category.
When a system comes up for review, optimization can be evaluated by comparing the category (or entire organization) optimization results with the optimization run without that system. This will evaluate the value added of that system to the category or organization which can then be used for decisions on whether or not to continue with the system.
Ok, let me add this. We deal with estimated Budgets, and estimated Cost which when viewed are ONLY reliable at that point in time (of viewing) because Budgets change and it may take years before total Cost is realized. No ROI because we SELL nothing. Yes, that is what we have to work with, and yet still need to produce a Portfolio Optimization/Analysis/Management. We have total 3000 systems that come up for review(funding) at different points in time. Need some method to recommend continue/hold/eliminate System/or Systems as many as 100 at a time.
The program has been set up to optimize business streams (including those for non profit organizations) whereby the business units' cash flows can be used as price data. The optimization can then be used for capital allocation purposes within the organization as a whole.
In order to accomplish this, distinct business units must be identified and costs allocated for each period using methods such as activity based costing or value stream mapping.
If prices are chosen then the division be zero will occur when all prices are zero for two or more periods (e.g. Jan-21, Feb-21 here). It seems as though you should be using return as an input option.
It ran by replacing the date values with some dates; however the results are not robust given the number of zero price data for many periods. Choosing the return option improved this. The real question is what the data is actually meant to portray, as it does not correspond to the stock symbols.
The program accepts zero or non numerical price data on the condition that there is at least one positive price observation for the asset. Blank and non numerical values (such as null) as transformed to zero. Theoretically, there should not be any non positive or non numerical price observations as the price should be replicated for subsequent observations.
If the option is selected to process data as returns, then zero value observations are processed accordingly as zero returns for the periods in question.
Excel Business Forums Administrator
Posted by Excel Helper on
Displaying page 1 of 1
Excel templates and solutions matched for Data in Prices Check-Box:
