Reference no: EM132165489

Reid’s Raisin Company Located in wine country, Reid’s 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 cereal and candy companies. At the beginning of the grape-growing season, RRC has two decisions to make: what cost to contract for grape purchases (which are delivered in the autumn), and determining the price for the sugar-coated raisins it sells. RRC’s customers buy raisins in price-dependent quantities. Based on prior years’ experience, Mary Jo Reid, RRC’s 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, demand will increase by 15,000 pounds for each penny reduction in sugar-coated raisin price below $2.20 (ex: if the price decreases to $2.19, then the demand would be 750,000 + 15,000 = 765,000). The same relationship holds in the other direction: demand will drop by 15,000 for each penny increase (ex: if the price increases to $2.21, then demand would be 750,000 – 15,000 = 735,000). You will need to create a formula to handle this calculation for any price input. Once demand is known, the production requirements can then be determined. Sugar-coated raisins are made by washing and drying grapes into raisins, followed by spraying the raisins with a sugar coating. It takes 2.5 pounds of grapes and 0.05 pounds of coating to make 1 pound of sugar-coated raisins (the balance being water that evaporates during grape drying). In addition to the raw materials cost for the grapes, RRC pays $0.55 per pound of coating. RRC incurs a variable production cost of $0.20 per pound of grapes at the processing plant, which includes labor. Finally, RRC has annual fixed (overhead) costs at its grape-processing plant that total $200,000. Mary Jo has asked you to analyze the pre-tax profit for this situation in order to guide her in the upcoming negotiations and determine production and sales requirements for this year. Her goal is to examine the effect of various “what-if” scenarios on RRC’s profits.?

Tasks: 1. Build the spreadsheet model to solve this problem. Make sure you follow the guidelines for good spreadsheet design including proper cell referencing, good flow/layout, etc. Your grade on this portion of the HW reflects not only on having the correct calculations, but also for following those guidelines and creating a well-designed and easy-to-use worksheet. I will not be collecting sketches or influence charts, but please use these tools if necessary to facilitate your spreadsheet design process! • As the base case for this analysis, use a contract cost of $0.25 per pound of grapes along with the selling price of $2.20 per pound of sugar-coated raisins.

2. Perform a series of sensitivity analysis activities. For parts a & b, please provide these answers on the same worksheet containing your model. NOTE: DO NOT create multiple versions of your model, USE the model developed in Task 1 to answer these questions. If you encounter issues with the analysis during Task 2, use this as an opportunity to revise and improve your model. a. Using Goal Seek, determine the break-even point for the contract cost for grapes (per lb). i. What is this break-even cost? ii. How does this value compare to the current? • NOTE: Make sure you reset this cost to the base-case value in your model before proceeding. b. Create a One Input Data Table for raisin price, ranging from a low of $1.80 to a high $2.60 in $0.10 increments. i. What price yields the highest profit? c. Use the Scenario Manager to create the following scenarios: i. Scenario 1: Contract Cost is $0.30 and Raisin Price is $2.30 ii. Scenario 2: Contract Cost is $0.30 and Raisin Price is $2.10 iii. Scenario 3: Contract Cost is $0.20 and Raisin Price is $2.30 iv. Scenario 4: Contract Cost is $0.20 and Raisin Price is $2.10 Create a Scenario Summary with the raisin sales and profit as the Results Cells. Keep your Scenario Summary on a separate worksheet. Retitle the labels in your table with parameter names instead of cell names. d. Create a Tornado Chart for this model (NOTE: this requires the use of Crystal Ball). Make sure you add all precedents and use the default settings for the Tornado Analysis tool. Keep your Tornado Chart on a separate worksheet. If you created a well-designed spreadsheet model, the parameters will have names on the chart. If you only see cell names on the chart, add labels for all of the top ten parameters.