Help: using Excel for nonlinear programming.

[Wen-Feng Hsiao]

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
correct answer:
manufacture: B1(=1000-B2)
the number of manufactured items: D1(=INT(B1/18*1000)/1000)
promotion: B2(integer variable)
price: B3(integer variable)
demand: B4(=INT(1000*2000/18*EXP(-0.9*B3/18)*(1+B2/500)^2)/1000)
profit: B5(=IF(B4>D1,D1*B3-1000-250,B4*B3+(D1-B4)*14.4-1000-250)),

with the following contraints:
$B$2>=0
$B$2=integer
$B$2<=1000
$B$3=integer
$B$3>=0
$B$4<=$D$1

But it could not find a solution. However, if I modified the content
of D1 and B4 to:
D1(=B1/18)
B4(=2000/18*EXP(-0.9*B3/18)*(1+B2/500)^2))
It finds an unrealistic (fraction is included) local optimum solution.

Could someone give me a hint? Thanks a lot!

 

[Kevin Stecyk]

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

 

[Wen-Feng Hsiao]

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.

-------

 

[Kevin Stecyk]

Wen-Feng-Hsiao,

I will try to solve it. No promises though. I will send you an email when
my answer or my lack of an answer.

Best regards,
Kevin

 

[Kevin Stecyk]

Wen-Feng Hsiao,

I sent you a spreadsheet via e-mail which shows my methodology.

I hope, if nothing else, it provides you with some food for thought.

Best regards,
Kevin

 

[Dana DeLouis]

Just for educational curiosity, what values did you get?
I may have done this wrong, but I gave it a try...

Proft of $221,816.87
Price of $45.35
Promotion of 265 (meaning $265,000)

