Solver problem

[Alexey]

Hi, I am playing with learning solver and using a test case that I found I
think on the MS Site



A company produces Radios, CD Players and Speakers. These products are made
from common parts: speaker cones, power supplies, electronics, laser parts
and amplifiers. Parts are in limited supply. Use Solver to determine what
mix of products to build to give the greatest sales revenue. The data for
this problem is as follows:




Parts in Stock
Number used in a Radio
Number used in a CD Player
Number used in Speakers

Speaker cone
450
1
0
2

Power supply
300
1
1
0

Electronics
500
1
1
2

Laser part
800
0
1
0

Amplifier
600
1
1
0







The sales price per item is:

Radio
CD Player
Speakers

£35.00
£50.00
£40.00



LARGE ORDERS DISCOUNT:

The sales price per item is reduced by 5% for orders of 100 or more and by
10% for orders of 200 or more. I am trying to use Solver to work out what
is the best combination of items to make, taking into account the limited
number of parts, to create the maximum sales revenue



My calculations say is should be 0,300,99 but Solver says 0,300,100.



Can anyone advise me why this is?



Thanks

A

 

[Bernard Liengme]

Too long to give reply here.
Send me (my private email) a file and I will try to locate your error
Also give URL where problem was found
best wishes

 

[Dana DeLouis]

Also give URL where problem was found

Hi. Looks like the op is no longer around.
This problem is very "similar" to the sample file in Office.
With Excel 2007, it's under:
C:\Program Files\Microsoft Office\Office12\SAMPLES
and it's the "SolvSamp.xls" file.
The problem is the "Product Mix" tab.

The numbers are very similar.
For example, Excel 2007's example prices are 35,50,75, but this problem uses
35,50,40.

Things that strike me off hand with Microsoft's example are the following.
The Profit cells have a MAX function! This is generally a big no-no.
This will often cause an error with Excel's Solver.

Also, this problem took the easy way out and used a decaying factor of ^0.9
to simulate decreasing profit. Such "curve-fitting" is often a good way to
simplify certain complex problems. However, the usual method is to use
Binary constraints for each of the price Upper & Lower limits.

 

[Dana DeLouis]

My calculations say is should be {0, 300, 99}, but Solver says {0, 300,

100}

Hi. I show 100 Speakers also. I think you missed the constraint on the
Electronics.
The number of Electronics used in your solution is:
=300*1+99*2 = 498

But you have 500 on hand.
This allows you to make 1 more Speaker that uses the remaining 2.

Your model may have missed due to rounding issues. An Excel worksheet
doesn't really have a true "Integer" value when using Integer Constraints.
My guess is that some integer constraint was very close to an Integer say
99.999 and din't see this as 100. Try adjusting some of Solver' options.

 

[Dana DeLouis]

Here's another interpretation of the problem.
Here's my best guess as to what happened.

If you read the problem as this:
For a particular item, if the number sold >99, then the price of "ALL" items
is reduced by 5%, then the solution is {0,300,99}.

However, if the first 99 items are sold at price, and only the 100th item is
reduced by 5%, then the solution is {0,300,100}.

Most problems that I've seen use this Marginal reduction in price. I
believe the Solver model that you read probably was using this
interpretation of the price reduction.

Your solution was more abrupt.
Most likely, your solution was:

300*45 + 99*40 = 17460

And if you increased the speakers by 1 (to 100), then the price off "ALL"
speakers were reduce 5% to 38.

300*45 + 100*38 = 17300

Which is less of a profit, and why you show 99.