Excel solver - linear problem for business optimization

Hi,

I am new to using solver and don't understand how to define the objective function and constraints for this problem. I know the lead time can't exceed 30 days, cost must be minimized, and that the plants (defined as country 1-6) are controlled w a binary number to indicate if they are used or not. In addition, I must optimize for cost with a constraint to adjust for 700 extra tons from any country. *

Use the Excel spreadsheet attached and create an optimization template that meets the current 30-day lead time.

Run the optimization template to get the optimal network configuration of plants for the current 30-day lead time.
Hint: Determine the objective, decision variables and constraints.

Rerun your optimization template to investigate lower lead times and spare capacity requirements. What is the shortest lead time you can obtain that satisfies the requirements?
Hint: Consider what additional constraints will be needed to perform this optimization.

Does anyone have any idea how to go about this? Or what the objective function should be?

Thanks!!

Perhaps something like this?

I assume that costs (Fixed op. cost)is based on total amount if factory is running and not scaled to % utilization of factory used when running.

As for supply to Country 8 the smallest "Lead Time" value is in range L25:L30 is 24 so this is lowest max value that solver can use to find a solution.

File also contains a macro one can use to set max "Lead Time" binary grid in range I35:L40. Edit macro "adjust" with max "Lead Time" of your choice.

Alf

What about excess capacity? Should we consider the excess capacity into consideration as a second point?

I think it's difficult to determine what "excess capacity" is without adding a time element.

So I think the best answer to you question but also the most unsatisfying is: It depends

Alf

I also want to add solver for excess capacity, can you help with that?

Well you know the capacity for the 6 operating plants, then you just add 700 to each of them.

Then there is the "Fixt Op. Costs" does this stay the same when capacity increases? I would say not one probably should probably add a cost based on original capacity and "Fixt.Op.cost" to get a ton cost and multiply that with 700 and add this to the original "Fixed. Op. Costs"

Or there is a base cost for each plant and to this there is an added capacity cost, still easy to calculate but one must know the split.

Alf

The spare capacity of 700 ton to accomodate future growth, this is not for each plant, but for full company. I am getting how to add excel solver for this condition. Can you try to add that in excel? And fixed cost should not change since ur not going above capacity..

Move range D43:D50 to C43, add the range C43:C48 as cells to change. The sum of this range should be 700. The formula in M43 should be changed to "=Sum(C43:L43)" and all the other sum formulas down to M48 should be changed in the same way

Alf

Whatever solver decides to put there as long as the sum of that does not exceed 700.

Alf

I tried following ur steps and added everything in solver but not working.. Not sure why but I am not able to attach my excel. Can you send me ur excel to cross check where I am wrong?

The spare capacity of 700 ton to accommodate future growth, this is not for each plant, but for full company.

Looking at my setup in post #2 then I see that cell M14 is at max and you said that

And fixed cost should not change since ur not going above capacity.

so there can be no change in the capacity for that row.

M15 is zero even if the Lead Time values are quit small, it's probably not used because of the relatively high Fixed Op. Costs but if demand increases solver will eventually use this.

M16 has a spear capacity of 838 tonnes that can be used to meet a higher demand.

M17 is 0 but has a potential of 483 tonnes but stopped probably by high Fixed Op. Costs and some Lead Time values greater than 30, so increase Lead Time values.

M18 is 0 but has a potential of 1050 tonnes blocked by Lead Time values greater than 30 and perhaps high Fixed Op. Costs

M19 has a spear capacity of 269 tonnes blocked by Lead Time values greater than 30

So the model is capable of producing an additional 700 tonnes of future growth (assuming that ny model setup is right) if the demand exists and higher Lead Time values are accepted.

Not sure I can add more info as I already have done to much as there are forum rules for helping students with home work an I don't like getting my knuckles rapped for breaking forum rules.

Alf