Accounts Payable and Receivable in Excel report

Last updated on March 19th, 2021 at 06:15 am

switch language to bahasa

Case Example:
Mr. Adi has a small shop. Usually he writes down the buy and sell transaction of his shop in a book. As now he has more customers, suppliers and products, he plans to have his accounts payable and receivable in Excel.

Because he didn’t go to an Economic Schools, he will make the Excel program according to what he thinks is fit. So he starts gathering the data needed to make the accounts payable and receivable in Excel report.

Preparation

  • A list of customer and supplier name
  • A list of product name
  • Opening balance of the accounts payable and receivable
  • Opening balance of the inventory

After all those points above collected, Mr. Adi starts to fill the Excel Worksheet with those data.

1. Make a list of customer, supplier and product names.

First, Mr. Adi name the Excel worksheet by double clicking the “Sheet1” tab, then change the name to “TABLE” by typing in that tab. He also double-click “Sheet2”, then change it to “DATA”.

Naming the worksheet as the preparation to make the accounts payable and receivable in Excel.
Worksheet naming (please ignore the sheet name)

Then on sheet TABLE, he type all of the names of the customer, supplier, and product which he gathered before in the preparation step. This list of names in the worksheet TABLE will be used when inputting the buy or sell transaction. Below is the example image:

Example of a list of customer, supplier and product names in Excel's worksheet.
A list of customer, supplier and product name example.
(please ignore the sheet name)

2. Make the Main Data

The headers for the Main Data table for the accounts payable and receivable transaction.
The headers for the Main Data table
(please ignore the header and the sheet name)

Next, Mr. Adi create a transaction table in sheet DATA, with the headers from cell B1 to cell N1 as followed:

  • DATE = the date of the transaction
  • INV = invoice number
  • NAME = customer / supplier name
  • BOOK = stock / cash
  • POST = CU for Customer, SU for Supplier
  • ITEM = product name
  • QT = item quantity
  • PRICE = price of the item
  • SUM = QT * PRICE (positive/negative)
  • QTY = item quantity (positive/negative)
  • D/C = Debit / Credit
  • Y/M = Year and Month of the invoice
  • PAID = will be filled when paid

3. Fill the Main Data with the Inventory opening balance

saldo awal item untuk laporan stok
Inventory opening balance example
(please ignore the name)

4. Fill the Main Data with the accounts payable and receivable opening balance

Contoh saldo awal untuk laporan hutang piutang di Excel
Accounts Payable opening balance example
(please ignore the name)
contoh saldo awal hutang dagang non lunas
Accounts Receivable opening balance example
(please ignore the name)

NOTE:

  • In the INV column, the invoice number has a format as follow :
    • The first two characters are CU or SU followed by a hyphen. CU- represents as a customer invoice and SU- represents as a supplier invoice.
    • The six digits after the hyphen is a date format : YYMMDD.
    • The last three digits is the number of the invoice in a day. The next day, the number will reset to 001 both for CU and SU.
  • The value of SUM and QTY column will be a negative number for the accounts payable transaction (selling to the customer) and will be a positive number for the accounts receivable transaction (buying from the supplier)
  • D/C column value is Debit for a positive value and Credit for a negative value.
  • In the Y/M column, the date will always the first date.

5. Create a Dynamic Named Range

In order the range of the customer, supplier, product name list and the Main Data dynamic, Mr. Adi create a Dynamic Named Range as follow:

Click the Name Manager button in Formulas tab menu
Click New…. in the Name Manager window

In the New Name window, leave the “Scope:” and “Comment:” as it is, while for the “Name:” and “Refers to:” copy-paste the following …

Name : Customer
Refers to : =OFFSET(TABEL!$B$2,0,0,COUNTA(TABEL!$B$2:$B$10000),1)
Click OK – Click New

Name : data
Refers to : =OFFSET(DATA!$B$1,0,0,COUNTA(DATA!$B:$B),13)
Click OK – Click New

Name : Item
Refers to : =OFFSET(TABEL!$F$2,0,0,COUNTA(TABEL!$F$2:$F$10000),1)
Click OK – Click New

Name : Supplier
Refers to : =OFFSET(TABEL!$D$2,0,0,COUNTA(TABEL!$D$2:$D$10000),1)
Click OK – Click Close

After making four Dynamic Named Range one by one, the result in the Name Manager is like in the image below:

