Develop a Cash Flow Statement

If you want to finance your major purchase or project with a bank loan, your lender is likely to want to see a cash flow budget showing the effect of the project on your revenues, and proving that you can make the anticipated loan payments.

Even if you're not financing the purchase, you should consider creating such a budget (or, more likely, having your accountant do it for you). It's a way of systematically comparing the costs and financial benefits of your project over a period of time, and will enable you to get a good handle on how the project will affect your business. If done correctly for each project you consider, cash flow budgets should also point out projects that are financially unfeasible or only marginally feasible, thus saving you the trouble of finding that out the hard way.

Your cash flow projection should show estimated cash inflows and outflows for the project, by month, for at least the first year. As a starting point you can use the cash flow projections you've already done for your business, simply adding in the changes that you expect the project to bring. Then you can compare your original statement (without the project) to your new statement (with the project), to gauge the likely results of moving forward with your plans.

 
Business Tools

Included among the Business Tools is a cash flow budget worksheet. The worksheet is an Excel 4.0 template that can be used in Excel 4.0 or higher. Because it's a template, you can use the worksheet over and over again and still retain an original copy of it.

The worksheet is set up to be used for projecting your cash flow for six months at a time. We've formatted the worksheet and put in most of the cash inflow and outflow categories for you. All you have to do is put in your numbers and print it.

Once you've downloaded the worksheet, feel free to modify it to fit your own needs. Enjoy!

Ideally, you would also do a simplified projection that extends for the length of the asset's useful life, or at least for the length of the loan or lease used to finance it. You might also like to project your cash flow out to the date when the project's costs will be paid back by the benefits it generates.

Recognize, however, that the farther out in time you go, the less certain your figures will be, because of the increased chances that there will be unexpected changes in interest rates, technological developments, consumer tastes and habits, or other factors that can affect your business.

At this point, your simplified, long-range cash flow projection for the project should include only those inflows and outflows that are directly related to the project itself. Don't include overhead costs that you would have regardless of whether you did the project or not.

Example of a simplified cash flow projection. For example, let's say that you are thinking of purchasing a new machine that will allow you to offer a new product to your customers. The machine will cost $100,000 to purchase and install, and after five years (when you plan to sell it) the machine will be worth about $10,000. Your facility has plenty of room, so you won't have any additional rental costs for space, and you can piggyback advertising for the new product on to your existing advertising budget. You will, however, have to pay for insurance, personal property taxes, and a part-time employee to operate the machinery (these items are included in your fixed costs which will total $12,000 in the first year). Also, there will be costs for materials, supplies, and electricity that will vary depending on the volume of production. These variable costs will amount to about 60 percent of the sales revenues.

The following is a simplified example of a projected cash flow statement for the project:

 

Current Year 1 Year 2 Year 3 Year 4 Year 5
Price/Unit
$80 $84 $88 $93 $97
Multiplied by:
Units Sold

1000 1150 1323 1521 1749
Net Sales
$80,000 $96,600 $116,424 $141,453 $169,653
Variable Costs
$48,000 $57,960 $69,854 $84,872 $101,792
Fixed Costs
$12,000 $12,600 $13,230 $13,892 $14,586
Depreciation
$14,290 $24,490 $17,490 $12,490 $ 8,930
Gain/Loss - Equip. Sale




($12,310)
Pre-tax Income
$ 5,710 $ 1,550 $15,850 $29,591 $32,034
Tax Expense
$ 1,941 $527 $5,389 $10,060 $10,892
Net Income
$3,769 $1,023 $10,461 $19,531 $21,142
Adjustments
Add Back Depreciation
$14,290 $24,490 $17,490 $12,490 $ 8,930
Asset Purchase Salvage Value $100,000



$10,000
Net Cash Flow ($100,000) $18,059 $25,513 $27,951 $32,021 $40,072

The table makes a number of assumptions:

  • The average price of the product will increase by 5 percent a year, while the volume sold will increase by 15 percent a year.
  • Depreciation is computed using the IRS's tables for 7-year property, using the half-year convention under MACRS. Tax depreciation is used because it affects the outflow of cash in the form of tax payments.
  • Fixed costs will increase by an inflation factor of 5 percent a year.
  • The tax rate is calculated at 34 percent.

Once you've created a projected cash flow statement for your project, you can use some financial analysis tools to see whether the project makes sense for your business.