Help with payback calculation

[moosetracks555]

I am having a difficult time coming up the savings figure for the following situation. I am almost certain the solution is a rate calculation involving calculus. However, it has been so long I don’t know where to start.

We have logs that degrade when not sprinkled with water. Our inventory has grown and we have more logs than capacity to sprinkle. We estimate a 2% / week loss on logs that are not sprinkled. I am trying to find the cost savings of adding some additional capacity to our sprinkler system. We will continue to add inventory at about the same rate as consumption. However we will use the oldest logs first.

Here is a generic example…
Assume the logs cost $100 a piece
Week 1 we have 1000 logs not sprinkled, and we use 100 logs.
Week 2 we have 900 logs not sprinkled for 1 week, and we add 100 new logs
Week 3 we have 800 logs not sprinkled for 2 weeks, 100 logs not sprinkled for 1 week, and we add 100 new logs

If we upgrade the sprinklers we will have 500 logs not sprinkled
Week 1 we have 500 logs not sprinkled, and we use 100 logs.
Week 2 we have 400 logs not sprinkled for 1 week, and we add 100 new logs
Week 3 we have 300 logs not sprinkled for 2 weeks, 100 logs not sprinkled for 1 week, and we add 100 new logs

How would you calculate a cost savings?

 

[tomwalz]

Estimate total log count at the same time each week and use that.

I must not be reading it right. Week 2 you have 900 logs and add 100 so in week three you have 800 logs when i figure 900 + 100 is 1,000.

Thomas J. Walz
Carbide Processors, Inc.

http://www.carbideprocessors.com

Good engineering starts with a Grainger Catalog.

 

[rb1957]

each week you use 100 logs and add 100 logs ?
so each week you have 1000 logs, with 900 aging each week.

