We have a set of investment initiatives. Each investment initiative lasts a few years, has an initial investment outflow (and maybe additional investments thereafter) and a set of revenue and of expense flows. One initiative is renting out luxury yachts 🌴 in Florida. One year we may be renting one 🛥️ yacht in Florida, with an initial investment of 1M. The following year we might add a second, more luxurious, yacht 🛥️🛥️ bringing the investment to 2.5M
We receive different types of revenues from each initiative (weekly lease, daily lease,..) and we have costs of different types (personnel, marketing, operating the yachts...). We have also other initiatives: we rent out less environmentally controversial sailing ⛵ and rowing 🚣 boats. Similar initiatives (aka renting yachts in Denmark) are carried out from different maritime locations around the world.
We calculate the return for each local initiative for a given year as a ratio. This ratio is calculated as revenues minus costs divided by the outstanding investment of the initiative in that year. Since we do not subtract the investment value from the numerator, this is not strictly a ROI calculation.
We want to know what impacted the change of the total return of all initiatives between year one and year two. Which initiatives impacted on the change of the total rate of return and why: if it was because of a change of the rate of return of a given initiative - more revenues or less costs at equal investment - or because of a change in the amount of the capital invested in a given initiative.
The source dataset has this structure. A column for the market, a column for the type of initiative, a period column, columns with the different types of costs and revenues, and a column with the outstanding investment. The variance of costs and revenues expresses "price variance" while the variance in investment expresses "volume variance".
To feed it into 'Mparanza we need to "melt" this dataset to make it "long". We unpivot cost and revenue columns into a new "Metric" column. The investment corresponding to the revenues and costs of a given initiative in a given year is repeated in the Units column. The figure below contains, melted, the same information of the first row of the table above.
Type "ROI" in the "choose a sample dataset" widget to get the file.
Set the "ROI calculation" parameter to "True": this way the system will divide the values in the Amount column (costs and revenues) by the values in the Units column (investment) and give us back the return. Hit submit.
The total change of the return on investment rate is about plus 19%. The average rate of return in the two periods was a healthy 129%. Remember however that the numerator of this ratio is not netted, as a standard ROI ratio would be, of the initial cost of the investment.
Overall, with fix dimension analysis, we get this. It is largely about price hikes.
The default result with variable dimensions looks like this. It shows the largest contributors to the change of rate of return.
Drill down on the second row. Armenia's positive impact on return is due mainly to better Add On Sales and better Subscription revenue, across all products. This is partly offset, naturally, by higher expenses.