Compilete the steps below using cell references to given duta ar previons calculations. In some cases, a simple cell reference is all jou neted To copy/paste a formula across a now or down a columut, an absolute cell reference or a mined cell reforence mar be preferred, if a specific Encel reference to the cell in whick the duta is found. Make your compoufations only in the blive cells Aighlighted below. In all cases, unless otherwise directed, wse the eiviliest appeurance of the data in your formulas, ssmally the Given Dafa section. Bauer Industries is an automobile manufscturcr. Management is currently evaluating a pooposal to build a plant that will manuficture lightweight trocks. Bauer plans to use a cost of capital of 12% to evaluate this projoct. Based on extensive research, it has pocpared the incromental froc cash flow projections that are shown below (in millions of dollars). a. What is the NPV of the plant to manufacture lightweight trucks? b. Basod on input from the marksting deportment, Bauer is unocrtain about its revenue forocast. In particular, manngement would like to examine the sansitivity of the NPV to the revenue assumptions. What is the NPV of this project if revenoes are 10Ne higher than forscast? What is the NPV if tevenues are 10% lower than forccast? e. Rather then asuming that cash flows for this project are oonstant, mantegenent would like to explore the sensitivity of its analysis to possible growth in scvenues and operating expenses. Specifically, management would like to assume that revenues, manufacturing expenses, and marketing expenses afe as given in the table for yoar 1 and gow by 2% per year every your starting in your 2 . Management also plans to assume that the initial capital expenditures (and therefore deprociation), abditions to working capital, and continuation value rantin as initially specified in the table. What is the NPV of this pooject under these alternative assumptions? How does the NPV change if the revenues and operating expenses grow by 5% per year nather than by 2% ? d. To examine the sensitivity of this poojoct to the discount rate, managenent would like to compote the NPV for different discount rates. Creake a graph, with the disoont rite on the x-axis and the NPV on the y-axis, for discount rates ranging from 5% to 30%. For what nagos of disoount rates does the projact have a positive NPV? What is the NPV of the plant to mansfacture tightweight trucke? b. Based on iaput from the marketing department, Baner is uncertain about its revenue forscalt. In particular, managenent would like to ecamine 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 fodcesat? Variation in revenues 10% Year Sales Revenue - Manufacturing Expenses other than Deprecistion - Marketing Expenses - Deprociation - Net Operating Inoome - Income Tax - Unlevarod Net lnoome + Depreciarion - Additions to NWC - Capital Expenditures + Continuation Value = Free Cash Flow NPV (million) Sales Revease with 10% Declines e. Rather than assuming that cash flows foe this projoct ase coestant, management would like to explore the sensitivity of its analysis to possible growth in revenues and operating expenses. Specifically, management would like to asseme that revenues, manufacturing expenses, and marketing expenses are as given in the table for year 1 and grow by 2% per year every yeur starting in year 2. Management also plass to assume that the initial crpital expenditures (and therefore depeciation), additions to wocking copital, and contiasation valoe remain as initially spocified in the table. What is the NPV of this paoject under these altemative assumptions? How does the NPV change if the revenues and operaieg expenses grow by 5% per year rather than by 2% ? \begin{tabular}{l|l|} \hline Growth rate (1) & 2% \\ Growth rate (2) & 5% \\ \hline \end{tabular} Year Sales revenue - Manufacturing expenses other then depreciation - Maketing expenses - Depreciation = Net operating incone - Income tax = Unlevered net income + Deprociation - Additions to NWC - Capital expenditures + Contimuation value - Froe cash flow NPV (million) \begin{tabular}{|lr|} \hline 5 & 107.38 \\ \hline \end{tabular} Sales Provnue 5% Incrwases d. To examine the sensitivity of this projoet to the discount nae, management would like to eompute the NPV for ditferant diseonint rates. Create a graph, with the positive NPY? Discosnt NPV (min) finaxh The project han a positive NPV for the following range: to Reyuirrments 1 In eell ange D42:N42, by using eell references, caleulate the free cach flows for yeas 0:10, respectively (11 pt.). Note: Refer only to the cells provided in the table for part a. 2 In cell D46, by using eell seferences, calcelate the NPV of the project for this semario (1 pt.). Note: Refer to the free cash flows peeviously calculated is the table for part a 3 In eell nage E.53:N53, by using eell references, calculate the sales revenue if revenues aee 10% higher than forecant for years 1:10, respectively (10 pt.). Note: Refir only to edl E.30 and to the oells provided in part b. 4 In eell nage D65:N65, by using eell references, caleulate the free cach flows if revenues ave 10\% higher than forecast for yeas 0:10, respoctively (11 pt). Note: Refer only to the eells provided in the first table for part b. 5 In eell D67, by using cell sefereses, calcelate the NPV of the project if sevenues aee 10\% higher than forocast (1 pt). Nete: Refer to the free canh flows previeusly calcelated in the fist table for part b. 6 In eell nage E.70:N70, by using eell feferences, calculate the sales revenue if revenues ace 10\%, lower than forceast for yean 1:10, respotively (10 pt.). Note: Refer only to cell E.30 and to the cells provided is part b. 7 In eell ninge D82:N82, by using edl referenoes, caleulate the free cash flows if sevenues are 10\% lower than forecant for yean 0:10, nespoctively (11 pt). Note: Refer only to the cells provided in the secood table for part b. 8 In eell D84, by using cell seferenoes, caloulate the NPV of the project if revenues aee 10\% lower than forocast (1 pt). Note: Refer to the free cash flows previeusly calculated in the seoond table foe part b. the table for part a to obtain fies cash flows for further calculations. 10 In ocll nage G129-L.141, plot the NPV profile of the investment opportunity by using the line chart with the cost of capital (cell nange D129.D154) en the harixontal axis and the NPV (oell range E129:E1S4) on the vertical axis, Use the following labels: title: NPV Profile, horixntal axis label: Cest of Capital and vertical axis label: NPV (millios) (3pt). 11 In ocll F157, by uting ofl referenoes, input the lowest discount tate, from the range of clisount rites that you have graphod, which poovides a positive NPV ( pt.) 12 In oell H157, by uting oell reforences, input the highest disoount mate that povides a positive NPV (1 pt.)