Help with Optimization and Decision Support Modeling for Business HW3
Redhand
HW3_Template_Sp23.xlsxQ2
| Union Airways Personnel Scheduling Problem | ||||||||||||
| 6am-2pm | 8am-4pm | Noon-8pm | 4pm-midnight | 10pm-6am | Range Name | Cells | ||||||
| Shift | Shift | Shift | Shift | Shift | CostPerShift | C5:G5 | ||||||
| Cost per Shift | $170 | $160 | $175 | $180 | $195 | MinimumNeeded | J8:J17 | |||||
| Total | Minimum | NumberWorking | C21:G21 | |||||||||
| Time Period | Shift Works Time Period? (1=yes, 0=no) | Working | Needed | ShiftWorksTimePeriod | C8:G17 | |||||||
| 6am-8am | 1 | 0 | 0 | 0 | 0 | >= | 48 | TotalCost | J21 | |||
| 8am-10am | 1 | 1 | 0 | 0 | 0 | >= | 79 | TotalWorking | H8:H17 | |||
| 10am- 12pm | 1 | 1 | 0 | 0 | 0 | >= | 65 | |||||
| 12pm-2pm | 1 | 1 | 1 | 0 | 0 | >= | 87 | |||||
| 2pm-4pm | 0 | 1 | 1 | 0 | 0 | >= | 64 | |||||
| 4pm-6pm | 0 | 0 | 1 | 1 | 0 | >= | 73 | |||||
| 6pm-8pm | 0 | 0 | 1 | 1 | 0 | >= | 82 | |||||
| 8pm-10pm | 0 | 0 | 0 | 1 | 0 | >= | 43 | |||||
| 10pm-12am | 0 | 0 | 0 | 1 | 1 | >= | 52 | |||||
| 12am-6am | 0 | 0 | 0 | 0 | 1 | >= | 15 | |||||
| 6am-2pm | 8am-4pm | Noon-8pm | 4pm-midnight | 10pm-6am | ||||||||
| Shift | Shift | Shift | Shift | Shift | Total Cost | |||||||
| Number Working |
Q3
| Wyndor Glass Co. Product-Mix Problem | |||||||||
| Doors | Windows | Range Name | Cells | ||||||
| Unit Profit | DoorsProduced | C12 | |||||||
| Hours | Hours | HoursAvailable | G7:G9 | ||||||
| Hours Used Per Unit Produced | Used | Available | HoursUsed | E7:E9 | |||||
| Plant 1 | <= | HoursUsedPerUnitProduced | C7:D9 | ||||||
| Plant 2 | <= | TotalProfit | G12 | ||||||
| Plant 3 | <= | UnitProfit | C4:D4 | ||||||
| UnitProfitPerDoor | C4 | ||||||||
| Doors | Windows | Total Profit | UnitProfitPerWindow | D4 | |||||
| Units Produced | UnitsProduced | C12:D12 | |||||||
| WindowsProduced | D12 | ||||||||
Q4
| Driving to the Mile-High City | ||||||||||||
| From | To | On Route | Time (hr) | Nodes | Net Flow | Supply/Demand | Range Name | Cells | ||||
| Seattle | Portland | Seattle | = | From | B4:B19 | |||||||
| Seattle | Boise | Portland | = | NetFlow | H4:H12 | |||||||
| Seattle | Butte | Boise | = | Nodes | G4:G12 | |||||||
| Portland | Boise | Butte | = | SupplyDemand | J4:J12 | |||||||
| Boise | Butte | Billings | = | OnRoute | D4:D19 | |||||||
| Boise | Cheyenne | Salt Lake City | = | Time | E4:E19 | |||||||
| Boise | Salt Lake City | Cheyenne | = | To | C4:C19 | |||||||
| Butte | Billings | Grand Junction | = | TotalTime | D21 | |||||||
| Butte | Cheyenne | Denver | = | |||||||||
| Butte | Salt Lake City | |||||||||||
| Billings | Cheyenne | |||||||||||
| Cheyenne | Denver | |||||||||||
| Salt Lake City | Cheyenne | |||||||||||
| Salt Lake City | Grand Junction | |||||||||||
| Grand Junction | Denver | |||||||||||
| Total Time |
Q5
| From | To | Ship | Capacity | Unit Cost | Nodes | Net Flow | Supply/Demand | |||
| P1 | WH1 | <= | P1 | = | ||||||
| P1 | WH2 | <= | P2 | = | ||||||
| P2 | WH1 | <= | WH1 | = | ||||||
| P2 | WH2 | <= | WH2 | = | ||||||
| WH1 | RO1 | <= | RO1 | = | ||||||
| WH1 | RO2 | <= | RO2 | = | ||||||
| WH1 | RO3 | <= | RO3 | = | ||||||
| WH2 | RO1 | <= | ||||||||
| WH2 | RO2 | <= | ||||||||
| WH2 | RO3 | <= | ||||||||
| Total Cost |
Q6
| From | To | Ship | Capacity | Nodes | Net Flow | Supply/Demand | |||
| R1 | A | <= | R1 | ||||||
| R1 | B | <= | R2 | ||||||
| R2 | A | <= | R3 | ||||||
| R2 | B | <= | A | = | |||||
| R2 | C | <= | B | = | |||||
| R3 | B | <= | C | = | |||||
| R3 | C | <= | D | = | |||||
| A | D | <= | E | = | |||||
| A | E | <= | F | = | |||||
| B | D | <= | T | ||||||
| B | E | <= | |||||||
| B | F | <= | |||||||
| C | E | <= | |||||||
| C | F | <= | |||||||
| D | T | <= | |||||||
| E | T | <= | |||||||
| F | T | <= | |||||||
| Maximum Flow |
