Reids Raisin Company

Located in wine country, Reids Raisin Company (RRC) is a food-processing firm that purchases surplus grapes from grape growers, dries them into raisins, applies a layer of sugar, and sells the sugar-coated raisins to major cereal and candy companies. At the beginning of the grape-growing season, RRC has two decisions to make. The first involves how many grapes to buy under contract, and the second involves how much to charge for the sugar-coated raisins it sells. In the spring, RRC typically contracts with a grower who will supply a given amount of grapes in the autumn at a fixed cost of $0.25 per pound. The balance between RRCs grape requirements and those supplied by the grower must be purchased in the autumn, on the open market, at a price that could vary from a historical low of $0.20 per pound to a high of $0.35 per pound. Historically, the mean has been $.30 per pound. (RRC cannot, however, sell grapes on the open market in the autumn if it has a surplus in inventory, because it has no distribution system for such purposes.) The other major decision facing RRC is the price to charge for sugar-coated raisins. RRC has several customers who buy RRCs output in price-dependent quantities. RRC negotiates with these processors as a group to arrive at a price for the sugar-coated raisins and the quantity to be bought at that price. The negotiations take place in the spring, long before the open market price of grapes is known. Based on prior years experience, Mary Jo Reid, RRCs general manager, believes that if RRC prices the sugar-coated raisins at $2.20 per pound, the processors orders will total 750,000 pounds of sugar-coated raisins. Furthermore, this total will increase by 15,000 pounds for each penny reduction in sugar-coated raisin price below $2.20. The same relationship holds in the other direction: demand will drop by 15,000 for each penny increase. The price of $2.20 is a tentative starting point in the negotiations. Sugar-coated raisins are made by washing and drying grapes into raisins, followed by spraying the raisins with a sugar coating that RRC buys for $0.55 per pound. It takes 2.5 pounds of grapes plus 0.05 pound of coating to make one pound of sugar-coated raisins, the balance being water that evaporates during grape drying. In addition to the raw materials cost for the grapes and the coating, it costs RRC $0.20 to process one pound of grapes into raisins (a variable cost), up to its capacity of 1,500,000 pounds of grapes. For volumes above 1,500,000 pounds of grapes, RRC outsources grape processing to another food processor, which charges RRC $0.45 per pound. This outsourced price includes just the processing cost, as RRC supplies both the grapes and the coating required. RRC also incurs fixed (overhead) costs in its grape-processing plant of $200,000 per year.

1) Use the Excel Spreadsheet accompanying this document to construct a financial model for RRC as a base case.

2) Construct two scenarios, High and Low Price. For High Price, set RRCs selling price to $2.27. For Low Price, set RRCs selling price to $2.13. On your spreadsheet, clearly label the profit from each scenario.

3) Construct a data table (using What-If > Data Table) that displays profit as a function of Price (in rows) and pounds of grapes purchased (in columns). Price should initially range from $2.10 to $2.40 in $0.02 steps. Capacity should range from 1,000,000 to 2,000,000 in 100,000 lb steps. Use conditional formatting to display the highest profit cells in green. Copy this table and paste values and formatting, clearly labeling it as answering Q3.

4) Change the granularity of the rows and columns to home in on the optimal price/grape purchase weight combination. Report the optimal price rounded to the nearest $5,000, clearly labeled.

5) Using any technique you know, explain to a manager whether the Open Market (fall) price or the spring negotiated price is more important. You may use a graph if this is helpful.

6) It turns out that management needs to invest $1,000,000 into the business this year to deal with maintenance on equipment that has not been properly maintained. In addition, they need to spend $100,000 per year in every year in the future on maintenance as well. If RRCs discount rate is 10%, should they spend the money. Explain briefly why or why not, and explain the rationale behind your recommendation.

7) Ignore the assumptions in #6. Copy and Paste Special > Values over the table you used to optimize the price. Make the Open Market (Fall) Grape Purchase Price random ~Normal(.30, .025). Make the Spring Negotiated Grape Cost random ~Uniform(.20, .30). Make the price at which RRC can sell grapes random ~Normal($2.20, $.15). Run a simulation and report the distribution of outcomes. You may use the Analytic Solver Platform or Native Excel + a Table. Summarize the distribution of outcomes in managerially relevant terms.

8)If RRC needs to invest $1,000,000 in the business today, what is the probability that they will achieve a 2 year payback? You may assume that the distribution of profits from #7 is ~Normal() with a mean and SD equal to what you found in #7

Respond to questions 4-8