Dynamic Named Range for the account payable, receivable and inventory report.
Example of a Dynamic Named Range in the Name Manager
(please ignore the sheet name)

So that’s the first five beginning step which Mr. Adi did to make the accounts payable and receiable in Excel. In the next page, Mr. Adi will make two Pivot Tables. One for the accounts payable/receivable report, and one for the inventory report.

If there is something is not clear regarding this topic, please don’t hesitate to ask in the Comment box. I’ll try my best to answer.

261 replies on “Accounts Payable and Receivable in Excel report”

Hey there! I just wanted to ask if you ever have any trouble with hackers?My last blog (wordpress) was hacked and I ended up losing several weeks ofhard work due to no data backup. Do you have anymethods to protect against hackers?

I do believe all of the concepts you have presented in your post. They’re really convincing and will certainly work. Nonetheless, the posts are very brief for starters. May you please extend them a bit from next time? Thank you for the post.

Thanks for your marvelous posting! I genuinely enjoyed reading it, you are a great author.I will ensure that I bookmark your blog and may come back down the road. I want to encourage you continue your great writing, have a nice holiday weekend!

Aw, this was an exceptionally good post. Spending some time and actual effort to create a good articleÖ but what can I sayÖ I put things off a whole lot and never seem to get nearly anything done.

Thanks for a marvelous posting! I certainly enjoyed reading it, you are a great author.I will alwaysbookmark your blog and definitely will come back later in life.I want to encourage continue your greatposts, have a nice evening!

Usually I don’t learn article on blogs, however I would like to say that this write-up very pressured me to take a look at and do it! Your writing style has been amazed me. Thank you, quite great article.

I will immediately grasp your rss feed as I can’t in finding your email subscription link or e-newsletter service.Do you’ve any? Please allow me understand in order that I maysubscribe. Thanks.

Thank you for your sharing. I am worried that I lack creative ideas. It is your article that makes me full of hope. Thank you. But, I have a question, can you help me?

Hmm is anyone else experiencing problems with the pictures on this blog loading?I’m trying to figure out if its a problem on myend or if it’s the blog. Any suggestions would be greatly appreciated.

Howdy just wanted to give you a quick heads up and let you know a few of the pictures aren’t loading properly.I’m not sure why but I think its a linking issue.I’ve tried it in two different browsers and both show thesame results.

Great post however , I was wanting to know if you could write a litte more on this topic? I’d be very grateful if you could elaborate a little bit further. Appreciate it!

Excellent post. I was checking continuously this blog and I’m impressed! Extremely helpful info specially the last part 🙂 I care for such information a lot. I was seeking this particular information for a very long time. Thank you and good luck.

Hey! Do you know if they make any plugins to assist with Search Engine Optimization?I’m trying to get my blog to rank for some targeted keywords butI’m not seeing very good gains. If you know of any please share.Thanks!

When someone writes an article he/she keeps the plan of a user in his/her brain that how auser can understand it. So that’s why this piece of writing is perfect.Thanks!

Thanks for any other informative blog. The place else could I get that type of information written in such a perfect way?I have a challenge that I’m just now working on, and I’ve been at thelook out for such info.

Hey! I know this is kind of off topic but I was wonderingif you knew where I could locate a captcha plugin for my comment form?I’m using the same blog platform as yours and I’m having difficulty finding one?Thanks a lot!

Generally I do not read article on blogs, but Iwish to say that this write-up very pressured me to checkout and do so! Your writing style has been surprised me.Thank you, quite nice article.

Thank you for your sharing. I am worried that I lack creative ideas. It is your article that makes me full of hope. Thank you. But, I have a question, can you help me?

Great post. I used to be checking continuously this blog and I am impressed!
Very helpful info specifically the remaining section :
) I maintain such info a lot. I was seeking this particular information for a long time.
Thanks and good luck.

Hey there! I’ve been following your website for a while now and
finally got the courage to go ahead and give you a shout out from
Lubbock Tx! Just wanted to tell you keep up the good job!

Just wish to say your article is as astounding. The clarity in your publish is
just nice and i can suppose you’re an expert on this subject.
Well with your permission allow me to clutch your
RSS feed to stay up to date with forthcoming post.
Thanks a million and please carry on the enjoyable work.

Leave a Reply

Your email address will not be published. Required fields are marked *