Problem:
Chain Restaurant Problem: Linear Programming in Excel
A chain restaurant consists of five restaurants for which we need to evaluate the relative efficiency. Input measures for the restaurants include weekly hours of operation, number of full time staff and weekly supply expenses. Output measures of performance include average weekly contribution to profit, market share, and annual growth rate. Data for input and output measures are shown in the following table
Restaurant Hours Staff Supplies Profit Share Growth
1 96 16 850 3800 25 8
2 110 22 1400 4600 32 8.5
3 100 18 1200 4400 35 8
4 125 25 1500 6500 30 10
5 120 24 1600 6000 28 9
a) Formulate a linear programming model to determine the relative efficiency of a restaurant using the data provided.
b) Implement formulation in part (a) in Excel and use Solver to find the relative efficiency for each restaurant and identify those that are inefficient.