Located in Napa Valley, Terri's Raisin Company (TRC) 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, TRC 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, TRC 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 TRC's 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. (TRC cannot, however, sell grapes on the open market in the autumn if it has a surplus of inventory because it does not have a distribution system for such purposes.)The other major decision facing TRC is the price to charge for sugar-coated raisins. TRC has several customers who buy TRC's output in price-dependent quantities. TRC negotiates with these processors as a group to arrive at a price for the sugar-coated raisins and 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, TRC's general manager believes that if TRC 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 every 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. The information above allows us to construct a demand function that estimates the quantity of raisins demanded based on the price that we charge. This demand function is Q (p) = 4,050,0001,500,000p,where Q(p) is the quantity of raisins demanded (per pound) at the price per pound p. You can double-check that this is correct by substituting p= $2.20 into the demand function to confirm that the resulting quantity demanded is 750,000 pounds as stated above. Sugar-coated raisins are made by washing and drying grapes into raisins, followed by spraying the raisins with a sugar coating that TRC buys for$0.55 per pound. It takes 2.5 pounds of grapes plus 0.5pounds of coating to make one pound of sugar-coated raisins, the balance being water that evaporates during the drying of the grapes. In addition to the raw materials cost for the grapes and the coating, TRC's processing plant incurs a variable cost of$0.20 to process one pound of grapes into raisins, up to its capacity of 1.5 million pounds of grapes. For volumes above 1.5 million pounds of grapes, TRC outsources grape processing to another food processor, which charges TRC $0.45 per pound. This price includes just the processing cost, as TRC supplies both the grapes and the coating required. TRC also incurs fixed overhead costs in its grape processing plant of $200,000 per year. The general manager has asked you to analyze the situation to guide her in the upcoming negotiations. Her goal is to examine the effect of various "What if?" scenarios on TRC'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 pre-tax profit as a function of the selling price and open-market grape price. She believes that the open-market grape price is most likely to be $0.30.

a. Design a spreadsheet that will allow TRC to project profit for the base-case conditions identified in the last paragraph of the problem.

b. Construct a data table showing how profit varies as a function of the price set for sugar-coated raisins. Your table should cover a range of prices from$1.80 to$2.80 in steps of$0.10