Comprehensive Course Project Case Scenario

Scenario

Grizzly Bear Winery (GBW) is a small winery in Portland, OR. Kerry Smith is the CEO and President of the Board of Directors, which is made-up of local business owners and professionals. In 2020, GBW had a net loss of ($3,914). The company pays a 21% corporate tax rate in the years when it has a profit.

GBW has a relationship with a local vineyard owner who grows two types of wine grapes: a Cabernet and a generic red grape. Last year GBW bought 130,000 lbs. of Cabernet grapes, and 350,000 lbs. of generic grapes. The vineyard can guarantee this amount every year.

After the grapes are harvested, they are brought to the winery for processing into wine. The Cabernet wine is fermented in oak barrels. The generic wine is fermented in a stainless-steel tank. GBW bottles three wines: Cabernet Estate, a Blended wine, and a Red Table wine.

• The Cabernet Estate varietal contains only Cabernet grapes and requires 4 lbs of Cabernet grapes. The sales price is $23.50/bottle.
• The blended wine is made by combining 1 lb of Cabernet grapes and 3 lbs of generic grapes. The different grapes are fermented separately and blended before bottling. The sales price is $17/bottle.
• The Table wine is made from only generic grapes and requires 4 lbs of grapes. The sales price is $7/bottle.

All three wines are packaged at the GBW facility. Current year Sales and unit prices are shown below. Demand for each product is expected to grow significantly, as shown.

Table 1: Sales Data

	Price per bottle	# of bottles sold	Expected Demand (next year)
Estate	$23.50	20,000	42,000
Blend	$17.00	45,200	80,000
Table	$7.00	50,000	86,000
		115,200	208,000

Since demand is strong, and the company sells all the wine it produces, the CEO suspects that one of the wine varietals is not profitable and is therefore causing the company to be in the red. She needs your help in determining if any of the products are dragging down the profit margin, and if so, what should be done about it. She would like to know if she should cut pay for some employees, raise prices, change the sales mix, or drop a product.

Additionally, the CEO was recently offered the opportunity to buy additional grapes from another vineyard at current market prices. With a fixed capacity of 150,000 bottles, the winery has had enough capacity to meet demand up to this point. Thus far, the only factor that has limited the amount of wine produced has been the number of pounds of grapes that were available to be purchased. The new supplier has can offer an additional 100,000 pounds of Cabernet grapes at $1.15/lb., and an additional 100,000 pounds of the generic grapes at $0.78/lb. This is the same price as the current supplier.

The CEO would like to understand the implications of this opportunity and how the financial situation would change if she were to buy the extra grapes. Finally, given that demand is so strong, the CEO would like your recommendation on whether or not you would recommend increasing plant capacity.

Additional Cost Data

Direct Labor

Direct Labor Wage rate is $15/hr.

Table 2: Direct Labor costs

Direct labor by product	Cost per bottle	Hours of labor per bottle
Estate	$ 5.25	0.35
Blend	$ 4.50	0.30
Table	$ 3.75	0.25

Direct Materials

Table 3: Direct Materials Costs

Direct Materials by product	Cost per bottle
Estate	$5.40
Blend	$4.32
Table	$3.96

Overhead Costs

GBW currently computes overhead costs by using traditional cost allocation methods but is considering using Activity-based costing. GBW believes that there are three drivers of indirect costs, the number of varietals produced, the number of pallets ordered, and the number of manufacturing hours per year. The factory has capacity of 150,000 bottles and runs on a 24/7 schedule. There are 8,760 manufacturing hours per year (24 hours x 365 days).

Table 4: ABC - Activity Cost Pools

Activity Pools	Cost Driver	Expected Activity Level
Winemaking	# of varietals	3
Customer Orders	# of pallets	800
Production Support	Mfg hours	8,760

