You Are Employed At A Pharmaceutical Firm That Produces Two ✓ Solved

You are employed at a pharmaceutical firm that produces two

You are employed at a pharmaceutical firm that produces two specific supplements: a vitamin supplement designed for children and an iodine supplement for thyroid disorders. The production and sales teams need to decide how many units of each supplement to produce in order to maximize profits. Market research indicates there is huge demand for both products and the more you produce, the more you sell. Average profit per unit is $15 for the vitamin supplement and $21 for the iodine supplement. Labor hours per unit are 6 and 7 respectively; machine hours per unit are 7 and 12 respectively. Available machine hours are limited to 50,000 and total labor hours are limited to 30,000. Build a Solver optimization model (linear programming) to determine the optimal production quantities to maximize total profit subject to the labor and machine hour constraints. Develop an answer report explaining your model formulation, solution, sensitivity analysis, and practical recommendations.

Paper For Above Instructions

Executive summary

This report formulates a linear programming (LP) model to determine optimal production quantities for two supplements (Vitamin supplement and Iodine supplement) to maximize profit under labor and machine-hour constraints. Using the given unit profits and resource usage, the LP solution and sensitivity analysis are provided along with practical recommendations for implementation in Excel Solver and interpretation of dual values.

Model formulation

Decision variables:

• x1 = number of units of the vitamin supplement to produce (units)
• x2 = number of units of the iodine supplement to produce (units)

Objective (maximize total profit):

Maximize Z = 15x1 + 21x2

Constraints (resource limits):

• Labor hours: 6x1 + 7x2 ≤ 30,000
• Machine hours: 7x1 + 12x2 ≤ 50,000
• Nonnegativity: x1 ≥ 0, x2 ≥ 0

Solver setup in Excel

Implementation in Excel uses three main cells for decision variables (cells for x1 and x2) and a cell computing the objective Z = 15x1 + 21x2. Two constraint cells compute labor use (6x1 + 7x2) and machine use (7x1 + 12x2). In Solver, set the objective cell to maximize, by changing the x1 and x2 cells, add constraints: labor cell ≤ 30000, machine cell ≤ 50000, x1 ≥ 0, x2 ≥ 0. Use the Simplex LP solving method because the model is linear (Winston, 2004; Hillier & Lieberman, 2015).

Analytical solution and results

Corner-point analysis of the LP identifies candidate solutions: produce only vitamin (x2=0), produce only iodine (x1=0), or the intersection where both resource constraints bind. Solving the two binding constraints simultaneously:

6x1 + 7x2 = 30,000 and 7x1 + 12x2 = 50,000 yields x1 ≈ 434.78 units (vitamin) and x2 ≈ 3,913.04 units (iodine). Objective value at intersection: Z ≈ $88,695.65. For comparison, producing only vitamin (x1=5000, x2=0) yields Z = $75,000; producing only iodine (x1=0, x2≈4166.67) yields Z = $87,500. Thus the intersection solution maximizes profit under the given constraints.

Sensitivity analysis (dual values and interpretation)

Dual variables (shadow prices) measure the marginal value of additional resources. Solve the dual system:

6λ1 + 7λ2 = 15 and 7λ1 + 12λ2 = 21, which gives λ1 ≈ 1.4348 ($ per labor hour) and λ2 ≈ 0.9130 ($ per machine hour). Interpretation: while the solution basis remains unchanged, an additional hour of labor would increase profit by approximately $1.43 and an additional machine hour would increase profit by approximately $0.91 (Bertsimas & Tsitsiklis, 1997; Taha, 2017).

These shadow prices guide investment: if the firm can acquire labor hours at less than $1.43 per hour or machine hours at less than $0.91 per hour, it would be profitable to obtain more capacity. Ranges of optimality (i.e., how much objective coefficients could change before solution shifts) can be obtained from Solver's sensitivity report for more precise guidance (Winston, 2004; Microsoft, 2023).

Discrete units and practical rounding

Because production typically requires integer units, rounding is necessary. Rounding to the nearest whole unit while respecting constraints yields feasible alternatives: x1 = 434, x2 = 3,913 uses labor 6434 + 73,913 = 29,995 hrs (within limit) and machine 7434 + 123,913 = 50,000 hrs (binding). Profit = 15434 + 213,913 = $88,683, only $12 less than the continuous optimum — negligible in practice. If production must be integer but large-scale, the continuous LP values are very useful as near-optimal guides and can be refined with integer programming if necessary (Hillier & Lieberman, 2015).

Implementation recommendations

• Implement the exact LP in Excel Solver with Simplex LP. Save Solver sensitivity reports to capture shadow prices and allowable increases/decreases (Microsoft, 2023).
• If production must be integer at small scales, use Excel's Integer (or binary) options or an integer programming solver to obtain integral optimal solutions with similar structure (Winston, 2004).
• Monitor binding constraints: machine hours are likely to be binding first as result showed; consider equipment investments or outsourcing if scalable production growth is planned (Bertsimas & Tsitsiklis, 1997).
• Use dual values strategically to evaluate whether buying additional labor or machine time (e.g., overtime or renting machines) is profitable. Only pursue additional resources if their cost is less than the shadow price.
• Perform scenario analysis: test demand, profit margin, and resource-availability scenarios to assess robustness and plan for supply chain risk (Davenport & Harris, 2007).

Conclusion

Using a prescriptive analytics LP model provides a clear allocation decision: produce approximately 435 units of the vitamin supplement and 3,913 units of the iodine supplement to maximize profit under the current labor and machine-hour constraints (continuous solution). Shadow prices indicate strong marginal value for additional labor (≈$1.43/hour) and moderate value for machine capacity (≈$0.91/hour). Implementing the model in Excel Solver and reviewing sensitivity reports will allow the firm to make data-driven capacity and production decisions. This prescriptive approach aligns operational choices with profitability and supports strategic capacity investments when economically justified (Winston, 2004; Hillier & Lieberman, 2015).

References

• Bertsimas, D., & Tsitsiklis, J. N. (1997). Introduction to Linear Optimization. Athena Scientific.
• Davenport, T. H., & Harris, J. G. (2007). Competing on Analytics: The New Science of Winning. Harvard Business School Press.
• Hillier, F. S., & Lieberman, G. J. (2015). Introduction to Operations Research (10th ed.). McGraw-Hill Education.
• Microsoft. (2023). Use Solver to perform simple optimization in Excel. Microsoft Docs. https://support.microsoft.com/
• Taha, H. A. (2017). Operations Research: An Introduction. Pearson.
• Winston, W. L. (2004). Operations Research: Applications and Algorithms. Cengage Learning.
• Shmueli, G., Patel, N. R., & Bruce, P. C. (2010). Data Mining for Business Intelligence: Concepts, Techniques, and Applications in Microsoft Office Excel with XLMiner. Wiley.
• Simchi-Levi, D., Kaminsky, P., & Simchi-Levi, E. (2007). Designing and Managing the Supply Chain: Concepts, Strategies and Case Studies. McGraw-Hill.
• Chvatal, V. (1983). Linear Programming. W. H. Freeman.
• Boyd, S., & Vandenberghe, L. (2004). Convex Optimization. Cambridge University Press.