Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Solver problem
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Solver problem
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 -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "Alexey" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Solver problem
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 "Bernard Liengme" wrote in message ... 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 -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "Alexey" wrote in message ... 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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Solver problem
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 "Alexey" wrote in message ... 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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Solver problem
My calculations say is should be {0, 300, 99}, but Solver says {0, 300, 100} 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. -- HTH :) Dana DeLouis <snip |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
problem with solver | Excel Discussion (Misc queries) | |||
Interesting Solver problem (Solver encounters an error) | Excel Discussion (Misc queries) | |||
solver problem | Excel Worksheet Functions | |||
IF problem using Solver | Excel Worksheet Functions | |||
Solver problem | Excel Discussion (Misc queries) |