1: Directions: 1) Read the entire selection below 2) Answer the questionssee additional notes below.... Selection: Bradley's Food Stand John Bradley is a senior at Tech, and he's investigating different ways to finance his final year at school. He is considering leasing a temporary food facility outside the Tech stadium at home football games. Tech sells out every home game, and he knows, from attending the games himself, that everyone eats a lot of food. He has to pay $800 per game for the stand, and the food stands are not very large. Vendors can sell either food or drinks on Tech property, but not both. Only the Tech athletic department concession stands can sell both inside the stadium. He thinks slices of cheese pizza, hot dogs, and barbecue sandwiches are the most popular food items among fans and so these are the items he would sell. Most food items are sold during the hour before the game starts and during half time; thus it will not be possible for John to prepare the food while he is selling it. He must prepare the food ahead of time and then store it in a warming oven. For $500 he can lease a warming oven for the six game home season. The oven has 16 shelves, and each shelf is 3 feet by 4 feet. He plans to fill the oven with the three food items before the game and then again before half time. John has negotiated with a local pizza delivery company to deliver 14inch cheese pizzas twice each game2 hours before the game and right after the opening kickoff. Each pizza will cost him $4.40 and will include 8 slices. He estimates it will cost him $0.50 for each hot dog and $0.85 for each barbecue sandwich if he makes the barbecue himself the night before. He measured a hot dog and found it takes up about 16 square inches of space, whereas a barbecue sandwich takes up about 25 square inches. He plans to sell a slice of pizza for $1.40, a hot dog for $1.45, and a barbecue sandwich for $2.00. He has $1,120 in cash available to purchase and prepare the food items for the first home game; for the remaining five games he will purchase his ingredients with money he has made from the previous game. John has talked to some students and vendors who have sold food at previous football games at Tech as well as at other universities. From this he has discovered that he can expect to sell at least as many slices of pizza as twice hot dogs and barbecue sandwiches combined. He also anticipates that he will probably sell at least twice as many hot dogs as barbecue sandwiches. He believes that he will sell everything he can stock and develop a customer base for the season if he follows these general guidelines for demand. If John clears at least $1,200 in profit for each game after paying all his expenses, he believes it will be worth leasing the booth. (1). Formulate and solve a linear programming model for John that will help you advise him if he should lease the food stand. (2). If John was to borrow some more money from a friend before the first game to purchase more ingredients, could he increase his profit? If so, how much should he borrow and how much additional profit would he make? What factor constrains him from borrowing even more money than this amount (indicated in your answer to the previous question)? ____________________________________________________________________________ _________ ADDITIONAL INSTRUCTIONS: Your instructor will assign a linear programming project for this assignment according to the following specifications. It will be a problem with at least three (3) constraints and at least two (2) decision variables. The problem will be bounded and feasible. It will also have a single optimum solution (in other words, it won't have alternate optimal solutions). The problem will also include a component that involves sensitivity analysis and the use of the shadow price. You will be turning in two (2) deliverables, a short writeup of the project and the spreadsheet showing your work. Writeup. Your writeup should introduce your solution to the project by describing the problem. Correctly identify what type of problem this is. For example, you should note if the problem is a maximization or minimization problem, as well as identify the resources that constrain the solution. Identify each variable and explain the criteria involved in setting up the model. This should be encapsulated in one (1) or two (2) succinct paragraphs. After the introductory paragraph, write out the L.P. model for the problem. Include the objective function and all constraints, including any non-negativity constraints. Then, you should present the optimal solution, based on your work in Excel. Explain what the results mean. Finally, write a paragraph addressing the part of the problem pertaining to sensitivity analysis and shadow price. Excel. As previously noted, please set up your problem in Excel and find the solution using Solver. Clearly label the cells in your spreadsheet. You will turn in the entire spreadsheet, showing the setup of the model, and the results. Assignment 1: Directions: 1) Read the entire selection below 2) Answer the questionssee additional notes below.... Selection: Bradley's Food Stand John Bradley is a senior at Tech, and he's investigating different ways to finance his final year at school. He is considering leasing a temporary food facility outside the Tech stadium at home football games. Tech sells out every home game, and he knows, from attending the games himself, that everyone eats a lot of food. He has to pay $800 per game for the stand, and the food stands are not very large. Vendors can sell either food or drinks on Tech property, but not both. Only the Tech athletic department concession stands can sell both inside the stadium. He thinks slices of cheese pizza, hot dogs, and barbecue sandwiches are the most popular food items among fans and so these are the items he would sell. Most food items are sold during the hour before the game starts and during half time; thus it will not be possible for John to prepare the food while he is selling it. He must prepare the food ahead of time and then store it in a warming oven. For $500 he can lease a warming oven for the six game home season. The oven has 16 shelves, and each shelf is 3 feet by 4 feet. He plans to fill the oven with the three food items before the game and then again before half time. John has negotiated with a local pizza delivery company to deliver 14inch cheese pizzas twice each game2 hours before the game and right after the opening kickoff. Each pizza will cost him $4.40 and will include 8 slices. He estimates it will cost him $0.50 for each hot dog and $0.85 for each barbecue sandwich if he makes the barbecue himself the night before. He measured a hot dog and found it takes up about 16 square inches of space, whereas a barbecue sandwich takes up about 25 square inches. He plans to sell a slice of pizza for $1.40, a hot dog for $1.45, and a barbecue sandwich for $2.00. He has $1,120 in cash available to purchase and prepare the food items for the first home game; for the remaining five games he will purchase his ingredients with money he has made from the previous game. John has talked to some students and vendors who have sold food at previous football games at Tech as well as at other universities. From this he has discovered that he can expect to sell at least as many slices of pizza as twice hot dogs and barbecue sandwiches combined. He also anticipates that he will probably sell at least twice as many hot dogs as barbecue sandwiches. He believes that he will sell everything he can stock and develop a customer base for the season if he follows these general guidelines for demand. If John clears at least $1,200 in profit for each game after paying all his expenses, he believes it will be worth leasing the booth. (1). Formulate and solve a linear programming model for John that will help you advise him if he should lease the food stand. (2). If John was to borrow some more money from a friend before the first game to purchase more ingredients, could he increase his profit? If so, how much should he borrow and how much additional profit would he make? What factor constrains him from borrowing even more money than this amount (indicated in your answer to the previous question)? ____________________________________________________________________________ _________ ADDITIONAL INSTRUCTIONS: Your instructor will assign a linear programming project for this assignment according to the following specifications. It will be a problem with at least three (3) constraints and at least two (2) decision variables. The problem will be bounded and feasible. It will also have a single optimum solution (in other words, it won't have alternate optimal solutions). The problem will also include a component that involves sensitivity analysis and the use of the shadow price. You will be turning in two (2) deliverables, a short writeup of the project and the spreadsheet showing your work. Writeup. Your writeup should introduce your solution to the project by describing the problem. Correctly identify what type of problem this is. For example, you should note if the problem is a maximization or minimization problem, as well as identify the resources that constrain the solution. Identify each variable and explain the criteria involved in setting up the model. This should be encapsulated in one (1) or two (2) succinct paragraphs. After the introductory paragraph, write out the L.P. model for the problem. Include the objective function and all constraints, including any non-negativity constraints. Then, you should present the optimal solution, based on your work in Excel. Explain what the results mean. Finally, write a paragraph addressing the part of the problem pertaining to sensitivity analysis and shadow price. Excel. As previously noted, please set up your problem in Excel and find the solution using Solver. Clearly label the cells in your spreadsheet. You will turn in the entire spreadsheet, showing the setup of the model, and the results. Answer: Write-up: This problem is a linear programming problem with three variables which are the number of pizza, hot dog and sandwich to be kept with john so that he can increase his profits.. We have to maximize the profit made from the three eatables, Subjected to the constraints. The resources are limited and include money available in hand and the space available in the oven. This problem can be formulated in excel and can be solved using the solver module. The sensitivity analysis as well as the shadow prices of all the variable is available in the excel solver module. Decision Variables: X1 = Number of pizza to be ordered. X2 = Number of hot dog to be bought. X3 = Number of barbecue sandwich to be bought. Constraints: 1. The oven has 16 shelves, and each shelf is 3 feet by 4 feet. Area of the shelves is 16*3*4*12*12 = 27648 square inch. Area taken by each pizza = = 3.14*7*7 = 153.93804 square inch. Area taken by hot dog = 14 square inch. Area taken by barbecue sandwich = 25 square inches. Therefore 153.93804X1 + 14X2 + 25X3 <= 27648 2. Each pizza will cost him $4.40 and will include 8 slices and $0.50 for each hot dog and $0.85 for each barbecue sandwich. He will have to buy 2X1 pizza, 2X2 hot dog, 2X3 barbecue sandwich for each game so that he can sell it during the hour before the game starts and during half time. He has $1,120 in cash available to purchase and prepare the food items for the first home game Therefore, 4.4*2*X1 + .5*2*X2 + .85*2*X3 <= 1120 8.8X1 + X2 + 1.7X3 <= 1120 3. He has discovered that he can expect to sell at least as many slices of pizza as twice hot dogs and barbecue sandwiches combined. 8*X1 >= 2*(X2 + X3) 8X1 - 2X2 - 2X3 >= 0 4. He will probably sell at least twice as many hot dogs as barbecue sandwiches X2 >= 2*X3 X2 - 2X3 >= 0. Objective: Maximize Profit = Total revenue - Total cost Total cost = 8.8X1 + X2 + 1.7X3 + 800 + 500/6 Total revenue = 1.4*8*2*X1 + 1.45*2*X2 + 2*2X3 Z = 13.6X1 + 1.9X2 + 2.3X3 1. The following is the solution of the above linear programming problem using excel solver. Variables Coefficient s Solutions Z X1 13.6 X2 1.9 X3 2.3 87.5 1855 350 0 From the solution we see that the profit is $1855 and hence we can suggest john to lease the food stand. 2. From the sensitivity report we can easily see that the money john has is a binding constraint with a shadow price of 1.65625. We would suggest him to borrow $565.709 from his friend to increase his profit as this amount is the allowable increase according to the sensitivity report. Increasing the amount by 565.709, the profit will be $2791.956 which is 2791.956-1855 = $936.956 more than the current profit. From the answer report we see that the other binding constrain is constrain 3 which is that he can expect to sell at least as many slices of pizza as twice hot dogs and barbecue sandwiches combined. This factor constraints his profit if he wants to borrow even more money. Microsoft Excel 14.0 Answer Report Worksheet: [Solver1.xlsx]Sheet1 Report Created: 5/31/2015 1:40:04 PM Result: Solver found a solution. All Constraints and optimality conditions are satisfied. Solver Engine Engine: Simplex LP Solution Time: 0.093 Seconds. Iterations: 4 Subproblems: 0 Solver Options Max Time Unlimited, Iterations Unlimited, Precision 0.000001 Max Subproblems Unlimited, Max Integer Sols Unlimited, Integer Tolerance 1%, Assume NonNegative Objective Cell (Max) Cell Name $D$7 Z X1 Original Value 0 Final Value 1855 Variable Cells Cell Name $D$6 Solutions X1 $E$6 Solutions X2 $F$6 Solutions X3 Original Value 0 0 0 Final Value Integer 87.5 Contin 350 Contin 0 Contin Constraints Cell Name $D$18 Constrains1 LHS $D$19 Constrains2 LHS $D$20 Constrains3 LHS $D$21 Constrains4 LHS Cell Value Formula 18369.575 $D$18<=$E$18 1120 $D$19<=$E$19 1.136868E-013 $D$20>=$E$20 350 $D$21>=$E$21 Status Not Binding Binding Binding Not Binding Slack 9278.425 0 0 350 Microsoft Excel 14.0 Sensitivity Report Worksheet: [Solver1.xlsx]Sheet1 Report Created: 5/31/2015 1:40:04 PM Variable Cells Cell Name $D$6 Solutions X1 $E$6 Solutions X2 $F$6 Solutions X3 Final Reduced Objective Allowable Allowable Value Cost Coefficient Increase Decrease 87.5 0 13.6 3.12 13.88571429 350 0 1.9 1.0000E+030 0.3545454545 0 -0.759375 2.3 0.759375 1.0000E+030 Constraints Cell $D$18 $D$19 $D$20 $D$21 Final Shadow Constraint Allowable Allowable Name Value Price R.H. Side Increase Decrease Constrains1 LHS 18369.575 0 27648 1.0000E+030 9278.425 Constrains2 LHS 1120 1.65625 1120 565.7091141 1120 Constrains3 LHS 1.13687E-013 -0.121875 0 1018.181818 2240 Constrains4 LHS 350 0 0 350 1.0000E+030 Microsoft Excel 14.0 Limits Report Worksheet: [Solver1.xlsx]Sheet1 Report Created: 5/31/2015 1:40:04 PM Objective Cell Name $D$7 Z X1 Value 1855 Variable Cell Name $D$6 Solutions X1 $E$6 Solutions X2 $F$6 Solutions X3 Value 87.5 350 0 Lower Objective Limit Result 87.5 1855 0 1190 0 1855 Upper Objective Limit Result 87.5 1855 350 1855 0 1855 Variables Coefficients Solutions Z Constrains1 Constrains2 Constrains3 Constrains4 Constrains1 Constrains2 Constrains3 Constrains4 X1 X2 X3 13.6 87.5 1855 1.9 350 153.938 8.8 8 0 14 1 -2 1 LHS RHS 18369.58 27648 1120 1120 1.1E-013 0 350 0 2.3 0 25 <= 1.7 <= -2 >= -2 >= 27648 1120 0 0