Excel Solver - Variable cell needs to be a multiple of 200

[Kolin]

Hello everyone,

I am trying to complete an assignment utilizing excel solver to give an optimal production schedule. I have used solver perfectly fine and got VERY close to the correct answer, except for one minor detail. If more details are needed for this assignment, I can post them, but I'm trying to keep the amount of information minimal, as I only have a few cells incorrect.

In the solution shown on my dummy file, in Q3 Temporary output, it has 100 output, and Q4 has 500 output. My problem is, these outputs need to be multiples of 200 (0, 200, 400, 600 or 800) in order for the Hire Temporary cost @$60 / unit to be a correct figure. Permanent and Temporary production are the variable cells for solver to change, with the objective of keeping total costs after 5 quarters to a minimum. Is it possible to tell solver that these cells MUST be a multiple of 200 as a constraint?

The reason for this, is that temporary production lines have a maximum of 200 units it can produce, but it will ALWAYS incur $12,000 extra costs per production line used, regardless of how many units it produces. Because of this, I want to fully utilize each production line if a temporary line is opened. After this production line is open, it can be used for any future period with no more hire temporary costs, hence the formulas in those cells.

To counter this issue, I also tried to make a nest IF statement for the Hire Temporary cost instead that if Temp output >0 = 12,000, >200 = 24,000, >400 = 36,000 >600 = 48,000 but solver did not seem to take this into consideration when solving to minimize costs, and gave the same answer.

I really hope I'm not too confusing in my question, as there is quite a lot of information I have omitted from this assignment, in order to not post the entire question and data information here.


Thanks in advance!

[BillyRaySpivy]

Will I receive an 'A' for doing your assignment?

[Alf]

Having a quick look at your setup I have two suggestions you could try (both untested)

Insert a new row below row 7 and in that new row add this formula in cell B8 "=Mod(B7,200)" and drag this formula to cell F8. Then you need to add a constraint i.e. "$B$8:$F$8 = 0"

The second suggestion is you add a new row as before but in the new row you use this formula for cell B8 "=B7*200" and drag this formula to cell F8. Iwould probably set a constrain that B7:F7
should be equal to or greater than 0 and less than 10. Then you need to sum the B8:F8 values an add a constrain for those.

Alf