6-1 Assignment

profilehulksmashech
Modulesix.xlsx

Solver Part 1

Part 1
Table Chair
Profit totals $0.00 $0.00 $0.00
Volume 0 0
Profit per unit $63.60 $17.40
Selling price of table $129.00
Selling price of chair $35.00
Materials/Fabrication Needed for a Table Amount Amount Cost Unit Total Constraints
Metal tubing: 22 feet @ $0.10/linear foot A 22 $0.10 $2.20 Metal Tubing 0 1200
Plastic sheet: 16 sqft @ $0.20/square foot B 16 $0.20 $3.20 Plastic Sheet 0 700
Fabrication minutes C 15 $4.00 $60.00
Cost of table $65.40 Fabrication 0 480
Materials/Fabrication Needed for a Chair
Metal tubing: 10 feet D 10 $0.10 $1.00 Min number 0 4
Plastic sheet: 3 sqft E 3 $0.20 $0.60 Min number 0 10
Fabrication minutes F 4 $4.00 $16.00
Cost of chair $17.60
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 have 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.

Solver Parts 2 & 3

Table Chair
Profit totals $0.00 $0.00 $0.00
Volume 0 0
Profit per unit $63.60 $17.40
Selling price of table $129.00
Selling price of chair $35.00
Materials/Fabrication Needed for a Table Amount Amount Cost Unit Total Constraints
Metal tubing: 22 feet @ $0.10/linear foot A 22 $0.10 $2.20 Metal Tubing 0 1200
Plastic sheet: 16 sqft @ $0.20/square foot B 16 $0.20 $3.20 Plastic Sheet 0 700
Fabrication minutes C 15 $4.00 $60.00
Cost of table $65.40 Fabrication 0 480
Materials/Fabrication Needed for a Chair
Metal tubing: 10 feet D 10 $0.10 $1.00 Min number 0 4
Plastic sheet: 3 sqft E 3 $0.20 $0.60 Min number 0 10
Fabrication minutes F 4 $4.00 $16.00
Cost of chair $17.60
Part 2
From the Solver dialog box, run a sensitivity report and a limits report, and explain what they indicate.
Part 3a
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.
# Tables # Chairs Total profit
Part 3b
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.
# Tables # Chairs Total profit

Solver Part 4

Part 4
Create spreadsheets and use Solver to determine the correct volumes to be produced 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).