(I may of messed up with my understanding of the units (i.e. Demand &
Promotion were returned in Thousands)

 

[Kevin Stecyk]

Hi Dana,

The answer is 47 for Price and 392 for promotion for a profit of $335.82.

This was the same answer as Wen-Feng Hsiao got as well. The contraints were
the price and promotion had to both be integers. Wen-Feng Hsiao used a Java
application to help solve the problem.

I used a "crude" data table to zero in on the solution, and followed up with
a more detailed data table which zeroed-in on the actual answer.

My solution took 10-15 minutes to arrive at. So not bad.

Regards,
Kevin

 

[Dana DeLouis]

Thanks Kevin. Appreciate that. Could you help me out with my
understanding? Here is how I was following this.
With 47 & 392, I get a demand of:

((2000/18)*(1 + Promotion/500)^2)/E^(0.9*(Price/18))
/. {Price -> 47, Promotion -> 392}

33.72524848225471

This is in Thousands, so the demand (d) is about:
d = 33,725

Note: If I put the 1,000 into the demand equation, I get a newer equation
of ((4/9)*(500 + Promotion)^2)/E^(Price/20)
Meaning...
((4/9)*(500 + Promotion)^2)/E^(Price/20) /. {Price -> 47., Promotion ->
392.}

33725

If all the demand was manufactured, the cost would be:
d*18 + 392*1000 + 250000

1249050

This is over the $1,000,000 budget.

To stay within the budget, he would have to manufacture less, by solving for
n (keeping your same promotion of $392,000)

Solve[n*18 + 392*1000 + 250000 == 1000000, n]

n = 19,888

His profit would be:

(47 - 18)*19888 - 392*1000 - 250000

-65248
This is a loss. I don't see where I went wrong in my logic. Thanks for any
feedback. It would appear to me that a profit of $335.82 is rather small
for a 1 million dollar budget.

 

[Kevin Stecyk]

Dana,

With regard to the budget and profit, I didn't worry about it as it appears
to be a mathematical abstraction used for teaching purposes.

Both he and I arrived at the same answer using independent methods, so I
feel comfortable with the result.

With regard to the algebra, I didn't worry about it. I simply took his
equations as given and started using them as equations in Excel. I
literally did a copy paste.

I will try sending my solution to your listed hotmail address.

Regards,
Kevin

Thanks Kevin. Appreciate that. Could you help me out with my
understanding? Here is how I was following this.
With 47 & 392, I get a demand of:

((2000/18)*(1 + Promotion/500)^2)/E^(0.9*(Price/18))
/. {Price -> 47, Promotion -> 392}

33.72524848225471

This is in Thousands, so the demand (d) is about:
d = 33,725

Note: If I put the 1,000 into the demand equation, I get a newer equation
of ((4/9)*(500 + Promotion)^2)/E^(Price/20)
Meaning...
((4/9)*(500 + Promotion)^2)/E^(Price/20) /. {Price -> 47., Promotion ->
392.}

33725

If all the demand was manufactured, the cost would be:
d*18 + 392*1000 + 250000

1249050

This is over the $1,000,000 budget.

To stay within the budget, he would have to manufacture less, by solving for
n (keeping your same promotion of $392,000)

Solve[n*18 + 392*1000 + 250000 == 1000000, n]

n = 19,888

His profit would be:

(47 - 18)*19888 - 392*1000 - 250000

-65248
This is a loss. I don't see where I went wrong in my logic. Thanks for any
feedback. It would appear to me that a profit of $335.82 is rather small
for a 1 million dollar budget.

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

Hi Dana,

The answer is 47 for Price and 392 for promotion for a profit of $335.82.

This was the same answer as Wen-Feng Hsiao got as well. The contraints were
the price and promotion had to both be integers. Wen-Feng Hsiao used a Java
application to help solve the problem.

I used a "crude" data table to zero in on the solution, and followed up with
a more detailed data table which zeroed-in on the actual answer.

My solution took 10-15 minutes to arrive at. So not bad.

Regards,
Kevin

advice

is

level

to Then
just

constraints

to

 

[Kevin Stecyk]

Hi Dana,

Tried sending to your hotmail address. It was undeliverable.

Regards,
Kevin

Thanks Kevin. Appreciate that. Could you help me out with my
understanding? Here is how I was following this.
With 47 & 392, I get a demand of:

((2000/18)*(1 + Promotion/500)^2)/E^(0.9*(Price/18))
/. {Price -> 47, Promotion -> 392}

33.72524848225471

This is in Thousands, so the demand (d) is about:
d = 33,725

Note: If I put the 1,000 into the demand equation, I get a newer equation
of ((4/9)*(500 + Promotion)^2)/E^(Price/20)
Meaning...
((4/9)*(500 + Promotion)^2)/E^(Price/20) /. {Price -> 47., Promotion ->
392.}

33725

If all the demand was manufactured, the cost would be:
d*18 + 392*1000 + 250000

1249050

This is over the $1,000,000 budget.

To stay within the budget, he would have to manufacture less, by solving for
n (keeping your same promotion of $392,000)

Solve[n*18 + 392*1000 + 250000 == 1000000, n]

n = 19,888

His profit would be:

(47 - 18)*19888 - 392*1000 - 250000

-65248
This is a loss. I don't see where I went wrong in my logic. Thanks for any
feedback. It would appear to me that a profit of $335.82 is rather small
for a 1 million dollar budget.

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

Hi Dana,

The answer is 47 for Price and 392 for promotion for a profit of $335.82.

This was the same answer as Wen-Feng Hsiao got as well. The contraints were
the price and promotion had to both be integers. Wen-Feng Hsiao used a Java
application to help solve the problem.

I used a "crude" data table to zero in on the solution, and followed up with
a more detailed data table which zeroed-in on the actual answer.

My solution took 10-15 minutes to arrive at. So not bad.

Regards,
Kevin

advice

is

level

to Then
just

constraints

to

 

[Tushar Mehta]

I would take exception to the proposition that the problem was easily
solved in any programming language. Non-linear integer problems are
notoriously difficult -- very, very difficult -- to solve.

However, with a little 'intelligent massaging' of the problem they can
be trivial to solve -- just as this was trivially easy to solve with
XL/Solver.

There is some ambiguity as to whether the fixed manufacturing cost of
250,000 is part of the million dollar budget or not. I assumed it was.

The demand function "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." is what I programmed. However, it seems somewhat suspicious.
I've never heard of demand being a squared-multiplicative 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 Stecyk]

Tushar,

Your results differ from those of Wen-Feng Hsiao and myself. We arrived at
our results independently using different approaches. I will send you my
spreadsheet, for what it is worth.

Regards,
Kevin

I would take exception to the proposition that the problem was easily
solved in any programming language. Non-linear integer problems are
notoriously difficult -- very, very difficult -- to solve.

However, with a little 'intelligent massaging' of the problem they can
be trivial to solve -- just as this was trivially easy to solve with
XL/Solver.

There is some ambiguity as to whether the fixed manufacturing cost of
250,000 is part of the million dollar budget or not. I assumed it was.

The demand function "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." is what I programmed. However, it seems somewhat suspicious.
I've never heard of demand being a squared-multiplicative 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.

 

[Dana DeLouis]

Just for feedback, it appears that Tushar and I came up with very similar
results. However, our scales seem to be different. I have no idea where I
may have made a mistake.

We both had the same Promotion:Mine was 265,000

For Price:Mine was... 45.35
These numbers are the same, just scaled differently.

For Profit:Mine was: 221,816.9

Although these numbers are different, they both have the sequence
....218169...Hmmmm??

Anyway, very interesting. Just a thought. With a budget of 1 Million, and
an item of fairly low value, it "appears" to me that a 11 million profit is
rather high. That is a very high rate of return on this investment. I was
expecting about a 10%-20% return on investment. With 1 Million budget, I
was expecting a profit somewhere in the range of $100,000 to $200,000.
The other solution was a profit of $335.82. This solution doesn't sound
correct either. I don't think any company wound invest $ 1 Million for a
possible $335 return.
Again, I am probably wrong. It is just a thought. :>)

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

