Optimizing Protein And Fat Content Of Meat Blend For Local Elementary School

11 Pages / 2,647 Words Published On: 22-02-2021

A local elementary school has ordered 500 pounds of meat for $1.10 per pound. The only requirement is that the meat consist of at least 75% protein and at most 10% each of water and filler. Ordinarily, the owner would produce the blend of meats that achieved this objective in the least costly manner. However, with the concern of too much fat in school lunches, the owner also wants to produce a blend that minimizes the fat content of the meat produced.
A. Formulate mathematical multiple objective linear problem
B. Implement the model in Excel, and individually optimize the two objectives under consideration
C. Solve this problem with the objective of minimizing the maximum percentage deviation from the target values of the goals

	The objective will be to determine the blend of meat containing at least 75% of protein and almost 10% of each of filler and water with the least cost. Consider the data provided in the following table.
	Let Xi = percentage of meat i to include in the mix

	The owner weiner Meyer wants to produce meat with the least production cost.
	we need to develop decision variables to minimize the objectives.
	let;
	X1 be the quantity for meat 1				The objectives will be defined as follows
	X2 be the quantity of meat 2				Minimize $0.75X1 + $0.87X2 + $0.98X3
	X3 be the quantity of meat 3				OR
					Minimize 15X1 + 10X2 + 5X3

	MIN	$0.75X1 + $0.87X2 +$ 0.98X3
	MIN	0.15X1 + 0.10X2 + 0.05X3
	ST	0.70X1 + 0.75X2 + 0.80X3 ≥ 0.75
		0.12X1 + 0.10X2 + 0.08X3 ≤ 0.1
		0.03X1 + 0.05X2 + 0.07X3 ≤ 0.1
		X1 + X2 + X3 = 1
		X1, X2 , X3 ≥ 0

	meat 1	meat 2	meat 3	Actual
Cost per pound	$ 0.75	$ 0.87	$ 0.98	$ -
% Fat	15.0%	10.0%	5.0%	0.0%	Limits
% protein	70.0%	75.0%	80.0%	0.0%	75.0%
% water	12.0%	10.0%	8.0%	0.0%	10.0%
% filler	3.0%	5.0%	7.0%	0.0%	10.0%
% in milk	0.0%	0.0%	0.0%	0.0%


To run the solver click cell E6 as the target cell. In the subjects to constraints box, select cell B10:D10. Selecting the variables in the model for the constraints the solve button in the solver is clicked to obtain the solution.

(b)
After developing the model, the solver will be used to determined the minimum fat content required.
The objectives and constraints are heighlighted as per the variables to be optimized.

		Optimized Min cost = $0.865 per pound, Min Fat Content = 5%

	meat 1	meat 2	meat 3	Actual
Cost per pound	$ 0.75	$ 0.87	$ 0.98	$ 0.98
% Fat	15.0%	10.0%	5.0%	5.0%	Limits
% protein	70.0%	75.0%	80.0%	80.0%	75.0%
% water	12.0%	10.0%	8.0%	8.0%	10.0%
% filler	3.0%	5.0%	7.0%	7.0%	10.0%
% in milk	0.0%	0.0%	100.0%	100.0%


The same procedure used to optimize the percentage fat will be used to obtain the minimized percentage deviation from the target values.

		MIN	Q
		ST	(0.75X1 + 0.87X2 + 0.98X3 - 0.865 )/0.865 ≤ Q
			(0.15X1 + 0.10X2 + 0.05X3 - 0.05 )/0.05 ≤ Q
			0.70X1 + 0.75X2 + 0.80X3 ≥ 0.75
			0.12X1 + 0.10X2 + 0.08X3 ≤ 0.1
			0.03X1 + 0.05X2 + 0.07X3 ≤ 0.1
			X1 + X2 + X3 = 1
			X1, X2 , X3 ≥ 0
Applying "=(E15-F15/F15)" in G15 to obtain percentage deviation. The same procedure will be used to run the solver.


	meat 1	meat 2	meat 3	Actual	Targets	% deviation
