Mfrhtyj

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.

I'd probably use Variable and Fixed cost analysis as an input to a forecast of future sales and costs. I have a full blog about it linked below with an excel template. I'll talk about your specific example as much as I can.

Variable and Fixed Costs - Small Business Decisions

Variable costs may change drastically from day to day, but the real point of what you're trying to do is understand the general trend so that you can forecast your expected ROI. You should use a longer period of time to understand what portion of your sales revenue is variable with the cost than a single day. In the end, a month over month value for expected ROI should be sufficient. Daily analysis would be overkill in any of the business programs I've been involved in, most of which were multi-million dollar programs.

You should use the template provided to determine your variable costs. The template is set up for 12 months if you have the data. Then you should estimate what kind of sales volume you can expect to continue from your latest sales data. The Sales team at your company should be able to help you with that. With an understanding of the variable costs, you can project your expected sales and the expected costs associated with those sales.

If your company has several other products, I'd also suggest you use the sales revenue specific to your product as the top line instead of the overall company sales. This will help identify which portion of the overall company sales are correlated with your specific product. The two numbers should be relatively similar in percentage, but drastically different in scale. Ex: 12% variable cost for $1 Million in sales at the company level is $120,000. 14% variable cost of $10,000 in sales of your specific product is $1,400. Similar variable cost percentage rates, but vastly different scales. Now, this comparison is important because in the case that another product had sales increases at the same time as your product, it'll be very difficult to ensure that the costs your allocating to your product are actually a result of your product's changes in sales.

If you work in a company that is so large your product won't make up the difference in a rounding error on their P&L (not an insult, I've been there), then you should look for standard allocations for SAR and add them to your direct costs.