Stock control example

You are a tractor retailer that sells various models of farm tractors. The Model 12 is your best seller, costs you $80,000 to buy, and you retail it at $99,000. You have orders of, on average, 2.7 a month, irrespective of the time of year (we could extend this to take care of seasonal variations if important, using a Seasonal Poisson model). If you can't meet the order, you know the potential client will buy from a competitor. Your current policy is to keep no more than 7 in stock. You take an inventory at the end of every month. If you have less than 3 in (stock + already ordered, but not received) you immediately order more to regain a stock of 7, but it takes 2 months to receive delivery. If you have more than 3 in stock, you don't order any more. This month you have 5 in stock, and $200,000 in cash. The tax rate is 30%. Monthly running costs (wages, rent of premises, etc) are $28,000. There is a cost of debt of 2% per month if your cash position goes below zero. The owners receive dividends of $8,000 per month provided there is money in the account.

What does your future cash position profile look like? In particular, what is the maximum debt facility you should maintain? Does this policy maximise your long-term profit? What, if any, changes to your inventory policy would improve your finances?

This spreadsheet models the cashflows over time.

A graph of the cash flow position for a single iterations of the model looks like this:

Figure 1: Snapshot of cash position over time

where the red dots are month's in which tax is paid. The business projection is that your company's cash position will be very volatile, and frankly not very profitable.

We need to test models for their logic. An easy and intuitive way to check the model is by stressing the model parameters, which you can see here. Let's change some of the parameters and review the effect they have on this graph. This gives us a good visual check of the model's behaviour.

Set SalePrice = PurchasePrice=$99000:

Figure A1: Snapshot of cash position over time when sales price = purchase price

The prediction is a steady downward trend, which makes sense. Now, let's instead change the sales rate to a very low value (0.001 tractors/month):

 

You get essentially the same graph, but without the volatility, because you are not selling any tractors. Now let's instead set the monthly running costs to $0. We get:

Figure A2: Snapshot of cash position over time when there are no running costs

 

A dramatic improvement in profitability as you might expect. Now let's set the Maximum stock to 100 tractors:

 

Figure A3: Snapshot of cash position over time when maximum stock = 100

 

At a periodic rate of roughly (100-2)/2.7 = 36 months you are making an order of some 98 tractors, selling them all, and reordering, producing the saw-tooth cash position. The costant negative cashflow position shows the burden of carrying so much stock and paying interest on the debt.

You can continue in this fashion for each parameter value, or combinations, until you are satisfied that the model is behaving well. It also provides a good method to convince others that the model works, particularly if they are not so familiar with modelling.

The first question we need to answer is what the cashflow position looks like over time. Single snapshots like Figure 1 don't give us a very good feel because they are just one scenario. After running a simulation, we can produce the following summary chart:

 

Figure 2: Cash position forecast summary chart

 

The chart shows that the way the business is setup it is expected to do no better than break even (red line) and it may need to borrow up to $50,000 to stay afloat.

Optimising the inventory policy

Let's see what we can do to improve the forecast cash position by changing the inventory policy. We have two parameters we can play with: MinimumStock and MaximumStock. The first clue is to look at a comparison of stock and sales:

Figure 3: Stock and sales comparison snapshot for 1st year

You can see in Figure 3 that Sales often equal stock, which means we are probably losing sales by not having stock available, and often stock dips down to zero. Let's look at what happens if we increase the MaximumStock from 7 to 10:

Figure 4: Stock and sales comparison snapshot for 1st year, when MaximumStock = 10

Now sales are not limited for a while when an order has been placed, but the stock level is allowed to go too low before the next order. So let's leave MaximumStock at 10, and increase MinimumStock to 6:

Figure 5: Stock and sales comparison snapshot for 1st year, when MaximumStock = 10 and MinimumStock = 6

Sales now seem much more free of stock restrictions. In order to optimise the inventory policy we need to specify the measure we wish to maximise, and then vary the MaximumStock and MinimumStock parameters until we find that maximum. We could use the mean of a cell that calculates the average profit per year, but let's be a little more conservative and use the 30th percentile of that cell, i.e. the average profit level that we are 70% confident of exceeding.

Figure 5: 30th percentile of mean yearly profit over 10 years for various combinations of MinimumStock and MaximumStock.

From this plot the best options appear to be {MinimumStock, MaximumStock} = {13,14} or {14,15}. Of course the greater the MaximimSiock and MinimumStock the more capital will be tied up in stock, and the greater the risk of a negative cash position. We could do some further analysis to see what those levels were and balance the risk of having a high negative cash position against an improved long-term profit level. We could also perform a sensitivity analysis on, for example, the level of demand to see how robust the ranking is of the various options.