PROBLEM 1: (Linear Programming)

Although not as renowned as its Belgian and Swiss competitors, a small artisanal chocolaterie in Luxembourg has just finalised an agreement to rent a sales counter in the shopping centre of its region. The chocolaterie aims to use this counter to sell gift boxes made up of different blends of fine chocolates. The management of the chocolaterie has designed five gift boxes that could appeal to customers: TRADITIONAL (T), MILK (M), DARK (D), VOYAGE (V), and LUXE (L). Each gift box is made up of eight to ten fine chocolates.

The composition and the sales price of each of the gift boxes is given in the table below. For example, a DARK box sells for €20.99 and is made up of 4 dark chocolates, 2 truffle chocolates, 1 ginger chocolate, and 1 chili chocolate. 

The quantity of chocolate of each type available during each week for making gift boxes is presented in the table above. The cost for each unit of each type of chocolate is also given in the above table.

It takes 5 minutes to make each gift box. The chocolaterie’s management has determined that at most 8 hours, i.e., 480 minutes, should be set aside each week for making gift boxes. The chocolaterie’s management has put some restrictions on the quantity of gift boxes to make of each type. First, at least 10 boxes of MILK, DARK, VOYAGE, and LUXE types must be made each week. However, the production of MILK and DARK is limited to 30 and 20 boxes, respectively. The management also wishes to make at least as many VOYAGE boxes as LUXE boxes. Finally, since TRADITIONAL boxes are well-known throughout the region, the management wants at least 25% of the boxes made to be of this type.

The chocolaterie’s management wants to determine the number of boxes of each type to make each week in order to maximise the chocolaterie’s profits while respecting the constraints imposed. In order to do this, the management has implemented a linear programming model in Excel. This model has been solved using Solver. Screenshots of the spreadsheet containing the optimal solution and the Solver settings have been reproduced below. You will also find the sensitivity analysis report. According to the Excel implementation, it is optimal to produce 23, 30, 15, 14, and 10 boxes each week of type T, M, D, V, and L, respectively. The maximised weekly profit is €632.53.

Question 1: 

In reference to the spreadsheet that implements the model, indicate which formula should appear in each of the following cells:

C18 (= 5.01 ):

I18 ( = 632.53 ):

H38 ( = -4 ):

Question 2: 

Some values have been deleted from the sensitivity analysis report. Using appropriate justifications, provide the missing values for the following cells:

E10:

F10:

G10:

E11:

D23:

E23:

F23:

G23:

H23:

Question 3:

The shop owner thinks that it is possible to increase the price of the TRADITIONAL box, but she does not want to change the current optimal production plan, i.e., the optimal solution should stay the same. Is it possible to do that? If so, then state the maximum possible selling price of the TRADITIONAL box and the additional profit that would result. If not, then state why it is not possible to change the price of this box without altering the optimal production plan. Justify your answer based on the sensitivity analysis report.

Question 4:

The employees complain that they are producing too few LUXE boxes and have the impression that they are wasting their time. They ask the colleague in charge of the spreadsheet implementation to examine the impact of producing two more boxes. What would be the anticipated impact on the objective function value and on the optimal solution of making two more boxes of the LUXE type? Justify your answer based on the sensitivity analysis report.