MBAX 6007 Week 5 Spreadsheet Modeling Exercise Example

A Two-Stock Spreadsheet Model of Hearthside Home Care's Hiring Pipeline: Every Number Traceable, a Base Run That Overshoots and Two Tests That Try to Break It

Student Name

Walden University

MBAX 6007: Complexity and Systems Thinking Dynamics

Instructor Name

Month Day, Year

A Two-Stock Spreadsheet Model of Hearthside Home Care's Hiring Pipeline: Every Number Traceable, a Base Run That Overshoots and Two Tests That Try to Break It

Why a Small Model

This exercise builds a deliberately small model of Hearthside Home Care, the term's invented aide agency, whose roster alternates between shortage and surplus. The model has two stocks, three flows and one hiring rule, run monthly in a spreadsheet. Small models teach more than large ones when every number can be traced to an assumption, and Ghaffarzadegan et al. (2011) argue that small system dynamics models are often more useful to decision-makers than large ones for exactly that reason. The aim is not to forecast Hearthside's roster but to test whether the structure identified in earlier weeks can produce the pattern the agency experiences.

The Model

Two stocks hold the agency's caregivers. Caregivers in the pipeline combines screening and training into a single stock, measured in people. Active caregivers are aides taking visits, also in people. Three flows connect them. Hiring adds people to the pipeline each month. Completions move people from the pipeline to active status at a rate equal to the pipeline divided by its average length, two months, which treats the pipeline as a first-order delay. Quits remove active caregivers at a fixed fraction each month.

The hiring rule copies what Hearthside's managers describe: each month they hire enough to replace expected quits and to close the perceived gap between caregivers needed and active caregivers over two months. Perceived active caregivers are last month's figure, which represents the perception delay. The rule ignores the pipeline, as the delay analysis found.

Table 1

Parameters, All Illustrative

Composite organization. All values are round and illustrative, not Hearthside data.

The Base Run

The base run starts at 100 active caregivers against a need of 120. Hiring begins at 14 people a month and stays near that level for four months, because the gap the managers see barely moves while hires sit in the pipeline. Active caregivers dip to 96 in month 1, pass the need of 120 in month 5 and keep rising, peaking near 137 in month 8, an overshoot of about 14 percent. Hiring stops entirely from month 7 to month 11, but people already in the pipeline keep arriving. The roster then drains through quits, falling to about 111 in month 16, below the need, before rising again to about 126 in month 22. The run shows a damped oscillation with a period of roughly fourteen months: the roster rises above the need, falls below it and rises again, each swing smaller than the last.

The first chart, active caregivers against the constant need of 120 over 24 months, shows that pattern. The second chart shows hiring and completions together, and its most striking feature is the gap between them: hiring peaks in month 2, while completions peak in month 4, after the decision-makers have already begun to cut back.

Two Attempts to Break It

A model earns confidence by surviving tests that could expose it, not by producing a pleasing chart (Sterman, 2000). Two tests were run. The first is an extreme-condition test: if all hiring stops from month 0, active caregivers should fall steadily at 4 percent a month with no oscillation, since no pipeline can overshoot. They do, from 100 to about 61 by month 12. The second test compares the base run with the pattern Hearthside describes: a shortage followed by a hiring push, a surplus some months later and a return to shortage about a year after the first. The base run matches the shape and roughly the timing, though the real swings do not damp as quickly. That difference is informative. The model holds the quit fraction constant, while earlier weeks found that quits rise both in shortages, through fatigue, and in surpluses, through short hours. Adding those effects would likely sustain the oscillation.

What the Model Shows and Does Not

The model shows that a hiring rule which ignores the pipeline, combined with a two-month pipeline and a one-month perception delay, is enough to produce an overshoot and a swing, with constant demand and no outside shocks. It does not show that Hearthside's real swing has only this cause. Seasonal changes in referrals, the reinforcing overtime loop and the resignation delay are all left out. Forrester (1961) built his early models the same way, beginning with the smallest structure that could generate the behavior and adding detail only when a test showed it was needed.

Appendix: Spreadsheet Formulas in Words

Pipeline next month equals pipeline this month plus hiring minus completions. Active next month equals active this month plus completions minus quits. Completions equal pipeline divided by 2. Quits equal 0.04 times active. Hiring equals the larger of zero and the sum of the perceived gap divided by 2 and 0.04 times perceived active, where perceived active is last month's active caregivers.

References

Forrester, J. W. (1961). Industrial dynamics. MIT Press.

Ghaffarzadegan, N., Lyneis, J., & Richardson, G. P. (2011). How small system dynamics models can help the public policy process. System Dynamics Review, 27(1), 22-44. https://doi.org/10.1002/sdr.442

Sterman, J. D. (2000). Business dynamics: Systems thinking and modeling for a complex world. Irwin/McGraw-Hill.