Question: Linear programming modeling and solvertable
Acme Manufacturing makes a variety of household appliances at a single manufacturing facility. The expected demand for one of these appliances during the next 4 months is shown in the following table along with the expected production costs and the expected capacity for producing these items.
| Month | ||||
| 1 | 2 | 3 | 4 | |
| Demand | 420 | 580 | 310 | 540 |
| Production Cost | $49.00 | $45.00 | $46.00 | $47.00 |
| Production Capacity | 500 | 520 | 450 | 550 |
Acme estimates it costs $1.50 per month for each unit of this appliance carried in inventory at the end of each month. Currently, Acme has 120 units in inventory on hand for this product. To maintain a level workforce, the company wants to produce at least 400 units per month. They also want to maintain safety stock of at least 50 units per month (minimum inventory at the end of the month). Acme wants to determine how many of each appliance to manufacture during each of the next 4 months to meet the expected demand at the lowest possible total cost.
- Provide the complete linear programing formulation. Clearly specify decision variables, objective function and constraints.
- Build a model in Excel and paste a screenshot here. Use “FORMULATEXT” in your model to show calculations.
- According to Excel Solver, what is the optimal production plan for Acme Manufacturing? What is the minimum total cost?
- Use SolverTable to investigate the effect on the total cost of changes in the initial inventory from 0 units to 120 units in increments of 20.
Question 2
A company manufactures mechanical heart valves from the heart valves of pigs. Different heart operations require valves of different sizes. The company purchases pig valves from three different suppliers. The cost and size mix of the valves purchased from each supplier are given in the following table.
| Cost/valve | % small | % medium | % large | |
| Supplier 1 | $20 | 40 | 40 | 20 |
| Supplier 2 | $16 | 30 | 35 | 35 |
| Supplier 3 | $12 | 20 | 20 | 60 |
Each month, the company places an order with each supplier. At least 500 large, 300 medium and 300 small valves must be purchased each month. Because of the limited availability of pig valves, at most 500 valves per month can be purchased from each supplier.
- Provide the complete linear programing formulation. Clearly specify decision variables, objective function and constraints.
- Build a model in Excel and paste a screenshot here. Use “FORMULATEXT” in your model to show calculations.
- Use Solver to determine how the company can minimize the cost of acquiring the needed valves. What is the optimal order plan? What is the minimum total cost?
- Use SolverTable to investigate the effect on total cost of increasing its minimal purchase requirements each month. Specifically, see how the total cost changes as the minimal purchase requirements of large, medium, and small valves all increase from their original values by the same percentage. Revise your model so that SolverTable can be used to investigate these changes when the percentage increase varies from 2% to 20% in increments of 2%
Do you have a similar assignment and would want someone to complete it for you? Click on the ORDER NOW option to get instant services. We assure you of a well written and plagiarism free papers delivered within your specified deadline.