Modelling with Excel Part 8: A Comparative Study ‑ Part 2

Stephen Hall continues his series on how to use Excel for project engineering. Download the interactive workbook to experiment with as the series develops

THIS article describes enhancements to the wash load modelling example that I presented last month (online only).¹ This is a simulation of a batch process that can have a number of parallel trains that run various production “recipes”. The washers can stand in for any batch operation such as batch chemical synthesis or cell culture.

Last month I described progressively complex calculations that estimate how many wash loads can be completed in a day when the recipes – and delays between loads – are randomised according to any specified distribution. This article considers how to resource-level the operations by limiting the number of simultaneous operations, and gives strategies for improving the performance, or speed, of the calculations.

Resolving Conflict

Calc 5 in Figure 3 of last month’s article includes a matrix that plots the recipe steps for each wash load against a timescale. The calculation sets a cadence for each washer that is independent of the other washers. Calcs 6 and 7 resolve unwanted conflicts, first in Calc 6 by locking out the water system so it can only furnish water to one (or any specified number) of washers at a time, and then in Calc 7 adding conflict avoidance for operating staff who are tasked with loading and unloading the washers.

Snips of the matrix are shown in Figure 1 with the water dispensing steps (2 and 6) highlighted. The top snip shows the original cadence from Calc 5. Calc 6 looks at the matrix column by column and when a conflict is found it moves the conflicting load (and subsequent loads in the same washer) to the right by inserting blank cells. This simulates a water supply lock-out that restricts delivery of water to one washer at a time. Calc 7 also locks out operating staff so they can only load or unload one washer at a time. For this run of the model, resolving the conflicts reduced the number of wash loads from 67 to 45. It is clear, therefore, that conflict resolution is an important element to incorporate into this simulation.

All the calculations are performed in VBA subroutines. You can study them in the accompanying workbook. With my approach, the execution time (not including making the Gantt charts) is approximately 1.5 seconds for Calc 5 and 12 seconds for Calc 7. Execution time will vary depending on computer hardware and may differ significantly from my results. But in any event, it is painful to simulate a month or more of operations to gain the full effect of the Monte Carlo analysis since this would take more than five minutes of computer time.

Shifting gears

Calc 8 performs a day-by-day computation with no conflict avoidance. All of the randomisation for each load and step are incorporated, but the only thing that I am reporting are the calculated number of loads for each recipe and totals for each simulated day. As each day’s washes are calculated, using the function from Calc 3, the results are collected in an array variable within the subroutine.

I implemented this as a user-defined function (UDF) subroutine. When a UDF returns an array as its result, the entire array is written to the worksheet. For this example, I averaged the number of loads and placed that value in the last row of the array. Using dynamic naming for each of the columns (eg “lCalc8Recipe1”), I constructed formulas for the daily minimum and maximum loads for each recipe and for the total.

The result is a day-by-day reporting of a model with no resource restrictions, and a summary that gives average, minimum, and maximum loads per day, by recipe, over the selected time period. 

When a UDF returns an array, the entire array is displayed on the worksheet and it “spills” into the adjacent cells. Those cells must be empty to avoid a conflict. In this case, my UDF is called with an argument that specifies the number of days to simulate: =“Calc8MultiDay(Calc8Days_to_Simulate)”. The UDF returns an array that includes a top row with headers, a row for each day in the simulation, and two additional rows that have the averages and the execution time. Figure 3 shows the results for a three-day simulation.

Bringing it together

The next progression is to perform resource levelling over an extended timeframe. Although I appreciate seeing the washing steps displayed in the timing matrix, the fact is that Excel needs time to write to those cells and more so to manipulate them in the manner that I’ve done by inserting new cells into the matrix. I toyed with foregoing the matrix entirely and just manipulating the results as given in the summary table (see Calc 4 in the workbook), but this proved too complicated for me to achieve.

I decided to approach the problem by building the same matrix entirely within the VBA code, thus avoiding worksheet manipulations. Two-dimensional arrays in VBA are equivalent to rectangular ranges on the worksheet. They each have rows and columns, and they are accessed in similar ways in the code.

My final calculation combines the wash-by-wash model with resource levelling, puts the result in an array, and reports the results like Calc 8. The enhancement is that instead of writing a matrix to the worksheet, it is saved in another array within the UDF and the timing manipulations that I described for Calc 7 are implemented within that array. The timing matrix array is cleared after each simulated day and started afresh for the next.

Just like Calc 8, I implemented Calc 9 as a UDF. See Figure 4 for the results of a five-day simulation. Notice that the execution time was just 0.8 seconds, and the summary and output provide the basic information about the expected performance of the wash room.

If you want to compare two approaches to solving a problem, test their speeds by tracking thousands of executions using the timer. For example, consider a case where a worksheet variable is used many times in a calculation

Programming tips

Much of the VBA programming for this exercise was straightforward. I think that with a little bit of study you can follow the logic in the companion spreadsheet. The VBA subroutines are grouped by calculation in the “mWasherSim” VBA module. I commented liberally throughout, but I am highlighting a few things here, tricks that you can use in your other projects.

