Excel

today I will discuss "Product Mix" using solver. I will try to discuss this as briefly as possible.
I will discuss the process using a simple example. Suppose, a company has six products and each product's consumption for raw material and labor in shown in the table -1.

for example to make 160 pounds of product 2 requires 5 unit of labor and 2.6 unit of raw materials. demand column shows demand for the month for each product. also suppose , we have labor available to use 4500unit and raw materials to be used 1600unit. unit profits are calculated as sales-variable cost.
the question is, what should be the optimum product mix to maximize profit by utilizing available resources for the month????

Manually this problem could be solved by trial and error basis which is time consuming and inefficient. We will see how we can get the solution with few clicks by using SOLVER program in excel.

About the Solver:

the solver program has 04 basic parameters as follows:

*set target cell:
this is the cell where our goal is achieved. in the example our goal is to achieve maximum profit; hence the cell reference is: D12. Total profit is calculated in D12 by adding profits of different (all 7) products.

*equal to:
this is the parameter for our goal, that is if we want to achieve a fixed value we select value of option and put the desired value; if we select Max- it will calculate maximum value for the target cell.

*by changing cell:
if profit is derived as: total production x profit per unit = total production X (sales-variable cost)per unit, we can get our result by changing any of the cells in the formula. In our example we want a product mix, therefore our changing cells should reflect D2:I2 as they are the no of product for each category.

*Adding constraints:
simply constraints are conditions. one of the conditions could be availability of labor- you cant use more labor than you have in your hand. the other could be demand- you could restrict solver not to produce more of one unit which exceeds it market demand.

our example: to begin, open solver and then do the followings:

1. click on target cells and set it to D12
2. set equal to : Max
3.By changing cell: select from D2:I2

4.adding constraints: click Add button and the following window will appear:

first we want to add labor constraints: labor usage cannot exceed labor available. the argument is labor usage <= labor available.
in the cell reference we select the cell where total labor usage [=sumproduct(D2:I2,D4:I4)] is located (D14); we put argument box <= ; and constraints box we select cell F14 (available resources).

for raw material we do similar steps. raw material usage <= labor available.

for demand : we don't want to produce any product which exceeds demand: pound made <= demand. we will add in constraint box as : D2:i2 <= D8:I8.

click ok, and the solver window will appear similar to this:

for accuracy click options button and select as follows:

**assume linear model
** assume non negative
press ok.
press solve and the solution will arrive as follows:

so we need to produce 596.667 pounds of product 4 and 1084 pounds of product 5 to maximize our profit up to $6,625.20 Note, all the constraints are met, productions are within demand and within resources available.

if you have further question or want to comment, send email: admin@syedkollol.com