Good Morning, Can you please provide the excel spreadsheet info to the attached problem? Thanks very much for any help. Julia Robertson is a senior at Tech, and she’s investigating different ways to finance her final year at school. She is considering leasing a food booth outside the Tech stadium at home football games. Tech sells out every home game, and Julia knows, from attending the games herself, that everyone eats a lot of food.
She has to pay $1,150 per game for a booth, and the booths 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. She thinks slices of cheese pizza, hot dogs, and barbecue sandwiches are the most popular food items among fans and so these are the items she 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 Julia to prepare the food while she is selling it. She must prepare the food ahead of time and then store it in a warming oven.
For $600 she 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. She plans to fill the oven with the three food items before the game and then again before half time. Julia has negotiated with a local pizza delivery company to deliver 14-inch cheese pizzas twice each game—2 hours before the game and right after the opening kickoff. Each pizza will cost her $6 and will include 8 slices. She estimates it will cost her $0.45 for each hot dog and $0.90 for each barbecue sandwich if she makes the barbecue herself the night before.
She 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. She plans to sell a slice of pizza and a hot dog for $1.60 apiece and a barbecue sandwich for $2.30. She has $1,500 in cash available to purchase and prepare the food items for the first home game; for the remaining five games she will purchase her ingredients with money she has made from the previous game.
Julia 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 she has discovered that she can expect to sell at least as many slices of pizza as hot dogs and barbecue sandwiches combined. She also anticipates that she will probably sell at least twice as many hot dogs as barbecue sandwiches. She believes that she will sell everything she can stock and develop a customer base for the season if she follows these general guidelines for demand.
If Julia clears at least $1,000 in profit for each game after paying all her expenses, she believes it will be worth leasing the booth.
1. Formulate and solve a linear programming model for Julia that will help you advise her if she should lease the booth.
2. If Julia were to borrow some more money from a friend before the first game to purchase more ingredients, could she increase her profit? If so, how much should she borrow and how much additional profit would she make? What factor constrains her from borrowing even more money than this amount (indicated in your answer to the previous question)?
3. When Julia looked at the solution in (A), she realized that it would be physically difficult for her to prepare all the hot dogs and barbecue sandwiches indicated in this solution. She believes she can hire a friend of hers to help her for $100 per game. Based on the results in (A) and (B), is this something you think she could reasonably do and should do?
4. Julia seems to be basing her analysis on the assumption that everything will go as she plans. What are some of the uncertain factors in the model that could go wrong and adversely affect Julia’s analysis? Given these uncertainties and the results in (A), (B), and (C), what do you recommend that Julia do?
1. Formulation of LPP
Suppose Julia is able to sell X1 pizza slices, X2 hot dogs and X3 barbecue sandwiches to maximise the profit.
Cost of one pizza slice = 6/8 = 0.75 as one pizza of 8 slices is costing 6 dollars.
Selling price of one slice = 1.6
Hence, profit from selling one slice = 1.6-0.75 = 0.85
Hence, profit earned from sellling of X1 slices = 0.85X1
Similarly, cost of one hot dog = 0.45
Selling price of one hot dog = 1.6
Hence, profit from selling one hot dog = 1.6-0.45 = 1.15
Hence, profit earned from sellling of X2 hot dogs = 1.15X2
Similarly, cost of one barbecue sandwich = 0.9
Selling price of one barbecue sandwich = 2.3
Hence, profit from selling one barbecue sandwich = 2.3-0.9=1.4
Hence, profit earned from sellling of X3 barbecue sandwich = 1.4X3
Hence, total profit P = 0.85X1+1.15X2+1.4X3 ……. (1)
Our objective is to maximize profit P under the following constraints.
Space constraint
Space avaialbe in oven = 16*3*4*12*12 sq.inches = 27648 sq.in
Oven will be used 2 times during one game.
Hence, total available space = 2*27648 = 55296 sq.in.
Space needed by one pizza slice = pizza area/ number of slices in one pizza
= pi*(14/2)^2/8
= 22*7*7/(7*8)
= 19.25sq.in
Space needed by one hot dog = 16sq.in
Space needed by one barbecue sandwich = 25 sq.in
Hence, total space needed = 19.25X1+16X2+25X3
It can not be more than 55296 sq,in.
Hence, 19.25X1+16X2+25X3≤55296 …. (2)
Cost constraint
Money available with Julia = 1500 dollars
Money needed to produce food itens = 0.75X1+0.45X2+0.9X3
It can not be more than 1500 dollars.
Hence, 0.75X1+0.45X2+0.9X3 ≤ 1500
Demand constraint
She can sell at least as many slices of pizza as hot dogs and barbecue sandwiches combined.
Hence, X1≥X2+X3 or, X1-X2-X3≥0 …….. (3)
She also anticipates that she will probably sell at least twice as many hot dogs as barbecue sandwiches.
Hence, X2≥2X3 or, X2-2X3≥0 ……. (4)
Non-zero constraints
X1,X2 and X3 can not be less than zero.
Hence, X1≥0 ….. (5)
X2≥0 …… (6)
X3≥0 …….. (7)
So problem is to maximize P under constraints (2) to (7). We will use solver to find the answer.
Excel file is attached herewith.
Answers are given below.
Microsoft Excel 12.0 Answer ReportWorksheet: [support file for lpp problem through solver.xls]Sheet1
Report Created: 11/27/2015 4:58:40 PM
Target Cell (Max)
Cell / Name / Original Value / Final Value
$A$2 / Profit / 3.4 / 2500
Adjustable Cells
Cell / Name / Original Value / Final Value
$C$5 / X1 / 1 / 1250
$D$5 / X2 / 1 / 1250
$E$5 / X3 / 1 / 0
Constraints
Cell / Name / Cell Value / Formula / Status / Slack
$E$7 / 19.25C5+16D5+25E5 / 44062.5 / $E$7<=55296 / Not Binding / 11233.5
$E$9 / 0.75X1+0.45X2+0.9X3 / 1500 / $E$9<=1500 / Binding / 0
$E$11 / x1-x2-x3 / 0 / $E$11>=0 / Binding / 0
$E$13 / 2X2-X3 / 1250 / $E$13>=0 / Not Binding / 1250
$E$5 / X3 / 0 / $E$5>=0 / Binding / 0
$C$5 / X1 / 1250 / $C$5>=0 / Not Binding / 1250
$D$5 / X2 / 1250 / $D$5>=0 / Not Binding / 1250
Hence, Julia should stock 1250 slices of pizza and 1250 numbers of hot dogs. She need not stock sandwiches.
Expected profit per game = 2500
Lease rent of booth per game = 1150
Oven charge per gane = 600/6 = 100
Hence, net profit after all the expenses = 2500-1150-100 = 1250 dollars.
Hence, Julia can go for leasing of booth and selling food items.
2. From answer report, we see that budget of $1500 is binding constraint. Hence, increase in budget will increase profit. We go on increasing the budget till it becomes non-binding. It happens at the budget of $1882 as seen from the following report.
Microsoft Excel 12.0 Answer ReportWorksheet: [Book2]Sheet1
Report Created: 11/27/2015 5:02:58 PM
Target Cell (Max)
Cell / Name / Original Value / Final Value
$A$2 / Profit / 3.4 / 3137.361702
Adjustable Cells
Cell / Name / Original Value / Final Value
$C$5 / X1 / 1 / 1568.680851
$D$5 / X2 / 1 / 1568.680851
$E$5 / X3 / 1 / 0
Constraints
Cell / Name / Cell Value / Formula / Status / Slack
$E$7 / 19.25C5+16D5+25E5 / 55296 / $E$7<=$G$7 / Binding / 0
$E$9 / 0.75X1+0.45X2+0.9X3 / 1882.417021 / $E$9<=$G$9 / Not Binding / 17.58297872
$E$11 / x1-x2-x3 / 0 / $E$11>=0 / Binding / 0
$E$13 / 2X2-X3 / 1568.680851 / $E$13>=0 / Not Binding / 1568.680851
$E$15 / X1 / 1568.680851 / $E$15>=0 / Not Binding / 1568.680851
$E$17 / X2 / 1568.680851 / $E$17>=0 / Not Binding / 1568.680851
$E$19 / X3 / 0 / $E$19>=0 / Binding / 0
Hence, Julia can borrow 1882-1500 = 382 dollars and can increase the profit. The profit becomes $3137.
Net profit = 3137 -1150-100 = 1887 dollars.
Hence, additional profit = 1887-1000 = 887 dollars.
At this point, oven space becomes bindling constraint.
3. If Julia hires one friend for $100, her profit will decrease and it will be $1250-$100= $1150 if she sticks to part 1 plan. Profit is still above the targtted value of $1000. So she can hire friend for cooking help.
4. The main uncertain factor is demand. She believes that she will sell everything she can stockand develop a customer base for the season. If this is violated, then everything will collapse.
Note that, as per result in part1, the net profit she can earn is $1250. It is above her target of $1000. Hence, she can suffer a profit of $250 within her target. Now, as per the result in part 3, it isphysically difficult for her to prepare all the 1250 hotdogs and consequently, she has to hire afriend for $100 per game. Hence, it is better to reduce the number of hotdogs to be prepared sothat she can avoid hiring of the friend and at the same time reduce the risk of uncertain demand. She can reduce the number of hot dogs so that target profit is earned and she will not need friend for cooking of hot dogs. Her profit target will be achieved if she prepares 1033 hotdogs. At the same time, she will reduce investment also.