Help with Optimization and Decision Support Modeling for Business HW4
Prob1a
| Airline Demand | ||||
| Business | Liesure | |||
| Travelers | Travelers | |||
| Price | (Mid-Week) | (Sat-Night Stay) | Total | |
| $200 | 150 | 465 | 615 | |
| $300 | 105 | 210 | 315 | |
| $400 | 82 | 127 | 209 | |
| $500 | 63 | 82 | 145 | |
| $600 | 49 | 60 | 109 | |
| $700 | 35 | 45 | 80 | |
| $800 | 27 | 37 | 64 |
Demand Curve
(Mid-Week)y = 102922x-1.21
200 300 400 500 600 700 800 150 105 82 63 49 35 27 (Sat-Night Stay)y = 7490107x-1.83
200 300 400 500 600 700 800 465 210 127 82 60 45 37 Totaly = 3287816x-1.62
200 300 400 500 600 700 800 615 315 209 145 109 80 64Price
Demand
Prob1b
| Airline Price Model (Uniform Price) | |||||||
| Demand = a * (Price)^b | Range Name | Cells | |||||
| Total Demand | a | C5 | |||||
| a | 3,287,816 | b | C6 | ||||
| b | -1.62 | Capacity | E14 | ||||
| Demand | C14 | ||||||
| Variable Cost | $30 | FixedCost | C10 | ||||
| Profit | C16 | ||||||
| Fixed Cost | $30,000 | TicketPrice | C12 | ||||
| VariableCost | C8 | ||||||
| Ticket Price | $311.67 | ||||||
| Capacity | |||||||
| Demand | 300 | <= | 300 | ||||
| Profit | $54,501 | ||||||
Prob1c
| Airline Pricing Model | ||||||
| Demand = a * (Price)^b | ||||||
| Mid-Week | Sat-Night Stay | |||||
| a | 102,922 | 7,490,107 | ||||
| b | -1.21 | -1.83 | ||||
| Variable Cost | $30 | $30 | ||||
| Fixed Cost | $30,000 | |||||
| Ticket Price | $709.59 | $271.49 | ||||
| Total | Capacity | |||||
| Demand | 37 | 263 | 300.0000008208 | <= | 300 | |
| Profit | $58,457 | |||||
Prob2a (Case 1)
| Level of Activity | Profit |
| 0 | $0 |
| 1 | $9 |
| 2 | $16 |
| 3 | $21 |
| 4 | $24 |
| 5 | $25 |
Level of Activity
Profit
Prob2a (Case 2)
| Level of Activity | Profit |
| 0 | $0 |
| 1 | $6 |
| 2 | $14 |
| 3 | $24 |
| 4 | $36 |
| 5 | $50 |
Level of Activity
Profit
Prob2a (Case 3)
| Level of Activity | Profit |
| 0 | $0 |
| 1 | $5 |
| 2 | $6 |
| 3 | $3 |
| 4 | $4 |
| 5 | $7 |
Level of Activity
Profit
Prob2d (Case 1)
| Level of Activity | Profit |
| 0 | $0 |
| 1 | $9 |
| 2 | $16 |
| 3 | $21 |
| 4 | $24 |
| 5 | $25 |
y = -x2 + 10x
0 1 2 3 4 5 0 9 16 21 24 25Level of Activity
Profit
Prob2d (Case 2)
| Level of Activity | Profit |
| 0 | $0 |
| 1 | $6 |
| 2 | $14 |
| 3 | $24 |
| 4 | $36 |
| 5 | $50 |
y = x2 + 5x
0 1 2 3 4 5 0 6 14 24 36 50Level of Activity
Profit
Prob2d (Case 3)
| Level of Activity | Profit |
| 0 | $0 |
| 1 | $5 |
| 2 | $6 |
| 3 | $3 |
| 4 | $4 |
| 5 | $7 |
0 1 2 3 4 5 0 5 6 3 4 7
Level of Activity
Profit
Prob3d
| Unit Profit ($millions) | Campaign 1 | Campaign 2 | |||
| 0 to 2 | 3 | 5 | |||
| 2 to 4 | -1 | 1 | |||
| 4 to 5 | -4 | -2 | |||
| Total | Resource | ||||
| Resource Used per Unit | Used | Available | |||
| Resource 1 | 4 | 1 | 12 | <= | 20 |
| Resource 2 | 1 | 4 | 18 | <= | 20 |
| Maximum | |||||
| Campaign 1 | Campaign 2 | Campaign 1 | Campaign 2 | ||
| 0 to 2 | 2 | 2 | <= | 2 | 2 |
| 2 to 4 | 0 | 2 | <= | 2 | 2 |
| 4 to 5 | 0 | 0 | <= | 1 | 1 |
| Total | 2 | 4 | |||
| Total Profit | |||||
| ($millions) | |||||
| 18 |
Prob3e
| Unit Profit ($millions) | Campaign 1 | Campaign 2 | ||||
| 0 to 2 | 3 | 4 | 0 to 3 | |||
| 2 to 2.5 | 0.5 | 0.5 | 3 to 3.5 | |||
| 2.5 to 3 | -0.5 | -0.5 | 3.5 to 4 | |||
| 3 to 5 | -3 | -2 | 4 to 5 | |||
| Total | Resource | |||||
| Resource Used per Unit | Used | Available | ||||
| Resource 1 | 4 | 1 | 13.5 | <= | 20 | |
| Resource 2 | 1 | 4 | 16.5 | <= | 20 | |
| Maximum | ||||||
| Campaign 1 | Campaign 2 | Campaign 1 | Campaign 2 | |||
| 0 to 2 | 2 | 3 | <= | 2 | 3 | 0 to 3 |
| 2 to 2.5 | 0.5 | 0.5 | <= | 0.5 | 0.5 | 3 to 3.5 |
| 2.5 to 3 | 0 | 0 | <= | 0.5 | 0.5 | 3.5 to 4 |
| 3 to 5 | 0 | 0 | <= | 2 | 1 | 4 to 5 |
| Total | 2.5 | 3.5 | ||||
| Total Profit | ||||||
| ($millions) | ||||||
| 18.5 |
Prob3f
| Unit Profit | Campaign 1 | Campaign 2 | |||
| Net Profit | 3X1 - (X1 - 1)2 | 3X2 - (x2-2)2 | |||
| Total | Resource | ||||
| Used Per Unit Produced | Used | Available | |||
| Resource 1 | 4 | 1 | 13.5 | <= | 20 |
| Resource 2 | 1 | 4 | 16.5 | <= | 20 |
| Total Profit | |||||
| Campaign 1 | Campaign 2 | ($millions) | |||
| Level | 2.5 | 3.5 | 13.50 |
Prob4a
| 0 | |
| <= | |
| x = | 0.405 |
| <= | |
| 5 | |
| Profit = | x5 - 13x4 + 59x3 - 107x2 + 61x |
| = | 10.735 |
Prob4c
| 0 | |
| <= | |
| x = | 0.405 |
| <= | |
| 5 | |
| Profit = | x5 - 13x4 + 59x3 - 107x2 + 61x |
| = | 10.735 |
Prob5a
| Beating the Market (Evolutionary Solver) | ||||||||||||||
| Beat | Zero | |||||||||||||
| Quarter | Year | DIS | BA | GE | PG | MCD | Return | Zero? | Return | Range Name | Cells | |||
| Q4 | 2016 | 13.10% | 19.08% | 7.47% | -5.59% | 6.34% | 6.19% | Yes | 0.00% | BeatMarket? | J4:J27 | |||
| Q3 | 2016 | -4.38% | 2.28% | -5.17% | 1.32% | -3.39% | -1.93% | No | 0.00% | Market | K4:K27 | |||
| Q2 | 2016 | -1.50% | 3.14% | -0.23% | 9.35% | -3.55% | 1.70% | Yes | 0.00% | NumberBeatingTheMarket | J36 | |||
| Q1 | 2016 | -5.49% | -11.39% | 2.87% | 4.55% | 7.19% | 0.64% | Yes | 0.00% | OneHundredPercent | D33:H33 | |||
| Q4 | 2015 | 3.47% | 11.10% | 24.46% | 11.37% | 20.84% | 12.73% | Yes | 0.00% | OneHundredPercent2 | K31 | |||
| Q3 | 2015 | -9.94% | -4.99% | -4.25% | -7.30% | 4.55% | -4.67% | No | 0.00% | Portfolio | D31:H31 | |||
| Q2 | 2015 | 8.82% | -6.98% | 8.03% | -3.75% | -1.58% | 1.09% | Yes | 0.00% | Return | I4:I27 | |||
| Q1 | 2015 | 11.37% | 16.18% | -0.94% | -9.41% | 4.90% | 3.14% | Yes | 0.00% | StockData | D4:H27 | |||
| Q4 | 2014 | 7.14% | 2.63% | -0.42% | 9.61% | -0.30% | 4.87% | Yes | 0.00% | Sum | I31 | |||
| Q3 | 2014 | 3.83% | 0.72% | -1.67% | 7.41% | -5.07% | 1.87% | Yes | 0.00% | ZeroPercent | D29:H29 | |||
| Q2 | 2014 | 7.08% | 1.96% | 2.30% | -1.71% | 3.59% | 2.75% | Yes | 0.00% | |||||
| Q1 | 2014 | 4.81% | -7.54% | -6.83% | -0.25% | 1.89% | 0.27% | Yes | 0.00% | |||||
| Q4 | 2013 | 19.93% | 16.59% | 18.33% | 8.53% | 1.70% | 11.84% | Yes | 0.00% | |||||
| Q3 | 2013 | 2.12% | 15.23% | 3.80% | -1.08% | -2.04% | 1.69% | Yes | 0.00% | |||||
| Q2 | 2013 | 11.18% | 19.93% | 1.08% | 0.65% | 0.09% | 5.40% | Yes | 0.00% | |||||
| Q1 | 2013 | 14.07% | 14.66% | 11.06% | 14.42% | 13.92% | 13.89% | Yes | 0.00% | |||||
| Q4 | 2012 | -3.29% | 8.94% | -6.74% | -1.31% | -2.99% | -1.78% | No | 0.00% | |||||
| Q3 | 2012 | 7.78% | -5.76% | 9.81% | 14.22% | 4.46% | 7.68% | Yes | 0.00% | |||||
| Q2 | 2012 | 10.80% | 0.49% | 5.01% | -8.10% | -9.05% | -0.94% | No | 0.00% | |||||
| Q1 | 2012 | 16.74% | 1.99% | 13.05% | 1.56% | -1.53% | 6.21% | Yes | 0.00% | |||||
| Q4 | 2011 | 26.43% | 21.99% | 18.83% | 6.45% | 15.11% | 16.83% | Yes | 0.00% | |||||
| Q3 | 2011 | -22.76% | -17.60% | -18.56% | 0.19% | 4.84% | -8.70% | No | 0.00% | |||||
| Q2 | 2011 | -9.39% | 0.54% | -5.13% | 4.07% | 11.64% | 0.88% | Yes | 0.00% | |||||
| Q1 | 2011 | 14.87% | 13.94% | 10.39% | -3.55% | -0.06% | 5.51% | Yes | 0.00% | |||||
| 0% | 0% | 0% | 0% | 0% | ||||||||||
| <= | <= | <= | <= | <= | Sum | |||||||||
| Portfolio | 27.6% | 10.0% | 10.0% | 28.6% | 23.8% | 100% | = | 100% | ||||||
| <= | <= | <= | <= | <= | ||||||||||
| 100% | 100% | 100% | 100% | 100% | ||||||||||
| Number of Quarters | ||||||||||||||
| Beat Zero Return | ||||||||||||||
| 19 |
Prob5b
| Beating the Market (Evolutionary Solver) | ||||||||||||||
| Beat | Ten | |||||||||||||
| Quarter | Year | DIS | BA | GE | PG | MCD | Return | Ten? | Return | Range Name | Cells | |||
| Q4 | 2016 | 13.10% | 19.08% | 7.47% | -5.59% | 6.34% | 15.07% | Yes | 10.00% | BeatMarket? | J4:J27 | |||
| Q3 | 2016 | -4.38% | 2.28% | -5.17% | 1.32% | -3.39% | -0.26% | No | 10.00% | Market | K4:K27 | |||
| Q2 | 2016 | -1.50% | 3.14% | -0.23% | 9.35% | -3.55% | 1.67% | No | 10.00% | NumberBeatingTheMarket | J36 | |||
| Q1 | 2016 | -5.49% | -11.39% | 2.87% | 4.55% | 7.19% | -6.33% | No | 10.00% | OneHundredPercent | D33:H33 | |||
| Q4 | 2015 | 3.47% | 11.10% | 24.46% | 11.37% | 20.84% | 14.88% | Yes | 10.00% | OneHundredPercent2 | K31 | |||
| Q3 | 2015 | -9.94% | -4.99% | -4.25% | -7.30% | 4.55% | -4.41% | No | 10.00% | Portfolio | D31:H31 | |||
| Q2 | 2015 | 8.82% | -6.98% | 8.03% | -3.75% | -1.58% | -2.14% | No | 10.00% | Return | I4:I27 | |||
| Q1 | 2015 | 11.37% | 16.18% | -0.94% | -9.41% | 4.90% | 10.87% | Yes | 10.00% | StockData | D4:H27 | |||
| Q4 | 2014 | 7.14% | 2.63% | -0.42% | 9.61% | -0.30% | 1.84% | No | 10.00% | Sum | I31 | |||
| Q3 | 2014 | 3.83% | 0.72% | -1.67% | 7.41% | -5.07% | -0.13% | No | 10.00% | ZeroPercent | D29:H29 | |||
| Q2 | 2014 | 7.08% | 1.96% | 2.30% | -1.71% | 3.59% | 2.35% | No | 10.00% | |||||
| Q1 | 2014 | 4.81% | -7.54% | -6.83% | -0.25% | 1.89% | -6.30% | No | 10.00% | |||||
| Q4 | 2013 | 19.93% | 16.59% | 18.33% | 8.53% | 1.70% | 16.27% | Yes | 10.00% | |||||
| Q3 | 2013 | 2.12% | 15.23% | 3.80% | -1.08% | -2.04% | 10.70% | Yes | 10.00% | |||||
| Q2 | 2013 | 11.18% | 19.93% | 1.08% | 0.65% | 0.09% | 13.49% | Yes | 10.00% | |||||
| Q1 | 2013 | 14.07% | 14.66% | 11.06% | 14.42% | 13.92% | 13.68% | Yes | 10.00% | |||||
| Q4 | 2012 | -3.29% | 8.94% | -6.74% | -1.31% | -2.99% | 3.66% | No | 10.00% | |||||
| Q3 | 2012 | 7.78% | -5.76% | 9.81% | 14.22% | 4.46% | -0.56% | No | 10.00% | |||||
| Q2 | 2012 | 10.80% | 0.49% | 5.01% | -8.10% | -9.05% | 1.48% | No | 10.00% | |||||
| Q1 | 2012 | 16.74% | 1.99% | 13.05% | 1.56% | -1.53% | 5.23% | No | 10.00% | |||||
| Q4 | 2011 | 26.43% | 21.99% | 18.83% | 6.45% | 15.11% | 20.95% | Yes | 10.00% | |||||
| Q3 | 2011 | -22.76% | -17.60% | -18.56% | 0.19% | 4.84% | -16.69% | No | 10.00% | |||||
| Q2 | 2011 | -9.39% | 0.54% | -5.13% | 4.07% | 11.64% | -0.64% | No | 10.00% | |||||
| Q1 | 2011 | 14.87% | 13.94% | 10.39% | -3.55% | -0.06% | 12.21% | Yes | 10.00% | |||||
| 0% | 0% | 0% | 0% | 0% | ||||||||||
| <= | <= | <= | <= | <= | Sum | |||||||||
| Portfolio | 3.9% | 64.1% | 25.9% | 0.0% | 6.2% | 100% | = | 100% | ||||||
| <= | <= | <= | <= | <= | ||||||||||
| 100% | 100% | 100% | 100% | 100% | ||||||||||
| Number of Quarters | ||||||||||||||
| Beat 10% Return | ||||||||||||||
| 9 |