I am hoping that you can all assist, for which I would be grateful
so we have a bolt & it's worth $100
This bolt has a 40 year in service life based on its required usage (we will only need it for forty years - not a single one but access to this particular type of bolt
It is used by 5 platforms which came into life in different years, and go out of life in different years
eg
1 - 2000 - 2020 (20 year life)
2 - 2001 - 2025 (24 year life)
3 - 2004 - 2030 (26 year life)
4 - 2004 - 2035 (31 year life)
5 - 2006 - 2040 (34 year life)
Each of the 5 platforms has a start date (in service date) and an end date ( out of service date)
They all use this specific bolt
It's life based on those 5 platforms that use it is the year range 2000 - 2040 (so 40 years)
So as a single unit it has a 40 year depreciation cost ( we use the straight line method)
The bolt in this example is $100 per bolt ($100 depreciating in value each year to $0 - no scrap value)
The problem
I am trying to figure out based on the stock held against each platform how to work out total stock depreciation opposed to that of 1 bolt. If held on the shelf.
So random numbers here but lets say each of the platforms use each year
1 - 1 Bolts
2 - 2 Bolts
3 - 3 Bolts
4 - 4 Bolts
5 - 5 Bolts
1 - 1 Bolts x 20 = 20 Bolts
2 - 2 Bolts x 24 = 48 Bolts
3 - 3 Bolts x 26 = 78 Bolts
4 - 4 Bolts x 31 = 124 Bolts
5 - 5 Bolts x 34 = 170 Bolts
In the current window we have 440 bolts used over 40 years.
For 1 bolt the deprecation is $100 over 40 years
for 440 it is (440*100)/40 years
I think?????
But I am trying to work out the value of each platform as a percentage of the whole
Soooo the percentage of the deprecation of the whole
I think
1 - ( 20 Bolts * $100) / 20 Years
2 - (24 = 48 Bolts * $100) / 24 years
3 - (78 Bolts * $100) / 26 years
4 - (124 Bolts * $100) / 31 years
5 - (170 Bolts * $100) / 34 years
Because surely although they all use the bolt they contribute differently to its overall deprecation. Based on the quantity of that bolt which they uniquely consume
SO
If Platform 4 Extended its life and usage by a further 3 years.. It would use a further 93 bolts. Making its depreciation
4- (217 Bolts * $100) / 34 Years
Completely changing its percentage of the usage of the bolt and its subsequent depreciation?
The end goal is to work out how much Each platform shares the depreciation.
Then with each platform coming to its end of life what the new depreciation figure would look like.
Is this something that is possible to determine in excel?
Using Platform
In Service day/month/year (to get days) Out of service day/month/year
Initial purchase value of product
end value of product
Taking the earliest date of the platforms in the list as the beginning
and the end service date from the longest in service platform
In order to argue accurately costing out the monies saved by purchasing the correct amount of bolts to cover the life of each individual platform and not hold surplus at any point in the chain
The proof I am looking for is to demonstrate don't buy more milk than you can drink before it expires.
As once its expired it was a waste a money
In this case.
If we buy too many bolts, all those that we don't use which we will have paid full price for, and they will depreciate to the final day of the final platform without every being consumed. Costing money unnecessarily. But if we buy the exact amount required for each platform to cover it's life (each year the price of the bolt may change - up or down) then we won't be wasting money buying an asset that we will never use.