Answer only the first part and these all data. please show your work. use formulas where appropriate and not to type in any amounts that are being calculated. Only the given information should be manually entered into Excel. All the math required for Part 1 (e.g., addition, subtraction, multiplication, and division) should be expressed using formulas that contain cell references, not amounts or numbers you have typed into the formula bar

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 40,000 $250.00 $90.00 6 DLH Espresso Machine 2,000 $225.00 $75.00 5 DLH Coffee Grinder 20,000 $450.00 $150.00 10 DLH The company has budgeted its annual indirect costs to be $20,025,000. For the past 5 years, the company's pricing formula has been to set each product's target price at 115% 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 $664. The company CEO, Lynn Benter, recently asked the controller, Charlie Pena, "Why are we having trouble competing with these other companies? They are selline products just like our coffee brewer for $609. 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! raised this as a potential issue last year, but the company decided to keep the current systern 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 corrpiled the basic data needed to implement an sctivity based costing system. These data are displayed in the following table, which shows the activity cost podle indirect cnets in cach activity cost pool, cost drivers, and amounts of each cost driver used by each product: Activity Cost Pool Cost Driver Indirect costs in cost pool Coffee Brewer 34,740 MH Products Espresso Machine 17,370 MH Coffee Grinder 63,690 MH $8,947,000 Machine hours (MH) 39,375 EH 8,750 EH 39.375 EH Machine maintenance and depreciation Engineering and product repair Material purchasing, receiving, and handling Miscellaneous manufacturing overhead 4,559 orders 776 orders 4,365 orders $4,067,000 Engineering hours {EH) ( $3,649,000 Number of material orders (orders) $3,362,000 square feet of factory space Sq. ft.) 20,328 sq.ft 7,260 sq. ft. 20,812 sq. ft. a 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: 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 joh-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 systern) with the current target selling price per unit (from the job-order costing system) for each product. 2. Write a memo to Charlie Pena that summarizes your analysis and explains what has been happening because of the company's traditional product costing system. What strategic options does management have? What do you recommend the company do, and why? Your recommendation(s) and reason(s) should be clearly indicated in your memo. The memo should be typed and follow the proper format