Help: using Excel for nonlinear programming.

[Kevin Stecyk]

Dana,

Not to beat this thing to death, but like Wen-Feng, I too tried using Solver
and it failed to arrive at the proper answer. I obviously had the correct
equations since I arrived at the correct answer on my own using a different
approach. I believe the constraints I entered were correct, though I no
longer have them. It seemed to me that Solver's ability to pinpoint the
correct solution was highly dependent upon the seed values given.

Just my two cents.

Regards,
Kevin

Thanks Tushar. I get it now. The fixed costs were "not" part of the
$1 million budget. With that, I get the same answer. Thanks. I now see
that the others got the same answer also. When he said a profit of $335.82,
it looked like dollars and cents. I now see that he really meant a $335,820
profit. ;>)

Here is my attempt at using Solver. The Demand equation is the same, just
multiplied by 1000. I used Range names in Column A, Equations were in
Column B.


-A- -B-
Price 47
Promotion 392
Demand =((4/9)*(500 + Promotion)^2)/EXP(1)^(Price/20)
Cost =250000 + 18*Demand + 1000*Promotion
Sales =Price*Demand
Profit =Sales-Cost


Here are some of the constraints I used. I maximized Sales here.

=MAX(Sales)

=Promotion>=0
=Promotion=INT(Promotion)
=Promotion<=500

=Price>=18
=Price<=100
=Price=INT(Price)

=Cost<=1250000

= = = = = = =

Here are the answers I get:
Price 47
Promotion 392
Demand 33,725
Cost 1,249,054
Sales 1,585,087
Profit 336,032


= = = = = = = = = = = = = = = = = = = = =

I used another program to check Solver...

Demand[Price_, Promotion_] := ((4/9)*(500 + Promotion)^2)/E^(Price/20)
Sales[Price_, Promotion_] := Price*Demand[Price, Promotion]
Cost[Price_, Promotion_] := 250000 + 18*Demand[Price, Promotion] +
1000*Promotion

NMaximize[
{
(*Maximize the next line*)
Sales[Price, Promotion] - Cost[Price, Promotion],

(*Subject to the following ...*)
Cost[Price, Promotion] <= 1250000,
{Price, Promotion} \[Element] Integers,
Promotion >= 0
},
{{Price, 18, 100}, {Promotion, 0, 500}},
WorkingPrecision -> 50]

Returned the same answer...

336,032.
Price -> 47,
Promotion -> 392

--
Dana DeLouis
Using Windows XP & Office XP
= = = = = = = = = = = = = = = = =

Actually, it turns out I made a typo. When I recreated the problem
from scratch, I mistyped the demand function using EXP(-0.09...)
instead of EXP(-0.9...)

With that correction in place and by spending a bunch more time
studying the original post, which lead to the conclusion that the fixed
manufacturing cost is not part of the $1,000,000 budget, the profit is
about $336,500.

--
Regards,

Tushar Mehta, MS MVP -- Excel
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

they

can

cost

of

function

of

the promotion budget. From what I remember of my
marketing/pricing/strategy classes it should be a concave, not a
convex, function! But, I stayed with the original specification.

Also, I saw no reason to produce any excess quantity. Since timing is
not part of the problem description, I ensured that the production
quantity equaled demand. With the caveat, that if the optimal price
was less than $18, one would produce nothing. However, I saw no
reason
to program that into XL. Such a decision remains part of the 'post
mortem' analysis.

Finally, this is a classic example of when *not* to insist on integer
solutions. When dealing with quantities in the scale of
hundreds/thousands/millions, it makes a lot more sense to relax the
integer constraints, and then validate the final result by imposing
the
integer requirements at the end. [I suspect that the Java /
Mathematica solutions you mentioned used this technique.]

My XL set up was:
B C
2 Total Budget 1,000,000
3 Fixed manuf. Budget 250,000
4 Promotion 265,076
5 Variable Manuf. Budget 484,924
6 Price 453.53
7 Demand 26,940.22
8
9 Unit cost 18
10 Units produced 26,940.22
11 Excess production 0
12
13 Total revenue 12,218,169
14 Net Profit 11,218,169

The formulas in column C were:

1000000
250000
265076.044667111
=C2-C4-C3
453.528928910334
=2000/18*EXP(-0.09*C6/18)*(1+C4/1000/500)^2*1000

18
=C5/C9
=C10-C7

=C6*C7
=C13-C2

The Solver model was:
=MAX($C$13)
=COUNT($C$4,$C$6)
=$C$4>=0
=$C$11=0
=$C$4<=Sheet1!$C$2-Sheet1!$C$3
={100,100,0.000001,0.05,FALSE,FALSE,FALSE,1,1,1,0.0001,TRUE}

For the sake of authenticating the non-integer solution, I developed a
complementary result in col. E. The results from Col. C were INTed
and
ROUNDed as appropriate. The results are below.
E
6 453.53
7 26,940
8
9 18
10 26,940
11
12
13 12,218,098
14 11,218,098

--
Regards,

Tushar Mehta, MS MVP -- Excel
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

Kevin,

Thanks for your hint.
And this is exactly a homework problem designated
by me to my students. I am currently teaching Decision
Support Systems in undergraduate part. They were asked
to use Excel for supporting the decisions about the
price and promotion level of a certain product.

This problem was actually neatly solved by a simple Java
code and graphically illustrated by Mathematica v4.0.
However, my students has little knowledge about Java and
Mathematica. I believe the Excel can do the same job.

However, when I tried to illustrate the power of Excel
in my class. I was stuck there for a whole hour, and
kept changing the constraints. But all were in vain.

My conclusion would be Excel cannot solve integer-nonlinear
programming problems. Or some experts can tell me how to solve
this kind of problems. Any suggestion would be very appreciated.

-------
Wen-Feng Hsiao,

Is this a homework problem? You know we don't do homework
problems.


Here's my hint, for what's it is worth. Just remember, my advice
is
only
worth the price paid.

You are asked to find, "Please find the Price and Promotion level
to
maximize the profit."

I would just use a data table and plug your information in. Then
just
find
the max of the data table.

Or you can use Solver as you attempted to do with the constraints
to
find
the Max.

Good luck!

Regards,
Kevin



Dear experts,

I spent much time on trying to use Excel for solving the
following
nonlinear programming problem:

Total budget: one million dollars (for Promotion and
Manufacture)
The production cost is 18 dollar/per item
The estimated demand is
D=2000/18*EXP(-0.9*Price/18)*(1+Promotion/500)^2 (in thousands),
where
Promotion is in thousand dollars and Price is in dollar.
The over manufactured items can only be sold 14.4 dollar/per
item
The fixed manufacture cost is 250 thousand dollars.
Please find the Price and Promotion level to maximize the
profit.

I use Solver with the following setting, but could not

find

the