Question

I have done a fcff in excel and i was wondering how do i preform a sensitivity analysis with it. I did the FCFF but need help with the sensitivity analysis. Here is the question: You are thinking of investing $1000 in a machine today. It would be used to produce sales for 10 years. The projected sales number in year 1 is 1200, after which sales will grow at 2% annually. Cost of manufacturing for this project is 50% of sales. You also know that the machine is depreciated with the straight-line method over the life of the project, and the project needs an additional investment in raw materials at time zero to get the production off the ground. The materials cost $200 and will need to be paid cash. Marginal corporate tax rate is 35%. Your cost of capital is 8% p.a. The project is financed 100% equity. In EXCEL: a) Using FCFF find the value of the project. b) Perform a sensitivity analysis of the project’s value to the discount rate. Attached is my FCFF excel sheet. Also what if the question said that the machine was sold for $200 at the end of the project even though it depreciated to 0 through straight-line method, where do I put the 200. Thanks

Answer 1

Sensitivity analysis is nothing but change in NPV (project's value) in relation to change in any variable. So, if you want to do sensitivity analysis of NPV is relation to discount rate, then first compute the NPV at the current discount rate (8%). Then, choose a different discount rate and again compute the NPV. Now, you compare them -

Sensitivity in relation to discount rate = [ New NPV - Old NPV ] / [ New Discount rate - Old discount rate ]

The above measures the change in NPV due to change in discount rate. You can choose any discount rate for computing the NPV but it is preferred if you take small increments, say 1%. So, you can compute the new NPV with 9%. Choosing a higher discount rate will lower your NPV and you will get a decrease in NPV for every percentage change in discount rate. But, if you choose a lower rate, say 7%, you will get a higher NPV and you can measure the increase in NPV for every percentage change in discount rate.

Salvage value - Salvage value is a cash inflow for the project. Its at the end of the project, so it appears in year 10 (or last year). Now, taxes are applicable when you sell an asset unless otherwise given in question. So, your inflow would be Salvage value net of tax.

Tax on sale is paid on gain on sale.

Gain on sale = Salvage value - Remaining book value

In the current case since, book value is zero, Gain on sale = Salvage value.

Tax on gain = $200 x 35% = $70.

Net cash inflow = $200 - $70 = $130.

So, you will input or add $130 to the year 10 after tax cash inflow.

Hope this helps and, I don't see any excel file attached. Let me know in the comments, if you need any further help / complete solution, and I can help you with that.