In 2018, David Tucker quit his job at a large beer company to start his own brewery, Arizona Microbrewery, Inc. (AMI). His family supported his decision and invested in the business along with David. On January 10, 2019, AMI started its operations and now produces four labels of specialty beers (Saguaro Pale Ale, Bisbee Bock, Ocotillo Amber Pilsner andSedona Stout).

AMI sells all beer in cases of 24 bottles to beer distributors (primarily in the Southwestern United States). In much of theUnited States (including Arizona), selling beer involves a three-tier system. Under this system, producers manufacture the beer, sell it to distributors, who, in turn, resell the beer to retailers (such as liquor stores, drug stores, grocery stores, etc.). AMI employs two salespeople, who receive a fixed monthly salary plus a $2.00 commission on each case of beer sold. Since the company is so new, sales have been growing erratically (from 2019 to 2020, sales growth was over 45%; however, from 2020 to 2021, sales growth is forecast at 12%). The Arizona Microbrewery Analysis Excel workbook posted to the Data Analytics Exercise 2 folder on Blackboard provides projected product sales and cost information forecasts for 2021.

AMI rents its facility which includes a production area where AMI makes the beer, a refrigeration area to store the beer,and a small office area. AMI brewery has five machines with 9,300 total machine hours available per year to producebeer. While AMI could use an empty space in its rented facility to expand its beer operations, the company would need to purchase additional machinery for about $100,000. While AMI has some cash on hand, neither the company nor Davidsfamily have another $100,000 to invest in the business for additional machinery right now. Since the business is new andhas been showing only small profits, David has not been able to get a loan to expand the business. Instead, David wants to fully utilize the machines the company already has. In 2021, David projects the company will use a little over 8,300 machine hours (as shown in the Arizona Microbrewery Analysis Excel Workbook) and the existing five machines have a total of 9,300 machine hours available during the year (assuming normal maintenance and some repairs needed during the year). Thus, the existing machines have about 1,000 additional hours available for use. David wants to keep producingand selling all four of his product lines, as many of the beer distributors like buying from breweries with several differentbeers. However, he wants to direct the salespeople to emphasize a certain product when they are talking with the beerdistributors. Given the current machine availability, David is not sure what beer product line to tell the salespeople to emphasize to maximize his profits.

While the business has been generating a small profit, David has been reinvesting earnings back into the business. Hewants to grow the business to generate more profit for him and for his family. David has been considering increasing theprice on Sedona Stout from $26.50 per case to $29.00 per case. He thinks that, with this price increase, unit sales will decrease from 4,184 cases to 3,750 cases per year. However, this price change would only reduce total annual Stout revenues from $110,876 to $108,750. Alternatively, David could drop the price of Sedona Stout to $25 per case. This price is much closer to the prices of the Bisbee Bock and the Ocotillo Amber Pilsner beers that he also sells. Based on his market research, David thinks that this price cut will result in Sedona Stout sales increasing to 4,700 cases per year. He is leaning towards choosing this alternative as it will increase Stout revenues from $110,876 to $117,500 per year. Your assignment is to analyze Davids business.

REQUIREMENTS:

First, in the Arizona Microbrewery Analysis Excel workbook posted to Blackboard, complete the worksheets by answering the following questions:

1. Using the projected 2021 sales data, calculate the AMIs overall weighted average contribution margin per case and its product mix (based on number of cases) for each product line.
2. Using the product mix you identified in Question 1, how many cases would David need to sell in 2022 to earn a Net Operating Income (NOI) of $85,000? (Remember: David can only sell whole cases). What is the percentage growth (over projected 2021 sales) in total cases necessary to meet this NOI projection? In addition to the total number of cases, please provide a breakdown of the cases by product line and a projected 2022 contribution margin income statement.
3. If David wants to maximize profits at projected 2021 sales levels, which product line should the salespeopleemphasize? If David becomes constrained by the number of machine hours available for his operations, which product line should the salespeople emphasize?
4. What is the projected annual contribution margin for Sedona Stout if David a) keeps the sales price the same (no change) from 2021 sales volume of 4,184 cases, b) increases the sales price to $29 but sells only 3,750 cases and 3) decreases the sales price $25 and sells 4,700 cases.

Prepare a memorandum to David providing him with your insights about his business. Be sure to specifically address the following two questions:

1. Which of the following options do you recommend David choose regarding Sedona Stout pricing: a) keep the sales price the same (no change), b) increase the sales price or 3) decrease the sales price. Why?
2. What is the likelihood of David achieving a NOI of $85,000 in 2022? Why?