Please use this excel format to solve below question

Mitchell Gordon receives a call from Ed Dawkins, the Vice President of Operations at Tomaco. Dawkins tells Gordon that they are stuck with some B tomatoes that they cannot put to good use. Dawkins offers Gordon some of these tomatoes at 18 cents per pound, generally agreed as the normal tomato price. Mitchell Gordon refuses to buy the tomatoes, claiming that the 18 cents per pound is based on the fact that the incoming crop normally contains a mix of grade A and grade B tomatoes.

Do you agree with Mitchell Gordon?

What is the contribution of additional B tomatoes?

Would you buy additional B tomatoes at 18 cents per pound? As a result of Gordons refusal, Dawkins offers up to one million pounds of B tomatoes at 15 cents per pound.

Would you buy additional B tomatoes at 15 cents per pound?

If yes, how would you allocate these B tomatoes to the different products?

Red Brand Canners case

Red Brand Canners Revisited Red Brand Canners is a medium-size company specialising in canning and distributing a variety of fruit and vegetable products under private brand names in the western states of the US. On Monday September 13th, Mitchell Gordon, Vice-President of Operations, asked the Controller, the Sales Manager, and the Production Manager of Red Brand Canners to meet with him to discuss the amount of tomato products to pack that season. The tomato crop, which had been purchased at planting, was beginning to arrive at the cannery. Packing operations would have to start by the following Monday because, after this time, the fruit would begin to deteriorate. In effect this ruled out the possibility of reselling any part of the crop which, if it remained unpacked, would be worthless. The Meeting William Cooper, Controller, and Charles Myers, Sales Manager, were the first to arrive in Mr. Gordon's office. Dan Tucker, Production Manager, came in a few minutes later and said that he had picked up Produce Inspection's latest estimate of the quality of the incoming tomatoes. According to the report, about 20% of the 3,000,000 pound crop was Grade "A" and the remaining portion Grade Mr. Gordon asked Mr. Myers about the demand for tomato products for the coming year. Mr. Myers replied that they could sell all of the whole canned tomatoes they could produce. The expected demand for tomato juice and tomato paste, however, was limited. The Sales Manager then passed around the latest demand forecast (Exhibit 1) reminding the group that selling prices had been set in light of long-term marketing strategy of the company, and that potential sales had been forecast at these prices After looking at Mr. Myers' estimates of demand, Mr. Cooper said that it looked as though the company, "should do quite well on the tomato crop this year". With the new accounting system that had been set up, he had been able to compute the contribution for each product, and according to his analysis the incremental profit on whole tomatoes was greater than for any other tomato product. In May, after Red Brand had signed contracts agreeing to purchase the grower's production at an average delivered price of 18 cents per pound, Mr. Cooper had computed the tomato products contributions (Exhibit 2) Red Brand Canners Exhibit 1. Demand Forecasts Product Selling Price (per case) $ 12.00 $16.20 S13.80 S 13.50 S 14.70 S 11.40 Demand Forecast (cases) 800,000 10,000 5,000 50,000 15,000 80,000 Pounds (per case) 24-212 whole tomatoes 24-22 choice peach halves 24-212 peach nectar 24-2/2 tomato juice 24-24 cooking apples 24-22 tomato paste 20 27 25 Exhibit 2. Product Item Profitability (per case) Product 24-2% Whole Tomatoes 24-2% Peach Halves 24-22 Peach Nectar Juice Apples Paste 24-22 Tomato Cooking Tomato 24-2% 24-2% Selling Price Direct labour Variable overhead Variable selling Packaging material Fruit Total Variable Costs Contribution Allocated Overhead Net Profit 2.00 16.20 13.80 13.50 0.96 0.90 0.69 0.66 0.84 0.78 1.20 2.55 1.80 3.24 10.80 1.20 5.40 2.70 8.40 6.30 2.25 4.50 10.35 12.60 3.06 1.20 0.36 0.69 0.36 0.96 0.36 Mr. Tucker called Mr. Cooper's attention to the fact that, although production capacity was ample, it was impossible to produce all whole tomatoes because too small a portion of the crop was 'A" quality Red Brand used a numerical scale to record the quality of both raw produce and prepared products. This scale ran from zero to ten, the higher number representing better quality. "A" tomatoes averaged nine points per pound and "B" tomatoes five points per pound. Mr. Tucker noted that the minimum average input quality for canned whole tomatoes was eight and for juice six points per pound. Paste could be made entirely from "B" grade tomatoes. This meant that whole tomato production was limited to 800,000 pounds. Mr. Gordon stated that this was not a real limitation. Recently solicited to purchase 80,000 pounds of Grade "A" tomatoes at 25% cents per pound, he had turned down the offer. He thought, however, that the tomatoes were still available Mr. Myers, who had been doing some calculations, said that although he agreed that the Company "should do quite well this year", it would not be by canning whole tomatoes. It seemed to him that tomato costs should be allocated on the basis of quality and quantity rather than by quantity only, as Mr. Cooper had done. Therefore, he had recomputed the marginal profit on this basis and from his results (see Exhibit 3), believed that Red Brand should use 2,000,000 pounds of the "B" tomatoes for paste, and the remaining 400,000 pounds of "B" tomatoes and all of the "A" tomatoes for juice. If demand expectations were realised, a profit contribution of $144,000 would be made in this year's tomato crop. Red Brand Canners Exhibit 3. Myers' Analysis of Tomato Costs In calculating the cost of the tomatoes used in the different products, we should distinguish based on the quality of the tomatoes ("A" or "B"). Although average price paid for the tomatoes, namely 18 cents per pound, is for the entire crop, I suggest using different costs for the different qualities. Below is a suggestion on how to compute these costs. Assume that x = cost per pound of A" tomatoes used in a product (in cents) "y cost per pound of "B" tomatoes used in a product (in cents) Then: " (600.000 lbs. x)(2,400,000 lbs. y) should equal (3,000,000 lbs. 18), and . x/9 should equal y/5 The first condition ensures that the total price paid for the tomato crop is correct, the second condition ensures that the tomato cost is directly related to its quality, measured by its point value The resulting prices are x-27.9 cents per pound y = 15.5 cents per pound This gives rise to the marginal profit analysis given in Exhibit 4. Exhibit 4. Myers' Marginal Profit Analysis of Tomato Products Product Selling Price Variable Costs (excluding tomato cost) Contribution (excluding tomato cost) Whole Tomatoes ( 12.00 7.56 4.44 4.47 (0.03) Tomato Juice (S) Tomato Paste (S) 13.50 9.54 3.96 3.72 0.24 11.40 5.85 5.55 3.90 1.65 Tomato Cost Marginal Contribution Red Brand Canners Assignment Make a report of approximately 5 pages (excluding appendices) summarising your results, your interpretation of the results and your conclusions and recommendations. Also explain how you derived your results. In your report, follow the structure of the assignment, i.e. provide results and recommendations for each of the 4 questions separately. You will be graded not only on correctness but also on conciseness and elegance of the solutions and explanations. Please submit your report by 10am, Friday, 25-January 2019. One report per group is sufficient. 1. [15] The Model without the option of additional A tomatoes Open the file "RBC.xls". Using this template, build a model for the problem Red Brand Canners is faced with, disregarding the availability of the additional A tomatoes. Make a recommendation about which products to produce in what quantities. How can you be sure that your recommendation is the best possible one? . 2. [20] Additional supply of B tomatoes Save the model as "RBC 2" Mitchell Gordon receives a call from Ed Dawkins, the Vice President of Operations at Tomaco Dawkins tells Gordon that they are stuck with some B tomatoes that they cannot put to good use Dawkins offers Gordon some of these tomatoes at 18 cents per pound, generally agreed as the normal tomato price. Mitchell Gordon refuses to buy the tomatoes, claiming that the 18 cents per pound is based on the fact that the incoming crop normally contains a mix of grade A and grade B tomatoes. Do you agree with Mitchell Gordon? What is the contribution of additional B tomatoes? Would you buy additional B tomatoes at 18 cents per pound? As a result of Gordon's refusal, Dawkins offers up to one million pounds of B tomatoes at 15 cents per pound. Would you buy additional B tomatoes at 15 cents per pound? If yes, how would you allocate these B tomatoes to the different products? 3. [30] Closing down production lines? Open the original model "RBC.xls", and save it as "RBC 3". Ignore the additional B tomatoes. Starting up the production line for each of the products entails a substantial set-up cost. Dan Tucker estimates that the set-up costs are $50,000 per line. This means that if RBC decides not to start up a particular line, they would save $50,000, but they would not be able to produce that particular product. If we decide not to start up the Paste line, will this increase or decrease our total profit? Which production lines would you advise RBC to start up? Hints: (i) Do not use the IF function. (i) Formulate the problem in a most efficient way so that your approach can be generalizable to arbitrary number of production lines. (iii) When finding the optimal profit, find the decision that finds the optimal revenue and then subtract the cost. 1 RED BRAND CANNERS 2 MIX DECISION Whole JuicePasteTotal Required Available 4 Grade A 5 Grade B 6 Total Production 7 75 225 300 4,400 1,0002,000 525 175 700 2,000 2,000 600 2,400 600 2,400 3,000 Demand QUALITY Quality Whole Juice 4,725 875 Paste 10 Grade A 11 Grade B 12 Total Quality 13 Required Total Quality 14 Average Quality 5 Required Average Quality8.0 16 17 18 Contribution Margin 19 675 1,12510,000 5 5,6001,80010,000 5,6001,800 10,000 6.0 6.0 5.0 5.0 8.0 PROFIT WholeJuice Paste Total Contribution $247 $198 $222 $676,300 Red Brand Canners Revisited Red Brand Canners is a medium-size company specialising in canning and distributing a variety of fruit and vegetable products under private brand names in the western states of the US. On Monday September 13th, Mitchell Gordon, Vice-President of Operations, asked the Controller, the Sales Manager, and the Production Manager of Red Brand Canners to meet with him to discuss the amount of tomato products to pack that season. The tomato crop, which had been purchased at planting, was beginning to arrive at the cannery. Packing operations would have to start by the following Monday because, after this time, the fruit would begin to deteriorate. In effect this ruled out the possibility of reselling any part of the crop which, if it remained unpacked, would be worthless. The Meeting William Cooper, Controller, and Charles Myers, Sales Manager, were the first to arrive in Mr. Gordon's office. Dan Tucker, Production Manager, came in a few minutes later and said that he had picked up Produce Inspection's latest estimate of the quality of the incoming tomatoes. According to the report, about 20% of the 3,000,000 pound crop was Grade "A" and the remaining portion Grade Mr. Gordon asked Mr. Myers about the demand for tomato products for the coming year. Mr. Myers replied that they could sell all of the whole canned tomatoes they could produce. The expected demand for tomato juice and tomato paste, however, was limited. The Sales Manager then passed around the latest demand forecast (Exhibit 1) reminding the group that selling prices had been set in light of long-term marketing strategy of the company, and that potential sales had been forecast at these prices After looking at Mr. Myers' estimates of demand, Mr. Cooper said that it looked as though the company, "should do quite well on the tomato crop this year". With the new accounting system that had been set up, he had been able to compute the contribution for each product, and according to his analysis the incremental profit on whole tomatoes was greater than for any other tomato product. In May, after Red Brand had signed contracts agreeing to purchase the grower's production at an average delivered price of 18 cents per pound, Mr. Cooper had computed the tomato products contributions (Exhibit 2) Red Brand Canners Exhibit 1. Demand Forecasts Product Selling Price (per case) $ 12.00 $16.20 S13.80 S 13.50 S 14.70 S 11.40 Demand Forecast (cases) 800,000 10,000 5,000 50,000 15,000 80,000 Pounds (per case) 24-212 whole tomatoes 24-22 choice peach halves 24-212 peach nectar 24-2/2 tomato juice 24-24 cooking apples 24-22 tomato paste 20 27 25 Exhibit 2. Product Item Profitability (per case) Product 24-2% Whole Tomatoes 24-2% Peach Halves 24-22 Peach Nectar Juice Apples Paste 24-22 Tomato Cooking Tomato 24-2% 24-2% Selling Price Direct labour Variable overhead Variable selling Packaging material Fruit Total Variable Costs Contribution Allocated Overhead Net Profit 2.00 16.20 13.80 13.50 0.96 0.90 0.69 0.66 0.84 0.78 1.20 2.55 1.80 3.24 10.80 1.20 5.40 2.70 8.40 6.30 2.25 4.50 10.35 12.60 3.06 1.20 0.36 0.69 0.36 0.96 0.36 Mr. Tucker called Mr. Cooper's attention to the fact that, although production capacity was ample, it was impossible to produce all whole tomatoes because too small a portion of the crop was 'A" quality Red Brand used a numerical scale to record the quality of both raw produce and prepared products. This scale ran from zero to ten, the higher number representing better quality. "A" tomatoes averaged nine points per pound and "B" tomatoes five points per pound. Mr. Tucker noted that the minimum average input quality for canned whole tomatoes was eight and for juice six points per pound. Paste could be made entirely from "B" grade tomatoes. This meant that whole tomato production was limited to 800,000 pounds. Mr. Gordon stated that this was not a real limitation. Recently solicited to purchase 80,000 pounds of Grade "A" tomatoes at 25% cents per pound, he had turned down the offer. He thought, however, that the tomatoes were still available Mr. Myers, who had been doing some calculations, said that although he agreed that the Company "should do quite well this year", it would not be by canning whole tomatoes. It seemed to him that tomato costs should be allocated on the basis of quality and quantity rather than by quantity only, as Mr. Cooper had done. Therefore, he had recomputed the marginal profit on this basis and from his results (see Exhibit 3), believed that Red Brand should use 2,000,000 pounds of the "B" tomatoes for paste, and the remaining 400,000 pounds of "B" tomatoes and all of the "A" tomatoes for juice. If demand expectations were realised, a profit contribution of $144,000 would be made in this year's tomato crop. Red Brand Canners Exhibit 3. Myers' Analysis of Tomato Costs In calculating the cost of the tomatoes used in the different products, we should distinguish based on the quality of the tomatoes ("A" or "B"). Although average price paid for the tomatoes, namely 18 cents per pound, is for the entire crop, I suggest using different costs for the different qualities. Below is a suggestion on how to compute these costs. Assume that x = cost per pound of A" tomatoes used in a product (in cents) "y cost per pound of "B" tomatoes used in a product (in cents) Then: " (600.000 lbs. x)(2,400,000 lbs. y) should equal (3,000,000 lbs. 18), and . x/9 should equal y/5 The first condition ensures that the total price paid for the tomato crop is correct, the second condition ensures that the tomato cost is directly related to its quality, measured by its point value The resulting prices are x-27.9 cents per pound y = 15.5 cents per pound This gives rise to the marginal profit analysis given in Exhibit 4. Exhibit 4. Myers' Marginal Profit Analysis of Tomato Products Product Selling Price Variable Costs (excluding tomato cost) Contribution (excluding tomato cost) Whole Tomatoes ( 12.00 7.56 4.44 4.47 (0.03) Tomato Juice (S) Tomato Paste (S) 13.50 9.54 3.96 3.72 0.24 11.40 5.85 5.55 3.90 1.65 Tomato Cost Marginal Contribution Red Brand Canners Assignment Make a report of approximately 5 pages (excluding appendices) summarising your results, your interpretation of the results and your conclusions and recommendations. Also explain how you derived your results. In your report, follow the structure of the assignment, i.e. provide results and recommendations for each of the 4 questions separately. You will be graded not only on correctness but also on conciseness and elegance of the solutions and explanations. Please submit your report by 10am, Friday, 25-January 2019. One report per group is sufficient. 1. [15] The Model without the option of additional A tomatoes Open the file "RBC.xls". Using this template, build a model for the problem Red Brand Canners is faced with, disregarding the availability of the additional A tomatoes. Make a recommendation about which products to produce in what quantities. How can you be sure that your recommendation is the best possible one? . 2. [20] Additional supply of B tomatoes Save the model as "RBC 2" Mitchell Gordon receives a call from Ed Dawkins, the Vice President of Operations at Tomaco Dawkins tells Gordon that they are stuck with some B tomatoes that they cannot put to good use Dawkins offers Gordon some of these tomatoes at 18 cents per pound, generally agreed as the normal tomato price. Mitchell Gordon refuses to buy the tomatoes, claiming that the 18 cents per pound is based on the fact that the incoming crop normally contains a mix of grade A and grade B tomatoes. Do you agree with Mitchell Gordon? What is the contribution of additional B tomatoes? Would you buy additional B tomatoes at 18 cents per pound? As a result of Gordon's refusal, Dawkins offers up to one million pounds of B tomatoes at 15 cents per pound. Would you buy additional B tomatoes at 15 cents per pound? If yes, how would you allocate these B tomatoes to the different products? 3. [30] Closing down production lines? Open the original model "RBC.xls", and save it as "RBC 3". Ignore the additional B tomatoes. Starting up the production line for each of the products entails a substantial set-up cost. Dan Tucker estimates that the set-up costs are $50,000 per line. This means that if RBC decides not to start up a particular line, they would save $50,000, but they would not be able to produce that particular product. If we decide not to start up the Paste line, will this increase or decrease our total profit? Which production lines would you advise RBC to start up? Hints: (i) Do not use the IF function. (i) Formulate the problem in a most efficient way so that your approach can be generalizable to arbitrary number of production lines. (iii) When finding the optimal profit, find the decision that finds the optimal revenue and then subtract the cost. 1 RED BRAND CANNERS 2 MIX DECISION Whole JuicePasteTotal Required Available 4 Grade A 5 Grade B 6 Total Production 7 75 225 300 4,400 1,0002,000 525 175 700 2,000 2,000 600 2,400 600 2,400 3,000 Demand QUALITY Quality Whole Juice 4,725 875 Paste 10 Grade A 11 Grade B 12 Total Quality 13 Required Total Quality 14 Average Quality 5 Required Average Quality8.0 16 17 18 Contribution Margin 19 675 1,12510,000 5 5,6001,80010,000 5,6001,800 10,000 6.0 6.0 5.0 5.0 8.0 PROFIT WholeJuice Paste Total Contribution $247 $198 $222 $676,300