EXCEL EQUATIONS PLEASE Case Problem 1 PRODUCT MIX TJ’s, Inc., makes three nut mi

Question

EXCEL EQUATIONS PLEASE

Case Problem 1 PRODUCT MIX

TJ’s, Inc., makes three nut mixes for sale to grocery chains located in the Southeast. The three mixes, referred to as the Regular Mix, the Deluxe Mix, and the Holiday Mix, are made by mixing different percentages of five types of nuts. In preparation for the fall season, TJ’s has just purchased the following shipments of nuts at the prices shown:

Type of Nut Shipment Amount (pounds) Cost per Shipment ($)

Almond 6000 7500

Brazil 7500 7125

Filbert 7500 6750

Pecan 6000 7200

Walnut 7500 7875

The Regular Mix consists of 15% almonds, 25% Brazil nuts, 25% filberts, 10% pecans, and 25% walnuts. The Deluxe Mix consists of 20% of each type of nut, and the Holiday Mix consists of 25% almonds, 15% Brazil nuts, 15% filberts, 25% pecans, and 20% walnuts. TJ’s accountant analyzed the cost of packaging materials, sales price per pound, and so forth, and determined that the profit contribution per pound is $1.65 for the Regular Mix, $2.00 for the Deluxe Mix, and $2.25 for the Holiday Mix. These figures do not include the cost of specific types of nuts in the different mixes because that cost can vary greatly in the commodity markets. Customer orders already received are summarized here:

Type of Mix Orders (pounds)

Regular 10,000

Deluxe 3,000

Holiday 5,000

Because demand is running high, it is expected that TJ’s will receive many more orders than can be satisfied. TJ’s is committed to using the available nuts to maximize profit over the fall season; nuts not used will be given to a local charity. Even if it is not profitable to do so, TJ’s president indicated that the orders already received must be satisfied. Managerial Report

Perform an analysis of TJ’s product-mix problem, and prepare a report for TJ’s president that summarizes your findings. Be sure to include information and analysis on the following:

1. The cost per pound of the nuts included in the Regular, Deluxe, and Holiday mixes 2. The optimal product mix and the total profit contribution 3. Recommendations regarding how the total profit contribution can be increased if additional quantities of nuts can be purchased 4. A recommendation as to whether TJ’s should purchase an additional 1000 pounds of almonds for $1000 from a supplier who overbought 5. Recommendations on how profit contribution could be increased (if at all) if TJ’s does not satisfy all existing orders

Explanation / Answer

1) The cost per pound of the nuts included in the Regular, Deluxe, and Holiday mixes

This can be calculated quite easily by first calculating the (wholesale) price per pound for each type of nut:

Almond = 1.25

Brazil = 0.95

Filbert = 0.90

Pecan = 1.20

Walnut = 1.05

Since we are given the proportions of nuts to be included in each mix we can sum up the partial cost of each nut within a mix to obtain the cost of the mix in total:

Regular = 1.25*0.15 + 0.95*0.25 + 0.90*0.25 + 1.20*0.1 + 1.05*0.25

            = $1.03

Deluxe = 1.25*0.2 + 0.95*0.2 + 0.90*0.2 + 1.20*0.2 + 1.05*0.2

            = $1.07

Holiday = 1.25*0.25 + 0.95*0.15 + 0.90*0.15 + 1.20*0.25 + 1.05*0.2

            = $1.10

Based on the figures above, we know that in order to achieve the specified profit margins, our selling prices for each mix type is:

Regular = $1.03 + 1.65 = $2.68/lb

Deluxe = $1.07 + $2.00 = $3.07/lb

Holiday = $1.10 + $2.25 = $3.35/lb

2) In the optimal product mix and the total profit contribution the analysis was done using Excel with the following LP model:

Objective:

Maximize function 1.65*(Quantity Regular) + 2*(Quantity Deluxe) + 2.25*(Quantity Holiday)

Subject to constraints: (measured in pounds)

Quantity Regular <= 10000

Quantity Deluxe <= 3000

Quantity Holiday <= 5000

Almonds used <= 6000

Brazils used <= 7500

Filberts used <= 7500

Pecans used <= 6000

Walnuts used <= 7500

Almonds used = 0.15Qreg + 0.2Qdel + 0.25Qhol

Brazils used = 0.25Qreg + 0.2Qdel + 0.15Qhol

Filberts used = 0.25Qreg + 0.2Qdel + 0.15Qhol

Pecans used = 0.10Qreg + 0.2Qdel + 0.25Qhol

Walnuts used = 0.25Qreg + 0.2Qdel + 0.20Qhol

Based on this model, we can obtain these results:

Optimal profit contribution = $33,750

Product mix:

Regular = 10000

Deluxe = 3000

Holiday = 5000

Nut usage:

Almond = 3350

Brazil = 3850

Filbert = 3850

Pecan = 2850

Walnut = 4100

3) Recommendations regarding how the profit contribution can be increased if additional nuts can be purchased

Currently there is an extreme over-supply of all types of nuts, and for that reason the problem was constrained by the number of orders placed. At this point no particular type of nut is preventing a higher profit contribution. In fact, if we were to remove the constraints relating to the number of orders received, the profit contribution would skyrocket to $62250, and at that point we can see that pecans would be the only type with excess 'slack' (given the current mixes).

4) A recommendation as to whether TJ's should purchase an additional 1,000 pounds of almonds for $1,000

The correct solution to this question can really only be found by plugging in the change into the model. The first thing to note is that you are able to obtain these almonds cheaper that before ($0.25/lb discount). In addition, if no constraints are placed on order levels, we find that the profit contribution rises from $62250 (as we saw from the previous question) to $66500. This is a marginal profit of $4250, and when you offset the $1000 cost for the almonds it is $3250 in pure profit. Therefore it is recommended that this purchase be made (assuming orders are anticipated to consume the increased availability that will result).

5) Recommendations on how profit contribution could be increased (if at all) if TJ's does not satisfy all existing orders

Through sensitivity analysis in the Excel model, the holiday mix appears to be the least profitable in terms of maximizing the objective function. This is due to the mix and proportion of nuts used in this mix; not only is it more costly (since it is using higher price nuts), but the profit contribution in comparison to the other two mixes is smaller compared to the costs of the raw nuts.

Therefore if TJ's were to focus on making regular and deluxe mixes he could become more profitable

Related Questions

Navigate

• Browse (All)
• Browse E
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.