Solver Scenario for Optimal Whole Number w/Office97

[CDobyns]

Every time I think I know what I'm doing with Excel Solver - I surprise myself (by what I apparently don't understand . . .).

I'm trying to solve for the maximum number of company store "certificates" I need to buy, up to say a maximum of $600, in order to buy the maximum number of movie tickets possible (also from the company store) for my employees - but to the closest number of whole movie tickets possible, and since for some reason, you can't "add" actual $$$ money to your purchase to "round up" a fractional amount remaining at the company store (nor can you get change back from a partial certificate purchase).

I already (now) know that 17 is the optimal number, to purchase 60 movie tickets, but assuming the below represents columns and rows, cell B3 would be the cell to change, and B5 would be the target cell, which would be set to <=$600 - again, with objective that both B3 and B4 would result in whole numbers.

Movie Ticket Price.......$8.50
Certificate Price..........$30.00
# of Certificates.........17
# of Movie Tickets......60
Total Certificate Cost..$510.00

All that said, I can't see to get the set-up correctly to get Solver to return 17 in B3. Any help?

 

[GooberTron]

Without trying I would guess that you want to maximise B5 using B3 with the constraints that B5 <= $600 and the other constraint of B3 being an 'int'.

 

[CDobyns]

Hmm, with very little trying (on your part, but a lot of trying on my part) - I'd say you were exactly right!

Under the constraints I do have:
$B$3........= Integer
$B$3........> 1
$B$5........<=600

However, Solver is indeed "maximizing" the result (to $600), resulting in 70.5882 movie tickets. And since I cannot add to or "round off" that 0.5882 portion of a movie ticket (hmm, maybe that's equivalent to a Children's Price ticket. . . no, probably not), I still need B4 to return a whole number result.

 

[GooberTron]

Sorry I missed the certificate vs ticket thing. Can you not add another constraint of B4 being an integer too? Also by not trying I meant that I hadn't opened Excel myself to try it out., testing would have been a better phrase!

 

[CDobyns]

No problem, I knew what you meant about not trying (Trying = Testing).

Yes, I thought that adding that one additional constraint would do the trick also.

$B$4.........= Integer

But when I try to add that constraint, I get a message dialog that says:

Integer constraint Cell Reference must include only Adjustable Cells.

Not sure why that cell is so special, that I'm getting that message.

 

[GooberTron]

I have a slightly goofy workaround. Change B4 to be non-calculated, just any old number.
Have another cell which calculates the cost of B1*B4 (i.e. the number of tickets * ticket cost).
Have a further cell which calculates the ratio of this new number divided by the B5. Then you can set solver to have this new cell as a target of 1 by changing B3:B4 with the constraints that B5<600, and B3 and B4 are ints.

 

[CDobyns]

Hmm, nice try (seriously). I had even given some prior thought to the "ratio" approach. The problem with this (your solution), is that doesn't this presuppose that you already know the value of B5 - when you really only know that final cost value, after you know what the optimized number of tickets are?

I think the actual solution is some variation on what you've suggested, but it can't make the upfront assumption that any of the values in B3:B5 are known before Solver provides the optimized result.

I told you this was tricky, but at least now I'm starting to feel better about myself . . . and my inability to solve this.

 

[GooberTron]

I'm not quite following, surely the constraint on B5 is all you need to know (max budget).

 

[CDobyns]

Not quite. The maximum budget was $600. The B5 value ($510), was the value derived by the 17 certificates that would be purchased, that would result in exactly 60 movie tickets being able to be purchased for that amount - although the amount is of course less than the maximum budget allowable ($600). It's a do-loop (I think we're caught in . . .).

 

[shg]

Is spending $510 for 60 tickets with zero waste really better than spending $570 for 67 tickets and wasting $0.50?

Ask your employees