Tushar,

Your results differ from those of Wen-Feng Hsiao and myself. We arrived at
our results independently using different approaches. I will send you my
spreadsheet, for what it is worth.

Regards,
Kevin

I would take exception to the proposition that the problem was easily
solved in any programming language. Non-linear integer problems are
notoriously difficult -- very, very difficult -- to solve.

However, with a little 'intelligent massaging' of the problem they can
be trivial to solve -- just as this was trivially easy to solve with
XL/Solver.

There is some ambiguity as to whether the fixed manufacturing cost of
250,000 is part of the million dollar budget or not. I assumed it was.

The demand function "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." is what I programmed. However, it seems somewhat suspicious.
I've never heard of demand being a squared-multiplicative 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

 

[Tushar Mehta]

Hi Kevin,

I am fairly comfortable with my solution -- given my understanding of
the problem. And, of course, it is very possible that we have a very
different understanding of the problem.

It is also possible that, depending on how closely you adhered to the
original setup of Wen-Feng Hsiao, you *might* have run afoul of a
possible mistake in all the divisions and multiplications by 1,000 in
the original model. I can't be sure but all the scale changes made my
head spin. So, I side-stepped the entire scaling issue by working with
non-scaled numbers. The only place I factored in the 'in thousands'
scale effect was where absolutely necessary (e.g., the computation of
the demand).

--
Regards,

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

Tushar,

Your results differ from those of Wen-Feng Hsiao and myself. We arrived at
our results independently using different approaches. I will send you my
spreadsheet, for what it is worth.

Regards,
Kevin

I would take exception to the proposition that the problem was easily
solved in any programming language. Non-linear integer problems are
notoriously difficult -- very, very difficult -- to solve.

However, with a little 'intelligent massaging' of the problem they can
be trivial to solve -- just as this was trivially easy to solve with
XL/Solver.

There is some ambiguity as to whether the fixed manufacturing cost of
250,000 is part of the million dollar budget or not. I assumed it was.

