Question

42. A pharmaceutical company manufactures two drugs at Los Angeles and Indianapolis. The cost of manufacturing a pound of each drug depends on the location, as indicated in the file P13_42.xlsx. The machine time (in hours) required to produce a pound of each drug at each city is also shown in this table. The company must produce at least 1000 pounds per week of drug 1 and at least 2000 pounds per week of drug 2. It has 500 hours per week of machine time at Indianapolis and 400 hours per week at Los Angeles. a. Determine how the company can minimize the cost of producing the required drugs.

Answer 1

Consider the Production plan of pharmaceutical company manufacturing two drugs at Los Angles and Indianapolis. Unit cost and Machine time is given in the data below. The company must produce 1000 pounds of drug 1 and 2000 pounds of drug 2. Excel sheet is implemented as follows Pharmaceutical data Unit cost Machine time Drug 1 Drug 2 Indianapolis Los Angeles Indianapolis Los Angeles $4.10 $4.00 $4.50 $5.20 0.20 0.24 0.30 0.33 Units Produced 0 1000 1666 334 Constraints Drug 1 Drug 2 1000 >= 2000 >= 1000 2000 Machine Time Indianapolis Los Angeles 500 499.8 >= 350.22 >= 400 Total cost 13233.8 Changing cells: Units of drug 1 and drug 2 produced in Indianapolis and Los Angles is assigned as changing cells. Constraints: Units of Drug 1 produced = 18+C8 and units of Drug 2 produced =D8+E8. Company must produce 1000 pounds of drug 1 and 2000 pounds of drug 2. Hence the following constraints are added. Drug 1 > 1000 Drug 2 > 2000 Machine time in Indianapolis is, = B8 x B6+ D8 x D6 And at Los Angles, =C8x C6+E8x E6

It is given that 500 hours of machine time is available at Indianapolis and 400 hours is available at Los Angeles. Hence the following constraints are added. Indianapolis 500 Los Angles > 400 Objective cell: Total cost is obtained by formula = SUMPRODUCT (B5:E5, B8: E8). Total cost is minimized to obtain optimal solution. Obtain the minimum cost of production by using excel solver as follows Step 1: Select excel options-> Add Ins. Select Solver Add In. Screenshot is shown. Excel Options Popular View and manage Microsoft Office add-ins. Formulas Proofing Add-ins Save Location Type Name Active Application Add-ins Solver Add-in Advanced C..ce Office Library SOLVER SOLVER.XLAM Excel Add-in Customize Add-Ins Trust Center Resources Inactive Application Add-ins Analysis ToolPak Analysis ToolPak - VBA Conditional Sum Wizard Custom XML Data Date Smart tag lists) Euro Currency Tools Financial Symbol (Smart tag lists) Headers and Footers Hidden Rows and Columns Hidden Worksheets Internet Assistant VBA Invisible Content Lookup Wizard Person Name (Outlook e-mail recipients) C:\...ce Office12\Library Analysis ANALYS32.XLL C:..Office1 Library Analysis ATPVBAEN.XLAM sumiftam Cl.es Microsoft Office\Office12\OFFRHD.DLL C:\..illes microsoft shared Smart Tag MOFL.DLL eurotool.xam C:\...iles microsoft shared Smart Tag MOFL.DLL C..es Microsoft Office Office12OFFRHD.DLL C:\..es Microsoft Office Office12\OFFRHD.DLL C:\..es Microsoft Office Office12 OFFRHD.DLL C:\..rosoft Office Office12\Library HTMLXLAM C.es Microsoft Office Office12OFFRHD.DLL look up.clam C:\...es microsoft shared Smart Tag FNAME.DLL Excel Add-in Excel Add-in Excel Add-in Document Inspector Smart Tag Excel Add-in Smart Tag Document Inspector Document Inspector Document Inspector Excel Add-in Document Inspector Excel Add-in Smart Tag Document Related Add-ins No Document Related Add-ins Disabled Application Add-ins Add-in Solver Add-in Publisher Location: C:\Program Files\Microsoft Office\Office12\Library SOLVERISOLVER.XLAM Description: Tool for optimization and equation solving Manage: Excel Add-ins Go... ок | Cancel Step 2: Go to Data > Solver. In the pop-up window, enter total cost as objective cell. Select Min.

Solver Parameters Solve Total_cos Min Value of: 0 Set Target Cell: Equal To: Max By Changing Cells: Units_Produced Close ERE Guess Options Add Subject to the Constraints: Drug_1 >= $D$11 Drug_2 >= $D$12 Indianapolis <= $D$15 Los_Angeles <= $D$16 Units_Produced = integer Change Reset All Delete Help - Step 4: Select Units produced in changing cells. Step 5: Select ADD to obtain the following pop up box. Enter constraints and select OK. Add Constraint Cell Reference: Constraint: OK Cancel Add Help

