ACCT 331: Case #15, Spring 2021 Barista Supplies Inc. manufactures different types of products for commercial use. The company produces three major products: coffee brewers, espresso machines, and coffee grinders. Barista uses a job-order cost accounting system with manufacturing overhead applied on the basis of direct labor hours. The company has been in business for 40 years and has used the same cost system for the entire time. Relevant data are as follows: Annual sales (units) Direct materials cost per unit Direct labor cost per unit Direct labor hours (DLH) per unit Coffee Brewer 30,000 $175.00 $60.00 4 DLH Espresso Machine 1,500 $150.00 $45.00 3 DLH Coffee Grinder 15,000 $185.00 $105.00 7 DLH The company has budgeted its annual indirect costs to be $20,196,000. For the past 5 years, the company's pricing formula has been to set each product's target price at 120% of its unit product cost. Recently, however, the coffee brewer has come under increasing price pressure from competitors. The result was that the price on the coffee brewer has been lowered to $670. The company CEO, Lynn Benter, recently asked the controller, Charlie Pena, "Why are we having trouble competing with these other companies? They are selling products just like our coffee brewer for $589. That's only a couple of dollars more than our production cost. Are we really that inefficient, or are they losing money on all those sales? What's going on here?" Charlie responded by saying, "I think it's because of our outdated product-costing system. You might recall that I raised this as a potential issue last year, but the company decided to keep the current system in place. In my opinion, the costing system we're using is distorting our product costs. Let me put some data together and show you what I mean." Lynn gave Charlie the approval to run some numbers, and he compiled the basic data needed to implement an activity-based costing system. These data are displayed in the following table, which shows the activity cost pools, indirect costs in each activity cost pool, cost drivers, and amounts of each cost driver used by each product: Activity Cost Pool Indirect costs Cost Driver Products in cost pool Coffee Espresso Coffee Brewer Machine Grinder Machine maintenance and $9,215,000 Machine hours (MH) 57,769 MH 14,090 MH 69,041 MH depreciation Engineering and product $4,089,000 Engineering hours 23,360 EH 3,504 EH 31,536 EH repair (EH) Material purchasing $3,649,000 Number of material 4,830 orders 920 orders 5,750 orders receiving and handling orders (orders) Miscellaneous $3,243,000 square feet of 15,400 sq. ft. 9,856 sq. ft. 36,344 sq. ft. manufacturing overhead factory space (Sq. ft.) Assume you are a staff accountant who works for Charlie Pena and he has assigned this project to you. The requirements are: 1. Prepare a Microsoft Excel file that does the following: a Calculate the per unit product cost for each product, based on the current job-order costing system. b. Calculate the new per unit product cost for each product, based on the new data collected by Charlie, using an activity-based costing system. C. Compare the new per unit product cost (from the activity-based costing system) with the current per unit product cost (from the job-order costing system) for each product. d. Calculate the target selling price per unit for each product, based on the current job-order costing system. e. Calculate the new target selling price per unit for each product, based on the activity-based costing system f. Compare the new target selling price per unit (from the activity-based costing system) with the current target selling price per unit (from the job-order costing system) for each product. ACCT 331: Case #15, Spring 2021 Barista Supplies Inc. manufactures different types of products for commercial use. The company produces three major products: coffee brewers, espresso machines, and coffee grinders. Barista uses a job-order cost accounting system with manufacturing overhead applied on the basis of direct labor hours. The company has been in business for 40 years and has used the same cost system for the entire time. Relevant data are as follows: Annual sales (units) Direct materials cost per unit Direct labor cost per unit Direct labor hours (DLH) per unit Coffee Brewer 30,000 $175.00 $60.00 4 DLH Espresso Machine 1,500 $150.00 $45.00 3 DLH Coffee Grinder 15,000 $185.00 $105.00 7 DLH The company has budgeted its annual indirect costs to be $20,196,000. For the past 5 years, the company's pricing formula has been to set each product's target price at 120% of its unit product cost. Recently, however, the coffee brewer has come under increasing price pressure from competitors. The result was that the price on the coffee brewer has been lowered to $670. The company CEO, Lynn Benter, recently asked the controller, Charlie Pena, "Why are we having trouble competing with these other companies? They are selling products just like our coffee brewer for $589. That's only a couple of dollars more than our production cost. Are we really that inefficient, or are they losing money on all those sales? What's going on here?" Charlie responded by saying, "I think it's because of our outdated product-costing system. You might recall that I raised this as a potential issue last year, but the company decided to keep the current system in place. In my opinion, the costing system we're using is distorting our product costs. Let me put some data together and show you what I mean." Lynn gave Charlie the approval to run some numbers, and he compiled the basic data needed to implement an activity-based costing system. These data are displayed in the following table, which shows the activity cost pools, indirect costs in each activity cost pool, cost drivers, and amounts of each cost driver used by each product: Activity Cost Pool Indirect costs Cost Driver Products in cost pool Coffee Espresso Coffee Brewer Machine Grinder Machine maintenance and $9,215,000 Machine hours (MH) 57,769 MH 14,090 MH 69,041 MH depreciation Engineering and product $4,089,000 Engineering hours 23,360 EH 3,504 EH 31,536 EH repair (EH) Material purchasing $3,649,000 Number of material 4,830 orders 920 orders 5,750 orders receiving and handling orders (orders) Miscellaneous $3,243,000 square feet of 15,400 sq. ft. 9,856 sq. ft. 36,344 sq. ft. manufacturing overhead factory space (Sq. ft.) Assume you are a staff accountant who works for Charlie Pena and he has assigned this project to you. The requirements are: 1. Prepare a Microsoft Excel file that does the following: a Calculate the per unit product cost for each product, based on the current job-order costing system. b. Calculate the new per unit product cost for each product, based on the new data collected by Charlie, using an activity-based costing system. C. Compare the new per unit product cost (from the activity-based costing system) with the current per unit product cost (from the job-order costing system) for each product. d. Calculate the target selling price per unit for each product, based on the current job-order costing system. e. Calculate the new target selling price per unit for each product, based on the activity-based costing system f. Compare the new target selling price per unit (from the activity-based costing system) with the current target selling price per unit (from the job-order costing system) for each product