| 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 = 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 | ||||||||||||||||
Order an Essay Now & Get These Features For Free:
Turnitin Report
Formatting
Title Page
Citation
Outline
