Simulations and Modelling with the RAND() Function

Lemonade Stall

The RAND() function can be used in simulations where the value returned represents an approximation of an event in the real world. For example, we might simulate the sales made by a lemonade stall in a street somewhere. The things we need to model in this case include:

• time of day - not a random event
• flow of people along the pavement - this will affect the volume of sales
• temperature at a particular time - this will affect people's thirst and desire for lemonade

One possible model might look like this:

The details are as follows:

• The maximum temperature expected is entered in F3
• A random value is based on this to represent the actual temperature on a given day: =TRUNC(RAND()*(F3-25)+25)
• This temperature is used as the basis of the hourly temperatures recorded in column B - they get hotter towards the afternoon and then fall again
• Pedestrian flow is entered from estimates (or recorded observations)
• The number of pedestrians is calculated from a random value based on the estimated flow: =TRUNC(RAND()*(C6-20)+20)
• The proportion of people who buy a drink is calculated based on the temperature:
     =IF(B6>35,0.6,IF(B6>30,0.4,IF(B6>25,0.3,IF(B6>20,0.2,IF(B6>15,0.1,IF(B6>=10,0.1,1))))))
• Sales of drinks for each hour are calculated by multiplying the pedestrians by the probability of buying a drink: =TRUNC(D6*E6)

The most complicated thing here is the IF statement. This examines the temperature and determines the probability of drink sales according to the value recorded - the higher the temperature the more likely a sale.

The model shown here represents just one way to set up the simulation for drinks sales, there are many other possible designs. The precise way in which values are calculated depends on decisions made by the modeller.

Press the Back Button to return