Simulating Customer Demand on a Model Railroad

In this post, I’d like to discuss one method to really improve on the car card and waybill operating system for simulating freight traffic and moving cars around a model railroad. This post won’t discuss the particulars of how the system works as a whole, just a way to enhance it by controlling or introducing variability into how many new cars are brought into the layout, and which customers will get switched.

Someone recently asked a similar question on a Canadian Railway Modelers facebook group of “how to determine which industries on a switching layout get cars in a session”, so I thought I’d take the opportunity to expand on my version. A few different ideas that people have used, of which this is only one, were given in response to the facebook question such as rolling a die for each industry, drawing random playing cards, etc. The spreadsheet system does have an advantage of being easily scaleable to a larger layout as well as a smaller one, but it’s certainly not the only approach.

The system described here was initially developed by my friends at the Waterloo Region Model Railway Club to generate traffic on our large club layout. I’ve copied it to play with generating traffic for my own layout (which doesn’t exist yet, but I can plan and experiment with traffic for the future) and also tweaked a few minor aspects of the system to customize it for myself.

Use With CC&WB and Other Systems

My club and I have used this in conjuction with a car card & waybill system to select number of fresh waybills to assign, but really the spreadsheet just generates a number of cars and you can use this with any sort of system from CC&WB, to tab-on-car, to manually written switch lists, or almost any other form of non-computerized car forwarding system. The spreadsheet just takes the every day decision making out of how many waybills/tab markers/cars to select for a customer after the initial up-front setup of the sheet. And if you find things aren’t quite balanced you can always play with tweaking the numbers in the spreadsheet.

If you’re using with with car cards and waybills (CC&WB) note that one major thing that is assumed by this system is that you are constantly removing and replacing waybills in the car cards and one waybill/car card combination is NOT permanently associated with each other. I’ve seen a number of modelers and even software programs (like Ship-It!, which our club used to actually create and print our car cards/waybills, but required some serious work-arounds of the system in some places including separate databases for the car cards and waybills due to the 1:1 permanent waybill to car card assignment issue…) using systems that permanently assign a specific four-cycle waybill to a specific car. This I think also leads to one of the more common complaints of the CC&WB method being “too rigid” with the same car always travelling the same pattern. Which is only an issue if you have a rigid mindset of never changing the waybill assigned to the car. Or your cars are so unique

Also, since we’re varying the number of new waybills assigned to empty cars in staging (or interchange tracks, or yard storage), this method will end up requiring breaking down and re-making inbound trains in a “fiddle” staging yard (or else assigning generic “through” waybills to remaining unused cars that are left over in the trains in staging, if you don’t want to physically change around any cars in staging).

If you aren’t already familiar with the “car card and waybill” system, I could do a separate primer later, but there’s plenty of information out there if you search the phrase “model railroad car card and waybill”. The short version is that it was designed by a model railroad back in the 1970s to simulate the important details from a real shipping waybill, but making all the paperwork reusable by separating the car and shipment information into two pieces of paper that combine together, usually by inserting the “waybill” (shipment details) into a pouch built in to the car card.

How it Works

The basic method is to generate car orders between a given minimum and maximum number of cars on a percentage frequency, or odds of occurring. (i.e. “30% of the time”, “50% of the time”, “100% of the time”…) By using a spreadsheet with a random number generator, it’s simple to generate varying orders for a number of different customers or car types. By varying the minimum/maximum and frequency values, it’s possible to have different orders range from completely constant (i.e. min/max the same at 100% frequency) to completely variable, with each order having a different statistical odds of occurring or range of amounts of cars to order.

I also figured out a way to tweak the spreadsheet to allow some orders to be generated on specific days of the week (e.g. Mondays, Wednesdays, etc.) which can be helpful if you have certain trains that every other session, or to reduce traffic for a “Weekend” session with less trains if you have fewer operators available. I’ll discuss this tweak at the end.

Setting up the Order “Pools”

Since the point is to control customer demand, you’ll generally want to have an order line for each significant customer/car type. However pools can also be somewhat generic for through cars.

For example, on my future layout, I’ll have several different pulpwood orders for my major customers: Abitibi/St. Marys Paper (possibly split into separate orders for the spurs at Trembley, Limer, and Wyborn), Newaygo (possibly split into separate orders for Mosher and Trembley spurs), and Miscellaneous. For the Newaygo sawmill, in addition to the inbound logs, I’d also have an order for lumber flatcars, and one for woodchip gondolas. A few other lumber flatcar orders would exist for other mills in Sault Ste. Marie and Hearst. I’ll also have some generic pools for CN overhead traffic between Oba and Hearst.

