please help!

B D E G H M N Problem 9-32 Complete the steps below using cell references to given data or previous calculations. In some cases, a simple cell reference is all you need. To copy/paste a formula across a row or down a column, an absolute cell reference or a mixed cell reference may be preferred. If a specific Excel function is to be used the directions will specify the use of that function. Do not type in numerical data into a cell or function. Instead, make a reference to the cell in which the data is found. Make your computations only in the blue cells highlighted below. In all cases, unless otherwise directed, use the earliest appearance of the data in your formulas, usually the Given Data section. 10 11 12 13 14 15 16 17 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 are as 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 alternative 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? 18 19 20 21 22 23 24 25 26 27 28 a. Cost of capital Tax rate 12% 21% 0 29 30 31 Sales Revenue Baseline 1 2 3 4 5 6 7 8 9 10 100.0 $ 100.0 S 100.0 S 100.0 S 100.0 $ 100.0 S 100.0 S 100.0 $ 100.0 $ 100.0 $ $ S 32 33 34 35 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 -35.0 $ -35.0 $ $ 35.0 $ -35.0 $ -35.0 $ -35.0 $ -35.0 $ 35.0 $ -35.0 $ 35.0 $ 35.0 -10.0 $ -10.0 $ -10.0 $ -10.0 S -10.0 $ -10.0 $ -10.0 $ -10.0 $ -10.0 $ -10.0 -15.0 $ -15.0 $ -15.0 $ -15.0 $ $ -15.0$ -15.0 $ -15.0 $ -15.0 $ $ -15.0 $ -15.0 $ -15.0 $ -15.0 $ -15.0 40.0 $ 40.0 $ 40.0 $ 40.0 s 40.0 S 40.0 l $ 40.0 $ 40.0 $ 40.0 $ S 40.0 $ 40.0 $ 40.0 -8.4 S $ -8.4 | s S -8.4 | $ -8.45 8.4 $ -8.4 $ -8.4 | S -8.4 $ -8.4 $ -8.4 31.6$ 31.6 | s 31.6$ 31.6 S 31.6 | 31.6 $ 31.6 S 31.6 $ 31.6 | s 31.6 15.0$ 15.0 s 15.0 $ 15.0 s 15.0 $ 15.0 $ S 15.05 15.0 S 15.0 $ S 15.0 -5.0 $ -5.0 | s -5.0 $ -5.0 $ -5.0 $ S -5.0 $ $ -5.0 s -5.0 S -5.0 s S -5.0 $ $ $ S S $ 36 37 38 39 40 $ -150.0 41 $ 12.0 42 43 44 45 46 What is the NPV of the plant to manufacture lightweight trucks? NPV (million) 47 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? 48 49 50 Variation in revenues 10% 51 Sales Revenue with 10% Increases 4 5 5 6 0 1 2 3 7 8 9 10 52 53 54 55 56 57 58 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 S $ $ S S S -35.0 S -35.0 S -35.0 $ -35.0 S -35.0 S -35.0 $ -35.0 S -35.0 $ -35.0 S -35.0 -10.0 $ -10.0 $ -10.0 $ -10.0 $ -10.0 $ -10.0 $ -10.0 $ -10.0-10.0 $ -10.0 S -10.0 -15.0 $ -15.0$ -15.0 $ -15.0 $ -15.0 $ -15.0 $ -15.0 $ -15.0 $ -15.0 S -15.0 -60.0 $ 60.0 $ -60.0 $ -60.0 $ -60.0 $ -60.0 $ -60.0 $ -60.0 $ -60.0 $ -60.0 12.6 $ 12.6$ 12.6$ 12.6$ 12.6 $ 12.6 $ 12.6 S 12.6 $ $ 12.6 12.6 S 12.6 -47.4 $ 47.4 $ -47.4 $ 47.4 $ 47.4 $ 47.4 $ -47.4 $47.4 $ 47.4 S-47.4 15.0 $ 15.0 $ 15.0 $ 15.0 $ 15.0 S 15.0$ 15.05 15.0 $ 15.05 15.0 -5.0 $ -5.0 $ -5.0 S -5.0 $ -5.0 S -5.0 $ -5.0 s -5.0 $ -5.0 S -5.0 59 60 61 62 63 S -150.0 64 S 12.0 65 66 67 NPV (million) Sales Revenue with 10% Declines 4 5 6 0 1 2 3 7 7 8 9 10 68 69 70 71 72 S 73 74 S $ S 75 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 -35.0 $ -35.0 $ -35.0 $ -35.0 $ -35.0 $ -35.0 $ -35.0 S -35.0 $ -35.0 S -35.0 -10.0 $ -10.0 $ -10.0 $ -10.0 $ -10.0 $ -10.0 $ -10.0 $ -10.0 $ -10.0 $ -10.0 $ -10.0 -15.0 $ -15.0 -15.0 $ -15.0 $ -15.0 $ -15.0 $ -15.0 $ -15.0 $ -15.0 $ -15.0 $ -15.0$ -15.0 -60.0 $ -60.0 $ -60.0 -60.0 $ 60.0 $ -60.0 $ -60.0 -60.0 / $ -60.0 $ $ -60.0 -60.0 $ -60.0 $ -60.0 12.6$ 12.6 | s 12.6 | $ 12.6 $ 12.6s 12.6 $ 12.6 S 12.6 $ 12.6 S 12.6 -47.4 | $ 47.4 $ -47.4 | $ 47.4$ 47.4 | 47.4 $ 47.4$ 47.4 $ 47.447.4 15.0 $ 15.0 S 15.0 $ 15.0 $ 15.0 $ 15.0 $ 15.0 S 15.0 $ 15.0 S 15.0 -5.0 $ -5.0 S -5.0 $ -5.0 $ -5.0 $ -5.0 S -5.0 S -5.0 $ -5.0 S -5.0 $ 76 77 78 79 $ S S S -150.0 80 81 S 12.0 82 83 NPV (million) 84 85 B D E F G H 1 K M N 0 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 are as 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 alternative assumptions? How does the NPV change if the revenues and operating expenses grow by 5% per year rather than by 2%? 86 87 88 89 90 Growth rate (1) ( Growth rate (2) 2) 2% 5% 91 0 0 Sales Revenue 2% Increases 1 2 2 3 3 4 5 6 7 8 9 10 100.0 $ 102.0 $ 104.0 S 106.1 $ 108.2 $ 110.4 $ 112.6 S 114.9 $ 117.2 $ 119.5 92 $ 93 94 $ S 95 96 S 97 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 -35.0 S -35.7 $ -36.4 S -37.1 S-37.9 $ -38.6 S-39.4 S 40.2 $41.0 S 41.8 -10.0 $ -10.2 $ -10.4 $ S $ -10.6 $ -10.8 $ -10.8$ -11.0 $ -11.3 $ -11.0 $ -11.3S -11.5 $ -11.7 $ -12.0 -15.0 $ -15.0 $ -15.0 $ -15.0 $ -15.0 $ -15.0 $ -15.0 $ -15.0 $ -15.0 $ -15.0 $ -15.0 40.0 $ 41.1 $ 42.2 $ 43.4 $ 44.5$ $ 45.7 $ 46.9 $ 48.2 $ 49.4 $ 50.7 -8.4 | $ -8.6$ -8.9 $ -9.1 $ -9.4$ -9.6$ -9.9$ -10.1 $ -10.4 $ -10.7 31.6 $ 32.5$ 33.4 $ 34.3 $ 35.2 $ 36.1 S 37.1 S 38.1 $ 39.1 $ 40.1 15.0 $ 15.0 $ 15.05 15.0 $ 15.0 $ 15.0 $ 15.0 S 15.0 $ 15.0 $ 15.0 -5.0 $ -5.0$ -5.0 s -5.0 $ -5.0 $ -5.0 $ -5.0 s -5.0 $ -5.0 $ -5.0 98 $ $ $ $ S S 99 100 101 102 $ -150.0 $ 12.0 49.1 $ 62.1 $ -150.0 $ 41.6 | $ 42.5$ 43.4 $ 44.3 $ 45.2 $ 46.1$ 47.1 S 48.1 | $ 103 104 105 106 107 108 NPV (million) $ 107.38 0 Sales Revenue 5% Increases 1 2 3 4 5 6 7 8 8 9 10 100.0 $ 105.0 $ 110.3 $ 115.8 $ 121.6 $ 127.6 $ 134.0 $ 140.7 $ 147.7 $ 155.1 109 $ 110 111 1121 113 114 115 116 117 118 119 120 121 122 123 124 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 S $ $ $ $ -35.0 $ -36.8 $ -38.6 $ 40.5 $ 42.5 $ 44.7 $ -46.9 $49.2 $ 51.7 $ -54.3 -10.0 $ -10.5 $ -11.0 $ -11.6 $ 12.2 $ -12.8 -12.8s $ -13.4 $ -14.1 $ -14.8 $ -15.5 -15.0 $ -15.0 $ -15.0 S -15.0 $ -15.0$ -15.0$ -15.0 S -15.0$ -15.0$ -15.0 40.0 $ 42.8 $ 45.6S 48.7 | $ 51.9 $ 55.2 $ 58.7 S 62.4 | $ 62.4$ 66.3 $ 70.3 -8.4 S -9.0 $ -9.6 $ -10.2 $ -10.9 $ -11.6$ -12.3 $ S -13.1 $ -13.9 $ $ $ -14.8 31.6 $ 33.8$ 36.1 $ 38.4 $ 41.0 $ 43.6 | s 46.45 49.3 $ 52.3 $ 55.6 15.0 $ 15.0$ 15.0 $ 15.0 $ 15.0 $ 15.0 $ 15.0 S 15.0 $ 15.0 $ 15.0 -5.0 $ -5.0 $ -5.0 $ -5.0 S -5.0 $ -5.0 $ -5.0 S -5.0 $ -5.0 $ -5.0 $ - 150.0 $ 12.0 62.3 $ 77.6 $ -150.0 $ 41.6 $ 43.8 $ 46.1 s 48.4 | s 51.0 S $ 53.6 s 56.4 | s 59.3 $ NPV (million) $ 138.58 A B D G H M N O 124 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? 125 NPV (million) 126 127 128 129 130 131 Discount Rate 5% 6% 7% 8% 9% 10% 132 133 134 135 11% 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 12% 13% 14% 15% 16% 17% 18% 19% 20% 21% 22% 23% 24% 25% 26% 27% 28% 29% 30% (min) (max) 24% 157 The project has a positive NPV for the following range: 5% to 158 163 164 166 160 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 161 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 162 for part a. 3 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. 4 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. 5 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 165 previously calculated in the first table for part b. 6 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. 7 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. 8 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. 9 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. 10 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 170 vertical axis label: NPV (million) (3 pt.). 11 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 171 pt.). 172 12 In cell H157, by using cell references, input the highest discount rate that provides a positive NPV (1 pt.). 173 174 167 168 169 B D E G H M N Problem 9-32 Complete the steps below using cell references to given data or previous calculations. In some cases, a simple cell reference is all you need. To copy/paste a formula across a row or down a column, an absolute cell reference or a mixed cell reference may be preferred. If a specific Excel function is to be used the directions will specify the use of that function. Do not type in numerical data into a cell or function. Instead, make a reference to the cell in which the data is found. Make your computations only in the blue cells highlighted below. In all cases, unless otherwise directed, use the earliest appearance of the data in your formulas, usually the Given Data section. 10 11 12 13 14 15 16 17 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 are as 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 alternative 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? 18 19 20 21 22 23 24 25 26 27 28 a. Cost of capital Tax rate 12% 21% 0 29 30 31 Sales Revenue Baseline 1 2 3 4 5 6 7 8 9 10 100.0 $ 100.0 S 100.0 S 100.0 S 100.0 $ 100.0 S 100.0 S 100.0 $ 100.0 $ 100.0 $ $ S 32 33 34 35 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 -35.0 $ -35.0 $ $ 35.0 $ -35.0 $ -35.0 $ -35.0 $ -35.0 $ 35.0 $ -35.0 $ 35.0 $ 35.0 -10.0 $ -10.0 $ -10.0 $ -10.0 S -10.0 $ -10.0 $ -10.0 $ -10.0 $ -10.0 $ -10.0 -15.0 $ -15.0 $ -15.0 $ -15.0 $ $ -15.0$ -15.0 $ -15.0 $ -15.0 $ $ -15.0 $ -15.0 $ -15.0 $ -15.0 $ -15.0 40.0 $ 40.0 $ 40.0 $ 40.0 s 40.0 S 40.0 l $ 40.0 $ 40.0 $ 40.0 $ S 40.0 $ 40.0 $ 40.0 -8.4 S $ -8.4 | s S -8.4 | $ -8.45 8.4 $ -8.4 $ -8.4 | S -8.4 $ -8.4 $ -8.4 31.6$ 31.6 | s 31.6$ 31.6 S 31.6 | 31.6 $ 31.6 S 31.6 $ 31.6 | s 31.6 15.0$ 15.0 s 15.0 $ 15.0 s 15.0 $ 15.0 $ S 15.05 15.0 S 15.0 $ S 15.0 -5.0 $ -5.0 | s -5.0 $ -5.0 $ -5.0 $ S -5.0 $ $ -5.0 s -5.0 S -5.0 s S -5.0 $ $ $ S S $ 36 37 38 39 40 $ -150.0 41 $ 12.0 42 43 44 45 46 What is the NPV of the plant to manufacture lightweight trucks? NPV (million) 47 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? 48 49 50 Variation in revenues 10% 51 Sales Revenue with 10% Increases 4 5 5 6 0 1 2 3 7 8 9 10 52 53 54 55 56 57 58 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 S $ $ S S S -35.0 S -35.0 S -35.0 $ -35.0 S -35.0 S -35.0 $ -35.0 S -35.0 $ -35.0 S -35.0 -10.0 $ -10.0 $ -10.0 $ -10.0 $ -10.0 $ -10.0 $ -10.0 $ -10.0-10.0 $ -10.0 S -10.0 -15.0 $ -15.0$ -15.0 $ -15.0 $ -15.0 $ -15.0 $ -15.0 $ -15.0 $ -15.0 S -15.0 -60.0 $ 60.0 $ -60.0 $ -60.0 $ -60.0 $ -60.0 $ -60.0 $ -60.0 $ -60.0 $ -60.0 12.6 $ 12.6$ 12.6$ 12.6$ 12.6 $ 12.6 $ 12.6 S 12.6 $ $ 12.6 12.6 S 12.6 -47.4 $ 47.4 $ -47.4 $ 47.4 $ 47.4 $ 47.4 $ -47.4 $47.4 $ 47.4 S-47.4 15.0 $ 15.0 $ 15.0 $ 15.0 $ 15.0 S 15.0$ 15.05 15.0 $ 15.05 15.0 -5.0 $ -5.0 $ -5.0 S -5.0 $ -5.0 S -5.0 $ -5.0 s -5.0 $ -5.0 S -5.0 59 60 61 62 63 S -150.0 64 S 12.0 65 66 67 NPV (million) Sales Revenue with 10% Declines 4 5 6 0 1 2 3 7 7 8 9 10 68 69 70 71 72 S 73 74 S $ S 75 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 -35.0 $ -35.0 $ -35.0 $ -35.0 $ -35.0 $ -35.0 $ -35.0 S -35.0 $ -35.0 S -35.0 -10.0 $ -10.0 $ -10.0 $ -10.0 $ -10.0 $ -10.0 $ -10.0 $ -10.0 $ -10.0 $ -10.0 $ -10.0 -15.0 $ -15.0 -15.0 $ -15.0 $ -15.0 $ -15.0 $ -15.0 $ -15.0 $ -15.0 $ -15.0 $ -15.0$ -15.0 -60.0 $ -60.0 $ -60.0 -60.0 $ 60.0 $ -60.0 $ -60.0 -60.0 / $ -60.0 $ $ -60.0 -60.0 $ -60.0 $ -60.0 12.6$ 12.6 | s 12.6 | $ 12.6 $ 12.6s 12.6 $ 12.6 S 12.6 $ 12.6 S 12.6 -47.4 | $ 47.4 $ -47.4 | $ 47.4$ 47.4 | 47.4 $ 47.4$ 47.4 $ 47.447.4 15.0 $ 15.0 S 15.0 $ 15.0 $ 15.0 $ 15.0 $ 15.0 S 15.0 $ 15.0 S 15.0 -5.0 $ -5.0 S -5.0 $ -5.0 $ -5.0 $ -5.0 S -5.0 S -5.0 $ -5.0 S -5.0 $ 76 77 78 79 $ S S S -150.0 80 81 S 12.0 82 83 NPV (million) 84 85 B D E F G H 1 K M N 0 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 are as 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 alternative assumptions? How does the NPV change if the revenues and operating expenses grow by 5% per year rather than by 2%? 86 87 88 89 90 Growth rate (1) ( Growth rate (2) 2) 2% 5% 91 0 0 Sales Revenue 2% Increases 1 2 2 3 3 4 5 6 7 8 9 10 100.0 $ 102.0 $ 104.0 S 106.1 $ 108.2 $ 110.4 $ 112.6 S 114.9 $ 117.2 $ 119.5 92 $ 93 94 $ S 95 96 S 97 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 -35.0 S -35.7 $ -36.4 S -37.1 S-37.9 $ -38.6 S-39.4 S 40.2 $41.0 S 41.8 -10.0 $ -10.2 $ -10.4 $ S $ -10.6 $ -10.8 $ -10.8$ -11.0 $ -11.3 $ -11.0 $ -11.3S -11.5 $ -11.7 $ -12.0 -15.0 $ -15.0 $ -15.0 $ -15.0 $ -15.0 $ -15.0 $ -15.0 $ -15.0 $ -15.0 $ -15.0 $ -15.0 40.0 $ 41.1 $ 42.2 $ 43.4 $ 44.5$ $ 45.7 $ 46.9 $ 48.2 $ 49.4 $ 50.7 -8.4 | $ -8.6$ -8.9 $ -9.1 $ -9.4$ -9.6$ -9.9$ -10.1 $ -10.4 $ -10.7 31.6 $ 32.5$ 33.4 $ 34.3 $ 35.2 $ 36.1 S 37.1 S 38.1 $ 39.1 $ 40.1 15.0 $ 15.0 $ 15.05 15.0 $ 15.0 $ 15.0 $ 15.0 S 15.0 $ 15.0 $ 15.0 -5.0 $ -5.0$ -5.0 s -5.0 $ -5.0 $ -5.0 $ -5.0 s -5.0 $ -5.0 $ -5.0 98 $ $ $ $ S S 99 100 101 102 $ -150.0 $ 12.0 49.1 $ 62.1 $ -150.0 $ 41.6 | $ 42.5$ 43.4 $ 44.3 $ 45.2 $ 46.1$ 47.1 S 48.1 | $ 103 104 105 106 107 108 NPV (million) $ 107.38 0 Sales Revenue 5% Increases 1 2 3 4 5 6 7 8 8 9 10 100.0 $ 105.0 $ 110.3 $ 115.8 $ 121.6 $ 127.6 $ 134.0 $ 140.7 $ 147.7 $ 155.1 109 $ 110 111 1121 113 114 115 116 117 118 119 120 121 122 123 124 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 S $ $ $ $ -35.0 $ -36.8 $ -38.6 $ 40.5 $ 42.5 $ 44.7 $ -46.9 $49.2 $ 51.7 $ -54.3 -10.0 $ -10.5 $ -11.0 $ -11.6 $ 12.2 $ -12.8 -12.8s $ -13.4 $ -14.1 $ -14.8 $ -15.5 -15.0 $ -15.0 $ -15.0 S -15.0 $ -15.0$ -15.0$ -15.0 S -15.0$ -15.0$ -15.0 40.0 $ 42.8 $ 45.6S 48.7 | $ 51.9 $ 55.2 $ 58.7 S 62.4 | $ 62.4$ 66.3 $ 70.3 -8.4 S -9.0 $ -9.6 $ -10.2 $ -10.9 $ -11.6$ -12.3 $ S -13.1 $ -13.9 $ $ $ -14.8 31.6 $ 33.8$ 36.1 $ 38.4 $ 41.0 $ 43.6 | s 46.45 49.3 $ 52.3 $ 55.6 15.0 $ 15.0$ 15.0 $ 15.0 $ 15.0 $ 15.0 $ 15.0 S 15.0 $ 15.0 $ 15.0 -5.0 $ -5.0 $ -5.0 $ -5.0 S -5.0 $ -5.0 $ -5.0 S -5.0 $ -5.0 $ -5.0 $ - 150.0 $ 12.0 62.3 $ 77.6 $ -150.0 $ 41.6 $ 43.8 $ 46.1 s 48.4 | s 51.0 S $ 53.6 s 56.4 | s 59.3 $ NPV (million) $ 138.58 A B D G H M N O 124 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? 125 NPV (million) 126 127 128 129 130 131 Discount Rate 5% 6% 7% 8% 9% 10% 132 133 134 135 11% 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 12% 13% 14% 15% 16% 17% 18% 19% 20% 21% 22% 23% 24% 25% 26% 27% 28% 29% 30% (min) (max) 24% 157 The project has a positive NPV for the following range: 5% to 158 163 164 166 160 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 161 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 162 for part a. 3 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. 4 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. 5 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 165 previously calculated in the first table for part b. 6 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. 7 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. 8 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. 9 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. 10 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 170 vertical axis label: NPV (million) (3 pt.). 11 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 171 pt.). 172 12 In cell H157, by using cell references, input the highest discount rate that provides a positive NPV (1 pt.). 173 174 167 168 169