• Microsoft Office
  • Microsoft Windows
  • Other Software
    • Microsoft Visual
    • Microsoft Project
    • Microsoft Visio
  • Anti Virus
  • Blog
    • Word
    • Excel
    • Powerpoint
    • Software tricks/tips
  • POLICIES
    • PAYMENT GUIDE
    • SHIPPING POLICY
    • REFUND POLICY
    • TERMS & CONDITIONS
    • Contact Us

No products in the cart.

  • Microsoft Office
  • Microsoft Windows
  • Other Software
    • Microsoft Visual
    • Microsoft Project
    • Microsoft Visio
  • Anti Virus
  • Blog
    • Word
    • Excel
    • Powerpoint
    • Software tricks/tips
  • POLICIES
    • PAYMENT GUIDE
    • SHIPPING POLICY
    • REFUND POLICY
    • TERMS & CONDITIONS
    • Contact Us

No products in the cart.

  • Microsoft Office
  • Microsoft Windows
  • Other Software
    • Microsoft Visual
    • Microsoft Project
    • Microsoft Visio
  • Anti Virus
  • Blog
    • Word
    • Excel
    • Powerpoint
    • Software tricks/tips
  • POLICIES
    • PAYMENT GUIDE
    • SHIPPING POLICY
    • REFUND POLICY
    • TERMS & CONDITIONS
    • Contact Us

No products in the cart.

  • Microsoft Office
  • Microsoft Windows
  • Other Software
    • Microsoft Visual
    • Microsoft Project
    • Microsoft Visio
  • Anti Virus
  • Blog
    • Word
    • Excel
    • Powerpoint
    • Software tricks/tips
  • POLICIES
    • PAYMENT GUIDE
    • SHIPPING POLICY
    • REFUND POLICY
    • TERMS & CONDITIONS
    • Contact Us
Excel

Steps to create a summary table of customer accounts receivable in Excel

0 Comments

The accounts receivable summary sheet must always be monitored and regularly updated in a business to help managers accurately track the collection of debts. In this article, Buffcom.net will guide readers through the steps to create an accounts receivable summary sheet for customers in Excel.

Step 1: DMKH Sheet

Here, you enter basic information about customers such as code, name, address, tax code, bank account, phone number, beginning balance, and beginning credit balance.

Steps to create a summary table of customer accounts receivable in Excel

Steps to create a summary table of customer accounts receivable in Excel

Step 2: DATA Sheet

Here, you enter the transactions that occur during the period. The information you need to enter includes the date, reference number, description, amount, debit account, credit account, and customer.

Steps to create a summary table of customer accounts receivable in Excel

Steps to create a summary table of customer accounts receivable in Excel

Step 3: THPTKH Sheet

Here, you will summarize data from the DMKH sheet to obtain the beginning balance, and retrieve data from the DATA sheet to obtain the transactions occurred during the period. Then, add the beginning balance and the transactions to get the ending balance.

  • In the Debit opening balance column, enter the following formula:
    =MAX(VLOOKUP(B11, DMKH!$A$3:$H$6, 7, 0) – VLOOKUP(B11, DMKH!$A$3:$H$6, 8, 0) + SUMIFS(SO_TIEN, CT_NO, B11, NGAY_GS, “<” & $F$6) – SUMIFS(SO_TIEN, CT_CO, B11, NGAY_GS, “<” & $F$6), 0)
  • In the Credit opening balance column, enter the following formula:
    =-MIN(VLOOKUP(B11, DMKH!$A$3:$H$6, 7, 0) – VLOOKUP(B11, DMKH!$A$3:$H$6, 8, 0) + SUMIFS(SO_TIEN, CT_NO, B11, NGAY_GS, “<” & $F$6) – SUMIFS(SO_TIEN, CT_CO, B11, NGAY_GS, “<” & $F$6), 0)
  • In the Debit transactions column, enter the following formula:
    =SUMIFS(SO_TIEN, CT_NO, B11, NGAY_GS, “>=” & $F$6, NGAY_GS, “<=” & $F$7)
  • In the Credit transactions column, enter the following formula:
    =SUMIFS(SO_TIEN, CT_CO, B11, NGAY_GS, “>=” & $F$6, NGAY_GS, “<=” & $F$7)
  • In the Debit closing balance column, enter the following formula:
    =MAX(D11 + F11 – E11 – G11, 0)
  • In the Credit closing balance column, enter the following formula:
    =-MIN(D11 + F11 – E11 – G11, 0)
Steps to create a summary table of customer accounts receivable in Excel

Steps to create a summary table of customer accounts receivable in Excel

Step 4: Sheet SCT_131