how long till the unsprinkled logs are no good ??
'cause then you'll scrap all the old logs and be left with a small inventory, that will age into scrap (so you'll scrap 1000 logs).

is it truely FIFO ???

if you sprinkle 500, then you've got a long term inventory, and you'll eventually scrap 500 logs.

with simple (=constant) supply and demand the solution is simple ... if a sprinkle system for 500 logs is less than $50k it worth it (depending on the cost of money).

the problem is much more difficult if you have varying supply and demand.

 

[moosetracks555]

You are correct there are 1000 logs during week 3. I have broken it down because some logs are 2 weeks old, and some are 1 week old. Read the full sentence for week 3 and see if it makes sense...

Week 3 says you have
800 Logs not sprinkled for 2 weeks
+100 Logs not sprinkled for 1 week
+100 Logs new
= 1000


We will have 1000 logs every week, but some will be older than others.

 

[moosetracks555]

It is not exactly fifo because we will use the un-sprinkled logs first. They way I figure it if we don't upgrade we will reach a steady state loss $ / week once we reach 10 weeks. In the other example with the upgrade we will reach steady state loss $ / week in 5 weeks. I am trying to figure out the losses before we reach steady state. We loose 2% of the logs value every week.

 

[vpl]

This is really none of my business, but why are you keeping so many logs on hand if you can't keep them sprinkled and you end up having to throw them out? Even with a new sprinkler system, you're still throwing money away if you add new logs and don't sprinkle them. Without knowing more than what you've posted, it seems reducing your inventory would provide the biggest cost saving.

Another thought: Can you rotate the sprinkler system or the logs so that you spray the logs on alternate weeks? This might increase the viability of the logs so they'd last long enough for you to sell them off.

Patricia Lougheed

Please see FAQ731-376: Eng-Tips.com Forum Policies for tips on how to make the best use of the Eng-Tips Forums.

 

[rb1957]

using the unsprinkled logs first is FIFO (since the unsprinkled logs are the oldest).

the key question is how long till the unsprinkled logs become useless ? is it a binary decision (good today, gone tomorrow) or a qualitative decision (after 5 weeks, 50% of the logs are no good, after 6 80%, ...).

liked patricia's idea of moving the sprinkler system around

 

[moosetracks555]

VPL,

I am in agreement with you of the obvious solutions. I thought about adding the reasons for why we are in this situation, but I did not want to get into discussions about why not do this or that. This is hopefully a short term situation and we suffer weather degrade about 20 weeks of the year. I just need to find for the short term which way we will loose the least amount of money.

 

[moosetracks555]

rb1957,

Actually the sprinkled logs will be the oldest, but you can take them out of the picture and just assume a FIFO with the un-sprinkled logs. We assume a 2% / week loss. Unfortunately the loss is not worth the cost of moving them around.

 

[rb1957]

you're starting with the unsprinkled logs (FI) and using them first, 'cause you're decreasing the number of oldest logs by 100/wk (FO).

it's a little different if you're just grabbing 100 logs /wk from where ever ... ie in the 2nd week you'd use 90 1wk old logs and 10 new logs.

i know you're trying to show the numbers simply, but that messes with understanding the problem. if you lose 2%/wk then after 1 week you'll have 980 logs (use 100, lose 20, add 100). because you've got equal input and output you'll ultimately lose your original 1000 logs.

 

[moosetracks555]

rb1957,

I understand what you are saying if I say each log suffers 2% degrade / week then after 50 weeks the logs are 100% degraded and we need to compare that to how many logs we will loose with the upgrade vs without. However the 2% is only an estimate and we are talking about a 20 week time period. I am looking for a mathematical equation showing how much money we will loose in the next 20 weeks if we do the upgrade compared to if we don’t do the upgrade.

I guess I should have worded this as a math problem to avoid the distraction of trying to solve the root cause. I understand this does not make sense to keep doing what we are doing… I agree. Unfortunately my job in this situation is not to say “You have too many logs” or some other common sense solution. I am trying to find out which way looses the least amount of money.

 

[rb1957]

recognise that you have a Very simplistic model. input = output, therefore the inventory will be wastage. putting in the sprinkler system protects 1/2 the inventory, forever?, and the remiander will be wastage.

surely this is simple to show from an excel sheet ... whihc would allow you to pay "what if" games till the cows come home.

surely, too, the answer is simple ... how much does the sprinkle system cost (including running costs) ? what is the value of the inventory that it protects ? if A > B, (including the cost of money, go for it ...

 

[moosetracks555]

Unfortunately I don’t see the simplicity.

You are saying if the sprinkler costs more that 500 logs you don’t do it. That is like saying I won’t buy a $1000 refrigerator because the groceries I am going to put in it don’t cost as much as the refrigerator.

The log inventory is revolving and the amount of money loss each week will be different with and without the upgrade. Since we are looking at a 20 week time period we can’t assume this will go on forever.

 

[ivymike]

based on the description above, it doesn't matter how long the logs have been sitting. You lose 2% per week from the unsprinkled inventory, which is always between 900 and 1000 logs. If 100 new logs come in on April 1, by April 8 you'll have lost 2 of those logs, unless you bring the logs in and use them during the same week (in which case you'll lose logs from another pile). 1000 logs in inventory means 20/wk lost, for the whole period, regardless of the order you use them.

As was suggested earlier, use the average weekly inventory level (950 unsprinkled logs)*2% and you get 19/wk wastage. Over 20 weeks you'll lose about 380 logs.

 

[TenPenny]

Because you have 1000 logs, and you use 100 per week, the system will reach 'steady state' after 10 wks.

After week 10, you will always be using 100 logs that are 9 wks old.

You can only sprinkle 500 logs, that's 5 weeks worth.

So are you not going to reach the point where you are always using logs that have not been sprinkled for 4 weeks? It might be not sprinkled for 5 wks, I'd have to play around with it for a couple of minutes.

It's certainly not calculus. Go get 10 red blocks and 10 blue blocks, and do a simulation, if that's helpful.

 

[moosetracks555]

Thank you all for your input. I agree with you. I don't know why I had such a hang-up, but it seemed like it needed to be more complicated than it is. I apologize for the confusion on my part

 

[rb1957]

except that he's adding 100 logs per week, so inventory is only going down by the 2% wasteage.

this is a very simple scenario because input = output. nothing changes the 2% wastage; over 50 weeks the unsprinkled inventory disappears, whether the inventory is 1000 logs or 500 logs.

if the sprinkler system protects 500 logs, then that's the value of the sprinkler system (adjusted for a bunch of accounting fudges)

 

[ivymike]

there is one potential subtlety... if you use a LIFO system and leave the unsprinkled logs sitting, then you can reduce the spoilage as the scenario is laid out. Say that initially you have 1000 unsprinkled new logs. A week later, at dawn, you have 980 good ones and 20 bad ones. You bring your fork truck over and pull 100 logs out. You've taken (approx) 98 good ones and 2 bad ones. You then bring in 100 new logs, and wait a week. If you keep pulling logs from the same spot, then each week you'll take 98 good and 2 bad (roughly). The pile that's left, however, will have progressively fewer good logs. If the spoilage is a percentage (2%) if the number of good logs, then the rate of spoilage will decline, and by the 20th week you'll be losing 12/wk instead of 20.

 

[ivymike]

okay, I ran a simulation using excel:
at time 0, you have 1000 new logs which are all good and all unsprinkled.
during week 1, you spoil some logs. I used a random number generator and gave each good log a 2% chance of going bad in each week (not exactly what you've described above)
at the end of week 1, you pull 100 logs from the pile, check whether they're good or bad (keep a tally), and consume them. you then replace the logs with new ones
week after week, you use a FIFO system to choose which 100 logs to pull (always pull the 100 oldest unsprinkled logs).

I ran the simulation 10 times, and found that the following were the averages over the 10 runs.
Average of total bad logs in inventory each week for 20 weeks:
17.9, 34.3, 47.5, 57.3, 68.1, 71, 79.4, 84.7, 86.8, 85.6, 83.4, 83.5, 82.3, 82.9, 84.1, 84, 85.4, 83.3, 83.1, 84.1
(Note that the number of bad logs in inventory peaks around week 8, 9, or 10, then remains fairly constant)

Average number of bad logs drawn from inventory each week for 20 weeks:
1.7, 3.8, 7, 9.1, 8.6, 12, 11.2, 13.5, 17.1, 18, 18.8, 18.3, 18.5, 17.4, 16.4, 18.4, 16.8, 20.2, 17.4, 18

In this scenario, you end up having ~366 bad logs in inventory at the end of 20 weeks, and you'll have drawn & consumed 282 bad logs. (total of 648 bad logs)

I'll re-run with the LIFO assumption and see how the results differ.

 

[ivymike]

LIFO results - another 10 simulations run, averages shown here - you'll have 338 bad logs in inventory at the end of 20 weeks, and you'll have drawn and consumed 40 bad logs (total 378 bad logs)

week-by-week averages for bad logs in inventory and bad logs drawn were as follows:
17.7, 35, 50, 68.6, 82.8, 99.3, 118.8, 134.9, 149.3, 164.1, 177.9, 191.5, 206.2, 220.8, 234.6, 245.8, 258.2, 272.9, 286.6, 298.2
1.3, 2.1, 2.8, 2.5, 2, 1.5, 2.3, 2.3, 2.4, 1.6, 1.8, 1.5, 1.9, 1.4, 1.6, 2, 2.4, 2, 2.8, 1.2

note that the number of bad logs in inventory never really levels off - it goes up by a slightly decreasing amount each week over 20 weeks. The real advantage was that you were drawing logs to consume from a pile which had about 2% bad each week, rather than up-to-30% towards the end as in the general population.