How to prepare a business plan using Excel dashboard

Excel is a dashboard for conducting any calculations. In order to calculate the efficiency of investments in the startup you do not need any special skills. Not so many special Excel formulas are used in the process. However, here are some peculiarities you should understand.

Excel instruments used to calculate NPV

Net present value is mandatory for determining the effectiveness of any investment project. If you prepare a business plan for an investor or creditor, they will definitely pay attention to this indicator. Find out more about NPV in this article. However, if you want to apply Excel functions for calculations, then use the NPV function.

The function is as follows:

= NPV (B5; B4; B3; C3; D3; E3; F3),

where:
the first value is the discount rate;
all other values are undiscounted cash flows.

However, calculating the business plan, it is necessary to take into account that the discount rate does not change in the first year, so the NPV formula will look as follows:

= NPV (B5; C3: F3) + B4 + B3

where:
B5 – discount rate
C3: F3 – cash flow starting from the second year
B4 – cash flow in the first year
B3 – investments (the cell has a negative value always).

Excel instruments used to calculate IRR

The internal rate of return is another indicator, easily calculated by means of Excel instruments. The IRR function is used and the undiscounted cash flow is a basis for calculations. The first value of the function is the amount of investments (this value is always negative). The next values represent the cash flow for a certain period. At the same time, there are no restrictions on the number of years (Excel supports calculations for more than 200 values).

= IRR (B139: G139)

Excel instruments used to calculate the Profitability Index

Profitability index is the inverse indicator of the discounted payback period.

It is determined by the ratio of discounted net cash flow to the value of funds invested in the project. Investors consider only those projects in which the indicator is more than 1. In a context of limited financial resources, the indicator shows an investment portfolio. The only drawback in determining this indicator refers to the approach to determining the discount rate, which can not always reliably predict the present value due to the non-consideration of risks and other unforeseen situations.

How to calculate Payback Period using Excel

Payback period is determined by two methods. The first method deals with the discounted cash flows, while the second one with undiscounted cash flows. Discounted cash flows are used more often, however, undiscounted cash flows are an alternative indicator that can be calculated in a business plan. Read more about the payback period of the investment project in this article.

You can also get templates:

  1. Download a business plan into Excel and get a ready model of calculations
  2. Business plan of a bar in Excel
  3. Bakery business plan in Excel
  4. Business plan of a coffee shop in Excel
  5. Business plan of a cafe in Excel
  6. Business plan of e-commerce store in Excel