As I mentioned above, this sort of "optimization" problem can't be
reduced to a simple general solution -- instead, they can only be
solved by multiple iterations through a solving process (a
"heuristic"). Luckily computers make this relatively simple and
quick.
In this case, the "optimal" solution appears to be the following:
Assign the six items (offices) letters A-F with the following requirements:
A: 20,000
B: 74,000
C: 145,000
D: 148,000
E: 278,000
F: 187,000
The forms should assign the following number of spots to each of the items:
Form 1: PRINT 27,177 copies
C (1 spot)
E (7 spots)
F (4 spots)
Form 2: PRINT 37,000 copies
B (2 spots)
C (3 spots)
D (4 spots)
E (2 spots)
F (1 spot)
Form 3: PRINT 6,882 copies
A (3 spots)
C (1 spot)
E (2 spots)
F (6 spots)
This results in the following number of each item provided:
A: 20646 (over by 646)
B: 74000
C: 145059 (over by 59)
D: 148000
E: 278003 (over by 3)
F: 187000
It requires a total number of 71,059 sheets to be printed (exceeding
the theoretical minimum of 71,000 sheets by only 59).
------
To achieve this result, I downloaded Lindo's free-trial What'sBest
add-in for Excel. It works like Excel's Solver function to solve this
type of problem, but is far faster and gives better results. I set up
the spreadsheet as necessary and then let it run for about 40 minutes
(in the background while I did other things), and it came to this
solution. After about 5 minutes, it had come up with a good solution
that required 71,300 sheets, but over the next 35 minutes it found
ways to reduce that to the optimal values shown above.
This trial version is free and will work forever, but it is limited to
30 integer variables at a time. This should suit you well, as it only
took 21 integer variables to solve this problem.
It can be downloaded from:
http://www.lindo.com/cgi/frameset.cgi?leftwb.html;wbf.html
------
The spreadsheet I created and used with the What'sBest add-in can be
found at the following URL:
http://69.61.18.4/temp/norman-9/lithographers_solution.xls
It will be available until Wednesday, January 5th at 5:00pm EST.
Please download it before then if you would like it.
If you need some assistance setting up a spreadsheet for use with the
What'sBest software, please let me know, and I can walk you through
how to do it with your next printing problem.
If you have any questions or anything is unclear, please let me know.
--Joey
Strategy:
My personal knowledge of Excel and optimization strategies
Google: excel advanced solver freeware |