The demand function "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." is what I programmed. However, it seems somewhat suspicious.
I've never heard of demand being a squared-multiplicative 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
correct answer:
manufacture: B1(=1000-B2)
the number of manufactured items: D1(=INT(B1/18*1000)/1000)
promotion: B2(integer variable)
price: B3(integer variable)
demand: B4(=INT(1000*2000/18*EXP(-0.9*B3/18)*(1+B2/500)^2)/1000)
profit: B5(=IF(B4>D1,D1*B3-1000-250,B4*B3+(D1-B4)*14.4-1000-250)),

with the following contraints:
$B$2>=0
$B$2=integer
$B$2<=1000
$B$3=integer
$B$3>=0
$B$4<=$D$1

But it could not find a solution. However, if I modified the content
of D1 and B4 to:
D1(=B1/18)
B4(=2000/18*EXP(-0.9*B3/18)*(1+B2/500)^2))
It finds an unrealistic (fraction is included) local optimum solution.

Could someone give me a hint? Thanks a lot!

 

[Kevin Stecyk]

Hi Dana,

<<However, our scales seem to be different.>>

I didn't pay attention to the scales. I literally just copy and pasted the
equations, and solved.

<<I have no idea where I may have made a mistake.>>

Maybe not a mistake, just a different interpretation of assumptions?

<< This solution doesn't sound correct either. I don't think any company
wound invest $ 1 Million for a possible $335 return.>>

I suspect the goal of the exercise was to determine the solution (operations
type research) as opposed whether the investment met hurdles. Most of the
permissible values were negative on my spreadsheet, only a few of the
permissible values were actually positive. My $335 is probably thousands.
See earlier comment wrt scales.

Again, I am willing to send you my spreadsheet so you can see where we
differ. You can plug your values into the equations to see if there is a
difference. I just need to know your e-mail.

My only comfort is that Wen-Feng got the same result using different
methodologies. So either "fools seldom differ" or we are both correct.

Again, if you provide your email--even in a cryptic form--I am more than
happy to send you my worksheet.

Best regards,
Kevin

Just for feedback, it appears that Tushar and I came up with very similar
results. However, our scales seem to be different. I have no idea where I
may have made a mistake.

We both had the same Promotion:Mine was 265,000

For Price:Mine was... 45.35
These numbers are the same, just scaled differently.

For Profit:Mine was: 221,816.9

Although these numbers are different, they both have the sequence
...218169...Hmmmm??

Anyway, very interesting. Just a thought. With a budget of 1 Million, and
an item of fairly low value, it "appears" to me that a 11 million profit is
rather high. That is a very high rate of return on this investment. I was
expecting about a 10%-20% return on investment. With 1 Million budget, I
was expecting a profit somewhere in the range of $100,000 to $200,000.
The other solution was a profit of $335.82. This solution doesn't sound
correct either. I don't think any company wound invest $ 1 Million for a
possible $335 return.
Again, I am probably wrong. It is just a thought. :>)

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

Tushar,

Your results differ from those of Wen-Feng Hsiao and myself. We arrived at
our results independently using different approaches. I will send you my
spreadsheet, for what it is worth.

Regards,
Kevin

I would take exception to the proposition that the problem was easily
solved in any programming language. Non-linear integer problems are
notoriously difficult -- very, very difficult -- to solve.

However, with a little 'intelligent massaging' of the problem they can
be trivial to solve -- just as this was trivially easy to solve with
XL/Solver.

There is some ambiguity as to whether the fixed manufacturing cost of
250,000 is part of the million dollar budget or not. I assumed it was.

The demand function "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." is what I programmed. However, it seems somewhat suspicious.
I've never heard of demand being a squared-multiplicative 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
correct answer:
manufacture: B1(=1000-B2)
the number of manufactured items: D1(=INT(B1/18*1000)/1000)
promotion: B2(integer variable)
price: B3(integer variable)
demand: B4(=INT(1000*2000/18*EXP(-0.9*B3/18)*(1+B2/500)^2)/1000)
profit: B5(=IF(B4>D1,D1*B3-1000-250,B4*B3+(D1-B4)*14.4-1000-250)),

