วิธีสร้างรายงานวิเคราะห์อายุลูกหนี้การค้าใน Excel (30, 60, 90 วัน)

รายงานวิเคราะห์อายุหนี้ (Aging Report) จะจัดกลุ่มใบแจ้งหนี้ที่ยังไม่ได้ชำระเงินออกเป็นช่วงๆ ตามระยะเวลาที่เกินกำหนดชำระ โดยทั่วไปคือ 0–30, 31–60, 61–90 และมากกว่า 90 วัน การตัดสินใจสองเรื่องจะเป็นตัวกำหนดว่ารายงานของคุณถูกต้องหรือไม่ เรื่องแรกคือคุณจะนับอายุหนี้จากวันครบกำหนดชำระหรือวันที่ในใบแจ้งหนี้ เรื่องที่สองคือใบแจ้งหนี้ที่ชำระเงินแล้วบางส่วนจะแสดงยอดเงินเต็มจำนวนหรือยอดคงเหลือที่ค้างชำระ

หากตัดสินใจสองเรื่องนี้ผิด ยอดรวมของแต่ละช่วงอายุก็จะผิดทั้งหมด ซึ่งแย่ยิ่งกว่าการไม่มีรายงานเลยเสียอีก

คู่มือนี้จะอธิบายว่าทำไมการสร้างรายงานจึงมักมีปัญหา แนวทางสามแบบที่ผู้คนนิยมใช้ และจุดที่แต่ละแนวทางเริ่มใช้ไม่ได้ผล นี่คือกระบวนการทำงานของข้อมูล ไม่ใช่คำแนะนำทางบัญชี ดังนั้นโปรดยืนยันวิธีการจัดการกับผู้ที่ดูแลสมุดบัญชีแยกประเภทของคุณ

ทำไมรายงานวิเคราะห์อายุหนี้ถึงทำให้สเปรดชีตพัง

ปัญหาแรกคือเรื่องของวันที่ การนับอายุหนี้จาก วันที่ในใบแจ้งหนี้ จะบอกคุณว่าเอกสารนั้นเก่าแค่ไหน ส่วนการนับอายุหนี้จาก วันครบกำหนดชำระ จะบอกคุณว่าลูกค้าชำระเงินล่าช้าไปนานเท่าใด และสำหรับการติดตามหนี้ ตัวเลขนี้คือตัวเลขที่คุณต้องการ

ทั้งสองวิธีต่างมีเหตุผลรองรับและให้รายงานที่แตกต่างกัน ปัญหาที่มักเกิดขึ้นคือไม่มีใครบันทึกไว้ในสเปรดชีตว่าใช้วิธีใด

ปัญหาที่สองคือการชำระเงินบางส่วน ใบแจ้งหนี้ยอด $10,000 ที่ได้รับชำระมาแล้ว $7,000 จะเหลือยอดลูกหนี้การค้า $3,000 และยอด $3,000 นี้จะต้องแสดงอยู่ในช่วงอายุหนี้ช่วงใดช่วงหนึ่งเท่านั้น รายงานวิเคราะห์อายุหนี้ที่สร้างขึ้นจากรายการใบแจ้งหนี้ทั้งหมด แทนที่จะสร้างจากรายการหนี้ที่ยังค้างชำระ (open-items list) จะทำให้ยอดรวมทุกอย่างสูงเกินจริงโดยที่คุณไม่รู้ตัว

ปัญหาที่สามคือรายงานนี้เป็นข้อมูล ณ จุดเวลาใดเวลาหนึ่ง (snapshot) ช่วงอายุหนี้จะถูกคำนวณเทียบกับวันปัจจุบัน ดังนั้นไฟล์ของเมื่อวานจึงล้าสมัยไปแล้ว และการสร้างรายงานใหม่ทุกครั้งจะต้องคำนวณใหม่ทุกแถว

