A few days ago
tmac_spirit

excel solver problem?

i have this task that uses Solver in Excel to create an optimisation model. please help..

let’s say i have a company and i have $10,000 in capital.

my company produces 2 products, call them A and B.

Product A costs $0.80/unit to produce and will sell for $7.00/unit.

Product B costs $0.75/unit to produce and will sell for $8.00/unit.

it has been said that advertising helps in creating demand for the products, and therefore it is a MUST to advertise the products.

$1 spent on advertising Product A will yield 5 demands for the product.

$1 spent on advertising Product B will yield 8 demands for the product.

it is also a condition that 40%-70% of the total production of each year must consist of Product A.

the question is:

how many Product A and Product B should be produced to maximise profit?

how much should be spent on advertising both products each year?

suppose that i have $10,000 in capital to fund the production and advertising of both products for a year.

thx!

Top 2 Answers
A few days ago
voyager

Favorite Answer

A = 4324

B = 6487

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

Profit per unit

A = 7- 0.8-(1/5) = 6

B = 8-0.75-(1/8) = 7.125

Since you get more profits from B, you should produce A at its minimum i.e 40%

In Solver, select the total cost cell for the “set the target cell” (10000). If you have quantities in cell B1,B2 and B3 where B1 is the number of units from A, B2 the number of units from B and B3 the total, insert the following formula

B3=B1+B2

B1=B2/0.6*0.4

B2 can be any number you want .

But B2 should be the cell that you select for “By changing cell”

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

If you need more info, email me

2

A few days ago
Anonymous
am nowhere at your question! as a lay man what would be the limit at your “maximise profit”(mean limit of your profit or it’s open)?
0