The Orchard Beverage Company produces healthy alternatives to widely available soft drinks. Orchard makes its high-quality...

Transcribed Image Text:

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: Expected Sales (cases) Selling price per case Material Cost per case Direct Labor per case Variable Overhead per case Fixed Overhead Citrus Mixed Berries Total 50,000 10,000 $ 100.00 $ 95.00 50.00 48.00 10.00 7.00 7.92 6.60 $ 550,000 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. 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: Expected Sales (cases) Selling price per case Material Cost per case Direct Labor per case Variable Overhead per case Fixed Overhead Citrus Mixed Berries Total 50,000 10,000 $ 100.00 $ 95.00 50.00 48.00 10.00 7.00 7.92 6.60 $ 550,000 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.