Average Collection Period Ratio and QuickBooks 2013
Debt Ratio in QuickBooks 2013
How to Calculate the Return Measure in QuickBooks 2013

How to Estimate the Net Cash Flows in QuickBooks 2013

The process of estimating the net cash flows from the investment requires a bit more work than estimating the amount that you need to invest. Although you're working with QuickBooks 2013, you'll need the help of Excel or another financial spreadsheet.

Sit down and think carefully about any additional revenues and any additional costs that the investment produces. Obviously, you hope that the net effect of the investment will save you cash. However, certain amounts of the investment will cost you. On the other hand, you also receive savings that the investment returns.

You want to construct a little schedule — this can be written on the back of a cocktail napkin or typed into a spreadsheet program like Excel — and use it to carefully estimate and calculate cash flows.

Suppose, in the case of the office building, that the following two items determine the net cash flows.

  • The new mortgage requires an annual $21,000 interest payment. To keep things simple (don’t worry about principal amortization just yet), suppose that this is an interest-only mortgage. Further suppose that you need to pay the entire mortgage balance in 20 years as part of a balloon payment. In the meantime, however, you’ll pay $21,000 at the end of every year.

  • Because you own your own building, you save $20,000 in rent the first year. This amount, however, increases every year. If the rent that you’ve been paying has increased every year by 3 percent because of inflation, you may want to assume that your rent savings, in order to be accurately forecasted, should be inflated by 3 percent every year as well.

    For example, you may want to assume that in the second year, your rent savings equal 103 percent of $20,000. In the third year, your rent savings equal 103 percent of $20,600 (which is the second year’s rent savings).

Does this business of rent savings make sense? With capital expenditure investment, the capital investment often saves you money in some way. Therefore, you need to estimate those savings over the years that you’ll use the capital investment.

In this case, the rent savings should be equal to the current rent savings plus inflation for each year. Another way to look at the rent savings amount is to say that the rent savings equals the rent that you won’t have to pay if you own the building.

The following table summarizes the cash flows that you enjoy by investing in this building. The table has a column for each year number. (The schedule shows 20 years of rent savings and mortgage payments.)

The schedule also includes three columns, which report on the rent savings, the annual mortgage interest payment, and the net cash flow amount. The net cash flow amount equals the rent savings minus the mortgage interest payment.

Notice that in the first two years, the mortgage interest payment exceeds the rent savings. However, in year 3 and beyond, the rent savings exceeds the mortgage payment.

Summary of Building Cash Flows
Year Rent Savings Mortgage Payment Net Cash Flows
1 20,000 21,000 –1,000
2 20,600 21,000 –400
3 21,218 21,000 218
4 21,855 21,000 855
5 22,511 21,000 1,511
6 23,186 21,000 2,186
7 23,882 21,000 2,882
8 24,598 21,000 3,598
9 25,336 21,000 4,336
10 26,096 21,000 5,096
11 26,879 21,000 5,879
12 27,685 21,000 6,685
13 28,516 21,000 7,516
14 29,371 21,000 8,371
15 30,252 21,000 9,252
16 31,160 21,000 10,160
17 32,095 21,000 11,095
18 33,058 21,000 12,058
19 34,050 21,000 13,050
20 35,072 21,000 14,072

When you look at the cash flows that stem from a capital investment, you need to make some assumption about what happens at the end of the investment’s life. In the case of the building investment, for example, you probably need to show that the mortgage is paid off. You also may want to show the sale of the building at some point.

To show you how this works, suppose that at the end of year 20, you pay off the mortgage (which will still be $300,000 because you have only been paying interest), and suppose that you sell the building for $630,000. This amount is an estimate.

To come up with this estimate, take the original $350,000 purchase price and then annually inflate that amount by 3 percent over 20 years. Doing so produces an estimated sale price in year 20 of $630,000. You’ll also pay selling costs that total $30,000.

This next table shows how these numbers produce a final, liquidation cash flow. The gross sales price equals $630,000. Then you have to pay the $300,000 mortgage. Then you also have $30,000 of selling costs.

If you subtract the mortgage and the selling costs from the gross sales price, the final cash flow, then, equals $300,000. This makes sense, right? The gross sales price of $630,000 minus $300,000 for the mortgage payment minus $30,000 for selling costs equals $300,000.

Estimating the Liquidation Cash Flow
Gross sale price $630,000
Less: Mortgage 300,000
Less: Selling costs 30,000
Final cash flow from sale $300,000

The final step is to combine the information shown. The net cash flows column summarizes the net cash flows. The liquidation cash flow column shows 0 during the first 19 years. Then in year 20, however, the liquidation cash flow shows as $300,000. The real deal combines the net cash flows and the liquidation cash flow.

Combining All Cash Flows
Year Net Cash Flows Liquidation Cash Flow The Real Deal
1 –1,000 0 –1,000
2 –400 0 –400
3 218 0 218
4 855 0 855
5 1,511 0 1,511
6 2,186 0 2,186
7 2,882 0 2,882
8 3,598 0 3,598
9 4,336 0 4,336
10 5,096 0 5,096
11 5,879 0 5,879
12 6,685 0 6,685
13 7,516 0 7,516
14 8,371 0 8,371
15 9,252 0 9,252
16 10,160 0 10,160
17 11,095 0 11,095
18 12,058 0 12,058
19 13,050 0 13,050
20 14,072 300,000 314,072
  • Add a Comment
  • Print
  • Share
blog comments powered by Disqus
How to Use the Accountant's Copy of the QuickBooks 2013 Data File
Days of Inventory Turnover Ratio and QuickBooks 2013
Use the Filters Tab to Modify QuickBooks 2013 Reports
How Profit-Volume-Cost Analysis Works in QuickBooks 2013
The QuickBooks 2013 Decision Tools

Inside Dummies.com