In a scenario where there are a number of variables how would you go about determining the risk profile of, for eg, an annual budget
For eg
ALL costs are fixed: £2M
Trying to simulate Revenue based on following variables
Avg Order value:
2017 £5k
2018 £7k
Max Order value £200k
Min Order Value £100
Order intake profile ie most orders come in in Qtr 2:
Qtr1 20%
Qtr 2 35%
Qtr 3 20%
Qtr 4 25%
Length of Contract per order
5 years - 15% chance
3 years - 20% chance
1 year - 65% chance
Using revenue recognition ie Revenue is recognised over the length of the contract eg 5 year order for £60k would be recognised at £1k per month starting 1 month after the order was taken.
eg annual order value (order £ / length of contract) for orders taken in Jan would be recognised 91.6%, orders taken in Feb would be recognised 83.3% and so forth.
Thank you for your questions.
I will try to address all your points.
The fixed cost are there as part of your project
The average is not fixed and it can be a random (subject to change) variable. If you have historical information about the order – you can calculate the standard deviation and have it part of the simulation.
For 2017 £5k =NORM.INV(rand(),5000,the calculated standard deviation)
For 2018 £7k =NORM.INV(rand(),7000,the calculated standard deviation)
If you do not have historical data use my favorite 3-point estimation: See
https://en.wikipedia.org/wiki/Three-point_estimation
average = (a + 4m + b) / 6 standard deviation = (b − a) / 6
a= most optimistic value; b= most pessimistic value and m= most likely
For orders values I think that since you have a Max of £200k and a Min of £100k,
Use the uniform distribution =100000+RAND()*100000
I am not sure what you meant for the order intake profile. Is it deterministic? Qtr1 20%, Qtr 2 35%, Qtr 3 20% and Qtr 4 25%. If it is a probability distribution function – you can set up a VLOOKUP and get the values based of the cumulative distribution. Otherwise, you multiply it by the Order Value
Length of Contract per order is more complicated.
You use a budget with 1, 3 and 5 years. Your NPV is going to have a lookup that sums the budget 65% of the time 1 year, 20% of the time 3 years and 15% of the time for 5 years.
