Gas Station Simulation

[SoConfuzed]

I have been given the following task to complete and honestly have no idea where to even begin. Can anyone on here help me please? Could someone possibly do a quick Excel document which I could build on? I really am lost and don't know where to go....

Scenario

The filling station at the local supermarket has three pumps available. The road into the station can be accessed from the supermarket car park and from the main bypass road. It has enough space for 5 vehicles. Vehicles wishing to enter, and finding the entrance blocked ie 5 waiting, will turn away and go elsewhere; they are effectively lost to the system.

In order to improve traffic flow it is suggested that one pump be designated for supermarket users only, although these customers can also use the other pumps.

As an analyst for the company you have been charged to evaluate the situation and maybe suggest improvements or alterations. You believe that the efficiency of the station, as well as the cost of fuel, are important factors in bringing custom to the supermarket. The supermarket has always sold the cheapest fuel within 5 miles, however, a national petrol supplier has recently begun a price watch scheme, promising to at least match the price of any local competitor.

The station opens with the supermarket at 8.00 am. You have found that at this time commuters mingle with shoppers and only one third of the vehicles attempting to use the station have used the supermarket first.

Data shows that the time between two arriving vehicles can be approximated by a Negative Exponential distribution, with an average rate of 60 per hour.

Historical data indicates that service time at the general pump follows the distribution

Time(mins) Probability
1 to under 3 0.6
3 to under 5 0.3
5 to 8 0.1

Because of a different vehicle and customer profile, the time for the customer only pump, you believe, can be approximated by a Negative Exponential distribution with a mean of 30 vehicles per hour.


Set up an EXCEL spreadsheet model to simulate the system for 1 hour. The model must output statistics of your chosen performance measures and be suitable for repeated experimentation (the results of which are not required).

NB For sampling from a negative exponential you use the cumulative negative exponential distribution

Thus your sample will be from

where v is the required random variate
 is the mean interarrival time
and u is a random number 0 - 1

Thank You Everyone

 

[Andrew Fergus]

Hi

I did the exact same assignment in COBOL over 20 years ago as part of a computer science course, although the statistics part is getting a little hazy....

The task is one of queue management. You can only hold 5 cars in the queue and 3 at the pumps. If you split the queue between the supermarket and the road then you are effectively managing 2 queues - this will be harder to programme. If I am reading this correctly then this assignment is about comparing the lost revenue of changing the queue management - is it better to split the supermarket queue or leave it as one big queue?

Pick a time slice (e.g. 1 minute or maybe even less, add that number of rows to your spreadsheet), and then go through the process of the probabilities of cars arriving at the end of the queue (apply a decision to either wait or go depending on how many cars are already in the queue, or if they go straight to a pump), select a time period for how long each car that gets to a pump will stay at that pump, work out if any cars have left any pumps, move the queue forward if any cars have left, and repeat this process for an hour, all the while counting how many cars have been served versus how many cars drove away without stopping.

Run a number of simulations using the random variable =RAND() to get a picture of how one queue would be expected to work, and if I am reading this correctly, then try another configuration of splitting off the supermarket queue, repeat the process and compare the results.

There are two constraints with this scenario, i.e. the number of pumps and the size of the forecourt - so you need to model not just the forecourt (i.e. the queue area) but also the 3 pumps.

As to actually designing the spreadsheet - that is something you will need to do. You won't find many volunteers to do your homework! Once you have something and get stuck, then feel free to post back with specific questions and I'm sure you will get plenty of help!

HTH, Andrew