with the following contraints:
$B$2>=0
$B$2=integer
$B$2<=1000
$B$3=integer
$B$3>=0
$B$4<=$D$1

But it could not find a solution. However, if I modified the content
of D1 and B4 to:
D1(=B1/18)
B4(=2000/18*EXP(-0.9*B3/18)*(1+B2/500)^2))
It finds an unrealistic (fraction is included) local optimum solution.

Could someone give me a hint? Thanks a lot!

 

[Tushar Mehta]

Hi Dana,

My guess is that you ran afoul with some problem that is hard to detect
with the constant adjusting of the scale in the original problem setup.
I completely side-stepped any possible problem by using a non-scaled
setup except where absolutely necessary.

I agree that the original result of $350 seemed ridiculous -- in part
that was what motivated me to look at the problem. Similarly, a $11
million profit is equally -- or more -- ridiculous.

That's when I took a closer look at the demand function. It uses
Demand =Constant * (1+Promotion/1000/500)^2 * 1000. That is way too
bizarre a relationship between promotion and demand. It is a squared
relation implying that demand increases infinitely as the square of the
amount spent on promotion. Almost any marketing course will indicate
otherwise. It is, at best, a monotonic concave function -- a log
function or a square root funtion being examples. More likely is a
concave function with a 1st order derivative that changes sign -- an
example being K1-(x-K2)^2 or a lognormal function or something shaped
(maybe distortedly) along those lines.

--
Regards,

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

 

[Kevin Stecyk]

Tushar,

<<I am fairly comfortable with my solution -- given my understanding of
the problem.>>

I didn't analyze the problem in the manner you have. I simply took it and
plugged away, as you probably saw.

I think we can both be happy with our solutions.

Peace!

Regards,
Kevin

Hi Kevin,

I am fairly comfortable with my solution -- given my understanding of
the problem. And, of course, it is very possible that we have a very
different understanding of the problem.

It is also possible that, depending on how closely you adhered to the
original setup of Wen-Feng Hsiao, you *might* have run afoul of a
possible mistake in all the divisions and multiplications by 1,000 in
the original model. I can't be sure but all the scale changes made my
head spin. So, I side-stepped the entire scaling issue by working with
non-scaled numbers. The only place I factored in the 'in thousands'
scale effect was where absolutely necessary (e.g., the computation of
the demand).

--
Regards,

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

Tushar,

Your results differ from those of Wen-Feng Hsiao and myself. We arrived at
our results independently using different approaches. I will send you my
spreadsheet, for what it is worth.

Regards,
Kevin

I would take exception to the proposition that the problem was easily
solved in any programming language. Non-linear integer problems are
notoriously difficult -- very, very difficult -- to solve.

However, with a little 'intelligent massaging' of the problem they can
be trivial to solve -- just as this was trivially easy to solve with
XL/Solver.

There is some ambiguity as to whether the fixed manufacturing cost of
250,000 is part of the million dollar budget or not. I assumed it was.

The demand function "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." is what I programmed. However, it seems somewhat suspicious.
I've never heard of demand being a squared-multiplicative 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
correct answer:
manufacture: B1(=1000-B2)
the number of manufactured items: D1(=INT(B1/18*1000)/1000)
promotion: B2(integer variable)
price: B3(integer variable)
demand: B4(=INT(1000*2000/18*EXP(-0.9*B3/18)*(1+B2/500)^2)/1000)
profit: B5(=IF(B4>D1,D1*B3-1000-250,B4*B3+(D1-B4)*14.4-1000-250)),

with the following contraints:
$B$2>=0
$B$2=integer
$B$2<=1000
$B$3=integer
$B$3>=0
$B$4<=$D$1

