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 Total

y = 3287816x-1.62

200 300 400 500 600 700 800 615 315 209 145 109 80 64

Price

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

0 1 2 3 4 5 0 9 16 21 24 25

Level of Activity

Profit

Prob2a (Case 2)

Level of Activity	Profit
0	$0
1	$6
2	$14
3	$24
4	$36
5	$50

0 1 2 3 4 5 0 6 14 24 36 50

Level of Activity

Profit

Prob2a (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

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 25

Level 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 50

Level 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