Calculating Cash Flow Using Excel Data Lists

Short Description
Because the OFFSET formula uses relative references, Excel modifies the formula to. reflect the formula’s new position. Why Does the OFFSET Function Work? …

Website: www.thatexcelguy.com | Filesize: 241kb

Content
Calculating Cash Flow Using Excel Data Lists
By: Curtis D. Frye
7/26/2006
Bank and credit card statements tell you where your money has gone, but they don’t
show you how much money you’ll have on hand after your checks clear. You can use
your checkbook’s register to calculate those totals by hand, but it’s much easier to let
Excel do the math for you.
There is a trick to creating formulas that allow you to delete rows without causing errors
in your worksheet. This technique, which uses the OFFSET function, avoids problems
associated with formulas that reference cells directly.
In this article, I’ll show you how to create a data list to manage your upcoming
transactions, add and delete rows from the list, and accurately calculate a running balance
using the OFFSET function.
Managing Excel Data Lists
I prefer to manage my cash flow worksheet using an Excel 2003 data list because of the
list’s built-in data entry, sorting, and filtering capabilities. You can also project your
future cash flow in Excel 2002 and prior versions, or in Excel 2003 should you prefer not
to organize your data as a list.
Creating a Data List in Excel…