Excel Spreadsheet and Report

Max, in innovative entrepreneur, is considering leasing a food booth outside the stadium at home football games. The stadium is packed every home game, and vendors sell massive amounts of food. Booth rental is $1,000 per game, and vendors can sell either food or drinks, but not both. Only the athletic department concession stands can sell both inside the stadium. He thinks slices of cheeseburgers, hot dogs, and pizza will be the popular food items.

Because of time and space constraints, it will not be possible for Max to prepare the food while he is selling it. Max has observed that nearly all the food sales occur during the hour before the game starts and during half time. He must prepare the food off-site and store it in the booth’s warming oven. He can lease a warming oven for the six-game home season for $600. 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.

Max estimates that hot dogs will cost him $0.50 each. If he makes the cheeseburgers himself, Max thinks they will cost him $1.00 each. Also, he has contracted with Pizza Joint to deliver 14-inch cheese pizzas, cut in 6 slices, twice each game – 2 hours before the game and right after the opening kickoff. Each pizza will cost him $4.50. Each hot dog will need about 16 in2 of shelf space, and each cheeseburger will need about 25 in2. He plans to sell a piece of pizza for $2.50 and a hot dog for $1.60 each and a cheeseburger for $2.25. He has $1,500 in cash available to purchase and prepare the food items for the first home game. Profits from previous game will be used to buy the ingredients for the remaining five.

Based on some discussions with previous vendors, Max thinks that he can expect to sell (1) at least as many slices of pizza as hot dogs and cheeseburgers combined; and (2) at least twice as many hot dogs as cheeseburgers. If he stocks his booth based on those demand estimates, he thinks that he will be able to sell everything he can stock in the booth.

Max believes it will be worth leasing the booth if he clears at least $1,000 in profit for each game after paying all his expenses.

Tasks:

A. Formulate a linear programming model for Max that will help him decide if he should lease the booth. Formulate the model for the first home game. Explain how you derived the profit function and constraints and show any calculations that allow you to arrive at those equations.

B. Use Excel Solver to find the optimal number of pizza slices, hot dogs and cheeseburgers he should sell at each game. Also determine the revenues, cost and profit. Address Max’s ability to have sufficient funds for the next home game to purchase and prepare the food. What would you recommend to Max?

C. If Max were to borrow some money from a friend before the first game to purchase more ingredients, he feels he can increase his profits. What amount, if any, would you recommend to Max to borrow?

D. Food prices have been rising lately. Assume purchase costs for the food is now $6.00 for each pizza, $0.75 for each hot dog, and $1.25 for each cheeseburger. Repeat the analysis of Part B. What would you recommend to Max to do at this point?

E. Max seems to be basing his analysis on the assumptions that everything will go as he plans. What are some of the uncertain factors in the model that could go wrong and adversely affect Max’s analysis? Given these uncertainties and the results in (B), (C), and (D), what do you recommend that Max do? Take into consideration his profit margin for each game.