Step 6: Select options. Tick Assume linear Model and Assume Non-Negative. Solver Options Max Time: seconds 100 100 OOK Cancel Iterations: Precision: 0.000001 Load Model... | Save Model... Convergence: 0.0001 Help Assume Linear Model Assume Non-Negative Estimates Derivatives Tangent Forward Quadratic Central Use Automatic Scaling Show Iteration Results Search Newton Conjugate Step 7: Select Solve. Now the solver searches through a number of possible solutions until it finds the optimal solution. The dialog box for solver solution is given below.

Solver Results Solver found a solution. All constraints and optimality conditions are satisfied. Reports Answer Sensitivity Limits Keep Solver Solution O Restore Original Values OK Cancel Save Scenario... | Help Select Keep Solver Solution and Select OK.

Thus the solver replaces the assumed values in the changing cells with the optimal solution obtained. Hence from the solver solution we obtain the following solution Drug 1 Drug 2 Indianapolis Los Angeles Indianapolis Los Angeles Units Produced 0 1000 1666 334 From the above table it can conclude that Indianapolis produced 0 units of drug 1, on the other hand Los Angeles produced 1000 units of drug 1. However it can be seen that Indianapolis produced 1666 units of drug 2. That is larger than 334 units of drug 2 produced by Los Angeles. Minimum Total cost of production to produce Drug 1 and Drug 2 in both cities that are Indianapolis and Los Angeles is $13233.8

Now perform sensitivity analysis to investigate how much the company would be willing to pay to purchase extra machine hours. Solver Table is used as follows. Step 1: Extract Solver Table.xlam to the location C:\Program Files\Microsoft Office\Office12Library Solver Table. Select Excel Options -> Add Ins. In the Add Ins window shown below select browse and select file Solvet Table xlam in Library folder.

Add-Ins X Ок — Add-Ins available: Analysis ToolPak Analysis ToolPak - VBA Conditional Sum Wizard Euro Currency Tools Internet Assistant VBA Lookup Wizard V Solver Add-in V Solvertable Cancel Browse... Automation... Solver Add-in Tool for optimization and equation solving

Step 2: Solve the Optimization problem using Solver. Step 3: Select Solver Table from menu and Click Run Solver Table Step 4: In the pop up window select two-way table and click OK. Type of table x ок You can create a oneway or twoway table, depending on whether you want to test the sensitivity of outputs to one or two inputs. Cancel - Type of table Oneway table Twoway table

Step 5: Enter cell C3 in Input 1 cell and C4 in Input 2 cell. Enter descriptive name for input cell. Screenshot is shown below. Parameters for twoway table ок Specify the following information about the inputs to be varied and the outputs to be captured. Input 1 will be put along the left side of the table, input2 along the top. Cancel Input1 cell: $C$3 Input2 cell: SC$4 (Optional) Descriptive name for input 1: Indianapolis (Optional) Descriptive name for input2: Los Angles Values of input to use for table Values of input to use for table Base input values on following: Minimum value: 10 Maximum value: 50 Increment: 10 Base input values on following: Minimum value: 10 Maximum value: 50 Increment: 10 Use the values from the following range: Input value range: Use the values from the following range: Input value range: Use the values below (separate with commas) Input values: 1 Use the values below (separate with commas) Input values: Output cel(s): 58$21 Note about specifying output cells: The safest way to select multiple output cells or ranges is to put your finger on the Ctrl key and then drag as many output cell ranges as you like. This will automatically insert commas between the ranges you select. Extra hours for Indianapolis and Los Angles is increased from 10 to 50 in increments of 10. Select B21 in Output cell. Step 6: Finally select OK to obtain the result. From the sensitivity analysis we find that cost of production increases when extra hours are increased in Indianapolis whereas cost decreases when extra hours are added in Los Angles. Values obtained are given below.

Los Angles Indianapolis 10 20 130 140 50 13210 13210 13210 13210 13210 13186.67 13186.67 13186.67 13186.67 13186.67 13163.33 13163.33 13163.33 13163.33 13163.33 13140 13140 13140 13140 13140 13116.67 13116.67 13116.67 13116.67 13116.67 Chart of Total Cost versus Extra hours in Los Angeles is given below. Sensitivity of $B$21 to Los Angles 13210 13210 13210 13210 13210 13210 13210 13210 13210 13210 10 20 40 50 30 Los Angles ($C$4) In the chart given above, it can conclude that between working hours 10 to 20 total cost is increasing with higher rate, between working hours 20 to 30 total cost is increasing with lower rate and after that total cost is constant for increasing working hours.

Chart of Total Cost versus Extra hours in Indianapolis is given below Sensitivity of $B$21 to Indianapolis 13250 13200 13150 13100 13050 + 10 20 40 50 30 Indianapolis ($C$3) From the above chart it can conclude that total cost is decreasing with extra working hours There is a negative correlation between these two variables.