• Sales Commissions: Distributors are paid a commission of $72.00 per pallet. Each pallet contains 12 cases, or 144 bottles of wine. In 2020, distributors ordered 800 pallets, for total commissions paid of $57,600.
• Recycled scrap grapes: If any juice remains after filling the oak barrels and the stainless-steel tank, it cannot be saved for the next year. Therefore, it is composted back into the vineyard. Because of this, ending raw materials is always valued at zero, and the cost of the unused grape juice, which the company refers to as Recycled Scrap Grapes, is treated as a variable overhead cost. This cost is the difference between the raw materials purchased and the raw materials consumed in production, dependent on the number of varietals produced.
• Utilities fermenting process: Utility costs are incurred primarily to maintain a constant temperature in the fermenting process. These costs were $5,470. This cost depends on the number of manufacturing hours per year. Since the company runs at near capacity, this cost is not expected to fluctuate unless additional capacity were added. In this case, it is expected that the cost would rise proportionately to the number of additional bottles produced. The ratio is approximately $0.12 per additional bottle.
• Waste treatment: After crushing, the pulp, skins, and stems that are left over must be disposed of. One-half of the waste can be recycled back onto the fields as a compost material; the other one-half, must be disposed of at a landfill for a dumping cost of $2,000.
• Wine Master fee: A Master Enologist (Wine-maker) formulates and tests the wines. GBW pays the wine master $10,000 per year per varietal for a total of $30,000.
• Lab Fees: Additionally, the Wine Master uses a local lab for testing the wines at various stages of production. Lab fees are approximately $15,000 per year.
• Sales Manager: One full-time employee is paid $88,000/year to sell the GBW wines through distributors.
• Production Supervisor: There is one production supervisor. His salary and benefits total $79,000 annually.
• Administrative salaries: The CEO earns $100,000 annually, and the office staff earn an additional $125,000 combined.
• Rent Expense: Administrative rent expenses for the Sales office in Portland, OR are $36,000/yr.

The following account balances are for the last day of the reporting period, Dec 31, 2020.

Account List	Account Balance Dec 31, 2020
Administrative Office Rent	$ 36,000
Administrative salaries	$ 225,000
Bottle, labels, corks	$ 50,530
Bottling labor	$ 95,000
Boxing & Crating labor	$ 129,500
Boxes	$ 12,514
Crush labor	$ 55,200
Depreciation	$ 54,000
Fermenting ingredients	$ 15,720
Grapes	$ 422,500
Harvest labor	$ 216,200
Lab fees	$ 15,000
Production Supervisor salary	$ 79,000
Recycled Scrap grapes	$ 3,080
Sales commissions	$ 57,600
Sales manager salary	$ 88,000
Utilities - fermenting process	$ 5,470
Waste treatment	$ 2,000
Wine master fee	$ 30,000
Check figure total	$ 1,592,314

Using the information from the case scenario and the account data found on WS 1 of the provided Excel template, organize the costs according to the following categories:

Category	Cost
Direct Materials	$501,264
Direct Labor	$495,900
Fixed Product Overhead	$180,000

You should compute the per-unit cost data where indicated. To ensure that you have classified all of the accounts, be sure that your check figure matches the total of all the accounts. Using the data from the case scenario and your results from WS 1, complete the Cost Data Sheet on WS 2 of the Excel Template. Be sure that the cost of Direct Materials and Direct Labor that you compute on this WS matches the total cost of Direct Materials and Direct Labor that you computed in Requirement 1 of this project. Additional check figures are below.

Category	Cost
Direct Materials for the Blend wine	$195,264
Direct Labor for the Blend wine	$203,400

Using the data from WS 1 and 2, create an Income statement using the Traditional GAAP format, and another using the Contribution Margin format. Give the percentage of Sales data as indicated. Additional check figures are below.

Category	Cost
Gross Profit	$402,686
Contribution Margin	$525,086

I need help filling in each table shown below. work sheet 1.

work sheet 2.

work sheet 3.

Category Direct Labor \begin{tabular}{|l|l|} \hline Direct Labor & \\ \hline & \\ \hline & \\ \hline & \\ \hline & \\ \hline & \\ \hline & $ \\ \hline \end{tabular} Variable Product Overhead cost \begin{tabular}{|l|l|} \hline & \\ \hline & \\ \hline & \\ \hline & \\ \hline & \\ \hline \end{tabular} $ Fixed Product Overhead cost \begin{tabular}{|l|l|} \hline & \\ \hline & \\ \hline & \\ \hline & \\ \hline & \\ \hline & \\ \hline \end{tabular} $ Variable SG\&A cost Fixed SG\&A cost \begin{tabular}{|l|l|} \hline & \\ \hline & \\ \hline & \\ \hline & \\ \hline & \\ \hline & \\ \hline \end{tabular} Check figure total $501,264 \begin{tabular}{|l|l|} \hline \multicolumn{1}{|c|}{ Overhead Summary } & \\ \hline Variable Product & \\ \hline Fixed Product & \\ \hline Variable SG\&A & \\ \hline Fixed SG\&A & \\ \hline \end{tabular} 22 NOTE - taxes are not paid in years where there is an NOL