นอกจากนี้ยังมีแถวข้อมูลที่จัดการยาก เช่น ใบลดหนี้ (credit notes) การชำระเงินล่วงหน้า ใบแจ้งหนี้ที่มีข้อพิพาท และยอดคงเหลือหลายสกุลเงิน ซึ่งแต่ละรายการจำเป็นต้องมีกฎเกณฑ์ในการจัดการ และกฎแต่ละข้อจะต้องไม่ถูกทำลายโดยคนถัดไปที่เปิดไฟล์นี้ขึ้นมาทำงาน

ปัญหาเหล่านี้ไม่ใช่เรื่องยากหากพิจารณาแยกกัน แต่มันยากเพราะมันประดังเข้ามาพร้อมกันเดือนละครั้ง ภายใต้กำหนดเวลาที่กระชั้นชิด

สิ่งที่คุณต้องสูญเสียจากปัญหานี้

รายการติดตามหนี้ที่นำไปใช้จริงไม่ได้ จุดประสงค์ของการจัดกลุ่มอายุหนี้คือเพื่อให้รู้ว่าควรโทรหาใครก่อน รายงานที่แสดงยอดคงเหลือสูงเกินจริงจะทำให้พนักงานเสียเวลาไปทวงถามเงินที่ได้รับชำระมาแล้ว

ต้องทำงานซ้ำเดิมทุกๆ เดือน เนื่องจากช่วงอายุหนี้จะแปรผันตามวันปัจจุบัน รายงานวิเคราะห์อายุหนี้จึงไม่มีวันสิ้นสุด ทุกรอบบัญชีคุณต้องเชื่อมโยงข้อมูลแบบเดิม ใช้สูตรเดิม และตรวจสอบด้วยตัวเองแบบเดิมซ้ำแล้วซ้ำเล่า

ยอดรวมไม่ตรงกับสมุดบัญชีแยกประเภท เมื่อยอดรวมของแต่ละช่วงอายุหนี้ไม่เท่ากับยอดคงเหลือของลูกหนี้การค้า รายงานนั้นก็จะหมดความน่าเชื่อถือ และการหาสาเหตุมักใช้เวลานานกว่าการสร้างรายงานตั้งแต่แรกเสียอีก

รายงานวิเคราะห์อายุหนี้จะได้รับความไว้วางใจก็ต่อเมื่อยอดรวมตรงกับสมุดบัญชีแยกประเภท หากทำไม่ได้ตามนี้ ส่วนอื่นๆ ของรายงานก็ไม่มีความหมายอะไรเลย

วิธีการแก้ปัญหาเฉพาะหน้าที่ผู้คนมักใช้

ทางเลือกที่ 1: กำหนดนิยามให้ชัดเจนก่อนเริ่มแตะต้องสูตร

เขียน 4 สิ่งนี้ไว้ที่ด้านบนสุดของชีต: วันที่ที่คุณใช้นับอายุหนี้, ช่วงอายุหนี้แต่ละช่วง, ยอดเงินเป็นยอดรวมหรือยอดสุทธิหลังหักชำระเงินแล้ว และวันที่เกณฑ์ในการคำนวณ (as-of date)

วิธีนี้ใช้เวลาเพียงสิบนาทีแต่ช่วยป้องกันข้อพิพาทที่พบบ่อยที่สุดได้ บทความจาก Journal of Accountancy ได้อธิบายขั้นตอนการสร้างรายงานแบบเดียวกันนี้ โดยเน้นย้ำถึงความสำคัญของการตั้งค่าให้ถูกต้องเป็นอันดับแรกเช่นกัน

นอกจากนี้ยังช่วยกำหนดแหล่งข้อมูลของคุณด้วย เพราะคุณต้องการข้อมูลเฉพาะรายการที่ยังค้างชำระพร้อมยอดคงเหลือ ไม่ใช่รายการใบแจ้งหนี้ทั้งหมดที่เคยออก

ข้อจำกัดคือ นิยามเหล่านี้ไม่ได้ช่วยคำนวณอะไรเลย มันแค่ช่วยป้องกันไม่ให้คุณคำนวณผิดพลาดเท่านั้น

