a.

b.

c.

d.

Complete the steps below using cell references to given data or previous calculations. In same cases, a simple cell reference is all your need. To copypaste 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 direcrions will specify the use of that function. Do not type in numerical data into a cell or fincrion. Instead, make a reference to the cell in which the data is found. Make your computations only in the bluc cells highlighted below; In all cases, unless otherwise directed, wse the earliest appearance of the dafa in your formalas, usally the Given Data section. 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 bas 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, Batuer 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 operativg 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 eapital 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 alterative 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? What is the NPV of the plant to manufacture lightweight trucks? 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% Year Sales Revenue - Manufacturing Expenses other than Depreciation - Marketing Expenses - Depreciation - Net Operating Income - Ineome Tax - Unlevered Net Income + Depreciation - Additions to NWC - Capital Expenditures + Continuation Value - Free Casb Flow NPV (millioe) Sales Revenue with 10% Increases Sales Revenae with 10% Declines c. Rather than assuming that cash flows for this project are constant, management would tike to explore the sensitivity of its analysis to posuble growth in revenues and operating expenses. Specifically, management would like to assume that revenues, manufacturing expenses, and naaketing expenses are as given in the table for year 1 and grow by 2% per year every year starting in year 2. Managenent also plans to assume that the initial capital expenditures (and therefore depreciation), additions to working capital, and continuation value reasin as initiatly specified in the table. What is the NPV of this preject under these altemative assumptions? How does the NPV change if the reveases and operating expenses grow by 5% per year tather than by 2% ? Growth rate (I) Growth rate (2) Vear Sales revenaue - Manufacturing expenses other than depreciation - Marketing expenses - Depreciation - Net operating incene - Income tax - Ualevered net income + Depreciation - Additions to NWC - Capital expendirares + Continuation value - Free cash flow NPV (mullion) Year Sales revenue - Manafacturing expenses other than deprociation - Marketing expencs -Depreciation - Net opsratige incoma - Income tax - Ualevered net income + Depreciation - Aditions to NWC - Capalal expendifures + Continuation valus - Free cash flow \begin{tabular}{|r|} \hline 2% \\ \hline 5% \\ \hline \end{tabular} a 5107,38 Sales Revenue 2% Increases 1 4 5 6 8 89 910 10 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? (min) (max) II the project has a positive NPV for the following range: to