Maximization Manufacturing Through Linear Programming in Wood Furniture

Assignment Task

 Overview

This exercise is designed to give you experience practicing using Solver before your final project submission, which is due in Module Seven. It’s recommended that you complete this assignment as early as possible. That way, you’ll have time to post to the General Questions discussion if you have any questions or problems.

Part 1

In Part 1 of this exercise, you see a spreadsheet that has been set up to use Solver. We are using Solver to decide how many tables and how many chairs we should make in order to produce the most money. Each table that is produced requires 22 linear feet of metal tubing, 16 square feet of plastic sheet, and 15 minutes of fabrication time. Management has specified that we need to produce at least 4 tables. Each chair that is produced requires 10 linear feet of metal tubing, 3 square feet of plastic sheet, and 4 minutes of fabrication time. Management has specified that we need to produce at least 10 chairs. Metal tubing costs $0.10 per foot, plastic sheet costs $0.20 per square foot, and fabrication time costs $4 per minute.

The table with gray cells simply totals the cost of producing each item. The table in blue calculates the profit made on each unit, the profit made after a specific volume is produced, and finally (in red) the total profit from producing both tables and chairs. The table in green tracks the amount of materials and fabrication consumed to produce the volume that will be decided. Lastly, the table in yellow indicates what materials and fabrication time are available, as well as minimums set by management.

Click each cell to observe the programming that was used, but do not change anything. Other programming could've been used to simplify the tables; however, this has been used to illustrate the process. After you have reviewed the programming, click the red cell, go to the ribbon bar, and click the Data tab. Then click the Solver button. (If you do not see Solver as an option, contact your instructor for guidance.) Once you click the Solver button, the Solver Parameters dialog box will open, and you can observe which cell has been identified as the target cell, which cells Solver will change, and how the constraints have been entered. Do not run Solver at this time.

Part 2

From the Solver dialog box, run a sensitivity report and a limits report, and explain what they indicate

Part 3

a. Now alter the management decision limiting fabrication to 480 minutes. Increase it to 600, and increase the minimum number of chairs to be produced to 16. Run Solver again and list the number of tables and chairs that should be produced.

b. Too many tables have been rejected by quality assurance, and the production line for tables will be slowed, increasing fabrication time to 26 minutes. However, they also found a way to decrease fabrication time on the chairs to 3 minutes. Use the original constraints for fabrication time (480 minutes) and the minimum tables (4) and chairs (10). Run Solver again and list the table showing the tables and chairs that should be produced.

Part 4

Create spreadsheets and use Solver to determine the correct miles for each truck to travel to minimize cost for the following problem. Your company has two trucks that it wishes to use on a specific contract. One is a new truck the company is making payments on, and one is an old truck that is fully paid for. The new truck’s costs per mile are as follows: 54₵ (fuel/additives), 24₵ (truck payments), 36₵ (driver), 12₵ (repairs), and 1₵ (misc.). The old truck’s costs are 60₵ (fuel/additives), 0₵ (truck payments), 32₵ (rookie driver), 24₵ (repairs), and 1₵ (misc.). The company knows that truck breakdowns lose customers, so it has capped estimated repair costs at $14,000. The total distance involved is 90,000 miles (to be divided between the two trucks). 

This Management has been solved by our PhD Experts at My Uni Paper.