ทางเลือกที่ 2: สร้างคอลัมน์ช่วงอายุหนี้ แล้วสรุปยอดรวมด้วย Pivot Table

คำนวณจำนวนวันที่เกินกำหนดชำระโดยนำวันที่เกณฑ์ (as-of date) ลบด้วยวันครบกำหนดชำระ จากนั้นจับคู่ตัวเลขนั้นกับป้ายกำกับช่วงอายุหนี้ ฟังก์ชัน TODAY จะช่วยให้คุณได้วันที่เกณฑ์ที่เป็นปัจจุบัน และฟังก์ชัน DATEDIF จะส่งกลับจำนวนวันระหว่างวันที่สองวัน

สำหรับตัวป้ายกำกับเอง การใช้ฟังก์ชัน IFS จะช่วยให้อ่านง่ายกว่าการใช้สูตร IF ซ้อนกันหลายชั้นเมื่อคุณกลับมาดูในอีกหกเดือนข้างหน้า จากนั้นให้รวมยอดตามลูกค้าและช่วงอายุหนี้ด้วยฟังก์ชัน SUMIFS ซึ่งช่วยให้สามารถตรวจสอบการคำนวณทีละแถวได้ง่าย

เมื่อต้องส่งรายงานต่อให้ผู้อื่น ควรระบุวันที่เกณฑ์ (as-of date) เป็นค่าคงที่แทนการใช้ฟังก์ชัน TODAY เพราะไฟล์ที่อัปเดตอายุหนี้เองโดยอัตโนมัติในสัปดาห์หน้าจะมีข้อมูลที่ขัดแย้งกับเวอร์ชันที่อยู่ในกล่องจดหมายของผู้อื่นไปแล้ว

ข้อจำกัดสูงสุดคือปริมาณข้อมูลและกรณีพิเศษต่างๆ แม้สูตรจะยังใช้งานได้ แต่ใบลดหนี้ การชำระเงินบางส่วน และข้อพิพาทต่างๆ ก็ยังต้องจัดการด้วยมืออยู่ดี

ทางเลือกที่ 3: สร้างแท็บกฎเกณฑ์ไว้ข้างๆ แท็บข้อมูลตัวเลข

รวบรวมการตัดสินใจที่จัดการยากไว้ในที่เดียว เช่น วิธีหักลบใบลดหนี้, จะยกเว้นหรือทำเครื่องหมายใบแจ้งหนี้ที่มีข้อพิพาทอย่างไร, และจะแปลงยอดคงเหลือที่เป็นสกุลเงินต่างประเทศอย่างไรและใช้อัตราแลกเปลี่ยนใด

นี่คือสิ่งที่จะช่วยให้รายงานนี้ยังคงใช้งานได้เมื่อมีคนอื่นมาทำต่อ แต่ในขณะเดียวกัน มันก็มักจะเป็นแท็บที่ถูกมองข้ามเมื่อต้องเร่งรีบทำรายงานให้ทันกำหนดสิ้นเดือน

ข้อจำกัดคือ แท็บกฎเกณฑ์เป็นเพียงการบันทึกแนวทางปฏิบัติแต่ไม่ได้นำไปใช้จริงโดยอัตโนมัติ ใครบางคนยังคงต้องนำกฎแต่ละข้อไปปรับใช้ในทุกรอบบัญชี คู่มือของเราเกี่ยวกับ วิธีตรวจสอบยอดธุรกรรมในสเปรดชีต จะครอบคลุมถึงขั้นตอนการจับคู่ข้อมูลที่เป็นพื้นฐานของงานนี้

ข้อจำกัดร่วมกัน ทั้งสามแนวทางนี้ตั้งอยู่บนสมมติฐานที่ว่าคุณเริ่มทำงานจากข้อมูลรายการค้างชำระที่สะอาดเรียบร้อยแล้ว แต่หากแหล่งข้อมูลของคุณคือไฟล์ส่งออกใบแจ้งหนี้ดิบและไฟล์การชำระเงินที่แยกกัน งานที่แท้จริงคือการเชื่อมโยงข้อมูลทั้งสองเข้าด้วยกันก่อนที่จะเริ่มจัดกลุ่มอายุหนี้เสียอีก

