Hi, I am in a tough spot for a deadline next week
and would so greatly appreciate any help with this optimization problem. I think using Solver is the way to go but I'm stuck.
I have to come up with the most cost effective way to store items in any assortment of warehouses over a 15 year period.
Inputs:
I have 15 data points for annual amount of products.
Storage option 1: third party ad hoc storage option--$15 an item, any amount of items, any amount of time, $25 to remove.
Storage option 2: WarehouseA capacity of 3000 items, mandatory 5 year lease, $43000 5 year cost
Storage option 3: WarehouseB capacity of 75000 items, mandatory 5 year lease, $77000 5 year cost
Storage option 4: WarehouseC capacity of 15000 items, mandatory 5 year lease, $130000 5 year cost
I understand how to use solver to approach a problem like this when it is a single point in time (something like this https://www.exceldemy.com/use-excel-solver-to-determine-which-projects-should-be-undertaken/), but I am struggling with how to approach this problem over time, especially with the constraint of the 5 year lease.
Any help would be GREATLY appreciated.
and would so greatly appreciate any help with this optimization problem. I think using Solver is the way to go but I'm stuck.I have to come up with the most cost effective way to store items in any assortment of warehouses over a 15 year period.
Inputs:
I have 15 data points for annual amount of products.
Storage option 1: third party ad hoc storage option--$15 an item, any amount of items, any amount of time, $25 to remove.
Storage option 2: WarehouseA capacity of 3000 items, mandatory 5 year lease, $43000 5 year cost
Storage option 3: WarehouseB capacity of 75000 items, mandatory 5 year lease, $77000 5 year cost
Storage option 4: WarehouseC capacity of 15000 items, mandatory 5 year lease, $130000 5 year cost
I understand how to use solver to approach a problem like this when it is a single point in time (something like this https://www.exceldemy.com/use-excel-solver-to-determine-which-projects-should-be-undertaken/), but I am struggling with how to approach this problem over time, especially with the constraint of the 5 year lease.
Any help would be GREATLY appreciated.
