Home / Excel / I was surprised about this

I was surprised about this

Simulation pictureI was doing something new the other day and was surprised at the outcome. I ended up with a situation where no matter my starting position, I usually ended up with either you flame out before you reached 50 rounds (most likely 10 rounds) or you succeed and run on to 650 rounds – my limit on the spreadsheet. See my histogram below.

And it was roughly 50% of the time you flame out before 10 rounds and 50% you run on to 650 rounds.

All I did was a simple business income statement with revenue, cost, and income, where the revenue and cost had a range of possible values and the initial cash funding was set at $100K.  I set it up to run so many rounds which was supposed to be an abstract representation of time (weeks, months, years). In each round, the revenue and cost figure were randomly picked out of the given range. At the end of each round, if you had enough money in the cash bank, you get to proceed to the next round. The cash bank will start the first round with the initial funding input and will either increase or drawn down based upon the round’s profits.

In addition to rounds, I had trials. The trials are where my crude simulations come into play. Each trial began with a different starting position of revenue and costs but had the same initial funding. So trial run 1 might start with revenue of $300K and cost of $100K in round one which would give a nice profit and thus proceed onto round 2 with its own randomly chosen revenue and cost. Trial 2 might start with revenue of $100K and cost of $250K in round 1, giving you a net loss and thus end the “game”. And Trial 3 would have yet another randomly chosen revenue and cost to start the rounds.

I designed this spreadsheet to run at 500 trials.

This is kind of a Monte Carlo simulation but without the probabilities added.

I can see deploying these kind of “simulations” where you had situations of uncertainty and you wanted to know the probability of success. This kind of spreadsheet does not take into account operational talent so that could change the game. But still, it’s interesting.

Simulation screenshot

 

[divider]How the spreadsheet was set up[/divider]

  • A tab was set up to handle the range of inputs for revenue and cost and the trials.
  • The trials were created with a macro to generate 500 trials by copying the formulas 500 times.
  • To randomize the revenue and cost, I used the RANDBETWEEN function.
  • I collected some descriptive statistics such as count, maximum, minimum and average, for each individual trial.
  • Finally, I created another tab to hold the graphs and overall statistics on all of the trials.

Leave a Reply

Your email address will not be published. Required fields are marked *

Top