From Scratch - Tracking Payables

[cindyj]

I am using Excel 2003 - I receive all of our payables here and then approve
and code them with amounts to distribute to expense accounts to be sent to
our corporate office for payment. I need to create a workbook that allows me
to track payables by multiple criteria.


Example Payable
Joe's Hardward Invoice Date 07/01/09 Invoice #12345 Invoice Total=$100
Distribution of Expenses;
Acct 2450(Hardware)=$50
Acct 2475(Auto Expense)=$50


right now I am doing double entry - one workbook that tracks individual
batches of payables sent - lists vendor, date, invoice # & invoice total. In
this one, Joe's hardware would be on one line, easy entry.

In the other workbook I enter each payable but broken by account - On this
one, Joe's Hardware would be on two lines (one for each internal account) but
with no way to reference the actual invoice total - this leaves room for
error if one account is left off or if distribution of the invoice total into
accounts is incorrect. This workbook then flows the data from the
"transactions" sheet to my summary sheets that provide totals by vendor and
by expense account.

How can I mesh these two entries to create a single entry method that tracks
batch totals as well as breaks down expense distribution???

 

[KC hotmail com>]

I'd set up all your expense accounts to the right of what you have, preceded
by a column which tells you the balance remaining to be allocated.

So row 1, starting in A, would have
Payable
Invoice Date
Invoice #
Invoice Total
Unassigned
2450 (Hardware)
2475 (Auto Expense)
....and your other expense accounts, in whatever order makes most sense for
you.

Your "Unassigned" column lets you know if your total expense entries under
each category balance with the invoice total.

I'd freeze pane at E2 to keep everything up to "Unassigned" in view as you
scroll to the right to the expense accounts you want.

Then you an pretty easily pull data from the desired expense column for any
given date range, payable account, invoice #, etc.