Excel and Frontline Solver - SUMPRODUCT function (Linear Optimization)

Linear Optimization

Linear objective functions and linear constraints

problem #10, p. 252 [1]

a. and b.

--- Excel and Frontline Solver  ---

Data
Componet	Hotel	Restaurant	Market
Decision	10000	32500	30000
wholesale Price	1.25	1.5	1.4	Total sales =	103250

Component	Hotel	Restaurant	Market	Cost Per Pound	Max num
Robusta	20%	35%	10%	$0.60	40000
Javan Arabica	40%	15%	35%	$0.80	25000
Liberica	15%	20%	40%	$0.55	20000
Brazilian Arabica	25%	30%	15%	$0.70	45000
Minimum	10000	25000	30000	No more than	100000

Constraints	Hotel	Restaurant	Market	Each bean total	Each bean expense
Robusta	2000	11375	3000	16375	$9,825.00
Javan Arabica	4000	4875	10500	19375	$15,500.00
Liberica	1500	6500	12000	20000	$11,000.00
Brazilian Arabica	2500	9750	4500	16750	$11,725.00
Each brand total	10000	32500	30000	Total Expense =	$48,050.00
			Bean total=	72500

Profits = $55,200.00

a.

     The company should buy 16375 pounds Robusta, 19375 pounds Javan Arabica, 20000 poundsLiberrica,

and 16750 pounds Brazilian Arbica.

b. 

     According to solver's solution, only Liberica reach max weekly availability, so Liberica is the economic

value componet. However, the number of Liberica already reach maximum number. There is no economic

value of an additional pound's worth of plant capacity.

--------------------------------------------------------------------------------------------------------------------------

c.

--- Excel and Frontline Solver  ---

Data
Componet	Hotel	Restaurant	Market
Decision	10000	32505	30000
wholesale Price	1.25	1.5	1.4	Total sales =	103257.5

Component	Hotel	Restaurant	Market	Cost Per Pound	Max num
Robusta	20%	35%	10%	$0.60	40000
Javan Arabica	40%	15%	35%	$0.80	25000
Liberica	15%	20%	40%	$0.55	20001
Brazilian Arabica	25%	30%	15%	$0.70	45000
Minimum	10000	25000	30000	No more than	100000

Constraints	Hotel	Restaurant	Market	Each bean total	Each bean expense
Robusta	2000	11376.75	3000	16376.75	$9,826.05
Javan Arabica	4000	4875.75	10500	19375.75	$15,500.60
Liberica	1500	6501	12000	20001	$11,000.55
Brazilian Arabica	2500	9751.5	4500	16751.5	$11,726.05
Each brand total	10000	32505	30000	Total Expense =	$48,053.25

                   Bean total = 72505

Profits = $55,204.25

	Previous	Add one pound
Liberica	20000	20001
Profits	55200	55204.25		value =	4.25
				4.25 + 0.55 =	4.8

The company can earn more $4.25 than before, but Solver already minus 0.55 as additional pound ofs

Liberica's expense. Thus, if the company want to earn more money, the company should pay less than $4.8.

d.

Data
Component	Hotel	Restaurant	Market
Decision	20000	25000	30000
wholesale Price	1.25	1.5	1.4	Total sales =	104500
Component	Hotel	Restaurant	Market	Cost Per Pound	Max num
Robusta	20%	35%	10%	$0.60	40000
Javan Arabica	40%	15%	35%	$0.80	25000
Liberica	15%	20%	40%	$0.55	20000
Brazilian Arabica	25%	30%	15%	$0.70	45000
Minimum	25000	25000	30000	No more than	100000
Constraints	Hotel	Restaurant	Market	Each bean total	Each bean expense
Robusta	4000	8750	3000	15750	$9,450.00
Javan Arabica	8000	3750	10500	22250	$17,800.00
Liberica	3000	5000	12000	20000	$11,000.00
Brazilian Arabica	5000	7500	4500	17000	$11,900.00
Each brand total	20000	25000	30000	Total  Expense	$50,150.00
			Bean total=	75000
				Profits =	$54,350.00
Hotel Minimum	0	5000	10000	15000	20000	25000
Profits	56050	55625	55200	54775	54350	54350

e.

Data
Componet	Hotel	Restaurant	Market
Decision	10000	32500	30000
wholesale Price	1.25	1.5	1.4	Total sales =	103250
Component	Hotel	Restaurant	Market	Cost Per Pound	Max num
Robusta	20%	35%	10%	$1.20	40000
Javan Arabica	40%	15%	35%	$0.80	25000
Liberica	15%	20%	40%	$0.55	20000
Brazilian Arabica	25%	30%	15%	$0.70	45000
Minimum	10000	25000	30000	No more than	100000
Constraints	Hotel	Restaurant	Market	Each bean total	Each bean expense
Robusta	2000	11375	3000	16375	$19,650.00
Javan Arabica	4000	4875	10500	19375	$15,500.00
Liberica	1500	6500	12000	20000	$11,000.00
Brazilian Arabica	2500	9750	4500	16750	$11,725.00
Each brand total	10000	32500	30000	Total Expense	$57,875.00
			Bean total=	72500
				Profits =	$45,375.00
Robusta
Cost Per Pound	0.20	0.40	0.60	0.80	1.00	1.20
Profits	61,750.00	58,475.00	55,200.00	51,925.00	48,650.00	45,375.00

Reference:
[1] Powell, Stephen G. Management Science: The Art Of Modeling With Spreadsheets, 4Th Edition. 1st ed. John Wiley & Sons, 2013. Print.