This excel tool was designed to fit into a financial analysis tool kit at the end of the modeling phase. What this means is after Year 0 through (up to) 10-year cash contributions and distributions have been determined, they would be plugged into the start of this DCF Analysis model.
The point of a Discounted Cash Flow Analysis is to figure out the value of all future incoming and outgoing cash flows as of today. If the present value of the future cash inflows is greater than the present value of all cash outflows, then the project is worth the risk. Risk is defined by a discount rate. A sensitivity analysis was done in that context.
You should also note the IRR is included in this, which tells you exactly what discount rate to apply so that the present value of future cash inflows equals the present value of all cash outflows.
The template has two tabs.
Tab 1: Has an area to input one set of cash inflows and outflows as well as up to 6 discount rates. The resulting NPV for each rate is then displayed in a table as well as a bar chart. The resulting IRR is also displayed.
Tab 2: Has an area to input up to 4 sets of cash inflows and outflows as well as up to 6 discount rates. The purpose of this tab is to be able to compare 4 different cash flow scenarios. Each scenario could be separate projects or 4 potential flows of a single project. The resulting 24 NPV outputs will then display in a table as well as a uniquely designed visual. The visual sort of works like a scatter plot with all 24 NPV displayed based on each scenario and each discount rate.
Tab 2 also has all 4 IRR (internal rate of return) outputs for the scenarios and that is displayed in a bar chart.
The user will plug in cash flows and discount rate values, the rest of the model auto-populates.