Help with payback calculation
Help with payback calculation
(OP)
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?
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?





RE: Help with payback calculation
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.
www.carbideprocessors.com
Good engineering starts with a Grainger Catalog.
RE: Help with payback calculation
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.
RE: Help with payback calculation
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.
RE: Help with payback calculation
RE: Help with payback calculation
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: Eng-Tips.com Forum Policies for tips on how to make the best use of the Eng-Tips Forums.
RE: Help with payback calculation
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
RE: Help with payback calculation
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.
RE: Help with payback calculation
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.
RE: Help with payback calculation
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.
RE: Help with payback calculation
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.
RE: Help with payback calculation
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 ...
RE: Help with payback calculation
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.
RE: Help with payback calculation
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.
RE: Help with payback calculation
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.
RE: Help with payback calculation
RE: Help with payback calculation
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)
RE: Help with payback calculation
RE: Help with payback calculation
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.
RE: Help with payback calculation
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.
RE: Help with payback calculation
If you add the sprinkler system then you clear the inventory in 5 weeks so the survival rate is .98^5=.904 or a 10%loss.
So if you ship 100 logs a week, you lose 18 logs/week now vs a loss of 10 logs/week after your new system is in place.
However, this does not mean you go out and buy the new system, since perhaps the large inventory (1000 logs) might be questioned.
RE: Help with payback calculation
n is the inventory of unsprinkled logs
For n=1000 the loss is 20/week regardless of the input or output, only the amount of unsprinkled inventory is involved.
If you are allowing the unsprinkled inventory to decline to 5000 then the loss is 10/week.
I think the decision is based on the cost of 10 logs in the short term until you get the overall inventory down.
RE: Help with payback calculation
RE: Help with payback calculation
i mean, if you took a bad log out of inventory, wouldn't you toss it away and take another ?
aren't the bad logs scrap ?
RE: Help with payback calculation
RE: Help with payback calculation
RE: Help with payback calculation
Here the gold courses aren't watering yet. Construction is slow. Maybe, as a temporary measure, use those crappy Melnor sprinklers from a Big Box store. They fall apart pretty fast but they are cheap.
Many, many mills had committed to buy logs that they now can't use. You have to estimate how much lumber you will cut and commit a long time ahead, maybe years. No one wants excess timber now except for a few very special cases.
If you have a 16 foot log you can get two eight foot pieces of lumber out of it end to end. If you lose a bit on the ends you can get one 12 foot piece of lumber out of it and cut the rest into chips for paper, which is a huge loss.
Thomas J. Walz
Carbide Processors, Inc.
www.carbideprocessors.com
Good engineering starts with a Grainger Catalog.
RE: Help with payback calculation
RE: Help with payback calculation
For 1000 unsprinkled logs, selling 100 FIFO per week at a declining value of 2% each week, you'd get the following:
Week 1: 100 logs x $100 = $10000 revenue
Week 2: 100 logs x $98 = $9800 (he did say value drop, not wasteage)
Week 3: $9600
Week 4: $9400
and so on down to Week 10 where 100, 9 week unsprinkled logs sell for $82 each = $8200. Since at this time we reach steady state, using FIFO, each 100 logs after this point sells for $8200.
So, for 20 weeks, we get:
10000+9800+9600+9400+9200+
9000+8800+8600+8400+(11)*8200 = $173,000 in revenue for the 20 weeks.
We need more info for the sprinkled case because, as has been mentioned before, if you only sprinkle 500 of the logs, only sell the unsprinkled ones, and never move the logs from sprinkled to unsprinkled, then 500 logs would just sit there wet and the unsprinkled inventory would be the only set that would get turned over. To get past this, I'm assuming that you'll take 50 unsprinkled logs and 50 sprinkled logs and sell that each week. When you buy your 100 new logs, 50 go to the sprinkled pile and 50 go to the unsprinkled. When you lay that out, the total revenue after 20 weeks would be calculated as follows:
Week 1: 100 logs x $100 = $10000
Week 2: 50 x $98 + 50 x $100 = $9900
Week 3: 50 x $96 + 50 x $100 = $9800
Week 4: $9700
and so on down to week 10 where you're selling 50, 9 week unsprinkleds for $4100 plus 50 sprinkleds for $5000 for a total of $9100 per week from week 10 to 20. For the 20 weeks, we get:
10000+9900+9800+9700+9600+
9500+9400+9300+9200+(11)*9100 = $186,500
Granted, it is a simple model - you can throw in compounding for the 2% reduction and interest on your money, but even when you do, it looks like you'd have plenty of money to buy a nice sprinkler system for the 20 week haul...
If you "heard" it on the internet, it's guilty until proven innocent. - DCS
http://www.eng-tips.com/supportus.cfm
RE: Help with payback calculation
RE: Help with payback calculation
If you "heard" it on the internet, it's guilty until proven innocent. - DCS
http://www.eng-tips.com/supportus.cfm
RE: Help with payback calculation
RE: Help with payback calculation
If you "heard" it on the internet, it's guilty until proven innocent. - DCS
http://www.eng-tips.com/supportus.cfm
RE: Help with payback calculation
19 May 09 10:40
Exactly - which is why I devalued the wood instead of throwing it out as some previous posts did."
I don't think anybody is suggesting you "throw out" whole tainted logs.
When they say you lose x number of logs they mean the sum of the scrap calculated in whole logs.
RE: Help with payback calculation
"We loose [sic] 2% of the logs [sic] value every week."
This is the OP's statement that I was referring to. I take it that they can sell the log, but for an average of a 2% loss per week of not being sprinkled.
If you "heard" it on the internet, it's guilty until proven innocent. - DCS
http://www.eng-tips.com/supportus.cfm
RE: Help with payback calculation
I'm curious - what was the outcome? I would imagine you could get a sprinkler system for less than $13.5k. Did they go for it?
If you "heard" it on the internet, it's guilty until proven innocent. - DCS
http://www.eng-tips.com/supportus.cfm