At the Waterloo Region club, which is a very large layout with a lot of traffic we actually developed a numeric classification/pool code system to replace the AAR car types on our car cards and waybills. We then organized and set up the customer order pools organized by these car types. This could be an entire discussion unto itself how we developed the specific details of this system, and while it’s probably sounding a lot more complicated right now than it actually is, and one doesn’t necessarily need to go this far, for now let’s just say that doing it this way for a large layout makes it much easier to organize the waybills and car cards by sorting on the classification/pool number and to generate realistic looking traffic by separating out different car types, configurations and assignments (for example assigned-service paper boxcars). Specific service assignments can also be indicated by a note on the car card like “Paper Service Only” but when re-waybilling over a hundred cars for a session, reducing the decision making by making the car types distinct is helpful. On a smaller layout, NOT reducing the decision making “could” be more fun by actually making you feel like you’re acting more like the local station agent in assigning cars for loading –

How to Setup the Spreadsheet in Excel

The original spreadsheet was created in MicroSoft Excel, and the formulas used here are specific to that program, although they may be similar if not the same in other spreadsheet software. Confirming that or figuring out the equivalent syntax or formula for different software is left to the reader.

Note however, that in laying out specific formulas, for simplicity of description I just referenced the columns (for example “= A + B”) while when actually entering the formulas you actually need to reference the specific cells (“= A2 + B2”). You can do this for the first column and then drag and fill down the sheet in Excel.

Basic Customer Demand based on a Percentage Frequency

The first four columns of the sheet are manually filled in and define the parameters of the order:

A – Description
B – Minimum cars to order
C – Maximum cars to order
D – Frequency% (0-100) – how often to fill this order

Then, we use a few columns to generate our random numbers and calculate the results:

E – Generate a random number between 0-100 to compare against the frequency column:
= RANDBETWEEN(0, 100)

F – Compare the generated result against the frequency. Output a 1 if less than or equal (so the order will happen), or 0 if over (it won’t happen today/this session):
= IF (E <= D, 1, 0)

G – Randomly generate the number of cars to pull within the minimum to maximum range specified. Note that we also multiply the result by the 1 or 0 in column F to make sure orders that have a frequency of less than 100%. This is the final number of cars to assign to this order for the session.
= RANDBETWEEN(B, C) * F

Generating Orders Based on a Specific Day of the Week (DOW)

The original spreadsheet we used at the Waterloo club was exactly as above, using a simple percentage frequency to control whether an order is filled or not. (Setting a minimum value of 0 cars to order will also reduce how many times a car shows up, but the statistical probability is a bit harder to calculate if the frequency is also less than 100.)

