Please help I do not understand this project! Thankyou!

Team Project #1 (20 points) Activity-Based Costing Directions: Each student/partnership will be responsible for completing the case detailed below these instructions. This project should be done using the spreadsheet template I put in Canvas. Note: You need to use formulas and cell references whenever possible. If you do not use formulas or cell references, I will deduct points. Part 1 of the assignment requires you to determine the cost of a unit of coffee (Mona Loa and Malaysian) using the traditional overhead allocation method. A unit of coffee is a one pound bag. The unit cost will include direct materials, direct labor, and manufacturing overhead (as determined using the predetermined overhead rate). You NEED to use Excel formulas whenever possible. Basically, the only time you will not use formulas is when you enter given information. Part 2 of the assignment requires you to determine the cost of a unit of coffee (Mona Loa and Malaysian) using the activity-based costing method. As with the traditional method, you will need to show direct materials, direct labor and manufacturing overhead (as determined using ABC). Once again, use formulas wherever you can. Part 2A is a comparison of both methods. Link the numbers to Parts 1 and 2 whenever possible. Show a side-by-side comparison of unit product costs calculated under each method (show the unit product cost of each type of coffee using both traditional and ABC costing). Unit product costs include direct materials, direct labor and manufacturing overhead costs on a per unit basis. Further, determine the changes in the profit margins for each bag of coffee under both methods (the sales price mark up % is given below). How much was the company losing per bag of the Malaysian coffee under the Traditional Method? Remember to use formulas. Part 3 requires you to write a memo on your findings. The memo should be addressed to the president of Coffee Bean, Inc. The tone and format of the memo should be professional. Make recommendations based on your findings. Point out what caused differences between the two methods. Also, point out any potential problems you see with your recommendations (hint: see the part of the chapter on the limitations of ABC). The memo should be less than 300 words. Presidents don't like to read long memos! Activity-Based Costing as an Alternative to Traditional Product Costing Coffee Bean, Inc. (CBI), is a processor and distributor of a variety of blends of coffee. The company buys coffee beans from around the world and roasts, blends, and packages them for resale. CBI currently has 40 different coffees that it sells to gourmet shops in one-pound bags. The major cost of the coffee is raw materials. However, the company's predominantly automated roasting, blending, and packing process requires a substantial amount of manufacturing overhead. The company uses relatively little direct labor. Some of CBI's coffees are very popular and sell in large volumes, while a few of the newer blends have very low volumes. CBI prices its coffee at manufacturing cost plus a markup of 30%. If CBI's prices for certain coffees are significantly higher than market, adjustments are made to bring CBI's prices more into alignment with the market because customers are somewhat price conscious. For the coming year, CBI's budget includes estimated manufacturing overhead cost of $4,500,000. CBI assigns manufacturing overhead to products on the basis of direct labor- hours. The expected direct labor cost totals $600,000, which represents 80,000 hours of direct labor time. Based on the sales budget and expected raw materials costs, the company will purchase and use $6,000,000 of raw materials (mostly coffee beans) during the year. The expected costs for direct materials and direct labor for one-pound bags of two of the company's coffee products appear below. Mona Loa Malaysian Direct materials $ 4.20 $ 3.20 Direct labor (0.05 hours per bag) $ 0.30 $0.30 (note that it takes 0.05 DLHs to make one bag of coffee- you'll need this to apply MOH using the Traditional Method) CBI's controller believes that the company's traditional costing system may be providing misleading cost information. To determine whether or not this is correct, the controller has prepared an analysis of the year's expected manufacturing overhead costs, as shown in the following table: Activity Cost Pool Purchasing Expected Activity for the Year 3,000 orders Expected Cost for the Year $ 600,000 Material handling 2,500 setups 1,120,000 Quality control Activity Measure Purchase orders Number of setups Number of batches Roasting hours Blending hours Packaging hours 1,000 batches 160,000 Roasting 1,200,000 Blending 120.000 roasting hours 80,000, blen "hours 21.000 packaging 1,000,000 Packaging 21,00 hours 420,000 Total manufacturing overhead cost $ 4,500,000 Data regarding the expected production of Mona Loa and Malaysian coffee are presented below. Mona Loa Malaysian Expected sales 70,000 pounds 1,500 pounds Data regarding the expected activities used by Mona Loa and Malaysian coffees are presented below. Mona Loa Malaysian Batches Setups Purchase order size Roasting time Blending time Packaging time 10 batches 30 setups 5 orders 700 hours 350 hours 70 hours 4 batches 12 setups 4 orders 15 hours 7.5 hours 1.5 hours Traditional Costing Step 1: Compute Predetermined Overhead Rate POHR: D Estimated MOH: Estimated Allocation Base: Step 2: Apply MOH to 1 bag of coffee Applied MOH per bag: POHR: Actual Allocation Base per bag of coffee: Step 3: Calculate Per Bag Cost of Coffee Mona Loa Malaysian Direct Materials per bag Direct Labor per bag Applied MOH per bag Total Cost per bag Activity-Based Costing Step 1: Determine Overhead Driving Activities. This is given in the instructions. Step 2: Allocate Overhead Costs Between Activities Identified in Step 1 (First Stage of Allocation). This is given in the instructions. Step 3: Calculate Activity Rates. Estimated Activity Activity Rate Activity Used By Mona Loa Allocated Overhead (in Total) Activity (Ac Estimated Overhead Purchasing (# of purchase orders) Materials Handling (# of setups) Quality Control (# of batches) Roasting (hrs of roasting) Blending (hrs of blending) Packaging (hrs of packaging) Step 4: Allocate Overhead. Mona Loa: Activity (Ac Activity Rate Purchasing (# of purchase orders) Materials Handling (# of setups) Quality Control (# of batches) Roasting (hrs of roasting) Blending (hrs of blending) Packaging (hrs of packaging) Total Allocated OH Malaysian: Activity (Ac Activity Rate Purchasing (# of purchase orders) Materials Handling (# of setups) Quality Control (# of batches) Roasting (hrs of roasting) Blending (hrs of blending) Packaging (hrs of packaging) Total Allocated OH Step 5: Calculated Per Bag Cost of Coffee Mona Loa Total Production for Each Coffi 70,000 bags Mona Loa Direct Materials per bag Direct Labor per bag Applied MOH per bag Total Cost per bag Activity Used By Malaysian Allocated Overhead (in Total) Malaysian 1,500 bags Malaysian Malaysian Traditional Costing Per Unit Cost Mona Loa Direct Materials per bag Direct Labor per bag Applied MOH per bag Total Cost per bag Activity-Based Costing Per Unit Cost Mona Loa Malaysian Direct Materials per bag Direct Labor per bag Applied MOH per bag Total Cost per bag Sales Price Per Bag *increase cost by 30% Make a recommendation for which method to use. Discuss reasons why MOH costs shifted between the two methods. What are the root causes? Discuss drawbacks of using ABC. Team Project #1 (20 points) Activity-Based Costing Directions: Each student/partnership will be responsible for completing the case detailed below these instructions. This project should be done using the spreadsheet template I put in Canvas. Note: You need to use formulas and cell references whenever possible. If you do not use formulas or cell references, I will deduct points. Part 1 of the assignment requires you to determine the cost of a unit of coffee (Mona Loa and Malaysian) using the traditional overhead allocation method. A unit of coffee is a one pound bag. The unit cost will include direct materials, direct labor, and manufacturing overhead (as determined using the predetermined overhead rate). You NEED to use Excel formulas whenever possible. Basically, the only time you will not use formulas is when you enter given information. Part 2 of the assignment requires you to determine the cost of a unit of coffee (Mona Loa and Malaysian) using the activity-based costing method. As with the traditional method, you will need to show direct materials, direct labor and manufacturing overhead (as determined using ABC). Once again, use formulas wherever you can. Part 2A is a comparison of both methods. Link the numbers to Parts 1 and 2 whenever possible. Show a side-by-side comparison of unit product costs calculated under each method (show the unit product cost of each type of coffee using both traditional and ABC costing). Unit product costs include direct materials, direct labor and manufacturing overhead costs on a per unit basis. Further, determine the changes in the profit margins for each bag of coffee under both methods (the sales price mark up % is given below). How much was the company losing per bag of the Malaysian coffee under the Traditional Method? Remember to use formulas. Part 3 requires you to write a memo on your findings. The memo should be addressed to the president of Coffee Bean, Inc. The tone and format of the memo should be professional. Make recommendations based on your findings. Point out what caused differences between the two methods. Also, point out any potential problems you see with your recommendations (hint: see the part of the chapter on the limitations of ABC). The memo should be less than 300 words. Presidents don't like to read long memos! Activity-Based Costing as an Alternative to Traditional Product Costing Coffee Bean, Inc. (CBI), is a processor and distributor of a variety of blends of coffee. The company buys coffee beans from around the world and roasts, blends, and packages them for resale. CBI currently has 40 different coffees that it sells to gourmet shops in one-pound bags. The major cost of the coffee is raw materials. However, the company's predominantly automated roasting, blending, and packing process requires a substantial amount of manufacturing overhead. The company uses relatively little direct labor. Some of CBI's coffees are very popular and sell in large volumes, while a few of the newer blends have very low volumes. CBI prices its coffee at manufacturing cost plus a markup of 30%. If CBI's prices for certain coffees are significantly higher than market, adjustments are made to bring CBI's prices more into alignment with the market because customers are somewhat price conscious. For the coming year, CBI's budget includes estimated manufacturing overhead cost of $4,500,000. CBI assigns manufacturing overhead to products on the basis of direct labor- hours. The expected direct labor cost totals $600,000, which represents 80,000 hours of direct labor time. Based on the sales budget and expected raw materials costs, the company will purchase and use $6,000,000 of raw materials (mostly coffee beans) during the year. The expected costs for direct materials and direct labor for one-pound bags of two of the company's coffee products appear below. Mona Loa Malaysian Direct materials $ 4.20 $ 3.20 Direct labor (0.05 hours per bag) $ 0.30 $0.30 (note that it takes 0.05 DLHs to make one bag of coffee- you'll need this to apply MOH using the Traditional Method) CBI's controller believes that the company's traditional costing system may be providing misleading cost information. To determine whether or not this is correct, the controller has prepared an analysis of the year's expected manufacturing overhead costs, as shown in the following table: Activity Cost Pool Purchasing Expected Activity for the Year 3,000 orders Expected Cost for the Year $ 600,000 Material handling 2,500 setups 1,120,000 Quality control Activity Measure Purchase orders Number of setups Number of batches Roasting hours Blending hours Packaging hours 1,000 batches 160,000 Roasting 1,200,000 Blending 120.000 roasting hours 80,000, blen "hours 21.000 packaging 1,000,000 Packaging 21,00 hours 420,000 Total manufacturing overhead cost $ 4,500,000 Data regarding the expected production of Mona Loa and Malaysian coffee are presented below. Mona Loa Malaysian Expected sales 70,000 pounds 1,500 pounds Data regarding the expected activities used by Mona Loa and Malaysian coffees are presented below. Mona Loa Malaysian Batches Setups Purchase order size Roasting time Blending time Packaging time 10 batches 30 setups 5 orders 700 hours 350 hours 70 hours 4 batches 12 setups 4 orders 15 hours 7.5 hours 1.5 hours Traditional Costing Step 1: Compute Predetermined Overhead Rate POHR: D Estimated MOH: Estimated Allocation Base: Step 2: Apply MOH to 1 bag of coffee Applied MOH per bag: POHR: Actual Allocation Base per bag of coffee: Step 3: Calculate Per Bag Cost of Coffee Mona Loa Malaysian Direct Materials per bag Direct Labor per bag Applied MOH per bag Total Cost per bag Activity-Based Costing Step 1: Determine Overhead Driving Activities. This is given in the instructions. Step 2: Allocate Overhead Costs Between Activities Identified in Step 1 (First Stage of Allocation). This is given in the instructions. Step 3: Calculate Activity Rates. Estimated Activity Activity Rate Activity Used By Mona Loa Allocated Overhead (in Total) Activity (Ac Estimated Overhead Purchasing (# of purchase orders) Materials Handling (# of setups) Quality Control (# of batches) Roasting (hrs of roasting) Blending (hrs of blending) Packaging (hrs of packaging) Step 4: Allocate Overhead. Mona Loa: Activity (Ac Activity Rate Purchasing (# of purchase orders) Materials Handling (# of setups) Quality Control (# of batches) Roasting (hrs of roasting) Blending (hrs of blending) Packaging (hrs of packaging) Total Allocated OH Malaysian: Activity (Ac Activity Rate Purchasing (# of purchase orders) Materials Handling (# of setups) Quality Control (# of batches) Roasting (hrs of roasting) Blending (hrs of blending) Packaging (hrs of packaging) Total Allocated OH Step 5: Calculated Per Bag Cost of Coffee Mona Loa Total Production for Each Coffi 70,000 bags Mona Loa Direct Materials per bag Direct Labor per bag Applied MOH per bag Total Cost per bag Activity Used By Malaysian Allocated Overhead (in Total) Malaysian 1,500 bags Malaysian Malaysian Traditional Costing Per Unit Cost Mona Loa Direct Materials per bag Direct Labor per bag Applied MOH per bag Total Cost per bag Activity-Based Costing Per Unit Cost Mona Loa Malaysian Direct Materials per bag Direct Labor per bag Applied MOH per bag Total Cost per bag Sales Price Per Bag *increase cost by 30% Make a recommendation for which method to use. Discuss reasons why MOH costs shifted between the two methods. What are the root causes? Discuss drawbacks of using ABC