Introduction

This page contains parts of a Monte Carlo type simulation that Dima and I worked on recently as he was writing up a mini project for his GCSE Business Studies coursework.

To remind yourself of what a Monte Carlo Simulation is, go to my example of a Hospital Out Patient's Clinic.

In brief, the project required the student to imagine inheriting £50 and from that work on ideas for using this money to open and operate a business for a month.

One of Dima's ideas was to make posters out of photographs of landscapes, buildings and people he would take and then attempt to sell.

To estimate the potential return on his investment, Dima thought he could use random numbers to help him estimate how many jobs he might get in the month and then to work from there on his sales, costs and profit.

The basic scenario is that we have modelled the one month period required by the assignment, where the month consists of 31 days. The model uses random numbers and probabilities to assess whether there will be a sale on a given day: yes or no. Secondly, once the model has decided whether there will be a sale or not on any given day, it then has to decide how many posters will be sold. We have limited the model to choosing either three posters sold or seven posters sold. We could have modelled one poster, two posters ... 'n' posters; but for such a basic project, such sophistication wasn't necessary.

We have also modelled three alternative scenarios: where there is a 60% chance of a sale on any given day, a 50% chance of a sale and a 40% chance of a sale.

Again, to keep the model simple, we have assigned the same probability under each of the three scenarios to the chances of selling either three or seven posters. There's a 30% chance of a sale of three posters and a 70% chance of a sale of seven posters: we could vary the probabilities but didn't.

The following table is a summary of what we found: taking three separate simulations, we found that Dima would sell 126 posters with simulation 1, 91 posters with simulation 2 and, coincidentally, 91 posters with simulation 3. Running the simulation again, could have given us, say, 105 posters, 84 posters and 130 posters for scenarios 1, 2 and 3 respectively.

The outcome of the simulations was that the potential returns on the £50 investement for each simulation ranged from £41 to £76 if the posters were to sell at £1 each and from £86.5 to £139 if the posters were to sell at £1.50 each.

Summary
Product	Value
Probability of no job	0.4	0.5	0.6
Probability of 3 jobs	0.3	0.3	0.3
	Potential for return on investment
No of Jobs	126	91	91
Poster 1	£ 76.0	£ 41.0	£ 41.0
Poster 2	£ 139.0	£ 86.5	£ 86.5

The Monte Carlo aspect of this example concerns the use of the random numbers and probabilities that we can see in the table above.

We prepared three separate simulations in which we decided for each of the 31 days in the month of Janaury, firstly, that the chances, probability, of any sale on any given day was 60%; secondly, we decided that the probability of a sale was 50%; and thirdly, we decided that the probability of a sale was only 40%. When we set up an Excel spreadsheet to deal with these probabilities, as we can see from the summary table above, we assigned probabilities to random numbers along these lines:

Using the function =RAND(), Excel presents us with random numbers from 0 to 0.9999999 in the format 0.1258765 ... to as many decimal places as we like. In the case of scenario 1 where we said that the probability of NO SALE is 40%, we assign the range of random numbers from 0 to 0.3999999; and this gives us a 40% chance of selecting NO SALE. If Excel gives us a random number other than in the range 0 to 0.39999999, that is 0.4 to 0.99999999 then the value is in the range of more than 40% and less than or equal to 100%, so we have a sale in that case.

The Excel formula for matching the random numbers to the probabilities is

=IF(A1<0.4,"N","Y") ... I have chosen cell A1 just for the purposes of demonstration; but the screenshots below show the real cell references we used.

As we will see shortly, this formula is an IF statement and it says that if the value in cell A1 is less than 0.4 then enter "N", meaning there is NO SALE on that day, otherwise enter yes, meaning that we have made a sale on that day.

Following on from this, if we now have a "Y" value, we use another formula to tell us how many posters we have sold on that day.

So, in another cell, enter the following formula

=IF(A1=0,0,IF(A1<0.3,3,7))

This new, NESTED, IF statement says, first of all, that if the value in cell A1 is zero, we already know that there is no sale on that day, so enter 0 and that is the end of it. If there is a sale, and the value is less than 0.3, enter 3, meaning that we have sold 3 posters, otherwise enter 7, meaning that we have sold 7 posters.

NOTE: we have used the SAME set of random numbers in for both IF statements: there is nothing wrong with this; but we could have generated a new set of random numbers for each statement if we'd wished.

The three graphics that follow are screen shots of the spreadsheet we developed that help to illustrate exactly what we did and how we did it.

The first screenshot shows the input and output data for the first scenario when we assumed a 60% chance of a sale on any given day

This second screenshot shows the formulae we used in our model, exactly as they appear in our spreadsheet. Please note: if you replicate this example, you will ALMOST CERTAINLY get completely different values from what you see here ... that's the nature of random numbers!

A third screenshot is a more complete picture and it shows formulae for calcualting the sales values and the potentials for the return on the investment too.

We used the same method for simulations two and three, using the cut off probabilities of 0.5 and 0.6 respectively.

Where did the probabilities of 0.4, 0.5 and 0.6 come from? We chose these arbitrarily ... we could have carried out some detailed market research to tell us what the probabilities might have been; but for this study that would have been too much. Alternatively, we could have used 0.3 or 0.55 ... for our probabilities for simulation one; and so on.

Having decided on whether there was a sale on a particular day, we then had to decide how many posters we would sell, having decided that we would make a sale. Again to keep the example simple, we decided to assign sales values of three or seven posters and assigned probabilities of 0.3 for each simulation. This means that if the random number that Excel gave us was greater than or equal to zero but less than 0.3, sales on that day would be three posters. Otherwise, sales of posters on that day would be seven posters.

Interpretation of Results

The beauty of the Monte Carlo Simulation method is that it provides us with as realistic a view of reality as we can think of and prepare a model for. More than that, though, it gives us some ideas of whether a project is financially viable. Take the example that we work through here and assess whether

freelance photography is a good idea as presented here
the idea is good by carrying out

one simulation run

two simulation runs

ten simulation runs and

then as many runs as you have the time for

What are your conclusions?

Do your conclusions change after having run one then two runs ... then ten ... then 50 ... then 100?

Conclusion

Most people who are preapred to spend some time on Monte Carlo Simulations find them interesting and because of that they tend to be fairly self explanatory. Using random numbers and the Monte Carlo Simulation is an effective way of setting up and testing a model to see if it is of any use. We hope that this demonstration is of value to anyone working along the lines of this demonstration.

Duncan & Dima Williamson
17 January 2002