I have some daily revenue data and I am trying to calculate the return on investment (ROI) by predicting what the expected revenue 'should be' and comparing it to what the company actually made. I used a moving average time series method on the data, but this assumes a linear trend, which is yielding poor results when calculating ROI because of the volatility in the actual data itself. I can post my results/data in an excel file if you want.
ROI is calculated using this formula: [(Actual Revenue - Forecasted Revenue From Time Series/Forecast Method) - Cost] / Cost Actual Revenue figures are given on a daily basis as is the cost.
I need to use an improved time series (or alternative) method to find an expected value for the several days worth of revenue. I am not sure what method to use as I am only familiar with the moving average time series method.
Can you please suggest a suitable method? Furthermore, if you have an example of how it works in excel, I would be grateful.
I have been told Stochastic Modeling is a good alternative as it is used to model stock prices but I cannot find a model in excel which I can use.
Can someone please help me out?
http://msdn.microsoft.com/en-us/library/ff524510%28v=vs.93%29.aspx
In the 4th table down (entitled Solver Foundation Ribbon) in the link above, if you take a look at 'Method' in the 'Simulation' group it reads 'Sets options to solve stochastic models. Choose between Automatic (the default), Monte Carlo, and Latin Hypercube.'
I used Monte Carlo simulation to simulate a hypothetical stock price in my dissertation, alhough I didn't use Excel. I used Maple. I don't know what sort of stock you're dealing with. Excel might not need to know that when employing the built in simulation methods it has for stochastic models. You could try the default or built in Monte Carlo. If that doesn't improve your results, you might want to write your own simulation. You may be able to do this in Excel itself. If you do want to try and write your own method, you might want to think about which sort of model would best fit the stock you're dealing with. The example I dealt with was modelling energy prices (gas and electricity). Models used for those (which may or may not also apply to the stock you're dealing with) are Mean Reverting Processes (such as the Vasicek Model or the Cox-Ingersoll-Ross model) and Jump Diffusion Processes (I think the latter are supposed to be more realistic).
I'd go for a jump diffusion model if you're tryng your own, but since I used mean reversion I'll just show you the method, which isn't too difficult:
the Vasicek model is $dS = a(b-S)dt + \sigma dz$
where
S is the spot price (or in your case revenue at a given time t)
dS is the expected change in price at a given time step
a is the 'mean reversion rate' (which you choose yourself)
b is the mean reversion level or long run equilibrium price (which again, you choose)
$\sigma$ is the volatility
dz is the random shock to price at a given time step
The idea is to solve this stochastic differential equation numerically (using Euler's method if you want to keep things reasonably simple or another method if you need to improve your results (i.e. Milstein method))
so the code I used (in Maple) was this:
(comments are actually done with a hash in Maple, but hash emboldens and enlarges in latex so I've changed hash to //)
// method stochEulerMRP takes the parameters you've chosen (start, a, b, $\sigma$, finalT, h) and uses Euler's method to produce and graph a prediction of how the stock price will change over the time you've specified
stochEulerMRP:=proc(start, a, b, $\sigma$, finalT, h)
(start = revenue or price at time $0$, a,b, $\sigma$ as above, h = time step, finalT = time you want simulation to run until)
global N, x;
local t, xLine, eps, i, pEuler, pLine, A B, Z;
N = floor(finalT/h);
t:=array([],0..N);
x:=array([],0..N);
eps:=array([],0..N);
xLine:=array([],0..N);
Z:=Random Variable(Normal(0,1)):
eps:=Sample(Z,N):
t[0]:=0;
x[0]:=start;
xLine:=0;
for i from 1 to N do
t[i[]:=t[i-1]+h;
x[i]:=x[i-1]+a*(b-x[i-1])*h+$\sigma$*eps[i]*sqrt{h};
xLine[i]:=xLine[i-1]+a*h;
od:
// plot results
pEuler:=[seq(t[i],x[i]], i=0..N)];
pLine:=[seq([t[i],xLine[i]],i=0..N)];
A:=listplot(pEuler,color=red, style=line, view=[0..1,-2..2]):
display(A);
end:
So that code produces one simulation (or guess) at how the stock price or revenue will change over the given time by using Euler's method to solve the stochastic differential equation you've specified using the Vasicek Model and choosing your own guesses of what the parameters a and b are. Monte Carlo simulation relies on the fact you can produce more than one of these simulations and then take the average of all of them at each point you want to predict and the more simulations you do, the closer this average will be to the true mean (so it's used for solving stochastic differential equations where you don't already know the true mean because you can't find it analytically, as you can for the Vasicek Model). I used the Vasicek model to see how well my simulation process was working, by looking at how close my averages were to the true mean.
You have to choose b and $\sigma$ so that $\frac{2b}{\sigma^2}\ge1$ if you want to prevent the revenue going negative (which you only want to do if this is impossible).
You can find the true mean and variance of the Vasicek Model on the Wikipedia page.
I must say, I did have some problems with my models: the accuracy wasn't improving as it said it should have done in a book I was referring to on the subject, called something like The Numerical Solution of Stochastic Differential Equations (Kloeden and Platen), (the problem was that accuracy is meant to improve by a certain order each time you halve the time step, but mine wasn't improving by the amount theirs was in the book, even though I was using exactly the same equation and almost everything else exactly the same so there might have been a glitch or 2 in my implementation (possibly in the random number generation), but hopefully this will help to get you started, even if it doesn't immediately produce good enough results.
An example of values of a, b, and $\sigma$, finalT, and step size(h) I used are 0.05, 70, 0.2, 1, and 0.01 respectively, but obviously these depend on what you're modelling.
One problem with this is time: I was doing $1,000-1,000,000$ runs. $1,000,000$ runs was taking an hour or more, but you may not need to do so many, and you're pc may be faster than the one I was using at the time. $100,000$ runs might not have taken too long, and $10,000$ was fine.