Question

The manager of a Burger Doodle franchise wants to determine how many sausage biscuits and ham biscuits to prepare each morning for breakfast customers. The two types of biscuits require the following resources:

Biscuit	Labor (hr)	Sausage (lb)	Ham (lb)	Flour (lb)
Sausage	0.010	0.10	-	0.4
Ham	0.024	-	0.15	0.4

The franchise has 6 hours of labor available each morning. The manager has a contract with a local grocer for 30 pounds of sausage and 30 pounds of ham each morning. The manager also purchases 16 pounds of flour. The profit for a sausage biscuit is $0.60; the profit for a ham biscuit is $0.50. The manager can sell as many biscuits as he makes. The manager wants to know the number of each type of biscuit to prepare each morning in order to maximize profit. Formulate a linear model for this problem.

Please provide answer with step by step instructions in excel.

Answer 1

Let,

xi = number of biscuits i to produce Where i = {Sausage = 1, Ham = 2}

Objective is to maximize profit = Max 0.6x1+0.5x2

Subject to,

0.01x1+0.024x2 <= 6 (Labor)
0.1x1 <= 30 (Sausage)
0.15x2 <= 30 (Ham)
0.4x1+0.4x2 <= 16 (Flour)

xi >= 0 (non-negativity constraint)

Solver step-by-step instruction

Step 1.

Write the decision variables and prepare the objective function with them

Step 2

Write the constraints and create Sumproduct formula after writing coefficients below the decision variables as derived from algebraic model above

Step 3

Now click on Data-> Solver

and select cell containing objective function and select option "Max" as we are maximizing cost here. Select decision variables' cells in "By changing variables Cells" tab.

To add constraints click on "Add" and add the constraints

Select "Simplex LP" and make sure "Make unconstrained value Non-negative" checkbox is ticked as this will take care of non-negativity constraints.

Click on "Solve" and solution will appear for optimal solution just below the decision variables written in Step 1 and minimized objective value will appear in the objective function cell

Solving in solver we get,

Number of Sausage biscuits to produce = 40 and number of Ham biscuits to produce = 0

maximized profit = $24

Solver screenshot =

Solver formula