Cost per pound	$ 0.75	$ 0.87	$ 0.98	$ -	$ 0.865	-100%
% Fat	15.0%	10.0%	5.0%	0.0%	5	-100%
% protein	70.0%	75.0%	80.0%	0.0%	75
% water	12.0%	10.0%	8.0%	0.0%	10
% filler	3.0%	5.0%	7.0%	0.0%	10
% in milk	0.0%	0.0%	0.0%	0.0%	minimax




(c)
		The solution is:
			X1 = 5.9%, X3=94.1%
The minimum maximum percentage deviation for the target values for the goal is 11.7%

			Meat 1	Meat 2	Meat 3	Actual	Target	% Deviation
		Cost per Pound	$ 0.75	$ 0.87	$ 0.98	$ 0.967	$ 0.865	11.7%
		% Fat	15.0%	10.0%	5.0%	5.6%	5.00%	11.7%
		% Protein	70.0%	75.0%	80.0%	79.4%	75.0%
		% Water	12.0%	10.0%	8.0%	8.2%	10.0%
		% Filler	3.0%	5.0%	7.0%	6.8%	10.0%
		% in Mix	5.9%	0.0%	94.1%	100%	Minimax:	11.7%




The objective for this model is to determine the optimal location for the traveler.
The decision variables are X and Y, where;
X and Y are the coordinates for the tower from the traveler's location.

This is the objective function

b)	Including decision variables, the required location for the caller in cells B8 And C8
	X- Position	Y- Position	Estimated distance
Tower	17	34	29.5
1	12	5	4
2	3	23	17.5
3

Caller location	0	0

Determine the distance of each tower from the personnel using the formula for calculating distance as:

	X- Position	Y- Position	Estimated distance	Actual distance
Tower	17	34	29.5	38.01
1	12	5	4	13.00
2	3	23	17.5	23.19
3

Caller location	0	0

The sum for standard deviation applied to cell E28 is shown in the spreadsheet model below

	X- Position	Y- Position	Estimated distance	Actual distance
Tower	17	34	29.5	38.01
1	12	5	4	13.00
2	3	23	17.5	23.19
3

Caller location	0	0	Sum of squared deviation	185.905

After putting the inputs on the model, the solver is used to give the optimal locations. The target cell as E28 with B28:C28 as the variable cells.
The resulting value for the decision variables are shown in the Answer (2c)

c)
			Estimated distance	Actual distance
Tower	X- Position	Y- Position
1	17	34	29.5	29.35
2	12	5	4	4.10
3	3	23	17.5	17.68

Caller location	8.037161	6.054461	sum of squared deviation	0.06516

The rescue personnel should be dispatched to search for the motorist at these coordinates:
X=8.03
Y=6.05

Cite This Work

To export a reference to this article please select a referencing stye below:

My Assignment Help. (2021). Optimizing Protein And Fat Content Of Meat Blend For Local Elementary School. Retrieved from https://myassignmenthelp.com/free-samples/busa542-applied-decision-modelling/table.html.

"Optimizing Protein And Fat Content Of Meat Blend For Local Elementary School." My Assignment Help, 2021, https://myassignmenthelp.com/free-samples/busa542-applied-decision-modelling/table.html.

My Assignment Help (2021) Optimizing Protein And Fat Content Of Meat Blend For Local Elementary School [Online]. Available from: https://myassignmenthelp.com/free-samples/busa542-applied-decision-modelling/table.html
[Accessed 03 October 2023].

My Assignment Help. 'Optimizing Protein And Fat Content Of Meat Blend For Local Elementary School' (My Assignment Help, 2021) <https://myassignmenthelp.com/free-samples/busa542-applied-decision-modelling/table.html> accessed 03 October 2023.

My Assignment Help. Optimizing Protein And Fat Content Of Meat Blend For Local Elementary School [Internet]. My Assignment Help. 2021 [cited 03 October 2023]. Available from: https://myassignmenthelp.com/free-samples/busa542-applied-decision-modelling/table.html.