Sunny Hills Raisin Company (SHRC), located in wine country, is a food-processing firm that purchases surplus grapes from grape growers. They dry the grapes into raisins, apply a layer of sugar, and sell these sugar-coated raisins to major cereal and candy companies. At the beginning of the grape-growing season, SHRC has two decisions to make. The first is how many grapes to buy under contract. The second involves how much to charge for the sugar-coated raisins it sells.

In the spring, SHRC 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. Any amount required by SHRC over that contracted amount must then be purchased on the open market in the autumn. The price on the open market could easily vary. The historical low has been $0.20 per pound and the historical high has been $0.35 per pound. (Note that SHRC cannot contract for a very, very large quantity in the spring because if they over-buy, the extra grapes are waste. That is, there is no mechanism for them to sell any surplus grapes they may have purchased under contract.)

The other major decision facing SHRC is the price to charge for sugar-coated raisins. SHRC has several customers who buy SHRC’s output in price-dependent quantities. SHRC 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, Lindabeth, SHRC’s general manager, believes that if SHRC prices the sugar-coated raisins at $2.20 per pound, the processors’ orders will total 750,000 pounds of sugar-coated raisins. Furthermore, she thinks this total will increase by 15,000 pounds for each penny reduction in sugar-coated raisin price of $2.20. A similar relationship exists for an increase in the per-pound price: each penny above $2.20 will decrease the demand by 15,000 pounds. Lindabeth is planning on using $2.20 as the tentative starting point in negotiations.

Sugar-coated raisins are made by washing and drying grapes into raisins, followed by spraying raisins with a sugar coating that SHRC buys for $0.55 per pound. It takes 2.5 pounds of grapes plus 0.05 pounds 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, SHRC’s processing plant incurs a variable cost of $0.20 to process one pound of grapesinto raisins, up to its capacity of 1,500,000 pounds of grapes. For volumes above 1,500,000 pounds of grapes, SHRC outsources grape processing to another food processor which charges SHRC $0.45 per pound. This price includes just the processing cost, as SHRC supplies both the grapes and the coating required. SHRC also incurs fixed (overhead) costs from the grape processing plant of $200,000 per year.

Lindabeth has asked you to analyze the situation to help guide her in the upcoming negotiations. One of her goals is to examine the effect of various “What-if?” scenarios on SHRC’s profits. As a basis for the analysis, she suggests using a contract purchase price of $0.25, with a supply quantity of 1 million pounds of grapes from the grower, along with a selling price of $2.20 for sugar-coated raisins. She is primarily interested in evaluating annual pretax profit as a function of the selling price and the open-market grape price, but she is open to other suggestions surrounding sensitivity analysis. She thinks the open-market grape price will most likely be $0.30 per pound.

Please solve Q1 for me in a manner which is easy to understand and upload the excel file for me. Ignore if you dont know how to draw an influence diagram. But if you know please do it will be helpful

Q1) Build a spreadsheet model that helps solve the problem statement. Your model should consist of a “base” model where the user can easily change the decisions and parameters to see how robust the results are. For example, what happens if the selling price is $2.19 or $2.21 instead of the suggested base of $2.20? Also draw the influence diagram?

Q2) What are your recommendations to Lindabeth?

Answer rating: 100% (QA)

Q1 To create a spreadsheet model to solve the problem statement follow these steps 1 Open a spreadsheet software like Microsoft Excel or Google Sheets 2 Set up your spreadsheet with the following colu... View the full answer