The Orchard Beverage Company produces healthy alternatives to widely available soft drinks. Orchard makes its high-quality beverages with fresh fruit (bought at the peak of the season, when the fruit is juiciest, plentiful, and least expensive). These drinks are made with no preservatives in small batches. Over the company's first five years, sales have been brisk, with cases selling out well before forecasted. The owners of the company are considering rapid expansion of its production facilities, so that it can better respond to demand and possibly expand beyond its current regional sales territory. Before the company seriously considers expansion, the owners turn to you, the Orchard CFO/controller, to build a spreadsheet that will help owners better understand company profitability. Furthermore, considering the volatility of fruit prices, the owners also need to the ability to perform sensitivity analysis so that owners can try "what if" scenarios with respect to the price of fruit and the percentage of fruit used in each of its two products. Orchard's drink-ologists have experimented with blends and have found a certain mix can be used as a substitute in its drinks. A mix of fresh pear and grapefruit is used as a substitute when supplies of fresh lemons or limes are too few or too costly. For its Mixed Berries drink, Orchard uses a blend of grapes as a substitute when necessary. The results of taste tests suggest that few consumers will detect differences when cheaper ingredients are used, in moderation. The "classic" blend of Orchard's Citrus and Mixed Berries drinks are: Citrus: 48% Lemon, 48% Lime, 4% Pear/Grapefruit Blend Mixed Berries: 40\% Blueberry, 30\% Blackberry, 20\% Raspberry, 10\% Grape Blend Looking forward to next year, the sales team and you have developed following projections: Orchard Beverage operates in a state with no state income taxes and projects to be in the 25% Federal income tax bracket. For serious consideration of expansion, Orchard expects an after-tax profit of $1,500,000 is necessary to service the debt of a major expansion, should it decide to expand. Requirements: Prepare a spreadsheet using the model on the next page to meet the owners' needs. Answer the three questions that follow the model. Input fields are grayed out. Questions: 1. Using the "Classic Blend" (no Price Changes=0\%), how cases of Citrus and Mixed Berries drinks are needed to break-even? Use the sales and cost figures from the projection developed last year. Round the number of cases in the package up to the next whole number. 2. Assuming the 25% Federal income tax rate, how many cases of Citrus and Mixed Berries drinks are needed to achieve the goal of Net Income after Taxes of $1,500,000 ? Answers within $50 of this target are acceptable, due to rounding. 3. Suppose the price of blueberries increases by 100% (doubles). Recommend an adjustment to the blend percentages or sales price that will enable Orchard to still make its desired after-tax profit. Explain your decision