costs.[Excel.Solver]

[bentor]

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.

 

[Roger Govier]

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]

Oh..

there's a mistake.. "Company 3" transportation cost is $24, not $19.

Sorry for that.

 

[bentor]

$2,4 of course..

...I definitely need coffee. =)

 

[Roger Govier]

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]

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.

 

[Roger Govier]

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]

Hello,

yes, I know it can be solve by using simply logic thinging, but still..
I have to stay with using Solver becouse our environment could change.
It means, that next time we would need 20000 posters, or prices can
change. So we need to create a flaxible tool.

 

[Tushar Mehta]

Cannot help but smile; you actually know a company that says "No, please
don't give us more work. We make too much money as it is!"

To me a clause like that is an attempt by a professor to fix after-the-
fact a problem in a homework assignment.

In any case, since shipping costs are per-poster, all you have to do is
add the shipping cost to the per-poster production cost. Then, look at
the numbers and pick the company with the lowest cost.

While a problem of this nature looks sophisticated enough to need
Solver, it isn't. Yes, it *can* be made so but this one isn't.

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Multi-disciplinary business expertise
+ Technology skills
= Optimal solution to your business problem
Recipient Microsoft MVP award 2000-2005