Linear Optimization Model Problem Excel Solver, Coursework Example

The reduced price tells what quantity the target coefficient must be reduced for a non-negative variable that’s zero within the best solution to become positive. The best answer for medium and large air compressors is that the lower bounds for medium compressors are a minimum or equal to 6,200, and medium air compressors are a minimum or equal to 2,600, respectively (Evans, 2013). This means the Klein Company should manufacture a minimum of 6200 medium compressors and 2600 large compressors (Object 1). The minimum small air compressors that need to be manufactured are 16157. This could be seen in the solver option (Object 1).

The profit is not enough with the production of large and medium air compressors according to the best solution (Evans, 2013). This suggests that the minimum of these should be manufactured. How much the target function can change is related to the set value. Increasing the RHS of the constraint by one, the set price will indicate the value of the objective function (Evans, 2010). The set price will always be null if the coercion incorporates a positive leeway. The profit may increase by $14.64 if there is an hour longer for painting available. The set value of $14.64 indicates this. When the available painting hours are changed to 46801, and it is solved again, the entire proceeds will increase by $14.64, as foreseen. This is indicated by the set value that identifies the value of having one additional unit of resources (Lin, 2010).

Memo to the Production Manager

The company requires painting capacity to be raised. $14.64 is the price for each paint unit that is short. Based on the value, there is negativity within the sensitivity report because the profits are being pulled down (Object 2 Microsoft Excel Sensitivity Report). If painting time is charged at a cheaper rate, and everything else stayed constant, the profit will rise to 3020 units of paint time.  With additional 3,020 units of paint time and everything else is constant, the profit will increase to $695,442.86 (Object 3 Total Profits).

References

Evans, J. R. (2010). Statistics, data analysis, and decision modeling. Boston: Prentice-Hall.

Evans, J. R. (2013). Business analytics: Methods, models, and decisions. Boston: Pearson.

Lin, C. (2010). Computing shadow prices/costs of degenerate LP problems with reduced simplex tables. Expert Systems with Applications, 37(8), 5848-5855. doi:10.1016/j.eswa.2010.02.021

Linear-Optimization Model. (n.d.). Springer Reference. Doi: 10.1007/springerreference_1842

Shadow prices. (n.d.). SpringerReference. Doi: 10.1007/springerreference_6145