Bauer Industries is an automobile manufacturer. Management is currently evaluating a proposal to build a plant that will manufacture lightweight trucks. Bauer plans to use a cost of capital of 12% to evaluate this project. Based on extensive research, it has prepared the incremental free cash flow projections that are shown below (in millions of dollars). a. What is the NPV of the plant to manufacture lightweight trucks? b. Based on input from the marketing department, Bauer is uncertain about its revenue forecast. In particular, management would like to examine the sensitivity of the NPV to the revenue assumptions. What is the NPV of this project if revenues are 10% higher than forecast? What is the NPV if revenues are 10% lower than forecast? c. Rather than assuming that cash flows for this project are constant, management would like to explore the sensitivity of its analysis to possible growth in revenues and operating expenses. Specifically, management would like to assume that revenues, manufacturing expenses, and marketing expenses given in the table for year 1 and grow by 2% per year every year starting in year 2. Management also plans to assume that the initial capital expenditures (and therefore depreciation), additions to working capital, and continuation value remain as initially specified in the table. What is the NPV of this project under these altemative assumptions? How does the NPV change if the revenues and operating expenses grow by 5% per year rather than by 2%? d. To examine the sensitivity of this project to the discount rate, management would like to compute the NPV for different discount rates. Create a graph, with the discount rate on the x-axis and the NPV on the y-axis, for discount rates ranging from 5% to 30%. For what ranges of discount rates does the project have a positive NPV? as 12% a. Cost of capital Tax rate 21% 0 Sales Revenue Baseline 1 2 3 4 5 6 7 8 9 10 100.0 $ 100.0 $ 100.0 S 100.0 $ 100.0 S 100.0 $ 100.0 S 100.0 $ 100.0 $ 100.0 S Year Sales Revenue Manufacturing Expenses other than Depreciation Marketing Expenses - Depreciation Not Net Operating Income - Income Tax Unlevered Net Income + Depreciation Additions to NWC - Capital Expenditures Continuation Value = Free Cash Flow = S S $ S S S -35.0 $ -35.0 $ -35.0 S -35.0 $ 35.0 S -35.0 $ -35.0 S -35.0 $ 35.0 S -35.0 -10.05 -10.0 $ -10.05 -10.0 $ -10.0 S -10.0 $ -10.0 S -10.0 $ -10.0 $ -10.0 -15.0 $ -15.0 $ -15.0 S -15.0 $ -15.0S -15.0 $ -15.0 S -15.0 $ -15.0 S -15.0 40.0 S 40.0 $ 40.0 S 40.0 $ 40.0 S 40.0 $ 40.0 S 40.0 $ 40.0 S 40.0 -8.45 -8.4 $ -8.4 S -8.4 $ -8.4 S -8.4 $ -8.45 -8.4 $ -8.4 $ -8.4 31.6 31.6 $ 31.6 S 31.6$ 31.6 s 31.6 $ 31.6 S 31.6 $ 31.6 31.6 15.0 | s 15.0$ 15.0 s 15.0 $ 15.05 15.0 $ 15.0 s 15.0 $ 15.0 $ 15.0 -5,0 s -5.0$ -5,0 s -5.0 $ -5.0 s -5.0 $ -5.0 S s -5.0 $ -5.0 s -5.0 $ $ S $ -150.0 S 12.0 What is the NPV of the plant to manufacture lightweight trucks? NPV (million b. Based on input from the marketing department, Bauer is uncertain about its revenue forecast. In particular, management would like to examine the sensitivity of the NPV to the revenue assumptions. What is the NPV of this project if revenues are 10% higher than forecast? What is the NPV if revenues are 10% lower than forecast? Variation in revenues 10% Sales Revenue with 10% Increases 4 5 6 0 1 2 3 7 8 9 10 Year Sales Revenue Manufacturing Expenses other than Depreciation - Marketing Expenses Depreciation end Net Operating Income - Income Tax - Unlevered Net Income + Depreciation - Additions to NWC - Capital Expenditures + Continuation Value = Free Cash Flow $ $ $ $ $ $ $ $ -35.0 S -35.0 $ s -35.0 $ -10.0 S -10.0 $ -10.0 $ -15.0 S -15.0 $ - 15.0 $ -60.0 S -60.0 $ -60.0 $ 12.6 | 12.6 $ 12.6 $ -47.4 s -47.4$ -47.4 | $ 15.0 S 15.0 S 15.0 $ -5.0 s -5.0 $ -5.0 $ -35.0 $ -35.0 $ -35.0 S -35.0 S -10.0 $ -10.0 $ $ -10.0 S -10.0 S -15.0 $ -15.0 $ $ -15.0 $ -15.0$ -60.0 $ -60.0 $ -60.0s -60.0 S 12.6$ 12.6 | s 12.6 s 12.6s -47.4 $ -47.4 s -47.4 s -47.4 s 15.0 $ 15.0 S 15.0 S 15.0 S -5.0 $ -5.0 $ -5.0 S -5.0 S s -35.0 $ 35.0 $ -10.0 $ -10.0$ -15.0 $ -15.0$ -60.0 $ -60.0 $ 12.6 $ 12.6$ -47.4 $ -47.4 $ 15.0 $ 15.0 $ -5.0 $ -5.0 $ -35.0 -10.0 -15.0 -60.0 12.6 -47.4 15.0 -5.0 S -150.0 S 12.0 NPV (million) Sales Revenue with 10% Declines 4 5 6 0 1 2 3 7 8 9 10 Year Sales Revenue Manufacturing Expenses other than Depreciation - Marketing Expenses - Depreciation = Net Operating Income Income Tax Income Tax = Unlevered Net Income + Depreciation - Additions to NWC - Capital Expenditures + Continuation Value = Free Cash Flow $ $ S $ $ $ $ $ $ $ $ -35.0 S -35.0 $ -35.0 $ -35.0 $ -10.0 S -10.0 -10.0$ -10.0$ -15.0 S -15.0 S -15.0 $ -15.0$ -60.0 S -60.0 $ -60.0 $ -60.0 $ 12.6 S 12.6 $ 12.6 $ 12.6 $ -47.4$ 47.4 | $ -47.4 $ -47.4 $ 15.0 S 15.0 $ 15.0 $ 15.0 $ -5.0 S -5.0 $ -5.0 $ -5.0 $ -35.0 $ -35.0 S -35.0 S -35.0$ -35.0 $ -35.0 -10.0 $ -10.0 S -10.0 S -10.0 $ -10.0 $ -10.0 -15.0 $ -15.0 S -15.0 S -15.0 $ - 15.0 $ -15.0 -60.0 $ -60.0 S -60.0 S -60.0 $ -60.0 $ -60.0 12.6 $ 12.6 | s 12.6 | 12.6 $ 12.6$ 12.6 -47.4 $ -47.4$ 47.4$ 47.4 | $ 47.4 | $ 47.4 15.0 $ 15.0 S 15.0S 15.0 $ 15.0 $ 15.0 -5.0 $ -5.0 S -5.0 S $ -5.0 $ -5.0 $ -5.0 S -150.0 $ 12.0 NPV (million) Rather than assuming that cash flows for this project are constant, management would like to explore the sensitivity of its analysis to possible growth in revenues and operating expenses. Specifically, management would like to assume that revenues, manufacturing expenses, and marketing expenses are as given in the table for year 1 and grow by 2% per year every year starting in ycar 2. Management also plans to assume that the initial capital expenditures (and therefore depreciation), additions to working capital, and continuation value remain as initially specified in the table. What is the NPV of this project under these alternative assumptions? How does the NPV change if the revenues and operating expenses grow by 5% per year rather than by 2%? Growth rate (1) Growth rate (2) 2% 5% 0 Sales Revenue 2% Increases 1 2 3 4 5 6 7 8 9 9 10 100.0 S 102.0 $ 104.0 S 106.1 S 108.2 110.4 S 112.6 S 114.9 $ 117.2 s 119.5 $ Year Sales revenue Manufacturing expenses other than depreciation - Marketing expenses - Depreciation = Net operating income - Income a = Unlevered net income + Depreciation Additions to NWC Capital expenditures + Continuation value = Free cash flow $ $ $ $ $ $ $ $ $ -35.0 S -35.7$ -10.0 s - 10.2 $ - 15.0 S - 15.0 $ 40.0 / s 41.1 s -8.4 | s -8.6 $ 31.6 S 32.5$ / 15.05 15.0 $ -5.0 s -5.0 $ -36.4 $ -37.1 S -10.4 $ -10.6S -15.0 s - 15.0 S 42.2 $ 43.45 -8.9 s -9.1s 33.4 $ 34.3 S 15.0 S 15.0 S -5.0 $ -5.0 S -37.9 $ -38.6 $ -10.8$ -11.0 $ -15.0 $ -15.0 $ 44.5$ 45.7 $ -9.4 | s -9.6 $ 35.2 $ 36.1 $ 15.0$ 15.0 | s -5.0 $ -5.0 $ -39.4 S -11.3 S -15.0|S 46,9 s -9.9 S s 37.1 | s 15.0 | s -5.0 S -40.2 $ 41.0 $ -41.8 -11.5 $ -11.7 $ -12.0 -15.0 $ -15.0 S -15.0 48.2$ 49.4 | s 50.7 -10.1 $ -10.4 $ -10.7 38.1$ 39.1 $ 40.1 15.0 $ 15.05 15.0 -5.0 $ -5.0 $ -5.0 $ - 150.0 $ 49.1$ $ 12.0 62.1 -150.0 $ 41.6 | s 42.5 43.4 $ 44.3s 45.2 $ 46.1 $ 47.1 | s 48.1$ NPV (million) $ 107.38 0 $ Year Sales revenue - Manufacturing expenses other than depreciation Marketing expenses Depreciation Net operating income - Income tax = Unlevered net income + Depreciation - Additions to NWC - Capital expenditures Continuation value = Free cash flow $ $ S $ $ $ $ $ Sales Revenue 5% Increases 1 2 3 4 5 6 7 8 9 9 10 100.0 $ 105.0 $ 110.3 $ 115.8 S 121.6 $ 127.6 $ 134.0S 140.7 $ 147.7 $ 155.1 -35.0 S -36.8$ -38.6 $ -40.5 | s -42.5$ -44.7 | s -46.9S -49.2 $ -51.7 $ -54.3 -10.0 S -10.5 $ -11.0 $ -11.6 S -12.2$ -12.8 $ -13.45-14.1$ -14.8 % -15.5 -15.0 s -15.0 $ -15.0 $ -15.0 S -15.0 -15.0 $ -15,0 s -15.0 $ -15.0 $ -15.0 40.0 S 42.8$ 45.6 | s 48.7 | s 51.9 $ 55.2 $ 58.7 | s 62.4 | $ 66.3 $ 70.3 -8.4 S -9.0 / S -9.6$ -10.2 | s -10.9 $ -11.6 $ -12.3S -13.1 $ -13.9 $ -14.8 31.6 | s 33.8$ 36.1 $ 38.4 S 41.0 $ 43.6 | s 46.4s 49.3 $ 52.3 | s 55.6 15.0 S 15.0 $ 15.0 $ 15.0 S 15.0 $ 15.0 $ 15.0 S 15.0 $ 15.0 $ 15.0 -5,0 s -5.0 S -5.0 $ -5.0 s -5.0 s -5.0 $ -5.0 S -5.0 S -5.0 | s -5.0 $ - 150.0 $ 62.3 $ 12.0 77.6 $ - 150.0 $ 41.6 | s 46.1$ 43.8$ 48.4 S 51.0 $ 53.6 | $ 56.4 59.3 $ NPV (million) $ 138.58 d. To examine the sensitivity of this project to the discount rate, management would like to compute the NPV for different discount rates. Create a graph, with the discount rate on the x-axis and the NPV on the y-axis, for discount rates ranging from 5% to 30%. For what ranges of discount rates does the project have a positive NPV? NPV (million) Discount Rate 5% 6% 7% 8% 9% 10% 11% 12% 13% 14% 15% 16% 17% 18% 19% 20% 21% 22% 23% 24% 25% 26% 27% 28% 29% 30% The project has a positive NPV for the following range: (min) 5% (max) 24% to Requirements 1 In cell range D42:N42, by using cell references, calculate the free cash flows for years 0:10, respectively (11 pt.). Note: Refer only to the cells provided in the table for part a. 2 In cell D46, by using cell references, calculate the NPV of the project for this scenario (1 pt.). Note: Refer to the free cash flows previously calculated in the table for part a. 3 4 5 6 7 In cell range E53:N53, by using cell references, calculate the sales revenue if revenues are 10% higher than forecast for years 1:10, respectively (10 pt.). Note: Refer only to cell E30 and to the cells provided in part b. In cell range D65:N65, by using cell references, calculate the free cash flows if revenues are 10% higher than forecast for years 0:10, respectively (11 pt.). Note: Refer only to the cells provided in the first table for part b. In cell D67, by using cell references, calculate the NPV of the project if revenues are 10% higher than forecast (1 pt.). Note: Refer to the free cash flows previously calculated in the first table for part b. In cell range E70:N70, by using cell references, calculate the sales revenue if revenues are 10% lower than forecast for years 1:10, respectively (10 pt.). Note: Refer only to cell E30 and to the cells provided in part b. In cell range D82:N82, by using cell references, calculate the free cash flows if revenues are 10% lower than forecast for years 0:10, respectively (11 pt.). Note: Refer only to the cells provided in the second table for part b. In cell D84, by using cell references, calculate the NPV of the project if revenues are 10% lower than forecast (1 pt.). Note: Refer to the free cash flows previously calculated in the second table for part b. In cell range E129:E154, by using cell references, calculate the NPV for discount rates 5%:30%, respectively (26 pt.). Note: Refer only to the cells provided in the table for part a to obtain free cash flows for further calculations. In cell range G129:L141, plot the NPV profile of the investment opportunity by using the line chart with the cost of capital (cell range D129:D154) on the horizontal axis and the NPV (cell range E129:E154) on the vertical axis. Use the following labels: title: NPV Profile, horizontal axis label: Cost of Capital and vertical axis label: NPV (million) (3 pt.). In cell F157, by using cell references, input the lowest discount rate, from the range of discount rates that you have graphed, which provides a positive NPV (1 pt.). 8 9 10 11 12 In cell H157, by using cell references, input the highest discount rate that provides a positive NPV (1 pt.)