How do I solve for allocating indirect cost to product in Step 5?

Step 4 Determine the the Activity rate for each Pool Activity Pools Winemaking Customer Orders Production Support Cost Driver # of varietals # of pallets Mfg hours Expected Costs assigned Activity Level to Pool Activity rate 3 $ 3,080 $ 1,026.67 583 $ 35,000 $ 60.03 8,760 $ 5,470 $ 0.6244 $ 43,550 Step 5 Allocate Indirect Cost to Product Estate Blend Table Activity Consumed Activity Consumed Activity Consumed Cost Pool Pool# 1 (Winemaking) Pool #2 (Customer Orders) Pool #3 (Production Support)*** Activity cost Activity rate allocated $ 1,027 $ $ 60$ $ 0.62 $ Activity cost Activity rate allocated $ 1,027 $ $ 60 $ $ 0.62 $ Activity cost Activity rate allocated totals $ 1,027 $ $ $ 60 0 $ $ 0.62 $ $ *** 8760 hours x sales mix $ $ $ $ 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 2018, GBW had a net margin loss of (- 5%) on Sales of approximately $882,500. The company pays a 21% corporate tax rate in the year's 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 75,000 lbs. of Cabernet grapes, and 150,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/bottle. The blended wine is made by combining 1 lb of Cabernet grapes and 2 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 3 lbs of grapes. The sales price is $5.50/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 Expected Demand sold (next year) Estate $23.00 12,000 42,000 Blend $17.00 25,000 30,000 Table $5.50 33,000 56,000 70.000 128.000 Since demand is strong and the company sells all of 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 95,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/1b., and an additional 100,000 pounds of the generic grapes at $0.78/1b. This is the same price as the current supplier. The new supplier requires purchase of the grapes in large bins that hold 10,000 lbs of grapes. Therefore, this constraint will affect production planning to minimize waste. 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 $12/hr. Table 2: Direct Labor costs Direct labor by product Cost per bottle Estate $3.00 Blend $2.40 Table $1.80 Hours of labor per bottle 0.25 0.20 0.15 Direct Materials Table 3: Direct Materials Costs Direct Materials by Cost per product bottle Estate $ Blend $ Table $ 4.63 4.15 3.25 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 95,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 Winemaking Customer Orders Production Support Cost Driver # of varietals # of pallets Mfg hours Expected Activity Level 3 583 8,760 Sales Commissions: Distributors are paid a commission of $60 per pallet ordered. Each pallet contains 10 cases, or 120 bottles of wine. In 2018, distributors ordered 583 pallets, for total commissions paid of $35,000. 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.11 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 $85,000/year to sell the GBW wines through distributors. Production Supervisor: There is one production supervisor. His salary and benefits total $75,000 annually Administrative salaries: the CEO $100,000 annually, and the office staff earn an additional $75,000 combined. Rent Expense: Administrative rent expenses for the sales office in Portland, OR are $24,000/yr. The following account balances are for the last day of the reporting period, Dec 31, 2018. Account Balance Account List Dec 31, 2018 Administrative Office Rent $ 24,000 Administrative salaries $ 175,000 Bottle, labels, corks $ 35,240 Bottling labor $ 21,000 Boxing & Crating labor 4,200 Boxes 12,550 Crush labor $ 5,200 Depreciation 54,000 Fermenting ingredients 15,520 Grapes 203,250 Harvest labor 125,000 Lab expenses 15,000 Production Supervisor salary 75,000 Recycled Scrap grapes 3,080 Sales commissions 35,000 Sales manager salary 85,000 Utilities - fermenting process 5,470 Waste treatment 2,000 Wine master contract rate for 3 varietals $ 30,000 Check figure total s 925,510 nunununun V | 0 0 0 0 0 0 0