Home |
Search |
Today's Posts |
|
#1
|
|||
|
|||
costs.[Excel.Solver]
Hello,
I've been given a task and I think the most reasonable way to solve it is by using Excel's Solver. Those are conditions of this task: Some company needs to print 13000 posters. We've got 3 options: Company 1: <500 posters - $5,59 'per poster 501-1400 - $5,31 1401-1500 - $5,03 1500 - $4,59 transportation costs = $1,97 'per poster Company 2: (it only takes orders between 5000 to 10000 posters) 5000-1000 - $4,38 transportation costs = $1,99 Company 3: <2000 - $4,75 2000-3000 - $4,35 3000-6000 - $4,29 6000-8000 - $4,23 8000 - $4,17 transportation costs = $1,9 Our job (or in fact Solver's job:) is to find quantitys that each Company will print which assure us the best price per poster - in other words: We want to print it as cheep as it's possible. Looking forward to your help, Bartosz Dlugokecki. |
#2
|
|||
|
|||
Hi
Unless I am misunderstanding the problem, there is nothing for Excel to solve. You want 13,000 posters. Company 3 is the cheapest cost. Use company 3. Regards Roger Govier bentor wrote: Hello, I've been given a task and I think the most reasonable way to solve it is by using Excel's Solver. Those are conditions of this task: Some company needs to print 13000 posters. We've got 3 options: Company 1: <500 posters - $5,59 'per poster 501-1400 - $5,31 1401-1500 - $5,03 1500 - $4,59 transportation costs = $1,97 'per poster Company 2: (it only takes orders between 5000 to 10000 posters) 5000-1000 - $4,38 transportation costs = $1,99 Company 3: <2000 - $4,75 2000-3000 - $4,35 3000-6000 - $4,29 6000-8000 - $4,23 8000 - $4,17 transportation costs = $1,9 Our job (or in fact Solver's job:) is to find quantitys that each Company will print which assure us the best price per poster - in other words: We want to print it as cheep as it's possible. Looking forward to your help, Bartosz Dlugokecki. |
#3
|
|||
|
|||
Oh..
there's a mistake.. "Company 3" transportation cost is $24, not $19. Sorry for that. -- Greetings, Bartosz Dlugokecki. |
#4
|
|||
|
|||
$2,4 of course..
...I definitely need coffee. =) |
#5
|
|||
|
|||
Hi
Sorry, I'm still not fully understanding the problem. $24 per what as opposed to $19. Your original question mentions $1.9 not 19. Regards Roger Govier bentor wrote: Oh.. there's a mistake.. "Company 3" transportation cost is $24, not $19. Sorry for that. -- Greetings, Bartosz Dlugokecki. |
#6
|
|||
|
|||
Sorry for all that..
it should be $2.4.. I need get some coffee.. :-) So. The problem is.. There are 3 companys that can print for our posters. They can do that according to price-list. It's obvious that more we order the best price we can get. But for example we can't print all 13000 posters in Company 2, becouse of theirs 10000 limit. So.. there are few conditins that we have to consider, like the best price in Company 3, but transport is most expencive. Problem is to make Solver to consider all our requirements including different price per poster dependent of quantity which we order in each Company. -- Bartosz Dlugokecki |
#7
|
|||
|
|||
Hi
I still can't see that it needs Solver. The cheapest solution is to split the order into 2 orders of 6,500 each and place with company 2. Total Cost = 6500 x (4.38 + 1.99) x 2 = 82,940 If they won't accept the 2 orders of 6500 each, then place the maximum order of 10000 with them = 10000 x (4.38 + 1.99) = 63,800 The balance needs to be placed with company 1 as their's is the next lowest cost = 6.56 (4.59 + 1.97) x 3000 = 19,680 This then gives a total cost of 83,480 or 540 greater than the "cheapest" solution. Regards Roger Govier bentor wrote: Sorry for all that.. it should be $2.4.. I need get some coffee.. :-) So. The problem is.. There are 3 companys that can print for our posters. They can do that according to price-list. It's obvious that more we order the best price we can get. But for example we can't print all 13000 posters in Company 2, becouse of theirs 10000 limit. So.. there are few conditins that we have to consider, like the best price in Company 3, but transport is most expencive. Problem is to make Solver to consider all our requirements including different price per poster dependent of quantity which we order in each Company. -- Bartosz Dlugokecki |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|