Linear Programming Example (Excel)

Document Sample
Linear Programming Example (Excel) Powered By Docstoc
					                  WHITE CELLS ARE ADJUSTABLE                                                                                                                                             © Kristoffer Burnett - Certified Management Accountant, 2009-2011
                                                                                                                                                                                               Small business solutions at http://www.imperoco.com
                 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
Overhead budget                         2,857,136           <=          2,857,136

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
Description: Linear programming is a method used to determine what mix of resources will maximize revenue or minimize costs.
ImperoCo Business Solutions ImperoCo Business Solutions http://www.imperoco.com
About ImperoCo's mission is to assist small and medium sized businesses in maximizing the efficiency of their operations; thereby increasing their ability to compete more effectively.