# Linear Programming Example (Excel)

Document Sample

```					                  WHITE CELLS ARE ADJUSTABLE                                                                                                                                             © Kristoffer Burnett - Certified Management Accountant, 2009-2011
Linear Programming Example
1

Objective

Gross profit maximization           \$      6,006,821

Total sales                         \$    17,206,233
Gross profit percentage                       34.9%

Decision variables
2
Price          Matl Cost       DL Cost        OH Cost        Gross Margin Optimum           Gross Margin
Item                   Per Unit         Per Unit       Per Unit       Per Unit        Percentage  Quantity             Dollars
Item1               \$        168.00    \$     25.56    \$     40.40    \$      36.66            38.9%   16,400         \$    1,072,232
Item2                        289.00        131.79           22.90           27.92            36.8%   12,100              1,287,319
Item3                        170.00          46.59          36.63           30.71            33.0%   25,800              1,446,606
Item4                        282.00          83.73          48.39           40.35            38.8%   12,900              1,412,937
Item5                        110.00          29.91          27.80           22.72            26.9%   26,639                787,727

Constraints

Amount          Operator        Hurdle
Maximum demand Item1                     16,400            <=              16,400 The maximum quantity expected to sell for each given item
Maximum demand Item2                     12,100            <=              13,400
Maximum demand Item3                     25,800            <=              25,800
Maximum demand Item4                     12,900            <=              12,900
Maximum demand Item5                     26,639            <=              33,500

Minimum demand all items                 93,839             >=             76,500 The minimum acceptable level of sales. Perhaps due to a shipping contract

Maximum capacity Item1                   16,400             <=             20,500 The maximum amount of each item that can be produced in the applicable time period. Perhaps due to material, labor, or machinery constraints
Maximum capacity Item2                   12,100             <=             12,100
Maximum capacity Item3                   25,800             <=             27,900
Maximum capacity Item4                   12,900             <=             15,400
Maximum capacity Item5                   26,639             <=             33,000

Direct material budget                  5,092,766           <=          5,183,174 Budgetary constraints
Direct labor budget                     3,249,510           <=          3,250,068

Non-negativity requirement Item1         16,400             >=                  -   The non-negativity constraint represents the logical fact that a negative quantity can not be produced
Non-negativity requirement Item2         12,100             >=                  -
Non-negativity requirement Item3         25,800             >=                  -
Non-negativity requirement Item4         12,900             >=                  -
Non-negativity requirement Item5         26,639             >=                  -

\$30,000                                                            Quantity and Gross Margin                                                         45.0%

40.0%

\$25,000

35.0%

\$20,000                                                                                                                                              30.0%

Optimum
25.0%             Quantity
\$15,000
Gross
20.0%             Margin
Percentage

\$10,000                                                                                                                                              15.0%

10.0%

\$5,000

5.0%

\$0                                                                                                                                           0.0%

Notes:

1 Changes can be made to this worksheet and the objective can be recalculated. In order to recalculate, the Solver Add-in will have to be used.
Click here to learn how to install and use Solver: Perform What-If Analysis with the Excel 2007 Solver Tool - Excel - Office.com
2 The optimum quantity is the amounts that will be adjusted (by Solver) in order to maximize gross profit.

```
DOCUMENT INFO
Shared By:
Categories:
Stats:
 views: 374 posted: 6/9/2010 language: English pages: 1
Description: Linear programming is a method used to determine what mix of resources will maximize revenue or minimize costs.