Join Curt Frye for an in-depth discussion in this video Running the simulation with added station capacity, part of Process Modeling in Excel Using VBA.
- [Voiceover] In the previous movie, we added code to our simulation to allow individual stations to handle multiple customers at a time. We indicated the number of customers that a particular station could handle by adding the capacity property and also indicating the capacities here. And we use VBA code to read those values into the properties. And the idea is to see how increase in capacity at the two stations with the largest average processing time would affect idle time within the simulation.
I've set the mean to 15, so in other words, on average, we would expect a customer to arrive every 15 tics of the clock. Now we can see how those values will change based on our simulation. With that background in place I'll press Alt + F11 to move to the Visual Basic editor. I'm in Module 2, which has a subroutine called Additional Capacity, and what I want to do is run it to see what the idle time looks like within the system.
So this is the code we created last time, so I'll just go ahead and press F5. The simulation runs and I'll press Alt + F11 to go back to the workbook. I am now on the results worksheet, and if I scroll down I can see that I have very low idle times. I have a one and nine, a ten, here I have a 20 and a 24, and 14s and so on, but a lot of zeros and other small values.
So it's very encouraging. It tells me that both the idle times are going down and of course, the percentage of time that a customer spends within the process is going down as well. But now let's see how sensitive these results are to changing the mean time. So I'll delete my current results. So I"ll got to the name box and type A2 then a colon and then F is idle time, and I'll just delete down to 300 even though I know I didn't have 300 customers.
And when I pressed enter those cells were selected. Delete and Ctrl + Home to release the overall selection and go back to cell A1. So I'll go back to the sims set up, worksheet and I will click inside cell J2, and edit that value to 10. So I now have a mean arrive time of 10, and if I scroll down my list of lookup values I can see that I get a one at 35.
So the largest arrive time that I could possibly get between customer is 35 tics of the clock. But that is extremely unlikely to happen and as you can see from the probabilities here. So I'll go ahead and scroll back up. There's our mean and now I can press Alt + F11 to go to the Visual Basic editor. Cursor still in the subroutine, so I'll press F5. And because we're adding more customers the simulation take a little bit more longer to run. Not too bad though.
And then I'll press Alt + F11 and here we are on the result sheet. And you can see already that the idle times are significantly higher, so we have our customers entering the system at smaller intervals than we had before, and as I scroll down, I can see that the idle times, instead of being a bunch of zeros, we now have quite a few in the 20s and 30s. So scrolling back up. So you can see that as you expected changing the arrival mean time will certainly increase the strain on the system leaving two people standing around and more idle time overall.
- Creating a class module in VBA
- Defining class properties
- Creating collections
- Describing process flow and programming goals
- Creating loops
- Increasing capacity of a model
- Running simulations
- Analyzing simulation results