Modeling inventory/stock without using circular references

[sagarxshah]

Hey guys,

How are you doing?

I'm preparing a financial model for my new importation business.

I'm specifically having an issue with the inventory aspect of the model.

My closing inventory is a sum of my opening inventory+additions of inventory in the year - sales of inventory in the year which gives the closing balance.

Now, my additions would depend on the closing balance and the closing balance also depends on the inventory. If I allow iterations, the model does not work so I need another solution.

How can I prepare the model so that my additions of inventory are automatic, but not directly linked to my closing stock, even though they need to be based on my closing stock?

Completely unclear, I know, but if you've got any questions to clarify, please ask.

Best,

Sagar

 

[Gerald Higgins]

Hi, welcome to the board.

I would think you're going to have to make a decision, to EITHER input data for the additions, and make the closing stock dependent on that, OR input data for the closing stock, and make the additions dependent on that.

I don't see any other way.

Normally in these situations I would have thought you would have data on the additions.
If you don't, then it might be legitimate to use closing stock to determine what the additions must have been, but in that case you need to input the closing stock.

If you think you must get it to work the way you have described, please help us with a worked example.
Say starting stock as 100, and through the year the sales were 520 (10 per week for 52 weeks).
What would the additions have been ?
What would the closing stock be ?
Use different numbers if you like.

 

[sagarxshah]

Hey Gerald,

I don't think I can input the additions manually or the closing stock manually.

This is because my model is time dependent, so if I change the start date of my project, then I manually have to change any inputs that I've manually added above. Also, I've got to have 60 months worth of projections, so changing this manually is not an option as I have to prepare a best case and worst case scenario for this.

So basically, just for examples sake, see the assumptions below:

1. Initial inventory at beginning of project is 10,000 pieces.
2. Subsequently, considering sales of 2000 per month would mean that I run out of stock in month 5. (also bearing in mind, my sales are increasing considerably month on month)
3. I am importing the goods, so I have to make sure my goods are already in the country by month 5, therefore I have to pay for them in Month 3.
4. I only order a new container (of 10,000 units), therefore in month 3, I will need to order a new container, because my stock has dropped below 4,000 units.
5. So my addition has to depend on my closing stock of the following month.
6. And my closing stock has to depend on the addition of the same month.

Hope this makes sense Gerald.

Thank you for taking the time to help, I really appreciate it.

Best,

Sagar

 

[Gerald Higgins]

So this looks like a typical challenge that many businesses face - forecasting inventory, demand (reductions in inventory) and supply (increases in inventory).

Excel can certainly help you with this, but I think you need to do some thinking to get it clear how exactly you want this to work.

Here's some detailed thoughts.

1. Initial inventory at beginning of project is 10,000 pieces.

OK good.

2. Subsequently, considering sales of 2000 per month would mean that I run out of stock in month 5. (also bearing in mind, my sales are increasing considerably month on month)

Let's not have two different sets of sales data. Let's settle on one please, at least for now.
If you want us to deal with considerable monthly increases in sales, then forget about "2000 per month" and give us a forecast.

3. I am importing the goods, so I have to make sure my goods are already in the country by month 5, . . .

It really doesn't matter, for these purposes, whether you are importing the goods, or digging them out of the ground yourself
The key thing here is what is the lead time between placing an order, and the goods becoming available.

. . . therefore I have to pay for them in Month 3.

This is really just a function of whatever payment terms you have in place with your supplier, and I'm not sure it's really relevant to the problem.
You MIGHT want to take payment schedules into account when ordering, in which case you need to give us details of whatever those constraints are.

4. I only order a new container (of 10,000 units), therefore in month 3, I will need to order a new container, because my stock has dropped below 4,000 units.

This is useful information, if your minimum order size is 10,000.
If your re-order trigger point is 4,000, fine, that's also useful information, although I'm not sure it needs to be that exact figure.

5. So my addition has to depend on my closing stock of the following month.

I still don't see how it can.

6. And my closing stock has to depend on the addition of the same month.

I think you might find it helpful to think in terms of FORECAST sales and supply, and therefore forecast inventory, and making re-order decisions based on that.

If you really think that inventory and closing stock must both depend on each other, then it would be helpful if you could provide a small example - say 6 months worth of data, showing exactly how you think this should work.
Show for each month, opening stock, reductions, additions, and closing stock, and describe the logic behind it.
I think you have to be able to do this before you can model it.