This problem will provide you with practice in NPV, margin and markup, breakeven analysis, forecasting, and decision analysis, which are all important tools for managers to know. The problem also combines material from two or more learning modules.

Scenario:

You are part of the management team for Schnauzer Security Systems, which manufactures building security systems. One of your products is a control panel that is used in a wide variety of applications. You have manufactured the product for the past 15 years, and you have enjoyed strong sales growth. Unfortunately, your design engineers have indicated that the current panel is rapidly becoming obsolete, and it will not be capable of interfacing with new sensor technology, which is rapidly taking over the market. So, you must discontinue the current product soon. The question is whether or not you can afford to change over to a new product given the high cost of redesign and retooling. Alternatively, you could outsource the technology to a Chinese manufacturer. But if you do so, you will not achieve significant profit from reselling the Chinese panel. If you choose to manufacture and sell the new panel, your engineers predict that the product life cycle will be about 10 years. So, it is important to recoup your initial investment before then.

Questions:

To answer the following questions, use the data in the accompanying Excel file.

1. Using the markup percentage, what is the sales price?
2. What is the margin given the price you calculated above?
3. Based on your calculations in Question 1, how much must you sell to break even in the first year? Hint: Let your fixed cost be the sum of initial investment and one year of annual fixed cost.
4. Since the sales are clearly trending upward, use the historical data to create a trend model, and then use that model to forecast sales for each of the next 10 years (2020-2029). Note: use TREND() for the forecasts. Set x and y to the history and the new x to the new dates.
5. Assuming that sales will be very close to your forecasts, what is the expected net income for each of the next 10 years?

Hint: Let Net Income = Forecast Sales X Margin Annual Fixed Cost

6. What is the net present value of the 10 years of profit computed in Question 4? Hint: Compute NPV of the 10 years of net income, and subtract the initial investment from this amount.
7. Should Schnauzer Security Systems invest in the new product? Use all of the results of your analysis to justify your answer.

Deliverables:

Please place all of your analysis on the spreadsheet provided. When you have completed the assignment, post your Excel file on the HW 5 assignment dropbox.

Data

EF H 4. Sales 5. Net Income 7. ?? C D Data Variable Cost Annual Fixed Cost Markup Percentage Discount Rate Initial Investment $11,260 $10,500,000 41% 9% $15,000,000 2664 A B 1 Year System Sales 2 2004 2558 3 2005 2575 4 2006 2685 2007 6 2008 2552 7 2009 2581 8 2010 2901 9 2011 2872 10 2012 2917 11 2013 3022 12 2014 2901 13 2015 2930 14 2016 3012 15 2017 3224 16 2018 3064 17 2019 3112 Year 2020 2021 2022 2023 2024 2025 2026 2027 2028 2029 1. Price 2. Margin 3. Breakeven 6. NPV = EF H 4. Sales 5. Net Income 7. ?? C D Data Variable Cost Annual Fixed Cost Markup Percentage Discount Rate Initial Investment $11,260 $10,500,000 41% 9% $15,000,000 2664 A B 1 Year System Sales 2 2004 2558 3 2005 2575 4 2006 2685 2007 6 2008 2552 7 2009 2581 8 2010 2901 9 2011 2872 10 2012 2917 11 2013 3022 12 2014 2901 13 2015 2930 14 2016 3012 15 2017 3224 16 2018 3064 17 2019 3112 Year 2020 2021 2022 2023 2024 2025 2026 2027 2028 2029 1. Price 2. Margin 3. Breakeven 6. NPV =