วิธีสร้างรายงานวิเคราะห์อายุหนี้ด้วย Powerdrill Bloom

ขั้นตอนที่ 1: อัปโหลดข้อมูลใบแจ้งหนี้และการชำระเงินของคุณ

อัปโหลดไฟล์ส่งออกรายการค้างชำระ หรืออัปโหลดไฟล์ใบแจ้งหนี้และไฟล์การชำระเงินพร้อมกัน Powerdrill Bloom จะวิเคราะห์โครงสร้างคอลัมน์ทันทีที่อัปโหลด ดังนั้นปัญหาเรื่องวันครบกำหนดชำระที่หายไป ยอดเงินที่ว่างเปล่า หรือเลขที่ใบแจ้งหนี้ที่ซ้ำซ้อน จะถูกตรวจพบก่อนที่จะเริ่มคำนวณช่วงอายุหนี้

ขั้นตอนที่ 2: อธิบายกฎการจัดกลุ่มอายุหนี้ด้วยภาษาธรรมชาติ

ระบุกฎเกณฑ์ที่คุณต้องการแทนที่จะต้องสร้างมันขึ้นมาเอง เช่น ระบุว่าคุณต้องการนับอายุหนี้จากวันครบกำหนดชำระ ณ วันที่ที่กำหนด กำหนดช่วงอายุหนี้แต่ละช่วง และระบุว่ายอดเงินควรเป็นยอดสุทธิหลังหักชำระเงินแล้ว

จากนั้นสั่งให้ระบบตรวจสอบข้อมูลไปพร้อมกันในคราวเดียว เช่น ถามว่าใบแจ้งหนี้ใดมียอดชำระเงินเกินกว่ายอดในใบแจ้งหนี้ หรือใบแจ้งหนี้ใดมีวันครบกำหนดชำระก่อนวันที่ในใบแจ้งหนี้ แล้วถามว่ายอดรวมของแต่ละช่วงอายุหนี้ตรงกับยอดคงเหลือของลูกหนี้การค้าหรือไม่

ขั้นตอนที่ 3: ส่งออกแผนภูมิ รายงาน หรือสไลด์นำเสนอ

ส่งออกตารางวิเคราะห์อายุหนี้รายลูกค้า แผนภูมิแสดงการกระจายตัวของช่วงอายุหนี้ หรือรายการติดตามหนี้ที่เรียงลำดับตามยอดค้างชำระที่เก่าที่สุด

ทำไมวิธีนี้ถึงดีกว่าการสร้างรายงานใหม่เองทุกเดือน

	วิธีทำด้วยตัวเอง	Powerdrill Bloom
การเชื่อมโยงใบแจ้งหนี้กับการชำระเงิน	ใช้สูตรค้นหาข้อมูล (Lookup) ในแต่ละไฟล์	อัปโหลดทั้งสองไฟล์แล้วพิมพ์ถามได้เลย
การเปลี่ยนวันที่เกณฑ์ (as-of date)	คำนวณใหม่และตรวจสอบความถูกต้องอีกครั้ง	ระบุวันที่ใหม่ที่ต้องการ
การหักลบยอดชำระเงินบางส่วน	สร้างคอลัมน์คำนวณยอดคงเหลือด้วยตัวเอง	สั่งให้แสดงยอดคงเหลือสุทธิหลังหักชำระเงิน
การตรวจสอบยอดรวมให้ตรงกับสมุดบัญชีแยกประเภท	ต้องตรวจสอบด้วยตัวเองทุกรอบบัญชี	ถามระบบว่ายอดรวมตรงกันหรือไม่

ขั้นตอนตรงกลางเหล่านี้นี่เองที่ดึงเวลาส่วนใหญ่ในแต่ละเดือนของคุณไป การจัดกลุ่มอายุหนี้เป็นเพียงเรื่องของคณิตศาสตร์ แต่งานที่แท้จริงคือการเตรียมข้อมูลรายการค้างชำระให้สะอาดเรียบร้อย

