# ROI VARIANCE DATASET

The app also calculates ROI variance, as long as the dataset has been prepared with the expected "ROI" structure.

Take this example.

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 analyze the contribution of different initiatives to the change of total ROI you should modify your dataset as follows:

• Set the quantity/volume column equal to the investment value of each element.
• Set the cost/revenue column equal to the return of the investment in amount
• If the return of the investment is the sum of different revenue and cost flows, add a dimension column called "Metrics" with the names of the different flows. In the cost/revenue column, return flows should be positive, cost flows negative
• If the return of the investment is the sum of different revenue and cost flows, in the the quantity/volume column you should repeat the value of the investment of each initiative for all the rows that pertain to that initiative

You need to "melt" this dataset to make it "long" and 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.

At the end of the exercise, 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. 