I have a revenue formula for a business. Let’s assume it’s for a lemonade stand. To simplify, assume the revenue formula at any time $t$ is:

$$\text{Revenue(t)} = \sum_{i = 1}^t \text{Price}(i) \cdot \text{#Cups}(i),$$

where $\text{Price}(i)$ is the price at time $i$ and $\text{#Cups}(i)$ is the number of cups sold at that time and price.

Also assume this is a brand new business, so I have no history whatsoever.

I have used a monte carlo method from the book “How to Measure Anything” that uses 90% estimation intervals (EI), the fact that there are 3.29 standard deviations in 90% of the normal distribution, the formula: norminv(rand(), mean of 90% EI interval range, (upper 90% EI bound – lower 90% EI bound)/3.29)), 10000 monte carlo scenarios, and a histogram to display a normal distribution for any single point in time for the revenue formula.

That’s good but only part of the problem I want to solve. What I really want to graph is the mean of the function represented by the revenue formula through time (instead of at a single point in time via a histogram) where the y-axis is dollars of revenue and the x-axis is time. I then want to show the curve that represents 3 standard deviations above every point on the mean function and a second curve that represents 3 standard deviations below every point on the mean function.

Any suggestions for a solution or where to start looking for one would be greatly appreciated! I have a computer science background so don’t mind if the solution needs VBA or some other programming lang. That said, I haven’t checked in a production line of code in about 15 years so take it easy on me :).

  • $\begingroup$ You need to tell us more about $\text{Price}(i)$ and $\text{#Cups}(i)$. Are these random variables? What's your assumption of their distribution? $\endgroup$ – Lucas Dec 8 '17 at 12:14
  • $\begingroup$ Yes. Let's assume both variables are random and normally distributed $\endgroup$ – Peter Dec 8 '17 at 12:19
  • $\begingroup$ Do you assume the distributions do not change when $i$ changes ? Do you assume you know mean and variance? Do you assume they are all independent? $\endgroup$ – Benoit Sanchez Dec 8 '17 at 14:09
  • $\begingroup$ @Benoit, let's assume the answer is "yes" to your 3 questions. Thanks! Peter $\endgroup$ – Peter Dec 9 '17 at 2:50

If you assume $\left(\text{Price}(i).\text{#Cups(i)}\right)_i$ are independent identically distributed variables with mean $\mu$ and standard deviation $\sigma$, then you can easily known the mean $\mu(t)$ and standard deviation $\sigma(t)$ of $\text{Revenue}(t)$:



This is just because the mean of the sum is the sum of the means, and with the independence assumption the variance of the sum is the sum of variances.This tells you that $\text{Revenue}(t)=t\mu\pm z\sqrt{t}\sigma$. This formula is not exactly a confidence interval but it seems to be what you want with $z=1.5$ or $z=3$.

  • $\begingroup$ What if Price and # Cups are not independent (as they likely are not)? $\endgroup$ – Peter Dec 10 '17 at 10:32
  • $\begingroup$ In my answer, you don't need Price and #Cups to be independent of each other. You need $Price_1\times Cup_1$, $Price_2\times Cup_2$, $Price_3\times Cup_3$... to be independent. If they are not, you need to assume a certain dynamical model, which is much more complicated. $\endgroup$ – Benoit Sanchez Dec 10 '17 at 14:35

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.