Here, we will create a detailed ledger for each object. We will consolidate data from the DMKH sheet to obtain the beginning balance and data from the DATA sheet to obtain the incurred debit and credit amounts for the period. Then, we will add the beginning balance and the amounts incurred during the period to obtain the ending balance of the object.

  • In the Beginning Debit column, you will enter the following formula:
    =MAX(VLOOKUP(G10;DMKH!A3:H6;7;0) + SUMIFS(DATA!E3:E251;DATA!A3:A251;”<“&G8;DATA!H3:H251;G10)-
    VLOOKUP(G10;DMKH!A3:H6;8;0) – SUMIFS(DATA!E3:E251;DATA!A3:A251;”<“&G8;DATA!G3:G251;G10);0)
  • In the Beginning Credit column, you will enter the following formula:
    =-MIN(VLOOKUP(G10;DMKH!A3:H6;7;0) + SUMIFS(DATA!E3:E251;DATA!A3:A251;”<“&G8;DATA!H3:H251;G10)- VLOOKUP(G10;DMKH!A3:H6;8;0) – SUMIFS(DATA!E3:E251;DATA!A3:A251;”<“&G8;DATA!G3:G251;G10);0)
  • In the Incurred Debit column, you will enter the following formula:
    =IF(D227=””;0;IF(DATA!H213=$G$10;DATA!E213;0))
  • In the Incurred Credit column, you will enter the following formula:
    =IF(D227=””;0;IF(DATA!I213=$G$10;DATA!E213;0))
  • In the Ending Debit column, you will enter the following formula:
    =MAX(I15+G275-J15 – H275;0)
  • In the Ending Credit column, you will enter the following formula:
    =-MIN(I15+G275-J15 – H275;0)

So the above article has provided guidance on how to create a summary table of accounts receivable from customers on Excel. Hopefully, the article will be useful to you in your work. Good luck!

 

Rate this post
27
304 Views
Guide readers on 3 ways to enter formulas in ExcelPrevGuide readers on 3 ways to enter formulas in ExcelMarch 3, 2023
How to Rotate Text in WordMarch 4, 2023How to Rotate Text in WordNext

Leave a Reply Cancel reply

You must be logged in to post a comment.

Buy Windows 11 Professional MS Products CD Key
Buy Office 2021 Professional Plus Key Global For 5 PC
Top rated products
  • Trend Micro Internet Security 3 Devices 1 Year Key GLOBAL Trend Micro Internet Security 3 Devices 1 Year Key GLOBAL
    Rated 5.00 out of 5
    $17.00
  • Windows 11 Pro Key Global Windows 11 Pro Key Global
    Rated 5.00 out of 5
    $6.00
  • AVG Internet Security 2021 10 Devices 2 Years Global AVG Internet Security 2021 10 Devices 2 Years Global
    Rated 5.00 out of 5
    $42.20
  • Trend Micro Maximum Security 3 Devices 1 Year key Global Trend Micro Maximum Security 3 Devices 1 Year key Global
    Rated 5.00 out of 5
    $15.00
  • Windows 11 Home Key Global 2021 Windows 11 Home Key Global 2021
    Rated 5.00 out of 5
    $6.00
Products
  • AVG Internet Security 2021 10 Devices 1 Year Global AVG Internet Security 2021 10 Devices 1 Year Global
    Rated 5.00 out of 5
    $30.00
  • Windows 11 Pro Key Global Windows 11 Pro Key Global
    Rated 5.00 out of 5
    $6.00
  • Windows 10 Home OEM Key Global Windows 10 Home OEM Key Global
    Rated 4.80 out of 5
    $6.00
  • Avast Ultimate Suite 2021 1 Year 10 Devices Global Avast Ultimate Suite 2021 1 Year 10 Devices Global
    Rated 5.00 out of 5
    $54.00
  • Microsoft Visio Professional 2016 Key 1PC Microsoft Visio Professional 2016 Key 1PC $9.00
  • Microsoft Visio Professional 2010 Key 1PC Microsoft Visio Professional 2010 Key 1PC $9.00
  • Windows Server2008 R2 Remote Desktop Services device connections (20)cal Windows Server2008 R2 Remote Desktop Services device connections (20)cal $29.00
  • Windows Server 2016 Essentials Key Global Windows Server 2016 Essentials Key Global
    Rated 4.79 out of 5
    $10.00
  • Microsoft Visual Studio Enterprise 2019 For 1 PC Microsoft Visual Studio Enterprise 2019 For 1 PC
    Rated 4.88 out of 5
    $15.00
  • Microsoft Visio 2016 professional CD Key Global Microsoft Visio 2016 professional CD Key Global
    Rated 4.94 out of 5
    $28.50
Product categories
  • Anti Virus
  • Microsoft Office
  • Microsoft Project
  • Microsoft Visio
  • Microsoft Visual
  • Microsoft Windows
  • Other Software
  • Uncategorized

Buffcom.net always brings the best digital products and services to you. Specializing in Office Software and online marketing services

BIG SALE 50% IN MAY

Microsoft Office
Microsoft Windows
Anti-Virus
Contact Us

Visit Us:

125 Division St, New York, NY 10002, USA

Mail Us:

buffcom.net@gmail.com

TERMS & CONDITIONS | PAYMENT GUIDE  | SHIPPING POLICY  | REFUND POLICY

Copyright © 2019 buffcom.net  All Rights Reserved.