In some cases however, you might have a railway that runs certain trains on alternating days, or a reduced schedule on weekends (which could be helpful to be able to run a “weekend” schedule for a session if you know you’re going to have a smaller operating crew, or a fuller session with extra traffic if you have an extended session with extra crews. Using a couple of additional slightly more advanced Excel formulas and features I was able to be able to also be able to specify Days of Week for each customer pool to be ordered. (I specifically set it up for Day of Week, but you can use the same technique for “Full”, “Normal” and “Reduced” sessions.)

Note – this will get a little bit more technical to evaluate the day of week.

In this version, the first six columns (A-F) are the same, but the final result will be calculated a little differently, so delete column G for now.

We’ll also need a few extra columns to represent the days of the week, so we’ll use up the next seven columns* (G through M) to represent the days Monday-Sunday. Put a “1” in the column for each day the shipment *should* occur. Leave blank for the days to be skipped.

*Stylistically, I actually put my DOW columns all the way to the right hand side of my own spreadsheet to make it more readable when using and editing the sheet, but we’re mainly talking concept here so I’m just going to keep everything in the order I discuss them right now. Just make sure if you put things in a different order to reference the column that contains the right data. Another suggestion to make the sheet better to look at is to change the text colour on the calculated comparison columns to white or light grey so those numbers aren’t visible if you print out the sheet. As mentioned before, you can also adapt this concept to make different orders for high/normal/low traffic sessions, using three columns here instead of seven. Adjust other column references accordingly.

Column “N” then evaluates against the selected Day of Week. This is where things get interesting. First of all, I stuck a cell at the very top of the spreadsheet where I can indicate the Day of Week for the session I’m generating orders for (1 for Monday, through 7 for Sunday). In my case, that’s specifically in cell B1. Excel has an “offset” function which allows you to fill the value of one cell with the value of another cell in a different column in the same row. The function looks like this: OFFSET(reference, rows, columns) where the reference is a starting cell reference, and the number of rows and columns to shift over from that starting position.

In our case what we want is to use that function to check whether there is a 1 in the column for today’s DOW, by using the value from cell B1. So we want to shift over than many columns on the same row. But we also want to make sure that when we fill the cell formulas down to following rows, that the cell we’re referencing here doesn’t shift down a row with the other cell references. So we want to use an absolute cell reference to make sure that this never changes. Excel does this by putting a $ in front of the part of the reference we don’t want to change. So to make sure that the offset *always* references cell B1, the cell reference should look like $B$1 instead of B1.

In this case, to be absolutely clear, and to show the difference between the relative and absolute cell references, I’ll show the formula as if it were for row 3 of the sheet. The final result in cell N3 should look like this:
= OFFSET(F3, 0, $B$1)

Then finally in Column “O”, we again generate our random number within the min/max range and multiply it by both the frequency result and the DOW result:
= RANDBETWEEN(B, C) * F * N

There you have it! Now the spreadsheet will control the minimum and maximum number of cars, frequency (% odds of occuring) and also specific days of the week to order cars for a customer. And remember, if you simply want a particular customer to *always* get cars, just set the frequency to 100 and make sure there’s a 1 in all of the Day of Week columns. This order sheet is now completely as rigid or flexible as you could possibly desire, on a customer-by-customer basis.

Full spreadsheet column definitions (based on starting position in row 3):

Column Description Formula
A Name/Description
B Minimum
C Maximum
D Frequency (%)
E Frequency Random* =RANDBETWEEN(0, 100)
F Frequency Check* =IF (E3 <= D3, 1, 0)
G DOW Monday
H DOW Tuesday
I DOW Wednesday
J DOW Thursday
K DOW Friday
L DOW Saturday
M DOW Sunday
N DOW Check* =OFFSET(F3, 0, $B$1)
O Final order amount =RANDBETWEEN(B3, C3) * F3 * N3

* – Calculated column that can have its visibility reduced for readability when using.

18 thoughts on “Simulating Customer Demand on a Model Railroad

  1. Great minds think alike!

    I’ve been working on a similar concept for a while although I haven’t done anything with day’s of the week. Interesting idea!

    Here’s what I’ve got available for free so far:

    https://goo.gl/mg7rtm

    I also have Raspberry Pi and Linux/Mac versions although the Windows version is easier to deal with for beginners.

    • The spreadsheet worked (and still does) fine on the club layout with just the % frequency, especially with full-blown sessions every other month, narrowing anything down to specific days isn’t an issue. Most of it is all 7 days a week anyways.

      But you get onto some of the more remote branches and regionals, and things can be a little different. CN only operated into Hearst 3 days a week (Ontario Northland still operated this frequency between Hearst and Cochrane today, in fact most of the ONR’s trains north of Englehart run on alternating days only.) The Algoma Central also did not run all the way to Hearst on weekends, only running a Hawk Junction to Oba turn job for interchange traffic.

    • When I first posted what I was doing with random numbers to the Model Railroad Hobbyist discussion groups, I was on the receiving end of some rather unprofessional comments from a guy who insisted that randomness had no place in car forwarding systems. He felt that everything should always run like clockwork and that trains should always have the some number of cars, etc…

      I’m glad that there are others out there who like to see a little variety in car movements. The interesting thing is that this fellow used to work for a prototype railroad. Curious…

      I know what you mean about branch line operations being only on certain days. I live on what is now a CP secondary main through southern Minnesota. (Originally C&NW, then DM&E, now CP.) CP used to own it all the way out to Rapid City but sold the South Dakota part not that long ago. The local yard job / way freight operates westbound Monday, Wednesday, and Friday, and eastbound Tuesday, Thursday, and Saturday. There are a couple through freights that run daily but the local traffic is every other day. Some days it’s hard to tell who’s railroad it is. For example, today the eastbound daytime job had two UP units and the westbound daytime job had two CSX units. It would be easy to model this line from the standpoint of not being locked into one road name. 😀

    • Now that you mention it, I actually think I vaguely recall that thread.

      Well it’s true that in reality it’s not exactly “random”, but faking it with a statistical probability is kind of our best tool for simulating it on the model railroad simply if we don’t want an industry serviced every day. We also can go weeks or months between formal multi-operator sessions on our layouts as well, so operators don’t necessarily notice the daily patterns, when a “week” on the railroad is spread out over an entire year…

    • Agreed.

      The system that I set up allows for a random number between two values set by the operator (min and max number of days between shipments) and then when the number does come up and it’s time for a shipment, another random number is chosen, again between two operator-set values (min and max car loads) so that, for an example, a grain elevator wouldn’t ship the exact number of cars each time, unless the operator wanted it to do that.

      Variety is the spice of life!

    • Yep, that’s quite similar to what I accomplish with the spreadsheet, although the spreadsheet doesn’t (and can’t) track a history of how many days have past. The way to fake that is with the %probability of occurring and/or the DOW, although when using statistical methods there is the chance of weird things happening like a rare shipment two days in a row. A 50% chance of occurring doesn’t mean it will actually happen every other day…

    • Well I’m just glad that there are a few other folks out there who like a little unpredictability in their operating sessions.

      The four-cycle car card system does get into a rut and that was what was driving me crazy at one club layout where I operated for a while. For example, the same reefers kept orbiting between a packing plant a customer down the line a bit. I know that railroads used to operate complete meat trains on pretty much dedicated routes in the past, but what was happening at the club was a bit too predictable. It got to the point where people weren’t keen on operating particular jobs because they were switching the same cars at the same spots session after session.

      Another thing that I worry about that sometimes causes people to lose interest in operations is that some guys make the process way too complicated. They try to always have trains be of the same size, or they try to calculate the best number of cars to be moved in an operating session. They involve siding lengths, number of spots for cars on spurs, etc… I like your spreadsheet idea because it really is simple enough that it doesn’t detract from the real fun – solving the puzzles created by car movements.

      I actually started out with an Open Office spreadsheet similar to yours a number of years ago, but like many software applications, I kept adding functionality until it ballooned into what it is today. People kept asking for more options so I finally had to port it to a web server / database server application that can be used totally on-line with mobile devices, or, like your spreadsheet, in conjunction with a cc/wb system.

      Happy New Year up there in the Great White North and keep up the good work!

      ps. I’m in Minnesota and right now we have a decent amount of snow and high temps that won’t make it above 0 F. I think that Winter has finally arrived.

    • LOL if you’re in Minnesota you’re farther north than I am. 😉

      But we’ve had some pretty good snow and cold for the last week. Decent snowfall the day before Christmas.

  2. I think I started this thread on facebook. Nice article. I like the approach. Exactly how often, and why a shipment is generated, is usually outside the scope of model railroading. Random numbers, when well controlled are an excellent option.

    This is a really good approach for getting cars onto the layout, but now, how about getting them off? I seem to be drifting towards that slippery slope of tracking every car, and where it is, to decide where it should go next. I’m not too sure that’s what I want to do with my life.
    What do you do about removing empties, or shipping off of your layout?

    • Hi Regan,

      So, boils down to a couple of different questions/aspects here, and bear in mind I’m using the spreadsheet alongside the car card & waybill system:

      1 – Removing waybills: When I complete the last cycle of a waybill, instead of turning it around to cycle 1 again, the waybill slip is removed and placed back in my waybill organizer. The car is now available to be re-billed.

      2 – Removing cars: Never remove something that’s at an industry, in your on-layout yard(s) (if you have one), or other “active” track because that would be considered as in the middle of “doing something” and part of the natural operations. What I like to do is cycle off cars that have “left” the layout, either in staging or delivered to an interchange track. Some people like to refer to an interchange on a model railroad as a “universal” industry (because it can take any kind of car), and while I get what they’re trying to do in order to really simplify things for some people’s operation, this is where I really break ranks, because that really undersells the point of an interchange. An interchange is NOT an just another industry, its the connection to the rest of the world. To my mind, staging and interchange tracks really make a big deal in terms of planning out your freight movements properly. Now, in your case, I believe in your facebook thread you said you had a small switching layout. I’m not sure if you have an off-layout “staging” track(s) that your train runs in and out of, or if you just start on the layout with a train of cars, do all the switching and end up with a train of what you picked up. If that were the case, I’d consider the train at the end of the session to be your “outbounds” off the layout – which would be going into staging if there was somewhere for your train to run to.

      3 – Selecting and assigning new waybills: This is the purpose of the spreadsheet. To determine how many of which waybills to pull out of the organizer and to assign to appropriate available cars to add to a train in staging,

      4 – Planning the car moves and creating the waybills: So obviously you really need the actual waybills before you can use them, but having a grounding in the concepts of the system will help actually create them properly. For a small layout, the planning is pretty brain dead easy – if you only have one staging/off layout destination, pretty much all your waybills will be something like “1. Empty or load from staging to industry, 2. Empty or load from industry to staging”. On a much larger layout with multiple “off-layout” staging or interchange destinations, there’s a lot more routing options, but it’s pretty similar. The only major piece of advice when planning here to keep things balanced, or if certain cars/car types have an assigned “home point” staging destination, is simply to make sure that the final cycle of waybill moves brings the car back to where it started. Use as many moves of the waybill as necessary (2, 3, 4) to accomplish. For example: “1. Staging A to industry. 2. Industry to Staging B. 3. Staging B to Staging A.” which the last move being an overhead bridge move to move the car back to where it belongs.

  3. It still amazes me how many grumpy people are in the Hobby. Let people enjoy their version. 🙂

    Quick Q:

    On a large club layout, how much work is being done in the various fiddle/staging yards, as you have to swap out cars, cars, and waybills between each session?

    At my previous club, the 4-cycle waybill made for very predictable operating sessions.

    Thanks!

    • Hi Brian, there is indeed a fair bit of physical work done to re-stage ahead of a session, since trains in staging are broken down and rebuilt. Probably only a quarter of waybills are actually “refreshed” though, as the waybill moves are all designed to return cars to their home yards, and only cars returned to their home point on their last move have the waybills removed.

      Rebuilding the trains (whether you play around with reassigning waybills or not) has significant advantages in blocking traffic properly though particular for set-outs for local and connecting traffic at the main yard.

  4. Good questions!

    In the web page-based system that I am using, one of the tasks that the humans have to do each operating session besides filling car orders is to look at the list of empty cars that comes from the system and decide where they are to go. It takes a couple of mouse clicks per car and goes pretty quick. The system then generates an empty car movement “waybill”, which can either be printed or just viewed on line.

    Another option for those using a typical cc/wb system is to have an “When Empty Return To:” section on the actual car card. Put down the name of the staging yard or off-stage location where you want the car sent. When a shipment reaches it’s destination and is unloaded, the waybill is pulled and returned to the deck of waybill cards. This exposes the empty move instructions on the car card.

    As far as moving cars that have come on to the layout from drawers, shelves, or other storage back off the layout, that is handled either by the empty movement instructions or by being loaded at some on-the-layout customer for shipment to an off-the-layout customer.

  5. This description looks interesting, and I’m going to try building a spreadsheet to try it out. One question though, do pick-ups and holds work the same, i.e., this sheet only codifies deliveries?
    Phil

    • Hi Phil, interesting question. The sheet as outlined above just drives orders (inbound cars to the layout/industries).

      I didn’t end up getting into turning waybills between sessions to move cars from “spotted” to “pickup” for the next operation, but to vary what gets picked up you can build it into the waybill with a little note to “hold 2 days for [un]loading” so that certain industries keep cars longer for loading. Mark the card with a paper clip at the end of each session until the number of paper clips equals the number of days. Then turn the waybill for pickup.

      Or for larger industries you could have a table that says how many cars/day that industry is capable of loading/unloading for when you turn the waybills to their next cycle between sessions. Say that pulpwood spur could physically hold up to 8 cars, but the loading crews can handle up to 5 cars/day for example. So if 6 cars are spotted, the first 5 will be loaded and ready for pickup, and one remains behind. If three or four cars are spotted, then they can all be lifted.

      However, you definitely also could experiment with using a spreadsheet almost identical to this one to randomize how many cars are ready to pick up at industries and use that to decide which waybills to turn for pickup between sessions. Use a row for each industry/track and use the same min/max (and maybe or maybe not frequency?) numbers to calculate how many cars to flip from “spotted” to “pickup”. If the actual number of cars at the spur is less than the number on the spreadsheet, turn them all. If there’s more cars spotted at the spur than that number, then the extra cars will be left behind. You could even use the DOW options above to also indicate for example industries that would be closed on weekends and wouldn’t generate an outbound car.

    • Thanks for the response, Chris. I think your original spreadsheet description, this response, plus the comments of others provides a great tutorial on car card/waybill use that has the added advantage of reflecting the various needs of each industry served, in a pretty straight-forward manner.
      Many thanks for sharing your insites and expertise.
      Phil

Leave a Reply

Your email address will not be published. Required fields are marked *


5 + nine =