This video will present you one way to forecast data based on historicals. In this example, we will plot the relationship between the Cost per Acquisition (CPA) versus the Return on Investment (ROI).

ROI is simply the output of sales over marketing spend for e-commerce intiatives for a company

As we can see, it is not a direct relationship. Hence, the use of a scatter plot is a useful exercise to understand the relationship between the CPA and the ROI.

The Scatter Plot

Select the two historical data sets, select “Insert” and the Scatter Plot icon under “Charts”.

Clearly a scatter plot is not an exact presentation of data, hence our forecast ROI formula will be an approximate computation of its relationship to CPA or the x-axis data set

We will now add a trendline to our scatter plot. Let’s format the trendline in red to make it more visible.

Formula with SLOPE & INTERCEPT

We can begin on composing our formula to derive y, or in this case the ROI based on projected CPA.

Where m is the slope of the data set. And b is the intercept between the x and y data, which is the historical CPA and ROI metrics respectively.

First, the SLOPE function is needed to return the slope of our known ROI and CPA numbers individually. As we can see, there are diminishing returns with sales or ROI as our CPA increases – hence the slope is negative in this example.

Whilst, the INTERCEPT function has the same syntax, where we need to reference the known y and x data points.

In summary, here is the formula for the SLOPE function thanks to the FORMULATEXT function. And the corresponding FORMULATEXT for the INTERCEPT function.

Next, we can input our projected Cost per Acquisition and Marketing Spend for the next 3 months.

Note these numbers are for illustrative purposes only, in order to demonstrate the application of our forecast ROI formula thanks to the SLOPE and INTERCEPT functions in Excel.

We can now use the formula to project ROI.

SLOPE & INTERCEPT functions in practice

Let’s calculate the ROI for October; which will be the slope times the CPA plus the intercept. Our pro forma ROI should be 1.4 times, which we can fill down for the other months.

Thanks to the forecast ROI by month, we can derive the monthly sales, which is the projected Marketing Spend times the ROI.

Finally, let’s apply this ROI formula on our historical data set; remembering this will always be an approximate estimate of ROI relative to our CPA or x-axis data set.

Hence is the source workbook presented in this article and video.

Download Spreadsheet Vault SpreadsheetVault – Excel SLOPE & INTERCEPT to forecast ROI (solution).xlsb