How can I use Excel to monitor my payable?

Question

Here's what I wanted to happen, I have five columns

Supplier    Amount    Payment   Supplier1   Supplier2

When I have a receipt of my payable, I'll encode the name of supplier and it's amount. When I did that, I want the amount of that receipt to appear on the column of that supplier. Example: I encoded Supplier1 and 12,000; I want 12,000 to appear under the Supplier1 column.

And when I do a payment, same with the scenario above but the amount that will appear on the column of that supplier is in negative format so I can determine the balance of my payable on that supplier. Example: Supplier1 10,000; I want (10,000) to appear under Supplier1 column.

Can I do this format on Excel? If yes, how?

Answer 1

Sounds like a job for Pivot Table!

^{(part of my "Sounds Like a Job For Pivot Table!" series)}

---

With your data, highlight it, go to insert - pivot table

click for larger

Then in pivot table tools go to options - formulas - calculated field

click for larger

Name it whatever you want, but set it up so it's invoice - payment

Now you can construct the pivot table. I'd go with Supplier as row label and then drag invoice and payment into the values box, which will create a sum of values in column labels. Now drag your calculated field down to the values box. Bam! You got yourself a stew going!

click for larger