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 initiatives 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.
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.
Let's format our Scatter Plot chart.
We can now add a trendline to our scatter plot.
Let's format the trendline
in red to make it more visible.
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
function 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.
Let's calculate the ROI for October; which will
be the slope times the CPA plus the intercept.
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.
Don't forget to subscribe to our channel,
like this post or make a comment.