But it could not find a solution. However, if I modified the content
of D1 and B4 to:
D1(=B1/18)
B4(=2000/18*EXP(-0.9*B3/18)*(1+B2/500)^2))
It finds an unrealistic (fraction is included) local optimum solution.

Could someone give me a hint? Thanks a lot!

 

[Tushar Mehta]

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

Tushar,

<<I am fairly comfortable with my solution -- given my understanding of
the problem.>>

I didn't analyze the problem in the manner you have. I simply took it and
plugged away, as you probably saw.

I think we can both be happy with our solutions.

Peace!

Regards,
Kevin

Hi Kevin,

I am fairly comfortable with my solution -- given my understanding of
the problem. And, of course, it is very possible that we have a very
different understanding of the problem.

It is also possible that, depending on how closely you adhered to the
original setup of Wen-Feng Hsiao, you *might* have run afoul of a
possible mistake in all the divisions and multiplications by 1,000 in
the original model. I can't be sure but all the scale changes made my
head spin. So, I side-stepped the entire scaling issue by working with
non-scaled numbers. The only place I factored in the 'in thousands'
scale effect was where absolutely necessary (e.g., the computation of
the demand).

--
Regards,

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

Tushar,

Your results differ from those of Wen-Feng Hsiao and myself. We arrived at
our results independently using different approaches. I will send you my
spreadsheet, for what it is worth.

Regards,
Kevin


I would take exception to the proposition that the problem was easily
solved in any programming language. Non-linear integer problems are
notoriously difficult -- very, very difficult -- to solve.

However, with a little 'intelligent massaging' of the problem they can
be trivial to solve -- just as this was trivially easy to solve with
XL/Solver.

There is some ambiguity as to whether the fixed manufacturing cost of
250,000 is part of the million dollar budget or not. I assumed it was.

The demand function "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." is what I programmed. However, it seems somewhat suspicious.
I've never heard of demand being a squared-multiplicative 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
correct answer:
manufacture: B1(=1000-B2)
the number of manufactured items: D1(=INT(B1/18*1000)/1000)
promotion: B2(integer variable)
price: B3(integer variable)
demand: B4(=INT(1000*2000/18*EXP(-0.9*B3/18)*(1+B2/500)^2)/1000)
profit: B5(=IF(B4>D1,D1*B3-1000-250,B4*B3+(D1-B4)*14.4-1000-250)),

with the following contraints:
$B$2>=0
$B$2=integer
$B$2<=1000
$B$3=integer
$B$3>=0
$B$4<=$D$1

But it could not find a solution. However, if I modified the content
of D1 and B4 to:
D1(=B1/18)
B4(=2000/18*EXP(-0.9*B3/18)*(1+B2/500)^2))
It finds an unrealistic (fraction is included) local optimum
solution.

Could someone give me a hint? Thanks a lot!

 

[Dana DeLouis]

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

Tushar,

<<I am fairly comfortable with my solution -- given my understanding of
the problem.>>

I didn't analyze the problem in the manner you have. I simply took it and
plugged away, as you probably saw.

I think we can both be happy with our solutions.

Peace!

Regards,
Kevin

Hi Kevin,

I am fairly comfortable with my solution -- given my understanding of
the problem. And, of course, it is very possible that we have a very
different understanding of the problem.

It is also possible that, depending on how closely you adhered to the
original setup of Wen-Feng Hsiao, you *might* have run afoul of a
possible mistake in all the divisions and multiplications by 1,000 in
the original model. I can't be sure but all the scale changes made my
head spin. So, I side-stepped the entire scaling issue by working with
non-scaled numbers. The only place I factored in the 'in thousands'
scale effect was where absolutely necessary (e.g., the computation of
the demand).

--
Regards,

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

Tushar,

Your results differ from those of Wen-Feng Hsiao and myself. We

arrived
at

our results independently using different approaches. I will send

you
my

spreadsheet, for what it is worth.

Regards,
Kevin


