QSO320 Module 6 Assignment
xlsx
keyboard_arrow_up
School
Oklahoma Baptist University *
*We aren’t endorsed by this school
Course
320
Subject
Industrial Engineering
Date
Jan 9, 2024
Type
xlsx
Pages
17
Uploaded by emilyadler3
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
Amount
Amount
Cost Unit Total
Constraints
A
22
$0.10
$2.20
Metal Tubing
0
1200
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
Metal tubing: 10 feet
D
10
$0.10
$1.00
Min number
0
4
Plastic sheet: 3 sqf
E
3
$0.20
$0.60
Min number
0
10
Fabrication minutes
F
4
$4.00
$16.00
Cost of chair
$17.60
Materials/Fabrication Needed for a Table
Metal tubing: 22 feet @ $0.10/linear foot
Plastic sheet: 16 sqf @ $0.20/square foot
Materials/Fabrication Needed for a Chair
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 afer 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. Afer 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.
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
- Access to all documents
- Unlimited textbook solutions
- 24/7 expert homework help
Microsoft Excel 16.0 Sensitivity Report
Worksheet: QSO320 Module 6 Assignment Report Created: 10/5/2023 Variable Cells
Final
Reduced
Cell
Name
Value
Gradient
$C$5
Volume Table
4
0
$D$5
Volume Chair
105
0
Constraints
Final
Lagrange
Cell
Name
Value
Multiplier
$I$12
Metal Tubing Constraints
1138
0
$I$13
Plastic Sheet Constraints
379
0
$I$15
Fabrication Constraints
480 4.3499999046
$I$18
Min number Constraints
4 -1.6500000954
$I$19
Min number Constraints
105
0
Microsoft Excel 16.0 Sensitivity Report
Worksheet: QSO320 Module 6 Assignment Report Created: 10/5/2023 Objective
Cell
Name
Value
$E$4
Profit totals
$2,081.40
Variable
Lower
Objective
Upper
Objective
Cell
Name
Value
Limit
Result
Limit
Result
$C$5
Volume Table
4
4
2081.4
4
2081.4
$D$5
Volume Chair
105
10
428.4
105
2081.4
Table
Chair
Profit totals
$254.40
$1,934.88
$2,189.28
Volume
4.00
111.20
Profit per unit
$63.60
$17.40
Selling price of table $129.00
Selling price of chair $35.00
Amount
Amount
Cost Unit Total
A
22
$0.10
$2.20
Metal Tubing
B
16
$0.20
$3.20
Plastic Sheet
Fabrication minutes
C
15
$4.00
$60.00
Cost of table
$65.40
Fabrication
Metal tubing: 10 feet
D
10
$0.10
$1.00
Min number
Plastic sheet: 3 sqf
E
3
$0.20
$0.60
Min number
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.
Materials/Fabrication Needed for a Table
Metal tubing: 22 feet @ $0.10/linear foot
Plastic sheet: 16 sqf @ $0.20/square foot
Materials/Fabrication Needed for a Chair
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
- Access to all documents
- Unlimited textbook solutions
- 24/7 expert homework help
Part 3a
# Tables
# Chairs
Total profit
19
77
$2,578.06
Part 3b
# Tables
# Chairs
Total profit
4
111
$2,458.08
For this scenario, the sensitivity report indicates the optimal solution for the number of tables and chairs that can be built with the available resources (4 tables/105 chairs) to achieve the greatest profit. Available resources are defined in the spreadsheet and the constraints are defined within solver as each material for use is limited by the amount available. From the report, we see that all fabrication (time) resources are consumed and left over material remains for plastic sheet, and metal tubing. The limits report displays the maximum profit to be achieved based upon managements requested minimums, and available resources. The minimum number of tables and chairs are defined as contraints and solver is tasked with determining the maximum values and combination for greatest profitability. Raw materials are still available for metal tubing and plastic sheets after the fabrication time is spent.
Now alter the management decision limiting fabrication to 480 minutes. Increase it to 600, and increase the minimum number of chairs again and list the number of tables and chairs that should be produced.
By changing the total fabrication time and increasing the minimum amount of chairs, profit was increased by $496.66. The additional fabrication time and increase for minimum chairs resulted in both fabrication time and metal tubing being consumed.
Too many tables have been rejected by quality assurance, and the production line for tables will be slowed, increasing fabrication time to 26 minutes
decrease fabrication time on the chairs to 3 minutes. Use the original constraints for fabrication time (480 minutes) and the minimum tables (4) and the table showing the tables and chairs that should be produced.
In the scenario, profit was increased by $376.68 over part two and chair production was increased by six units. When table fabrication time was i
fabrication time was reduced by one minute. In total, an additional 44 minutes was required by make the necessary amount of tables and the tim
helped to increase production.
Constraints
1200
1200
398
700
505
480
4
4
111
10
to be produced to 16. Run Solver s. However, they also found a way to chairs (10). Run Solver again and list increased to 26 minutes, chair me savings in chair fabrication
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
- Access to all documents
- Unlimited textbook solutions
- 24/7 expert homework help
Microsoft Excel 16.0 Sensitivity Report
Worksheet: QSO320 Module 6 Assignment Report Created: 10/5/2023 Variable Cells
Final
Reduced
Cell
Name
Value
Gradient
$C$5
Volume Table
19.35483871
0
$D$5
Volume Chair
77.419354839
0
Constraints
Final
Lagrange
Cell
Name
Value
Multiplier
$I$12
Metal Tubing Constraints
1200 0.1064516191
$I$13
Plastic Sheet Constraints
541.93548387
0
$I$15
Fabrication Constraints
600
4.083870857
$I$18
Min number Constraints
19.35483871
0
$I$19
Min number Constraints
77.419354839
0
Microsoft Excel 16.0 Sensitivity Report
Worksheet: QSO320 Module 6 Assignment Report Created: 10/5/2023 Objective
Cell
Name
Value
$E$4
Profit totals
$2,578.06
Variable
Lower
Objective
Upper
Objective
Cell
Name
Value
Limit
Result
Limit
Result
$C$5
Volume Table
19.35
4.00
1601.50
19.35
2578.06
$D$5
Volume Chair
77.42
16.00
1509.37
77.42
2578.06
Microsoft Excel 16.0 Sensitivity Report
Worksheet: QSO320 Module 6 Assignment Report Created: 10/5/2023 Variable Cells
Final
Reduced
Cell
Name
Value
Gradient
$C$5
Volume Table
4
0
$D$5
Volume Chair
111.2
0
Constraints
Final
Lagrange
Cell
Name
Value
Multiplier
$I$12
Metal Tubing Constraints
1200 2.1399999619
$I$13
Plastic Sheet Constraints
397.6
0
$I$15
Fabrication Constraints
437.6
0
$I$18
Min number Constraints
4 -27.479998779
$I$19
Min number Constraints
111.2
0
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
- Access to all documents
- Unlimited textbook solutions
- 24/7 expert homework help
Microsoft Excel 16.0 Sensitivity Report
Worksheet: QSO320 Module 6 Assignment Report Created: 10/5/2023 Objective
Cell
Name
Value
$E$4
Profit totals
$2,458.08
Variable
Lower
Objective
Upper
Objective
Cell
Name
Value
Limit
Result
Limit
Result
$C$5
Volume Table
4.00
4.00
2458.08
4.00
2458.08
$D$5
Volume Chair
111.20
10.00
292.40
111.20
2458.08
Part 4
New Truck
Old Truck
Constraints
Max Value
Fuel/mile
$0.54
$0.60
Repair Costs
$14,000.00
$14,000.00
Fuel/mile
Payments/mile
$0.24
$0.00
Miles
90,000
90,000
Payments/mile
Driver/mile
$0.36
$0.32
Driver/mile
Repairs/mile
$0.12
$0.24
Repairs/mile
Misc./mile
$0.01
$0.01
Misc./mile
Cost per mile
$1.27
$1.17
Cost per mile
Mile
1
1
Miles Per
New Truck
Old Truck
Total Operating Costs
$111,633.33
$111,633.33
63,333
26,667
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). The tables were created to obtain the cost per mile based upon the data provided. Once cost per mile was obtained, the contraints were defined as re
$14,000 and miles needed to equal 90,000. Total operating cost was calculated by taking the sum of each trucks mileage times the cost per mile. In a
calculated by multipyling the rate with mileage. Based upon the scenario, the best solution is to use the new truck the majority of the time as fuel an
New Truck
Total New
Old Truck
Total Old
$0.54
$34,200.00
$0.60
$16,000.00
$0.24
$15,200.00
$0.00
$0.00
$0.36
$22,800.00
$0.32
$8,533.33
$0.12
$7,600.00
$0.24
$6,400.00
$0.01
$633.33
$0.01
$266.67
$1.27
$1.17
$80,433.33
$31,200.00
epair costs needed to be less than or equal to another table, the total cost per line item was nd repair costs are lower per mile.
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
- Access to all documents
- Unlimited textbook solutions
- 24/7 expert homework help
Microsoft Excel 16.0 Sensitivity Report
Worksheet: QSO320 Module 6 Assignment Report Created: 10/5/2023 Variable Cells
Final
Reduced
Cell
Name
Value
Gradient
$C$19
New Truck
63333.333327
0
$D$19
Old Truck
26666.666674
0
Constraints
Final
Lagrange
Cell
Name
Value
Multiplier
$G$10
Repair Costs Constraints
$14,000.00 -0.8333335506
$G$11
Miles Constraints
90000.000001 1.3700000048
Microsoft Excel 16.0 Limits Report
Worksheet: [QSO 320 Module Six Assignment Submission.xlsx]Solver Part 4
Report Created: 12/7/2018 10:14:27 AM
Objective
Cell
Name
Value
$H$18
Total Operating Costs Max Value
$111,633.33
Variable
Lower
Objective
Upper
Objective
Cell
Name
Value
Limit
Result
Limit
Result
$C$19
New Truck
63,333
63,333
111,633
63,333
111,633
$D$19
Old Truck
26,667
26,667
111,633
26,667
111,633