Resource allocation report
Decision and Risk analysis
Executive Summarypage 2
Introduction page 2
1 The basic model without the option of additional A tomatoespage 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 Modelpage 5
Appendix 2 Additional Supply of A tomatoespage 7
Appendix 3 Advertisingpage 8
Appendix 4 Additional supply of B tomatoespage 9
Appendix 5 Closing down production lines?Page 10
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.
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: oMaximise on products selling at higher profit margin and higher return (whole tomatoes 24.7 cents/lb). oKeep to minimum the products selling with lower return (tomato juice 19.8 cents/lb). oKeep 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.
1The 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...