How to Calculate Incremental Cash Flow in Excel (2 Examples)

For any business undertaking any kind of decision, especially whether to make an investment in a new venture or reinvest in an old project, Incremental Cash Flow can actually help a lot. If you are curious to know how you can calculate Incremental Cash Flow in Excel, then this article may come in handy for you. In this article, we discuss how you can calculate Incremental Cash Flow with elaborate explanations.


Download Practice Workbook

Download this practice workbook below.


Overview of Incremental Cash Flow

Definition

When a company accepts a new project, it generates additional operating cash flow, which is referred to as incremental cash flow.

If the company has a healthy Cash Flow, it is more likely to invest in or increase its Cash Flow in a new project. Overall Cash Flow is the total amount of money moving in and out of the business. A business’s cash flow is critical to determine if it has sufficient to cover its expenses, and loans, as well as acquire new assets that will help the company grow. For businesses, Cash Flow is critical because it allows them to monitor their ability to pay bills, make loan payments, and pay taxes as well as purchase assets that help them run their operations more efficiently.

Components of Incremental Cash Flow

Incremental Cash Flow is important to know to understand whether an investor should invest in a project or not. It is normally affected by five separate factors.

  • Index of Profitability
  • Net present value
  • Rate of return
  • Payback period
  • Internal rate of return

Steps for Calculating Incremental Cash Flow

We can calculate the Incremental Cash Flow by following the below step-by-step procedure.

  • Determine the Revenue of the business.
  • Estimate the Expense of the company.
  • Initial cost listing.
  • Remove Expenses from Revenue.
  • Subtract the Initial cost from the value from step four.
  • Repeat the above procedures and compare the results.

Difficulties in Calculating Incremental Cash Flow

Accountants face severe challenges while calculating the Incremental Cash Flow for various reasons. The challenges are described below.

1. Sunk Costs

Sunk Costs are the costs that have already occurred. They’re usually taken into account before making any kind of investment. Normally, investors account for the cost in the future, so normally they try to avoid this type of cost.

2. Cannibalization

Cannibalization in the market analysis means the creation of a project which could potentially disrupt the operation of another running project under the same company. The new product could undercut the existing product or could conflict with the interests of the customer.

For example. A shopkeeper was selling a product made from porcelain material. At the same time, they were trying to launch a similar type of product made of cheap plastic. This second type of product could harm the sales of the first product because it could offer the same type of product for less value, although the quality might be reduced. This directly conflicts with the revenue of the first product. So, the new product might be making money, but it’s hurting sales of the first product, so the extra Cash Flow from the new product isn’t very useful in the long run.

3. Identification Difficulty

It becomes very difficult to identify the Cash Flow from various mixed products. Sometimes the Flow from the mix of products is mixed up and it is difficult to differentiate.

4. Unpredictable External and Internal Factor

The reason is that there is a limit to calculating the Incremental Cash Flow because there are a lot of external factors that can influence the incremental Cash flow. Although the internal factors can be resolved, the external factors’ impact estimation is a herculean task. The different effects, like cannibalization and sunk costs, make the Incremental Cash Flow calculation difficult.

5. Cost Allocation

When there are more projects, cost allocation will become a challenge. In order to complete this task successfully, we’ll need specialized accounting software and a capable group of individuals.


2 Suitable Examples to Calculate Incremental Cash Flow in Excel

We are going to present two examples to demonstrate how you can calculate Incremental Cash Flow. One is for launching a new venture, and the other is for upgrading an existing company.


1. Incremental Cash of a New Venture

For any investor, estimating the Incremental cost can help to make a decision on whether it is fruitful to invest in this venture or not. We used the SUM function in this example.

Step 1: Estimate Initial Investment Outflow

We will estimate the Initial Outflow for the initial investments in this step.

  • To begin, we need to collect information about the new investment.
  • Like how much money do we need to buy new machines?
  • At the same time, the amount of money we would get after selling the scrap at the end of its life cycle.

Incremental Cash of a New Venture to to Calculate Incremental Cash Flow in Excel

  • Then select cell D14 and enter the following formula

=D12-D13

Incremental Cash of a New Venture to to Calculate Incremental Cash Flow in Excel

Step 2: Estimate Operating Outflow

After we get the initial cost, we can calculate the Operating Outflow.

  • Then we collect the information about the operating inflow.
  • Operating Inflow consists of the Variable cost, Fixed cost.
  • In the below dataset, we have the variable cost per unit in the range of cells D5:D8.
  • And the Fixed cost is in the range of cells E5:E8.
  • Next, in the Revenue per unit in the range of cells F5:F8.

  • Then we calculated the Operating Inflow by entering the following formula in cell G5:

