Pivot Table for Accounts Payable/Receivable and Inventory

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

5. Design the Pivot Table

Because Mr. Adi doesn’t like the Compact Form report layout, so he change it into a Tabular Form report layout by clicking the Report Layout menu icon, then choose “Show in Tabular Form”. As seen in the image below.

Below is the image of the Pivot Table in a Tabular Form report layout.

Within the Design menu tab, the PivotTable Style Options is optional, it depends on how is the look of the PivotTable we want. Anyway in this case, Mr. Adi only check the “Column Headers” and leave the others unchecked. As seen in the image below.

The coloring of the Pivot Table is also optional. Excel already provided quite many styles template which can be accessed by clicking the down-arrow button as seen in the image below.

Again, it all depends on how we want to see the visual style of the Pivot Table. In this case, Mr. Adi choose Pivot Style Medium 5, as seen in the image below.

In order the Pivot Table header still visible when he scroll down the page, Mr. Adi freeze the first four rows at the top. Here are the steps : while the View menu tab active, click cell A4, choose Freeze Panes, then choose the Freeze Panes. As seen in the image below.

From the image below, we can see the Pivot Table’s header stay on top, although the page is already scrolled down to row 94.

Based from the image above, the Pivot Table is like this :
In the row 1, the POST field filter is “(All)”. Meaning the Pivot Table reports both the accounts payable and the accounts receivable name. To see just the accounts payable report, filter the POST field to CU by clicking the down-arrow of the POST field, then select the CU item there. The same, to see just the accounts receivable report, filter the POST field to SU.

So in reading the example image of the Pivot Table report above:
Adelia Priatni is Mr. Adi’s customer who owe him a total amount of money 11.390.900. It’s from her February 2020 purchase (consist with two invoices), where the subtotal for Feb 2020 is 3.583.700 and it’s from her March 2020 purchase (consist with six invoices), where the subtotal for Mar 2020 is 7.807.200.

Create the Pivot Table for Inventory

Finally, to complete his Pivot Table for Accounts Payable/Receivable and Inventory creation, now it’s time to make the Pivot Table for his Inventory. Just copy the ptCUSU Pivot Table in CUSU sheet then paste it into cell A1 of sheet STOCK. Here are the process.

Within the CUSU sheet and the Options menu tab active, click Actions, click Select, then choose “Entire PivotTable”. As seen in the image above. Ctrl+c (copy keyboard shortcut) on the keyboard, then activate the STOCK sheet. Make sure cell A1 is active in STOCK sheet, then ctrl+v (paste keyboard shortcut).Next, Mr. Adi name the Pivot Table as ptStock. And then he arrange the column fields as follow :

  • Drag POST then drop it in the Column Labels
  • Drag ITEM then drop it in the Row Labels
  • Drag QTY then drop it in the Values
  • Drag Y/M then drop it in the Report Filter

Set and format the ptStock column fields as needed, then style it as we like. The Pivot Table will look something like the sample image below.

In the attachment Excel sample file, the Pivot Table’s filter is Y/M and the headers are : ITEM, xOB (opening balance, positive value), SU (the item quantity bought, positive value), and CU (the item quantity sold, negative value).

The reading of the Pivot Table from the image above, for example :
In January 2021 (Y/M filter field), Arizona – Green Tea product opening balance is 3.01 (xOB field). During Jan 2021, Mr. Adi has bought this item 7.40 (SU field) and has sold it 4.28 (CU field). The balance of this product now is 6.13 —> (3.01 + 7.40 – 4.28).

That’s it. Now Mr. Adi has completed his Pivot Table for Accounts Payable/Receivable and Inventory creation. In the next topic, Mr. Adi will make a worksheet form application to input the sale and purchase transaction to the Main Data.

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

381 replies on “Pivot Table for Accounts Payable/Receivable and Inventory”

Hey There. I discovered your blog the usage of msn.This is a very neatly written article. I will make sure to bookmark it and return to readmore of your helpful information. Thank you for the post.I will certainly comeback.

Hi there just wanted to give you a quick heads up and let you know afew of the images aren’t loading properly. I’m not sure why but I thinkits a linking issue. I’ve tried it in two different browsers and both showthe same results.

I am not sure where you’re getting your information, but great topic.I needs to spend some time learning much more or understanding more.Thanks for excellent info I was looking for this info for my mission.

I’m really impressed with your writing talents and also with the format on your blog.Is this a paid theme or did you customize it your self?Anyway stay up the excellent high quality writing, it is uncommon to seea great blog like this one nowadays..

OBJECTIVE: To assess Danish obstetricians’ and gynecologists’ personal preference and general attitude towards elective cesarean section on maternal request in uncomplicated single cephalic pregnancies at term.link source

