Question

Question: The Sweet Smell Fertilizer Company markets bags of Manure labeled "not less than 60 pounds dry weight." The packaged manure is a combination of compost and sewage wastes. Each pound of compost cost sweet smell $0.05 and has a fertilizer rating of 2 units. Each pound of sewage cost $0.04 and has a fertilizer rating of 1 unit. To provide good-quality fertilizer, each bag should contain at least 35 pounds of compost, not more than 40 pounds of sewage, and have a total fertilizer rating of at least 100 units. Determine the lowest-cost blend of compost and sewage in each bag.

Solution:

Problem 2-24
	Compost	Sewage
Number of pounds	40.00	20.00
Cost	$0.05	$0.04	$2.80
Constraints:
Pounds per bag	1	1	60.00	>=	60
Fertilizer rating	2	1	100.00	>=	100
Min compost, pounds	1		40.00	>=	35
Max sewage, pounds		1	20.00	<=	40
			LHS	Sign	RHS

What are the steps to get from the question stated above to the solution in excel?

Answer 1

	Compost	Sewage
Number of pounds	40	20
Cost	0.05	0.04	2.8
Constraints:
Pounds per bag	1	1	60	>=	60
Fertilizer rating	2	1	100	>=	100
Min compost, pounds	1		40	>=	35
Max sewage, pounds		1	20	<=	40
			LHS	Sign	RHS

Formulas

	Compost	Sewage
Number of pounds	40	20
Cost	0.05	0.04	=SUMPRODUCT(B4:C4,B5:C5)
Constraints:
Pounds per bag	1	1	=SUMPRODUCT($B$4:$C$4,B7:C7)	>=	60
Fertilizer rating	2	1	=SUMPRODUCT($B$4:$C$4,B8:C8)	>=	100
Min compost, pounds	1		=SUMPRODUCT($B$4:$C$4,B9:C9)	>=	35
Max sewage, pounds		1	=SUMPRODUCT($B$4:$C$4,B10:C10)	<=	40
			LHS	Sign	RHS

Do to data -> data analysis -> solver

fill dialogue box as below in the image

Screenshot

solving

we get compost = 40

sewage = 20

Cost = 2.8