A Business Cash Flow Model (definitions)
The "Simple" Version
Many years ago, while starting up a new business, I participated in a "Small Business Startup" class. A part of
that class called for business plan generation, which, of course, called for cash flow estimates based on product and resource
costs, and sales forecasts. It's common to use a process-driven* spreadsheet to do this type of work, but the spreadsheet
environment quickly became complicated and slow. Without an (e.g. VBA) add-on, spreadsheets don't handle event lists and
waiting lines well; spreadsheets don't have convenient facilities to implement while and for loops; spreadsheet conditional
computations are difficult to implement; balking, reneging and jockeying are difficult to model.
Since the output data must be stored in the spreadsheet, usually in a column, the length of the output series is limited by the
maximum column length. It happened that I'd been working on a generic discrete event simulation package for several years
(Smalltalk-based), and this offered a meaningful opportunity to get deeper into the simulation world (see Simple Simulator
Application in Application Development).
The figure above right shows some of the output from that early model. Years later, the model graduated to the one at left --
built with a commercially available simulation package.
Questions that this model can answer include: "What markup must I use to sustain my current production environment (i.e.
current labor, plant and supply resource costs, and current production volume)?"; "
Given my current markup and resource costs, can I sustain the production level I need to meet expected orders?"; "
Given the current production level and resource availability, am I going to overwork my labor resources?".
Some of the output from this model is shown at right for multiple one year simulation replications. Supply, labor and
plant resource utilizations show a convergence to respective long-term expected values. "Jobs waiting for a resource"
and the "number of completed jobs" do not converge to an expected value for the timeframe and markup value selected
(a markup value of 3) -- indicating there is an availability issue with one or more of the resources.
There are several interesting aspects of this model. First, the model shown here isn't strictly discrete event, but
a combination of discrete event and continuous. It happens that there can be model solution speed and accuracy problems with
discrete event simulations when they try to model continuous or quasi-continuous behavior. In the cash flow part
of this model, for instance, if cash is represented by discrete money packets that move from point to point (e.g. from gross to
net revenue) in, say, packets of $1000, then the division of cash into non-$1000 packets for paying labor resources (for
example) disrupts the flow of the model and is simulation time consuming.
If the packets are smaller, say $10 or even $1, then the solution time rises exponentially. How does discrete packet size effect solution speed and accuracy? Do continuous and discrete solutions converge to the same value and give the same confidence intervals? The figure to the right shows
convergence to a labor utilization value (~0.75±0.05) for several solution types and five replications at each type.
The abscissa shows that I tried three discrete event $ packet sizes as well as a combined discrete event / continuous solution. As you can see, the combined discrete event / continuous (Flow) model arrives
at the "same" labor utilization and confidence interval as does the $10 packet discrete event model -- at 1/500th of
the solution time/cost! It should be noted in this figure that the $1000 packet size confidence interval does not include
the discrete event / continuous (Flow) mean value nor does the Flow CI contain the DE mean value, though the confidence intervals
do overlap. What does this mean? Statistically this means that the two models are probably solving different problems -- i.e., based on the relative mean value convergence shown from $1000 packets to $100 packets
to $10 packets, the $1000 packet size model results probably aren't accurate. For a 99% confidence interval, these models can be shown to be the "same" (the mean values are covered by
the opposing CIs).
Advanced Versions
This paragraph touches on two updated versions of the simple cash flow model.
The first version is described in a downloadable, but rather long, Microsoft Word presentation of a Microsoft Power
Point slide show (26MB). That document contains a general, high level description of how I attacked the modeling, and more
detailed descriptions of the model's input/output, verification and validation. The second model version, described in the
remainder of this paragraph, added several new reporting features. One of the new abilities for both versions was
to compare constrained and unconstrained business resource models.
An unconstrained business resource model is one in which the resources (Labor, Product Supplies and Plant in this case) are
free to seek their optimal levels, given an expected costs of sales and business income allocation. A constrained
business resource model is one in which resource floors and/or ceilings are established (for instance, the number of laborers might
be restricted); the constrained analytical model is then allowed to seek it's own optimal cash flow level, if possible. In
the figure at right (from the second updated model version), I've described a possible small business environment with manufacturing,
sales, shipping and cash allocation information. Labor, Product Supplies and Plant unit availabilities are not constrained. (Resource "units" are meaningful collections of
information used to capture the essence of each resource and it's cost(s), kind of a resource building block annotated with costs.
A "Labor Unit" is, in this model, one person that can work at either a manufacturing, sales or shipping station -- along with
related salary and overhead costs [totaling $2,926/month/unit here].
A "Product Supply Unit" encapsulates all non-reusable components necessary to construct one product -- along with primary and
related secondary costs [totaling $10/product here]. A "Plant Unit" encompasses product manufacturing, sales and shipping space and any
associated space maintenance cost -- such as cleaning, heating and lighting [totaling $1100/month/unit here].) Four random input replications
generated the output data and graphs shown in the figure at left, where it's estimated that an average of 1.92, 1.93 and 2.02 Supply, Labor and Plant
resource units, respectively, will allow the business to function for one year without running out of cash. (For purposes of this discussion, we've
assumed an ideal business environment where, for example, there are no process breakdowns and all products are sold relatively soon after they're
manufactured). Average resource unit utilizations are estimated at 35%, 55% and 54% respectively, indicating that a significant number of Product
Supplies are idle, that Labor might be slightly under used and that a more efficient use of Plant space could be looked into.
(At this point in the design/analysis process, the analyst will want to re-verify and re-validate the model -- including touching base
with domain experts to validate, for example, typical resource utilizations.) This model is estimating that 640 products can be
shipped in one year (the simulation runs for 1.25 years, including the three month warm-up), at an average sales price of $246 (~$163
coming from manufacturing costs, $73 from sales costs and $10 from shipping costs). Constraining the maximum number of available Labor, Product
Supply and Plant resource units to 1 each results in the data shown at right, where the number of shipped products has reduced to 624 (down
2.5%), the expected product sales price has not changed significantly ($241, down 2%), the Product Supplies utilization has increased
to 62%, and the Plant utilization has increased to to 99%. The Labor resource utilization is also forecast to be 99%, an
unreasonable number for Labor. (In this model, one Labor unit is automatically associated with one Plant unit of environment unless
explicitly constrained otherwise.)
It should also be noted that the production and sales queues appear to be somewhat unstable, steadily increasing through time. Increasing the Labor
limit to 2 units and keeping Product Supplies and Plant limited to 1 unit reduces the Labor utilization to a more reasonable
57%, while keeping the estimated number of shipped products about the same (618, see left, now constrained by available plant units) -- of
course, the feasibility of moving two Labor units into the same Plant space would need to be investigated. The estimated average sales
price remains about the same ($245) and the production and sales queues now appear to be more-or-less under control. The confidence
intervals for this last run are comparable to those of the original run, indicating that we're comparing apples to apples. As an aside,
increasing/decreasing allowable Plant units is equivalent, in the
Real World, to
increasing/decreasing the business floor space (or perhaps moving to a new, more/less expensive location or finding a less/more efficient
manufacturing process or ...). An increase/decrease in the maximum number of Product Supply units could reflect a Real World
increase/decrease in customer demand.
Minimum Cost, Maximum Profit and Minimum Sales Price
For a given business environment (as defined in the yellow/beige area, for example, in the above
"Advanced Business
Simulation Model" figure or in the figures that follow), the processes of minimizing cost and maximizing profit are
carried out by reducing specific cost-of-sales factors.
The above cash flow model has focused on minimizing sales price by first allowing supply, labor and plant resource utilizations to
self-adjust (to a level dictated by the other business environment parameters) at the expense of cost-of-sales and profit -- and then by
enforcing maximum resource availability restrictions that force-adjust resource utilizations to reasonable values. This shifts the
focus from an atomistic one (concerned with manipulating those many individual business components) to a holistic one (concerned with
manipulating the business as a whole). This isn't to say that minimizing cost and maximizing profit by addressing specific cost-of-sales
factors isn't important, I'm just pointing out that perhaps thinking about a minimum sales price might give additionally valuable information
about the business.
As such, this simulation comes closer to approximating a minimum cost environment than a maximum profit one. Directly manipulating the
above cash flow model to satisfy a minimum cost objective function (e.g. minimizing CostOfSales = GrossRevenue - NetProfit -
Allowances - Taxes - Operations by varying the "decision variables" Markup, Available Supplies, Available Labor and
Available Plant) gives rise to the results pictured in the figure at right. Directly manipulating the cash flow model to satisfy a
maximum profit objective function (e.g. maximizing Profit = GrossRevenue - CostOfSales - Allowances - Taxes - Operations
by varying the same decision variables) gives rise to the results pictured in the figure at left where the product markup has been allowed to
approach 5. The similarity between the results of these two models is that both converged to single Supply, Labor and Plant unit
availabilities. The major differences between these two results and the results reported in the figure
"2nd
Constrained Simulation Model Output", above, are that the constrained model settled on using two labor resources, the average
constrained sales price is slightly lower than that from the minimum cost model and the profit in the minimum cost model is roughly twice
that from the constrained model. The forecasted number of products delivered is the same, for all practical purposes, in the
constrained model as in the two optimized models (~1% difference). The overriding major difference between these models is in resource
utilization -- the 97% labor utilization predicted in the optimized models (for one labor resource) is clearly unrealistic.
Supply Chain Management
Finally, the above single-business model became a key part of the chain of entities involved in manufacturing, distributing, wholesaling, retailing and
consuming products. This type of distribution chain simulation has generally become known as the "Beer Game" or the
"Beer Distribution Game" simulation, named after an MIT supply chain management game developed in the early 1960's
(Wikipedia reference). An initial version of my distribution network is
pictured at right, where only minor modifications to the single-business model were necessary to incorporate order timing, volume feedback and income
from downstream entities. Each business cloud (block) at right encapsulates the business structure presented in previous paragraphs. The figure
shows three types of blocks: (1) Producer, where production is driven by order requests and constrained by resource availability, and resources are mostly
manufacturing-related, (2) Distributor/Wholesaler/Retailer, where sales are order driven and resource constrained, and resources are mostly sales-related,
and (3) Customer, where consumption is need driven and cost constrained. Individual Producer and Distributor/Wholesale/Retailer blocks (businesses)
converged, or didn't converge, to stable individual business environments depending not only on internal "controllable" parameters but also on
uncontrollable parameters coming from outside interests. Then, the network as-a-whole converged, or didn't converge, to a stable distribution network.
The Bottom Line
The bottom line here is that verified and validated simulations can provide business owners with a valuable "what if" tool for reasonably
drawing a line between cause and effect. But, as I point out in the Microsoft Word
document referenced above, "The simulation must accurately carry out the intent of the model...", "Rigorous model verification and
analysis validation leads to believable process understandings.", and "Ultimately, verified and validated simulations provide only estimates
of RWS [Real World Systems] performance.".
* Process-driven: models the logical sequence of events for each customer, using one row of the
spreadsheet for each customer. Activity-driven: models the activities that occur during fixed intervals
of time, using one row of the spreadsheet for each time interval. Event-driven: models the changes
in the system at the moment of each stochastic event, using one row of the spreadsheet for each event.
Site managed with Eclipse, a multi-language, multi-platform IDE
