SHOW FORMULAS IN EXCEL PLEASE

QUESTION #3 - Learning Curve Primo Caf is thinking about outsourcing production of the Bean Boiler. Currently, the cost of producing the Bean Boiler in-house is $13 per unit. Primo has been producing the Bean Boiler for many years, and, for the past 2 years, cost reduction has been virtually zero. You have found a supplier - Legit Mfg LLC - who can produce the Bean Boiler at $13.31 per unit. Legit Mfg has the capacity to produce the Bean Boiler in large volumes (30,000 units per production run). Primo Caf sells about 300,000 Bean Boilers per year - so your plan is to buy 10 productions runs/year from Legit Mfg. Legit Mfg quotes you a contract price of $3,950,000 for 300,000 units based on the following cost information. Legit Manufacturing LLC $ DM DL $ $ 6.00 2.00 3.00 OH 150% COGS SG&A 11.00 1.10 10% TC $ $ $ $ $ 12.10 1.21 10% Profit Sell Price 13.31 You know that Legit Mfg will have to retrain their employees to accommodate production of the Bean Boiler as well as retool some of their existing machinery. But - having seen the Bean Boiler produced in- house - you also know that once employees learn the new process, production becomes relatively standardized after the first run. Based on this knowledge, you believe that Legit Mife's cost model needs to reflect an 88% learning curve. Apply a learning curve as presented in class to complete the question below. Complete all calculations in the excel file that you will upload with your exam. 1. Marco wants a recommendation on what Primo Caf should do. Given the information above- and what you know about the product you are sourcing - what would you recommend? Why? DM 150% DL OH COGS SGA TCP PROFIT PRICE Quote $6.000 $2.000 $3.000 $11.000 $1.100 $12.100 $1.210 $13.310 10% 10% RUN 1 RUN 2 RUN 3 RUN 4 RUN 5 RUN 6 RUN 7 RUN 8 RUN 9 RUN 10 DM DL OH COGS SGA TCP PROFIT PRICE Units/Run Cumulative Units 30,000 30,000 30,000 60,000 30,000 90,000 30,000 120,000 30,000 150,000 30,000 180,000 30,000 210,000 30,000 240,000 30,000 270,000 30,000 300,000 Total Price/Run Cumulative Price QUESTION #3 - Learning Curve Primo Caf is thinking about outsourcing production of the Bean Boiler. Currently, the cost of producing the Bean Boiler in-house is $13 per unit. Primo has been producing the Bean Boiler for many years, and, for the past 2 years, cost reduction has been virtually zero. You have found a supplier - Legit Mfg LLC - who can produce the Bean Boiler at $13.31 per unit. Legit Mfg has the capacity to produce the Bean Boiler in large volumes (30,000 units per production run). Primo Caf sells about 300,000 Bean Boilers per year - so your plan is to buy 10 productions runs/year from Legit Mfg. Legit Mfg quotes you a contract price of $3,950,000 for 300,000 units based on the following cost information. Legit Manufacturing LLC $ DM DL $ $ 6.00 2.00 3.00 OH 150% COGS SG&A 11.00 1.10 10% TC $ $ $ $ $ 12.10 1.21 10% Profit Sell Price 13.31 You know that Legit Mfg will have to retrain their employees to accommodate production of the Bean Boiler as well as retool some of their existing machinery. But - having seen the Bean Boiler produced in- house - you also know that once employees learn the new process, production becomes relatively standardized after the first run. Based on this knowledge, you believe that Legit Mife's cost model needs to reflect an 88% learning curve. Apply a learning curve as presented in class to complete the question below. Complete all calculations in the excel file that you will upload with your exam. 1. Marco wants a recommendation on what Primo Caf should do. Given the information above- and what you know about the product you are sourcing - what would you recommend? Why? DM 150% DL OH COGS SGA TCP PROFIT PRICE Quote $6.000 $2.000 $3.000 $11.000 $1.100 $12.100 $1.210 $13.310 10% 10% RUN 1 RUN 2 RUN 3 RUN 4 RUN 5 RUN 6 RUN 7 RUN 8 RUN 9 RUN 10 DM DL OH COGS SGA TCP PROFIT PRICE Units/Run Cumulative Units 30,000 30,000 30,000 60,000 30,000 90,000 30,000 120,000 30,000 150,000 30,000 180,000 30,000 210,000 30,000 240,000 30,000 270,000 30,000 300,000 Total Price/Run Cumulative Price