I am really not sure which category this topic falls under, so please forgive me if I am posting in the wrong section.
I am in charge of procuring railcar leases for my company and I am trying to formulate an equation that will help me create a "score" for each offer when renewing leases, comparing the new vs old, or one company vs another.
Please see the attachment for reference. A few notes to start when looking at this are that 1) I do not know the depreciated values of these railcars and 2) the life span of a railcar is 50 year maximum, at which it then must be scrapped/destroyed and no longer used.
3 factors for this model are monthly lease rate, age of the railcar and capacity of the tank railcar in gallons.
1) in columns H and I, I first computed the $/gallon with the new and old lease rates to the capacity. Lower is obviously better.
2) Then in columns J and K, I created an "age factor", which is basically the % of the 50 year lifespan it has completed already, so the lower the better (lower=younger).
Note: Old age factor is the current age of the car less how long ago the previous lease for this car was signed (for these cars it was 5 years ago).
3) Last, in columns L and M, I created the "score" which is just the price per gallon multiplied by the age factor. I multiplied the result by 1000 just to make it easier to look at and compare (larger numbers versus fractions). Again, lower is better.
I just wanted to put this model and way of thinking out there for some constructive criticism and comments on maybe how I could improve on this. I don't have much experience in this and I am open to suggestions.
Please let me know what you think, thanks!