Speed control

One frustration when modelling in Excel is slow execution speed. VBA can help, but also hurt since intrinsic formulas often execute faster than similar algorithms that are programmed in VBA. Certain functions recalculate whenever anything changes on the worksheet; these “volatile” functions cause a noticeable slowing if they appear in hundreds, or thousands, of cells. When you have multiple workbooks open, the execution speed may also slow due to them recalculating in the background.

Measure performance with your code by tracking with the timer function. At the beginning of a subroutine, store the timer value in a variable. Display or write the total execution time at the end of your calculation. You can see this to the right of the summary of results section on the washes worksheet.

If you want to compare two approaches to solving a problem, test their speeds by tracking thousands of executions using the timer. For example, consider a case where a worksheet variable is used many times in a calculation. You can read the variable from the worksheet every time it is needed, or you can place its value into a VBA variable and use that version. Is one method significantly faster than the other?

I wrote two functions. The first, “Test1()”, reads “w” (the number of washers) from the worksheet and multiplies it by a random number. The second, “Test2(w)”, receives the value from a subroutine that reads “w” from the worksheet before calling the function. The calling subroutine calls each of these functions 100,000 times and displays the execution time for each. On my computer, Test1 takes 1.02 seconds and Test 2 requires 0.01 seconds, a 2-log difference in execution time! Find the listings at the bottom of the mWasherSim module. This is one reason that Calcs 4 through 9 all start by reading the wash recipe step times into an array [Cycle(Step,Recipe)] rather than referring to the worksheet for each iteration of the wash load calculation.

Turn off the event handler to prevent it from executing every time your subroutine updates a cell on the worksheet. The line “Application.EnableEvents = False” does the trick; turn it back on at the end of your subroutine. Similarly, stop updating the screen display until the calculations are done with; “Application.ScreenUpdating = False”. If you don’t need the worksheet to recalculate during execution of a subroutine, change the calculations to manual with “Application.Calculation = xlCalculationManual”. You are trapping errors, correct? Return the worksheet’s functionality to normal in your error handler.

Another tip: bypass code when you don’t need it. There is a checkbox, called “Update Gantt”, on the Washers worksheet. When checked, the Gantt charts that correspond to Calcs 5, 6, and 7 are redrawn when those calculations run, taking nearly two seconds to complete. If unchecked, the Gantt charts are not updated.

Update an input from multiple locations

For convenience, optional parameters for the calculations are listed in the calculations summary section of the workbook and also shown in the calculation details sections. They are shown twice. For example, the number of days to include in Calc 8 is changed at the cell in the summary (Figure 3), but I also want to change it where the calculation is located at row 484 (although this row changes depending on the number of washers being modelled).

Here is how to change the input from either location. The input that is grabbed by the Calc 8 function is at Row 484 and named “Calc8Days_to_Simulate”. At row 54 in the summary section the cell is named “Calc8Days_to_Simulate1” and it has a formula, “=Calc8Days_to_Simulate”. The trick is to use the event handler to detect when a user types in a value, overwriting the formula at row 54. The handler writes the value at row 484 and then restores the formula at row 54.

Find the complete listing, “Private Sub Worksheet_Change()” on the Washers VBA module.

Not done yet

This is not yet a fully rounded simulation, but I will leave it to others to add in more features if they so desire. The primary limitation that I see is that I have treated each day independently; the model gives a fresh start to each simulated day.

Three possible enhancements jump to mind. First, calendars could be included to change the operating hours based on the day of the week, holidays, and shutdowns. Second, rather than assuming that the wash room is operating for a full 24 hours with loads counted only if they complete within the timeframe, the model could incorporate additional rules that, for example, carry the operation into the next day without interruption. Or, if the calendar defines a two-shift operation of 16 hours, allows one or two loads to finish late.

The third enhancement would be to implement the potential for a longer delay that simulates a physical breakdown of a washer. With my design, all the washers are fully operational at the beginning of each day. I provided for a long delay between loads, but that delay expires at the end of the day rather than being carried over. This could be implemented in Calc 9 without too much difficulty.

Other limitations in the models, such as the number of wash steps and recipes, could also be removed.

My last comment concerns resource utilisation charting. I originally intended to include water consumption graphs in these models. The charts would show the flow rate and total daily water consumption. The data is readily available in the timing matrix, but it isn’t really useful to graph it. That’s because the daily consumption is readily calculated from the number of loads, and the peak flow rates are found from knowing the resource limitation, or in the case of no limitation the summary table that counts simultaneous water draws (Figure 4 in part 1).

This is the eighth in a series that provides practical guidance on using Excel for project engineering. Download the companion workbook from https://bit.ly/ModellingWithExcel. To read the rest of the articles in this series, visit the series hub at: https://www.thechemicalengineer.com/tags/modelling-with-excel

---

Disclaimer: This article is provided for guidance alone. Expert engineering advice should be sought before application.