ข้อผิดพลาดที่พบบ่อย

นับอายุหนี้จากวันที่ในใบแจ้งหนี้ทั้งที่ตั้งใจจะใช้วันครบกำหนดชำระ สำหรับการติดตามหนี้ วันครบกำหนดชำระมักจะเป็นเกณฑ์ที่ถูกต้องเสมอ ไม่ว่าคุณจะเลือกวิธีใด โปรดระบุไว้ในรายงานให้ชัดเจน

แสดงยอดเงินเต็มตามใบแจ้งหนี้แทนที่จะแสดงยอดคงเหลือที่ค้างชำระ ใบแจ้งหนี้ที่ชำระเงินแล้วบางส่วนควรจัดอยู่ในกลุ่มอายุหนี้ตามยอดคงเหลือที่ยังไม่ได้ชำระ การแสดงยอดเต็มจำนวนจะทำให้ยอดรวมทุกอย่างสูงเกินจริง

ปล่อยให้ฟังก์ชัน TODAY อัปเดตอายุหนี้ในไฟล์ที่ส่งต่อให้ผู้อื่นแล้ว ควรแปลงวันที่เกณฑ์ให้เป็นค่าคงที่ก่อนส่งรายงาน มิฉะนั้นคนสองคนอาจเห็นตัวเลขที่แตกต่างกันจากไฟล์เดียวกัน

ละเลยใบลดหนี้ (credit notes) ใบลดหนี้ที่ยังไม่ได้นำไปหักลบจะค้างอยู่ในบัญชีของลูกค้าและช่วยลดยอดหนี้ที่พวกเขาค้างชำระ การละเลยรายการนี้จะทำให้ยอดค้างชำระดูแย่เกินความเป็นจริง

จัดกลุ่มอายุหนี้ตามรายลูกค้าแทนที่จะจัดกลุ่มตามใบแจ้งหนี้ การจัดกลุ่มอายุหนี้ต้องทำทีละใบแจ้งหนี้ก่อน แล้วจึงค่อยรวมยอดตามรายลูกค้า การหาค่าเฉลี่ยอายุหนี้ของลูกค้าจะบดบังรายการที่ค้างชำระนานที่สุด ซึ่งเป็นรายการที่คุณจำเป็นต้องรู้

ไม่เคยตรวจสอบยอดกับสมุดบัญชีแยกประเภทเลย ยอดรวมของแต่ละช่วงอายุหนี้จะต้องเท่ากับยอดคุมลูกหนี้การค้า หากข้ามการตรวจสอบนี้ รายงานของคุณก็เป็นเพียงแค่กระดาษแผ่นหนึ่งที่ไม่มีประโยชน์อะไรเลย

สร้างรายงานใหม่ตั้งแต่ต้นในทุกรอบบัญชี กฎเกณฑ์ต่างๆ ไม่ได้เปลี่ยนไปทุกเดือน มีเพียงข้อมูลเท่านั้นที่เปลี่ยน ควรเก็บกฎเกณฑ์เดิมไว้แล้วเปลี่ยนเฉพาะข้อมูลส่งออก ซึ่งเป็นหลักการเดียวกับการทำ รายงานเปรียบเทียบงบประมาณกับค่าใช้จ่ายจริง (budget versus actual report)

บทสรุป

กำหนดวันที่ใช้นับอายุหนี้ให้ชัดเจน, ใช้ยอดคงเหลือที่ค้างชำระ, กำหนดวันที่เกณฑ์ให้เป็นค่าคงที่, และตรวจสอบยอดรวมให้ตรงกับสมุดบัญชีแยกประเภท สี่สิ่งนี้คือข้อแตกต่างระหว่างรายงานที่ผู้คนนำไปใช้งานจริง กับตารางข้อมูลที่ผู้คนเอาไว้โต้เถียงกัน