A fascinating discussion is worth comment. There’s no doubt that that you should write more on this subject matter, it may not be a taboo subject but generally folks don’t discuss such issues. To the next! All the best!!

The next time I read a blog, I hope that it doesnt disappoint me as much as this one. I mean, I know it was my option to read, but I truly thought youd have one thing interesting to say. All I hear is a bunch of whining about something that you possibly can fix when you werent too busy searching for attention.

Can I simply say what a reduction to seek out somebody who actually is aware of what theyre speaking about on the internet. You undoubtedly know the best way to convey a difficulty to light and make it important. Extra people need to read this and perceive this aspect of the story. I cant consider youre not more widespread because you positively have the gift.

I’m really enjoying the design and layout of your site. It’s a very easy on the eyes which makes it much more pleasant for me to come here and visit more often. Did you hire out a developer to create your theme? Superb work!

An attention-grabbing dialogue is price comment. I believe that you should write extra on this matter, it won’t be a taboo topic but typically individuals are not sufficient to speak on such topics. To the next. Cheers

of course like your web site however you need to check the spelling on several of your posts. Several of them are rife with spelling issues and I in finding it very bothersome to tell the truth then again I’ll definitely come again again.

Simply desire to say your article is as astonishing. The clarity in your post is simply excellent and i could assume you’re an expert on this subject. Well with your permission let me to grab your RSS feed to keep updated with forthcoming post. Thanks a million and please keep up the gratifying work.

I just couldn’t go away your website before suggesting that I extremely loved the standard information a person supply to your guests? Is gonna be again incessantly in order to check up on new posts

Coi Trực Tiếp Viettel Vs Bình Dương Trên V League 2021 Ở Kênh Nào? tcvn 104-07Đội tuyển chọn nước Việt Nam chỉ cần một kết trái hòa có bàn thắng nhằm lần thứ hai góp mặt tại World Cup futsal. Nhưng, để thực hiện được điều đó

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?

I am really impressed with your writing skills andalso with the layout on your blog. Is thisa paid theme or did you modify it yourself? Eitherway keep up the excellent quality writing, it’s rare to see a nice blog like this one today.

Hi! I just wanted to ask if you ever have any issues with hackers? My last blog (wordpress) was hacked and I ended up losing months of hard work due to no back up. Do you have any methods to prevent hackers?

Aw, this was a very nice post. Taking a few minutes and actual effort to produce a really good articleÖ but what can I sayÖ I put things off a whole lot and never manage to get nearly anything done.

I am not sure where you’re getting your info, but great topic. I needs to spend some time learning much more or understanding more. Thanks for magnificent information I was looking for this information for my mission.

Hey there! This is my first visit to your blog! We are a group of volunteersand starting a new project in a community in the same niche.Your blog provided us beneficial information towork on. You have done a outstanding job!

I like the valuable information you supply for your articles.
I will bookmark your blog and check again here frequently.
I am reasonably sure I’ll be informed lots of new stuff
proper right here! Best of luck for the following!

Hello there! This post couldn’t be written much better! Reading through this post reminds me of my previous roommate! He constantly kept talking about this. I am going to send this post to him. Fairly certain he’ll have a very good read. I appreciate you for sharing!

Its like you learn my mind! You seem to grasp a lot about this, such as you wrote the guide in it or something. I think that you simply could do with a few percent to power the message home a little bit, but other than that, this is excellent blog. An excellent read. I will certainly be back.

Nhà Cửa Dòng Xem Thẳng Soccer Bdtt Tv xem bong da trực tiepTrong vượt khứ, Bình Dương đang được giật ưu thế về đo đếm cùng với 15 thắng lợi, 8 trận hoà và chỉ thua SLNA 12 lần.

I blog frequently and I genuinely thank you for your content.This article has really peaked my interest. I will bookmark your blog and keep checking for new information aboutonce per week. I opted in for your Feed as well.

Have you ever considered publishing an ebook or guest authoring on other sites? I have a blog based on the same topics you discuss and would really like to have you share some stories/information. I know my audience would value your work. If you are even remotely interested, feel free to shoot me an e mail.

You can certainly see your skills within the article you write.The arena hopes for more passionate writers such as youwho are not afraid to say how they believe. At alltimes follow your heart.

I’m curious to find out what blog platform you’re working with?I’m experiencing some small security issues with my latest blogand I’d like to find something more risk-free. Do youhave any recommendations?

Hello! This is my first visit to your blog! We are a team of volunteersand starting a new project in a community in the same niche.Your blog provided us valuable information to work on. You have done amarvellous job!

เวฟนี้ดีจริงไม่ผ่านเอเย่นต์ ทุนหนา จ่ายไวไม่อยากให้เชื่อ แต่อยากให้ลอง

Leave a Reply

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