Analyze the following scenario:

Option #1 Artisan Pieces

The target price for the artisan pieces is $124 each. In addition, Kaitlyn anticipates that many

customers will purchase regular chocolates to fill the sculpture and/or place them around the piece. The

additional sale of chocolates/truffles are forecast to generate an additional $24 of margin per artisan

piece sold.

Due to the short supply of pastry chefs, Kaitlyn can only get a half-time pastry chef for 1,000 hours per

year at a cost of $40,000. Each piece should take the chef approximately 30 minutes to complete.

Aside from the pastry chef, a skilled tradesperson will be hired to run the machine; he or she will cost

$25,000 per year for 1,000 hours. While there is no shortage of this type of labour, Kaitlyn believes that

the total hours required will be the same as the pastry chef.

The chocolate has to be of relatively high quality, and it is estimated that each sculpture will require

roughly 4 pounds of chocolate at a cost of $8 per pound.

Other annual variable costs include $13,000 for specialty molds and $6,000 for decorative candy. In

addition, there will be a one-time cost of $10,000 for sculpting tools.

Option #3 Expensive High-End Chocolate Bars

Kaitlyn anticipates that she can sell 225,000 of these bars at a price of $4.50 each. However, since

these may be viewed as a substitute for her regular chocolates and truffles, she anticipates

cannibalization will result in a contribution margin loss of $36,000 annually. The processes involved in

making these high-end chocolate bars and maintaining their quality, in terms of both taste and

appearance, requires the use of a pastry chef subject to the same supply limitations and costs as

mentioned for the artisan pieces (Option #1). This strict attention to process also means that a skilled

tradesperson will be required to produce the bars and perform the required setups. Again, as with the

artisan pieces, it is anticipated that this person will be used for the same amount of time as the pastry

chef. Given the high-end nature of these bars, the type or quality of chocolate required is extremely

expensive and will cost approximately $13 per pound, with each bar requiring a quarter of a pound.

Other annual costs will include molds of $4,000 and flavours costing $15,000.

Assume that Option #1 (High-End Artisan Pieces) and Option #3 (High-End Chocolate Bars) are

not mutually exclusive, and Kaitlyn is able to produce any combination of these two products,

subject to the following:

i. Assume the contribution margin per unit is $74 for the high-end artisan pieces and $0.65

for the high-end chocolate bars.

ii. Machine time capacity is 800 hours annually. Recall that the Pastry Chef is only

available for 1,000 hours each year.

iii. The time required for each product is as follows:

	Artisan Pieces	Chocolate Bars
Machine time	0.25 hrs	0.004 hrs
Pastry-Chef time	0.5 hrs	0.004 hrs

iv. Due to an issue with the supply of the chocolate to be used for the high-end bars, Kaitlyn

can only produce 190,000 bars annually for the foreseeable future.

v. Due to an issue with the supply of specialty molds to be used for the artisan

pieces, Kaitlyn can only produce 1,800 pieces annually for the foreseeable future

Using Solver in Excel, determine the optimal product mix (# of units of each product to

produce/sell) that will maximize contribution/profit given the constraints faced by Kaitlyn in this

scenario.

What is the amount of contribution ($) the optimal product mix will generate?

Provide the following:

1. Define the formula to be maximized (objective function)

2. Define the production constraints (there are four).

3. Define the constraints that the number of units to be produced for each product cannot

be negative (ie. less than zero).

4. Enter the above information into Solver

5. Solve this will provide the product mix number of units for each product.

6. Calculate the total contribution the optimal product mix will generate.