สิ่งที่ทำให้งานนี้สิ้นเปลืองเวลาและทรัพยากรคือ ทุกอย่างต้องอ้างอิงตามวันปัจจุบัน ทำให้รายงานนี้ไม่มีวันเสร็จสิ้นอย่างถาวร ขั้นตอนการเชื่อมโยงข้อมูลและการตรวจสอบจะวนกลับมาให้ทำใหม่ในทุกรอบบัญชี

หากนั่นคือสิ่งที่ดึงเวลาช่วงสิ้นเดือนของคุณไป ลองใช้ Powerdrill Bloom กับไฟล์ส่งออกใบแจ้งหนี้และการชำระเงินของคุณ และสามารถศึกษาคู่มือของเราเกี่ยวกับ วิธีแปลงงบการเงินในรูปแบบ PDF ให้เป็นแผนภูมิ รวมถึงหน้า การวิเคราะห์กระแสเงินสดด้วย AI ของเราได้เช่นกัน

คำถามที่พบบ่อย

ช่วงอายุหนี้มาตรฐานในรายงานวิเคราะห์อายุหนี้ลูกหนี้การค้ามีอะไรบ้าง?

รายงานส่วนใหญ่จะใช้ช่วง 0–30, 31–60, 61–90 และมากกว่า 90 วัน และมักจะมีคอลัมน์สำหรับยอดหนี้ปัจจุบันหรือยังไม่ถึงกำหนดชำระด้วย ช่วงเวลาเหล่านี้เป็นเพียงแนวปฏิบัติทั่วไปไม่ใช่กฎตายตัว ดังนั้นโปรดระบุให้ชัดเจนว่าคุณใช้เกณฑ์ใด

ฉันควรนับอายุหนี้จากวันที่ในใบแจ้งหนี้หรือวันครบกำหนดชำระ?

ใช้นับจากวันครบกำหนดชำระหากคุณต้องการทราบว่าลูกค้าชำระเงินล่าช้าไปนานเท่าใด ซึ่งมักจะเป็นเป้าหมายหลักในการติดตามหนี้ ใช้วันที่ในใบแจ้งหนี้หากคุณต้องการทราบว่าเอกสารนั้นออกมาระยะเวลานานเท่าใดแล้ว

ฉันจะจัดการกับการชำระเงินบางส่วนอย่างไร?

แสดงยอดคงเหลือที่ค้างชำระ ไม่ใช่ยอดเงินเดิมตามใบแจ้งหนี้ และจัดยอดคงเหลือนั้นไว้ในกลุ่มอายุหนี้ช่วงใดช่วงหนึ่ง การทำงานจากข้อมูลรายการค้างชำระ (open-items extract) แทนที่จะใช้รายการใบแจ้งหนี้ทั้งหมดจะช่วยจัดการเรื่องนี้ได้โดยอัตโนมัติ

ฉันจำเป็นต้องใช้ฟังก์ชัน Excel ใดบ้าง?

ใช้ TODAY หรือวันที่ที่กำหนดตายตัวสำหรับวันที่เกณฑ์ (as-of date) และใช้ DATEDIF สำหรับคำนวณจำนวนวันที่เกินกำหนดชำระ ใช้ IFS ในการกำหนดป้ายกำกับช่วงอายุหนี้ และใช้ SUMIFS ในการรวมยอดตามลูกค้าและช่วงอายุหนี้ ไม่มีฟังก์ชันใดที่ซับซ้อนเลย สิ่งที่ยากคือการกำหนดนิยามให้ถูกต้องต่างหาก

ควรสร้างรายงานนี้ใหม่บ่อยแค่ไหน?

อย่างน้อยเดือนละครั้ง และสัปดาห์ละครั้งหากมีการติดตามหนี้อย่างต่อเนื่อง เนื่องจากช่วงอายุหนี้ทุกช่วงจะแปรผันตามวันที่เกณฑ์ (as-of date) ดังนั้นโปรดแปลงวันที่เกณฑ์ให้เป็นค่าคงที่ในแต่ละเวอร์ชันที่คุณส่งต่อให้ผู้อื่น