Now that we have shown how Excel can be used to solve nonlinear problems, we can look at more complex problemsfor example, a problem with more than one constraint. Consider the Western Clothing Company example presented earlier, except now the company produces two kinds of jeans, designer and straight-leg, and production is subject to resource constraints for denim cloth, cutting time, and sewing time. The company sells its jeans to several upscale clothing store chains, and sales demand is dependent on the price at which the company sells the jeans. The demand for designer jeans ( x x x where p p The cost of producing designer jeans is $12 per pair, and the cost of producing straight-leg jeans is $9 per pair; thus, the objective function for profit is maximize Z = ( p The production of jeans is subject to the following resource constraints for available cloth, cutting time, and sewing time:
The complete nonlinear programming model formulation is summarized as follows : maximize Z = ( p subject to
where x x p p Notice that the decision variables for this problem are p Exhibit 10.9 shows an Excel spreadsheet set up to solve our Western Clothing Company example with constraints. The decision variables for price are contained in cells D5:D6 . The formula for demand for designer jeans is contained in cell C5 and is shown on the formula bar at the top of the screen. The formula for total profit, =SUMPRODUCT ( C5:C6,E5:E6 ), is contained in cell E7. The formulas for the resource constraints are contained in cells C11, C12, and C13, and the resource availabilities for each constraint are in cells D11:D13 . The Solver Parameters window and solution are shown in Exhibit 10.10 and Exhibit 10.11, respectively. ## Exhibit 10.9.## Exhibit 10.10.
## Exhibit 10.11. |

Introduction to Management Science (10th Edition)

ISBN: 0136064361

EAN: 2147483647

EAN: 2147483647

Year: 2006

Pages: 358

Pages: 358

Authors: Bernard W. Taylor

Similar book on Amazon

flylib.com © 2008-2017.

If you may any questions please contact us: flylib@qtcs.net

If you may any questions please contact us: flylib@qtcs.net