I would take exception to the proposition that the problem was easily
solved in any programming language. Non-linear integer problems are
notoriously difficult -- very, very difficult -- to solve.

However, with a little 'intelligent massaging' of the problem they can
be trivial to solve -- just as this was trivially easy to solve with
XL/Solver.

There is some ambiguity as to whether the fixed manufacturing cost of
250,000 is part of the million dollar budget or not. I assumed it was.

The demand function "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." is what I programmed. However, it seems somewhat suspicious.
I've never heard of demand being a squared-multiplicative 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
correct answer:
manufacture: B1(=1000-B2)
the number of manufactured items: D1(=INT(B1/18*1000)/1000)
promotion: B2(integer variable)
price: B3(integer variable)
demand: B4(=INT(1000*2000/18*EXP(-0.9*B3/18)*(1+B2/500)^2)/1000)
profit: B5(=IF(B4>D1,D1*B3-1000-250,B4*B3+(D1-B4)*14.4-1000-250)),

with the following contraints:
$B$2>=0
$B$2=integer
$B$2<=1000
$B$3=integer
$B$3>=0
$B$4<=$D$1

But it could not find a solution. However, if I modified the content
of D1 and B4 to:
D1(=B1/18)
B4(=2000/18*EXP(-0.9*B3/18)*(1+B2/500)^2))
It finds an unrealistic (fraction is included) local optimum
solution.

Could someone give me a hint? Thanks a lot!

 

[Dana DeLouis]

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 $335,820.
;>)

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

Tushar,

<<I am fairly comfortable with my solution -- given my understanding of
the problem.>>

I didn't analyze the problem in the manner you have. I simply took it and
plugged away, as you probably saw.

I think we can both be happy with our solutions.

Peace!

Regards,
Kevin

Hi Kevin,

I am fairly comfortable with my solution -- given my understanding of
the problem. And, of course, it is very possible that we have a very
different understanding of the problem.

It is also possible that, depending on how closely you adhered to the
original setup of Wen-Feng Hsiao, you *might* have run afoul of a
possible mistake in all the divisions and multiplications by 1,000 in
the original model. I can't be sure but all the scale changes made my
head spin. So, I side-stepped the entire scaling issue by working with
non-scaled numbers. The only place I factored in the 'in thousands'
scale effect was where absolutely necessary (e.g., the computation of
the demand).

--
Regards,

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

Tushar,

Your results differ from those of Wen-Feng Hsiao and myself. We

arrived
at

our results independently using different approaches. I will send

you
my

spreadsheet, for what it is worth.

Regards,
Kevin


I would take exception to the proposition that the problem was easily
solved in any programming language. Non-linear integer problems are
notoriously difficult -- very, very difficult -- to solve.

However, with a little 'intelligent massaging' of the problem they can
be trivial to solve -- just as this was trivially easy to solve with
XL/Solver.

There is some ambiguity as to whether the fixed manufacturing cost of
250,000 is part of the million dollar budget or not. I assumed it was.

The demand function "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." is what I programmed. However, it seems somewhat suspicious.
I've never heard of demand being a squared-multiplicative 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
correct answer:
manufacture: B1(=1000-B2)
the number of manufactured items: D1(=INT(B1/18*1000)/1000)
promotion: B2(integer variable)
price: B3(integer variable)
demand: B4(=INT(1000*2000/18*EXP(-0.9*B3/18)*(1+B2/500)^2)/1000)
profit: B5(=IF(B4>D1,D1*B3-1000-250,B4*B3+(D1-B4)*14.4-1000-250)),

with the following contraints:
$B$2>=0
$B$2=integer
$B$2<=1000
$B$3=integer
$B$3>=0
$B$4<=$D$1

But it could not find a solution. However, if I modified the content
of D1 and B4 to:
D1(=B1/18)
B4(=2000/18*EXP(-0.9*B3/18)*(1+B2/500)^2))
It finds an unrealistic (fraction is included) local optimum
solution.

Could someone give me a hint? Thanks a lot!