Managerial Accounting

I need help with creating an executive summary Using the information from the excel spread sheets and scenario attached. The assignment instructions for theExecutive Summary instructions are below: use the information provided outlining the strategy used and already formulated and listed on part three worksheets of the excel spreadsheets document. This Executive Summary assumes you are from the perspective that you are the manager hired by the Board of Directors to determine the best company strategy. It is expected that you will focus on achieving the highest profit margin possible listed on the last excel spread sheet on the attached document.

ACC550 Term Project Case 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 2014, GBW earned an operating margin of 5% on Sales of approximately $757,250. The company pays a 35% corporate tax rate. GBW has a relationship with a local vineyard owner who grows two types of wine grapes; a Cabernet and a generic red grape. GBW buys 100,000 lbs. of Cabernet grapes, and 60,000 lbs. of generic grapes each 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: a Cabernet Estate, a blended wine, and a House Table wine. Every bottle requires a total of three pounds of grapes. The Cabernet Estate varietal contains only Cabernet grapes. The sales price is $22/bottle. The blended wine is made by combining two parts Cabernet grapes and one part generic grapes. The different grapes are fermented separately and blended before bottling. The sales price is $16/bottle. The Table wine is made from only generic grapes. The sales price is $17/box. Each box holds 4 bottles, making the equivalent price per bottle $4.25. 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 double for the next year. Table 1: Sales Data Price per bottle Estate Blend Table (box of 4 bottles) $22.00 $16.00 $4.25 ($17.00/box) # of bottles sold 21,000 15,000 13,000 Expected Demand (next year) 42,000 30,000 26,000 49,000 98,000 Although the company is turning a small profit each year, the CEO suspects that one of the wine varietals is not profitable. 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 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. Up to this point, the winery has had enough capacity to meet demand. 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 60,000 pounds of Cabernet grapes at $0.98/lb., and an additional 25,000 pounds of the generic grapes at $0.64/lb. 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. Additional Cost Data Direct Labor Table 2: Direct Labor costs Direct labor by product Estate Blend Table Cost per bottle $0.47 $0.47 $0.40 Hours of labor per bottle 0.05 0.05 0.05 Direct Materials Table 3: Direct Materials Costs Direct Materials by product Estate Blend Table Cost per bottle $5.57 $5.23 $2.64 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. Since the factory runs at near capacity of 50,000 bottles and on a 24/7 schedule, there are 8,760 manufacturing hours per year (24 hours x 365 days). Table 4: ABC- Activity Cost Pools Sales Commissions: Distributors are paid a commission of $185 per pallet ordered. Each pallet contains 10 cases, or 120 bottles of wine. In 2014, distributors ordered 175 pallets of the Estate wine, and 125 pallets of the Blend, for total commissions paid of $55,500. There are no commissions paid on the boxed Red Table wine. 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\Part 1 of the project includes worksheets 1-3. These worksheets provide in current financial position of Grizzley Bear Winery. *This part of your project is due at the end of unit 2. heets provide information that reflects the Instructions: Categorize the list of costs below by completing the yellow shaded boxes. Be careful to type only in Unit Data can be found in the Case Study write-up. Units Sold and Units packaged in Bottles are found on Table 1 Account List Administrative rent & office Administrative salary Bottle, labels, corks Bottling & Crating labor box labor boxes Crush labor Account Balance Dec 31, 2014 $ 30,000.00 $ 100,000.00 $ 90,000.00 $ 3,300.00 $ 300.00 $ 7,670.00 $ 2,920.00 Depreciation Fermenting ingredients Grapes Harvest labor Lab expenses Production Supervisor salary Recycled Scrap grapes Sales commissions $ $ $ $ $ $ $ $ 54,000.00 6,370.00 125,700.00 15,600.00 15,000.00 85,200.00 10,700.00 55,500.00 Sales manager salary Utilities - fermenting process Waste treatment Wine master contract Check figure total $ $ $ $ $ 65,000.00 5,470.00 2,000.00 30,000.00 704,730.00 aded boxes. Be careful to type only in the Yellow shaded boxes! ckaged in Bottles are found on Table 1: Sales data. Grizzley Bear Winery sold all units produced. Category Direct Labor Harvest Labor Box Labor Crush Labor Bottling & Crafting Labor cost $ 15,600.00 $ 300.00 $ 2,920.00 $ 3,300.00 $ Direct Materials Grapes Fermenting Ingredients Bottles, Labels, Corks Boxes 22,120.00 cost $ 125,700.00 $ 6,370.00 $ 90,000.00 $ 7,670.00 $ 229,740.00 Variable Product Overhead Utilities Recycled Scrap Grapes Fixed Product Overhead Production Supervisor Salary Waste Treatment Depreciation Lab Expenses Wine Master Contract Variable SG&A Sales Commissions Fixed SG&A Administrative Salary Administrative Rent & Office Sales Manager Salary cost $ 5,470.00 $ 10,700.00 $ 16,170.00 Check figure total cost $ $ $ $ $ 85,200.00 2,000.00 54,000.00 15,000.00 30,000.00 $ 186,200.00 cost $ 55,500.00 $ 55,500.00 cost $ 100,000.00 $ 30,000.00 $ 65,000.00 $ 195,000.00 $ 704,730.00 Instructions: Complete the yellow-shaded cells from the information found on the Case write-up and worksheet 1 of th Direct Materials Estate Blend Table Cost per # of bottles bottle produced $ 5.57 21,000 $ 5.23 15,000 $ 2.64 13,000 Total cost of Direct Materials $ 116,970 $ 78,450 $ 34,320 $ Direct Labor by Varietal Estate Blend Table 229,740 Total Hours of Hours of Total Cost of Cost per labor per # of bottles labor per DL per bottle bottle produced product product $ 0.47 0.05 21,000 1,050 $ 9,870 $ 0.47 0.05 15,000 750 $ 7,050 $ 0.40 0.05 13,000 650 $ 5,200 2,450 $ Overhead Variable Product Fixed Product Variable SG&A Fixed SG&A $ $ $ $ 16,170 186,200 55,500 195,000 22,120 e-up and worksheet 1 of this project. Instructions: Complete the Income Statements below under Absorption and Variable Costing. The data needed to written case study. Absorption Costing Income Statement Grizzley Bear Winery Year ended Dec 31, 2014 Price/ unit Sales equivalent # Bottles sold % of Sales Cabernet Estate $ 22.00 21,000 $ 462,000 61% Cabernet Blend $ 16.00 15,000 $ 240,000 32% Red Table Wine (Price per box) $ 17.00 13,000 $ 55,250 7% Total Sales 49,000 $ 757,250 $ 22,120 $ 229,740 $ $ $ 202,370 454,230 303,020 $ $ 250,500 52,520 Costs Of Good Sold Direct Labor Estate $ 9,870 Blend $ 7,050 Table $ 5,200 Total Direct Labor Direct Materials Estate $ 116,970 Blend $ 78,450 Table $ 34,320 total Direct Materials Production OH Vairable Product Overhead $ 16,170 Fixed Product Overhead $ 186,200 Total Production Overhead Totl Cost of Goods Sold Gross Profit 60% 30% Selling & Administrative expenses Vairable SG&A $ 55,500 Fixed SG&A $ 195,000 Total SG&A Costs Income before taxes 33% 7% Provision for Taxes @ Net Income 35% $ $ 18,382 34,138 2% 5% ting. The data needed to complete the yellow-shaded boxes is found on the Account List, and in the Variable Costing Contribution Margin Income Statement Grizzley Bear Winery Year Ended Dec 31, 2014 Price/ equivalent # bottle or box Bottles sold Sales Cabernet Estate $ 22.00 21,000 $ 462,000 Cabernet Blend $ 16.00 15,000 $ 240,000 Red Table Wine (Price per box) $ 17.00 13,000 $ 55,250 Total 49,000 Varaible Costs Direct Labor Estate $ 9,870 Blend $ 7,050 Table $ 5,200 $ Total Direct Labor 22,120 Direct Materials Estate $ 116,970 Blend $ 78,450 Table $ 34,320 $ 229,740 total Direct Materials Variable Overhead Variable Product Variable SG&A $ 16,170 $ 55,500 $ Total Variable Overhead Costs Total Variable Costs Contribution Margin Fixed Costs fixed Product Overhead $ 186,200 Fixed SG&A $ 195,000 Total Fixed Costs Income before taxes 71,670 Provision for Taxes @ Net Income 35% % of Sales 61% 32% 7% $ 757,250 $ $ 323,530 433,720 43% $ $ 381,200 52,520 50% 57% $ $ 18,382 34,138 2% 5% Part 2 of the project includes worksheets 4-7. These worksheets provide in current financial position of Grizzley Bear Winery. *This part of your project is due at the end of unit 3. heets provide information that reflects the Instructions: Complete the yellow-shaded cells. The data needed to complete these cells can be found on Worksheets 2 and 3. Be sure to use the Variable overhead from the Variable Costing (Contribution Margin) Income Statement. Traditional Allocation by Direct Labor hours Allocation rate = Total Variable Overhead Total Direct Labor Hours = Variable Overhead total DL OH/DL hours hour Estate 1,050 x $ 29.25 Blend 750 x $ 29.25 Table 650 x $ 29.25 2,450 Variable OH per product $ 30,716 $ 21,940 $ 19,014 $ 71,670 $ 71,670 = 2450 te these cells can be found on Costing (Contribution Margin) $ 29.25 Instructions: Complete the yellow-shaded cells. Data for this page can be found in the Case study write-up and worksheet 1 of this project. Activity-Based Costing Step 1 Identify Cost Objects Cost Object Estate Blend Generic Step 2 % of Sales mix (round to 1 decimal place) 21,000 43% 15,000 31% 13,000 27% 49,000 Identify Costs to Allocate Cost Utilities for fermenting process Sales commissions Recycled Scrap Grapes Step 3 quantity Indirect cost $ $ $ 5,470 55,500 10,700 Group Activites into Pools Activity Pools Pool #1 (Winemaking) Recycled Scrap Grapes Pool #2 (Customer Orders) Sales commissions Pool #3 (Product Support) Cost Driver Activity Cost Pool Cost # of varietals $ 10,700 $ 10,700 # of pallets ordered $ 55,500 $ 55,500 Utilities - fermenting process manufacturing hours $ Total OH cost Step 4 Step 5 5,470 $ 71,670 Costs assigned to Pool Determine the the Activity rate for each Pool Activity Pools Winemaking Customer Orders Production Support 5,470 $ Cost Driver # of varietals # of pallets ordered Manufacturing hrs Expected Activity Level 3 $ 300 $ 8,760 $ $ Activity rate 10,700 $ 3,566.67 55,500 $ 185.00 5,470 $ 0.62 71,670 Allocate Indirect Cost to Product Estate Cost Pool Pool # 1 (Winemaking) Pool #2 (Customer Orders) Pool #3 (Production Support)*** *** 8760 hours x sales mix % Activity Consumed 1 $ 175 $ 3,754 $ Blend Activity cost Activity rate allocated 3,567 $ 3,567 185 $ 32,375 0.62 $ 2,344 $ 38,286 Activity Consumed 1 125 2,682 he Case study write-up Blend Table Activity cost Activity rate allocated $ 3,567 $ 3,567 $ 185 $ 23,125 $ 0.62 $ 1,675 $ 28,367 Activity cost Activity Consumed Activity rate allocated 1 $ 3,567 $ 3,567 0 $ 185 0.00 2,324 $ 0.62 $ 1,451 $ totals $ $ $ 5,018 $ 10,700 55,500 5,470 71,671 Instructions: Complete the yellow-shaded cells. The data needed to complete this worksheet can be found on worksheets 3 - Contribution Margin Income Statement, and workseet 4 - Traditional OH Variable Cost Computation Variable Cost Direct Materials Direct Labor Variable overhead (use Traditional method) Total Variable Costs # bottles sold Variable Cost per bottle Estate 116,970 $ 9,870 $ 30,716 $ Blend 78,450 $ 7,050 $ 21,940 $ Table 34,320 5,200 19,014 $ 157,556 $ 21,000 107,440 $ 15,000 58,534 13,000 $ 7.5027 $ 7.1627 $ 4.5027 $ $ $ Instructions: Complete the yellow-shaded cells. The data needed to complete this worksheet can be found on worksheets 3 and 6 of this project. Contribution Margin Computation Total Lbs of grapes per bottle 3 Retail Variable CM per lb of Price/bottle Cost/bottle CM per bottle grapes Estate $ 22.00 $ 7.50 $ 14.50 $ 4.83 Blend $ 16.00 $ 7.16 $ 8.84 $ 2.95 Table $ 4.25 $ 4.50 $ (0.25) $ (0.08) NOTE - Table wine is priced per box. Each box holds 4 bottles Part 3 of the project includes worksheets 8-11. *Worksheets 8 and 9 are your decision tools. *Worksheets 10 and 11 are the projections for your chosen strategy. en strategy. Relevant Costs for Decision-Making Instructions: Complete the yellow-shaded cells using the relevant cost data for dropping or keeping a product this project can be found on worksheet 3, and worksheet 6. Be sure to use the Contribution Margin Income S be sure to identify which product you are analyzing. Relevant Costs for Decision-Making Total Number of bottles sold Percent of total Estate 49,000 Blend 21,000 Table 15,000 13,000 Sales Total Variable costs $ $ 757,250 $ 323,530 $ 462,000 $ 157,556 $ 240,000 $ 107,440 $ 55,250 58,534 Contribution Margin $ 433,720 $ 304,444 $ 132,560 $ (3,284) Total Fixed Costs $ 381,200 Net Income (before Taxes) $ 52,520 dropping or keeping a product. The data needed to complete Contribution Margin Income Statement on Worksheet 3. Also Name of Product BLEND Total Sales Total Variable costs Contribution Margin Drop or keep a product? Keep $ 757,250 $ 323,530 $ 433,720 Drop $ $ $ 34,000 216,090 301,160 Total Fixed Costs $ 381,200 $ 381,200 Net Income (before Taxes) $ 52,520 $ (80,040) Increase (Decrease) $ (723,250) $ (107,440) $ (132,560) $ $ $ (132,560) Instructions: Complete the yellow-shaded cells. Data needed to complete this worksheet can be found produced) will come from your new strategy forecast. ***Note You should use this worksheet to help y mix on the contrained resource (lbs. of grapes). You should strive to maximize production of the produc constrained resource. Constrained Resources Estate Lbs of Cabernet grape per bottle Lbs of Generic grape per bottle Total Lbs of grapes per bottle Blend 3 3 Table 2 1 3 3 3 CM per CM per lb of product grapes Estate $ 14.50 $ 4.83 Blend $ 8.84 $ 2.95 Table $ (0.25) $ (0.08) Projected # bottles lbs of Cabernet produced grapes needed Estate 38,666 115,998 Blend 22,000 44,000 Table 21,000 81,666 159,998 Excess or (Shortage) Projected lbs of Cabernet grapes purchased 159,998 - Projected lbs of Generic grapes purchased 85,000 - lbs of Generic grapes needed 22,000 63,000 85,000 plete this worksheet can be found in the case write-up. Product mix (bottles hould use this worksheet to help you determine the effects of changing your product maximize production of the product with the highest Contribution margin per Instructions: Complete the yellow-shaded cells using the data from your new strategy. You will find th worksheet 9- Constraints, worksheet 2 - cost data, and the case write-up. Sales Budget for Year Ended Dec 31, 2015 Projected unit Sales 38,666 22,000 21,000 Product Estate Blend Table 81,666 Direct Materials Budget Estate Physical Units Budget 38,666 lbs of grapes needed lbs of Cabernet per bottle lbs of Generic per bottle 3 - Total lbs of Cabernet needed Total lbs of Generic needed Total pounds of all grapes 115,998 115,998 Cost Budget Cost of Direct Materials Estate Blend Table $ $ $ 5.57 $ 5.23 2.64 215,370 $ 215,370 Total Cost of All Direct Materials Indirect Materials (Variable Overhead) Recycled Scrap grapes Cabernet Generic Cost of Unused grapes Utilities - fermenting process Cost at original capacity Lbs Purchased Cost/lb. 159,998 $ 0.98 85,000 $ 0.64 5,470 # additional bottles produced cost per additional bottle Cost of Utilities - fermenting process $ 32,666 0.11 Total Cost of All Indirect Materials Variable SG&A Sales Commissions Cost per pallet sold Bottles per pallet # of bottles sold (Estate & Blend only) # of pallets sold $ 185 120 60,666 506 Cost of Commssions Direct Labor Budget Direct Labor Hours Estate 38,666 0.05 1,933.3 Bottles Produced Hours needed per bottle Total hours needed Cost Budget Cost of Per bottle Total Cost of Direct Labor $ 0.47 18,173 rom your new strategy. You will find this information on write-up. $ $ $ Retail Price Revenue 22.00 $ 850,652 16.00 $ 352,000 4.25 $ 89,250 $ Blend $ $ 1,291,902 Generic Totals 22,000 21,000 2 1 3 44,000 22,000 66,000 63,000 63,000 159,998 85,000 244,998 $ $ $ 55,440 $ 215,370 115,060 55,440 115,060 $ 55,440 $ 385,870 115,060 81,666 Cost of Lbs purchased Cost of Lbs used Difference $ 156,798 $ 156,798 $ $ 54,400 $ 54,400 $ $ - 9,063 Blend 22,000 0.05 1,100.0 $ 0.47 10,340 $ $ 9,063 $ 93,527 Generic 21,000 0.05 1,050.0 0.4 8,400 $ Totals 81,666 4,083 36,913 Instructions - complete the Proforma statement below using data from your Budget an the original CM Income Statement on Worksheet 3. Projected Contribution Margin Income Statement Grizzley Bear Winery Year Ended Dec 31, 2015 Sales Cabernet Estate Red Table Blend Wine Cabernet (Price per box ) Retail Ptice Bottles sold $ 22.00 38,666 $ 850,652 $ 16.00 22,000 $ 352,000 $ 17.00 21,000 $ 89,250 $ 36,913 Total VariableCosts Total Direct Labor Total Direct Materials 229,740 Variable Overhead 16,170 Variable SG&A 55,500 Total Variable Costs Contribution Margin Fixed Costs Fixed Product $ 186,200 Fixed SG&A $ 195,000 Total Fixed Costs Income before taxes Provision for Taxes @ Net Income 35% using data from your Budget and from Statement % of Sales 66% 27% 7% $ 1,291,902 ** NOTE - all variable cost figures come from the Budget $ $ 338,323 953,579 26% $ $ $ $ 381,200 572,379 200,333 372,046 30% 74% 16% 29%