Red Brand Canners

RED BRAND CANNERS REVISITED

Resource allocation report

Decision and Risk analysis

Contents

Executive Summary page 2

Introduction page 2

1 The basic model without the option of additional A tomatoes page 2

2 Additional supply of A tomatoes (AA) page 3

3 Advertising page 3

4 Additional supply of B tomatoes (BB) page 4

5 Closing down production lines? page 4

Appendix 1 - The Basic Model page 5

Appendix 2 - Additional Supply of A tomatoes page 7

Appendix 3 - Advertising page 8

Appendix 4 - Additional supply of B tomatoes page 9

Appendix 5 - Closing down production lines? Page 10

Executive Summary

By using resource allocation techniques with the support of Excel Solver, we were able to find an optimal solution that maximizes RBC's profit ($136,300) for this year's tomato crop, taking into consideration the known constrains. Then, using technical reasoning (rather than intuition alone), we were able to recommend the purchase of additional A grade and B grade tomatoes, with optimal allocation of raw materials to the three product lines; we also evaluated the target product and maximum permissible expenditure for an advertising campaign. Finally we analyse the profitability of running the three production lines and recommend the closure of one of the product lines in order to maximize profits.

It is evident that the more the information available to the decision maker, the better prepared he or she is, to make decisions. Very often decision makers are mislead by their intuition (as in the case of Mr.Myers and Mr.Cooper). Hence whenever possible, a quantitative model should be constructed to solve resource allocation problems, minimizing costly errors from decisions made solely by intuitive reasoning.

Introduction

Objective

The objective of this workshop is to assist the management of Red Brand canners in their decisions to purchase and allocate resources effectively and thereby maximise profit. While doing so, we are careful not to base these business decisions solely on intuition and provide quantitative back up for each of our recommendations.

Inputs and constraints

* Demand for whole tomatoes and tomato juice is much more than Red Brand Canners can supply, whereas demand for tomato paste is limited.

* It is impossible to produce all whole tomatoes, as too small portion of the crop (20%) is grade A.

* Intuitively it is preferred that all tomato paste (quality required 5 points) requirement should be met by grade B tomatoes (quality 5 points); not all grade A tomatoes (quality 9 points) should be used to prepare whole tomatoes (quality required 8 points), in order to avoid excess quality.

* There is a linear relationship between the variables.

* All tomatoes are considered sunk costs because the decision of acquiring them was made in the past. So, they should not be considered for decision-making.

* For effective resource allocation, we aim to maximise profit contribution of each product, subject to the following:

o Maximise on products selling at higher profit margin and higher return (whole tomatoes 24.7 cents/lb).

o Keep to minimum the products selling with lower return (tomato juice 19.8 cents/lb).

o Keep a control on the quality: minimum quality requirement should be met, however higher than the minimum requirement is not required as this essentially reduces profits.

Further to the above intuitive analysis, we utilise a software tool, Excel's Solver, to further analyse the problem before we make our recommendations.

1 The basic model without the option of additional A tomatoes

The two models used by RBC were based solely on intuition and did not allocate the raw material optimally. Appendix 1 shows our basic model. In the Solver parameters, we set to maximum the 'Total Contributions'. The variable cells are the quantity of grade A and grade B tomatoes used for each of the three products. The Constraints set are:

i) [Supply] 'Total tomatoes' are less than/equal to 'available qty'.

ii) [Demand] 'Total production' is less than/equal to 'forecast demand'

iii) [Quality] 'Total quality' is greater than/equal to 'total required quality'

Solver calculates the optimal allocation within the above constraints, maximising 'total contributions' and hence 'total profit'. It can be seen that for paste, only 100% grade B tomatoes are used, meeting the full demand. For whole tomatoes, Solver has allocated 75% grade A and 25% grade B tomatoes; demand is only partially met. The remainder tomatoes are allocated to juice in the ratio 25% grade A and 75% grade B. Only 30% of the demand is met. In all products, there is no wastage.

We can also see that Solver has exactly matched the Total quality with the required total quality, thereby keeping quality to the minimum requirements. Profit is maximum at $136,300.

The above recommendation is the best possible one since the model is linear; the objective function and all constraints are linear equations. We also indicated this in Solvers' Options ('assume linear model'). Also in the Options, we set 'tolerance' to zero. Therefore we know that Solver will yield only the optimal solution. This is further tested, by varying the allocations; we find that any changes from the optimal solution always decreases the profits.

2 Additional supply of A tomatoes (AA)

Appendix 2 shows the modified model incorporating the option of buying up to a total of 80,000 pounds of additional AA tomatoes at 25.5 cents/pound. If we decide to buy the AA