Spreadsheet Models for Managers


Getting Access to Spreadsheet Models for Managers


ISpreadsheet Models for Managersf you use Excel to model businesses, business processes, or business transactions, this course will change your life. You'll learn how to create tools for yourself that will amaze even you. Unrestricted use of this material is available in two ways.

As a stand-alone Web site
It resides on your computer, and you can use it anywhere. No need for Internet access.
At this Web site
If you have access to the Internet whenever you want to view this material, you can purchase on-line access. Unlimited usage. I'm constantly making improvements and you'll get them as soon as they're available.

To Order On Line

Order "Spreadsheet Models for Managers, on-line edition, one month" by credit card, for USD 69.95 each, using our secure server, and receive download instructions by return email.
Or order via Google Checkout.
Order "Spreadsheet Models for Managers, on-line edition, three months" by credit card, for USD 199.00 each, using our secure server, and receive download instructions by return email.
Or order via Google Checkout.
Order "Spreadsheet Models for Managers, downloadable hyperbook edition" by credit card, for USD 199.00 each, using our secure server, and receive download instructions by return email.
Or order via Google Checkout.

To Order by Mail

Make your check payable to Chaco Canyon Consulting, for the amount indicated:
  • For the download: USD 199.00
  • For access online for three months: USD 199.00
  • For access online for one month: USD 69.95
And send it to:
Chaco Canyon Consulting
700 Huron Avenue, Suite 11J
Cambridge, MA 02138

To use the course software you'll need some other applications, which you very probably already have. By placing your order, you're confirming that you have the software you need, as described on this site.

Subscribe now!

Spreadsheet Models for Managers


This reading is especially relevant for Session 12Service Systems


Service systems are systems in which customers arrive and are serviced — the airline check-in counter, the order counter of a fast-food restaurant.

This page gives some explanations of the formulas for service system performance parameters. You don't need to read this to do the homework, but if you're curious about where the formulas come from, read on.

Assumptions

In modeling service systems, we'll make three simplifying assumptions.

The arrival rate is constant, and arrivals are described by a Poisson distribution with parameter l. This is just a fancy way of saying that customer arrivals are independent of each other.

The service rate is constant, and the distribution of service completion times is described by an exponential distribution with parameter m.

The system is in equilibrium — that is, the doors to the facility opened a long time ago. We have to assume this because just after the doors open, there is nobody in line, and it takes a while for things to settle, which makes it a lot more difficult to describe.

From these assumptions, we'll derive relationships for some important performance parameters of a service system. These formulas are useful in decision-making regarding capacity utilization for the system. The quantities that are most useful in describing the system are.

Single-server
systems

A most important relationship is the Fundamental Balance Equation, which is based on the equilibrium assumption. If we let Pn be the probability that there are n customers in the system, either waiting in line or being served, then

lPn-1 = mPn

What does this mean? Just that since we're in equilibrium, on average the number of customers in the system can't change. Thus if there are n-1 customers, the probability that we go to n in the next interval of time t is ltPn-1. Similarly, if there are n customers in the system, the probability that we finish one is mtPn. These two probabilities must be equal if we want to stay in equilibrium:

ltPn-1 = mtPn

and we see that the factor of t on both sides can be eliminated.

We can use the Fundamental Balance Equation to derive the average number of customers in the system. We'll do this by first calculating the probability Pn that there are n customers in the system. Let P0 be the probability that there are no customers in the system and P1 be the probability that there is one customer in the system. Then

P1 in terms of P0

and

P2 in terms of P0

Continuing in this way Pn in terms of P0. Now since

The normalization condition,

it follows that

Solving for P0

Here we assumed that l < m, in order to close the sum of powers of l/m. This sum is just a geometric progression. Unless this assumption is valid, the sum of powers is infinite. But this assumption is the only interesting case anyway, because if m < l, the waiting lines become infinite in length and never reach equilibrium. Thus P0 = 1 - l/m and

Pn = (1 - l/m)(l/m)n

From this we can immediately derive r, the utilization rate of the server. Since P0 is the probability of zero customers in the system, it's also the probability that the server is idle. Thus, the utilization rate r of the server is just 1 - P0, so

r = 1 - P0 = l/m

Another performance measure of interest is the probability that there are fewer than j customers in the system:

Probability of fewer than j customers

Thus

Probability of more than k customers.

Now that we know the probability of there being n customers in the system, it's relatively simple to figure out the average number of customers in the system.

Average number of customers in the system

We won't prove it, but this last form can be rewritten as

Average number of customers.

We can also derive a relationship between Ls and Ws. Let's think about the state of the system just after a customer has been serviced. On average there are then Ls customers in the system, and they have all arrived in the time it took that last customer to get through the system. On average, the time it took that last customer to get through the system was Ws, so the average number of new customers that arrived in that time was lWs. Thus

Ls = lWs

Since we have already have an expression for Ls in terms of l and m, we have

Length of the queue.

Let's now find expressions for the average length of the queue, and for the time spent waiting in the queue. For some businesses, these attributes of the system are critical determiners of customer satisfaction (or dissatisfaction!), and they also determine the size of the waiting facility.

Consider the state of the system just after a customer is called to be serviced. On average there are then Lq customers in the queue, and they have all arrived in the time it took that last customer to be called by the server for service. On average, the time it took that last customer to get through the queue was Wq, so the average number of new customers that arrived in that time was lWq. Thus

Lq = lWq.

Since m is the average number of customers serviced per unit time, the average time required to service a customer is 1/m. And since the average total time spent in the system is equal to the average time spent waiting in line plus the average time spent being serviced,

Ws = Wq + 1/m.

Since we know how to express Ws in terms of l and m, it follows that

Average total time

and

Length of the average line.

To find the average length of non-empty lines, we divide the average length of a queue by the probability that there is a queue at all:

Length of the average non-empty line

The expected waiting time for those who actually wait is the average time to service La customers:

Average time to service La customers

Multi-server systems

When a single service line feeds multiple servers, as in airline passenger ticketing facilities, the average service rate is just a multiple of the service rate for a single server. Suppose there are s servers in the system. Then if the average service rate for a single server is m, the average service rate for the multiple server system is sm.

Things are now complicated due to the numbers of different ways the servers can be occupied. Although the Fundamental Balance Equation for the multiple server system is just

lPn-1 = smPn

The other relations we "derived" so easily for the single-server system are no longer so simple. Here they are:.

Probability of no customers in the system Probability of no customers (svc-1)
Probability of n customers in the system Probability of n customers (svc-2)
Probability of n>s customers in the system Probability of more than s customers (svc-3)
Average number in the system Average number in the system (svc-4)
Average number waiting Average number waiting (svc-5)
Average waiting time Average waiting time (svc-6)
Average time spent in the system Average time spent in the system (svc-7)

For more about my spreadsheet consulting and training services, visit SpreadsheetAce.com.

Last Modified: Wednesday, 22-Oct-2008 05:31:20 EDT

Feeling queasy about spreadsheets?
We run our companies, in part, based on spreadsheets, yet we often wonder if they're really right. Learn more about improving the reliability and effectiveness of your financial models.
More
Sign Up for the On Line Version of This Course
With dozens of solved problems and special course tools, this course can dramatically increase your spreadsheet skills. For just USD 199.00 you receive three months of access with unlimited email support. Learn more about improving your spreadsheet sophistication and effectiveness.
More
Get the Hyperbook Edition of This Course
Identical to the edition you're viewing, but with dozens of solved problems and special course tools, and it stands alone — no Internet connection required. For just USD 199.00, it includes three months of unlimited email support. Learn more about improving your spreadsheet sophistication and effectiveness.
More