=F5*C5-(C5*D5+C5*E5)

  • This will estimate the Operating Inflow for Product Id A-051456.

  • Then drag the Fill Handle to cell G8.
  • Doing this will estimate the Operating Inflow for the Product Id mentioned in the range of cells B5:B8.
  • All of them are actually positive, meaning that the investors are actually making money out of this operation.

  • Then select cell H5 and enter the following formula:

=SUM(G5:G8)

  • Entering this formula will calculate the Total Operating Inflow of all the Product Id mentioned.

Calculating the Operating Outflow to Calculate Incremental Cash Flow in Excel

Step 3: Calculate Incremental Cash Flow

Now, as we have all the necessary information, we can calculate the Incremental Cash Flow.

  • The next step is to calculate the Terminal Cash flow and then calculate the Total Incremental Cash Flow.
  • In order to do this, organize the Total Operating Cash Inflow in cell H12.
  • And the Total Outflow from the Investment in cell H13.
  • For all types of businesses, there is a fee for Rent and Rates.
  • We input the rent and the rate value into cell H14.
  • Finally, we enter the following formula in the cell H15:

=H12-H13-H14

  • Entering this formula will actually subtract the out Flow from the Investment and the Rates & Rent, from the Operating Inflow.

Calculating the Operating Outflow to Calculate Incremental Cash Flow in Excel

  • And this is how we calculate incremental cash flow in Excel for a new venture.

Read More: How to Create Cash Flow Statement Format in Excel


2. Incremental Cash Flow for Upgrading Project

Now, in this example, we present how you can calculate the Incremental Cash Flow from upgrading an existing project. We used the SUM function in this example.

Step 1: Estimate Initial Investment Outflow

We will estimate the initial out Flow for the initial investments in this step.

  • To begin, we need to collect information about the new investment.
  • Like how much money do we need to buy new machines?
  • At the same time, how much money would we get after selling a scrap of the old assets?

Estimate the Initial Investment Outflow to Calculate Incremental Cash Flow for Upgrading Project in Excel

  • Then select cell D14 and enter the following formula

=SUM(D12:D13)

Step 2: Calculate Operating Outflow

After we get the initial cost, we can calculate the Operating Outflow.

  • Then we collect the information about the Operating Inflow.
  • Operating inflow consists of the Variable cost, Fixed cost.
  • In the below dataset, we have the Variable Cost per unit in the range of cells C5:C8.
  • And the Fixed cost is in the range of cells D5:D8.
  • Next is the Revenue per unit in the range of cells E5:E8.

  • Then we calculated the Operating Inflow by entering the following formula in cell F5:

=E5-D5-C5

  • This will estimate the Operating Inflow for the First Year.
  • This Operating Inflow is calculated by subtracting Fixed cost and Variable cost from the Revenue.

  • Then drag the Fill Handle to cell F8.
  • Doing this will estimate the Operating Inflow for each Year mentioned in the range of cells B5:B8.
  • All of them are actually positive, meaning that the investors are actually making money out of this operation.

Estimate the Initial Investment Outflow to Calculate Incremental Cash Flow for Upgrading Project in Excel

  • Then in cell G5, we enter the operating cost of the previous operation.

  • Then select cell H5 and enter the following formula:

=SUM(F5:F8)-G5

  • Doing this will estimate the Total Operating Inflow increase compared to the previous project.

Step 3: Calculate Terminal Cash Flow

Now we can calculate the Terminal Cash Flow which is supposed to occur at the end of the completion period.

  • Then we calculate the Terminal Cash flow after the end of the calculation period.
  • For this, we need to organize the information for this.
  • The selling value of the new asset is placed in cell H12.
  • For all types of businesses, there is a fee for Rent and Rates.
  • The Rate and Rates are in cell H13.

Calculate Terminal Cash Flow to Calculate Incremental Cash Flow for Upgrading Project in Excel

  • Then select cell H14 and enter the following formula:

=H12-H13

Calculate Terminal Cash Flow to Calculate Incremental Cash Flow for Upgrading Project in Excel

Step 4: Calculate Incremental Cash Flow

Now, as we have all the necessary information, we can calculate the Incremental Cash Flow.

  • Select cell E17 and enter the following formula in the cell:

=H14+H5-D14

  • Then we got the Incremental cash flow of the whole project in cell E17.

Calculate Incremental Cash Flow to Calculate Incremental Cash Flow for Upgrading Project in Excel

  • And this is how we calculate incremental cash flow in Excel for the upgradation of a project.

Read More: Cash Flow Statement Format in Excel for Construction Company


Conclusion

To sum it up, the issue of how to calculate Incremental Cash Flow in Excel is answered here with 2 examples.

For this problem, a workbook is available for this problem where you can practice these methods.

Feel free to ask any questions or feedback through the comment section. Any suggestion for the betterment of the Exceldemy community will be highly appreciable.


Related Articles

Rubayed Razib Suprov
We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo