Question

On Monday, February 10, Mr. Mitchell Gordon, Vice President of Operations, asked the Controller, the Sales Manager, and the Production Manager to meet with him to discuss the number of tomato products to pack that season. The tomato crop, which had been purchased at planting, was beginning to arrive at the cannery, and packing operations would have to be started by the following Monday. Red Brand Canners was a medium-sized company that canned and distributed a variety of fruit and vegetable products under private brands in the western states.

Mr. William Cooper, the Controller, and Mr. Charles Myers, the Sales Manager, were the first to arrive in Mr. Gordon’s office. Dan Tucker, the 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 their report, about 20 percent of the crop was Grade A quality, and the remaining portion of the 3-million-pound crop was Grade B.

Gordon asked Myers about the demand for tomato products for the coming year. Myers replied that they could sell all of the whole canned tomatoes they could produce. The expected demand for tomato juice and tomato paste, on the other hand, was limited. The Sales Manager then passed around the latest demand forecast, which is shown in Table 1. He reminded the group that the selling prices had been set in light of the long-term marketing strategy of the company and that the potential sales had been forecast at these prices.

Table 1 Demand Forecasts Product 24 - 2/2 whole tomatoes 24 - 27/2 choice peach halves 24 - 27/2 peach nectar 24 - 2%2 tomato juice 24 - 24/2 cooking apples 24 - 27/2 tomato paste Selling Price per Case $4.00 5.40 4.60 4.50 4.90 3.80 Demand Forecast (cases) 800,000 10,000 5,000 50,000 15,000 80,000

Bill Cooper, after looking at Myers’ estimates of demand, said that it looked like 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 the whole tomatoes was greater than the incremental profit on 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 6 cents per pound, Cooper had computed the tomato product contributions (see Table 2).

Dan Tucker brought to Cooper’s attention that
capacity, it was impossible to produce all whole tomatoes, since too small a portion of the tomato 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 0 to 10, the higher number representing better quality. According to this scale, “A” tomatoes averaged 9 points per pound and “B” tomatoes averaged 5 points per pound. Tucker noted that the minimum average input quality was 8 points per pound for canned whole tomatoes and 6 points per pound for juice. The paste could be made entirely from “B” grade tomatoes. This meant that whole tomato production was limited to 800,000 pounds.

Gordon stated that this was not a real limitation. He had been recently solicited to purchase 80,000 pounds of grade A tomatoes at 81⁄2 cents per pound and at that time had turned down the offer. He felt, however, that the tomatoes were still available.

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 the tomato cost should be allocated on the basis of quality and quantity rather than by quantity only, as Cooper had done. Therefore, he had recomputed the marginal profit on this basis (see Table 3), and from his results had concluded thatRed Brand should use 2,000,000 pounds of the “B” tomatoes for paste and the remaining400,000 pounds of “B” tomatoes and all of the “A” tomatoes for juice. If the demand expectations were realized, a contribution of $48,000 would be made on this year’s tomato crop.

3. Formulate this same linear programming problem on a spreadsheet and SOLVE using Excel solver. Keep at least FOUR DECIMAL POINTS in the solution output. (Provide the corresponding “Excel Spreadsheet”, “Answer Report” and “Sensitivity Report”). Interpret the solution (i.e. include “managerial statements” that communicate the results of the analyses). (7 points)

Please help me figure out how to format this properly with screenshots from excel, please.