如何在 Excel 中製作應收帳款帳齡分析表 (30、60、90 天)
帳齡分析表會根據未付發票的逾期天數將其分類到不同的區間，通常是 0–30 天、31–60 天、61–90 天以及 90 天以上。有兩個關鍵決定會影響您的報告是否正確。第一，您是要從到期日還是發票日開始計算帳齡；第二，部分付款的發票是要顯示全額還是剩餘餘額。
這兩點一旦弄錯，每個區間的總額都會出錯，這比完全沒有報告還要糟糕。
本指南將探討為什麼製作報告時容易出錯、人們常用的三種方法，以及每種方法在什麼情況下會失效。這是一個資料工作流，而非會計建議，因此請與負責管理您總帳的人員確認處理方式。
為什麼帳齡分析表會讓試算表出錯
第一個問題是日期選擇。從發票日計算帳齡可以告訴您這份單據有多久了；而從到期日計算帳齡則能告訴您客戶逾期了多久，對於催收工作來說，後者才是您需要的數字。
這兩種做法都有其道理，且會產生不同的報告。最常見的失敗情況是，試算表上根本沒有人記錄到底使用的是哪一種。
第二個問題是部分付款。一張 $10,000 的發票若已收到 $7,000，則應收帳款為 $3,000，且這 $3,000 必須準確地出現在其中一個區間中。如果帳齡分析表是基於發票清單而非未結項目清單建立的，就會在不知不覺中高估所有金額。
第三個問題是報告屬於即時快照。區間是相對於今天計算出來的，因此昨天的檔案就已經過期了，而且每次重新製作都必須重新計算每一列。
接著還有一些棘手的資料列。折讓單、預付款、有爭議的發票和多幣別餘額，每一項都需要制定規則。而且，這些規則還必須在下一個人打開檔案時不被破壞。
單獨來看，這些問題都不難解決。它們之所以困難，是因為每個月底在截止日期的壓力下，所有問題都會同時排山倒海而來。
這會讓您付出什麼代價
一份無法執行的催收清單。分類區間的目的在於知道該先打電話給誰。一份高估餘額的報告會讓人員去催收已經收到的款項。
每個月都要重做。因為區間是相對於今天計算的，所以帳齡分析表永遠沒有真正完成的一天。每個週期都要重複相同的聯結、相同的公式和相同的手動檢查。
總額與總帳對不上。當各區間的總和不等於應收帳款餘額時，這份報告就失去了公信力。而找出原因通常比一開始製作報告花費更多時間。
帳齡分析表之所以值得信賴，是因為其總額與總帳相符。如果這一點做不到，其他細節再完美也毫無意義。
人們嘗試的權宜之計
方案 1：在動用公式前先確定定義
在工作表的頂部寫下四件事：您從哪個日期開始計算帳齡、區間的界限是什麼、金額是總額還是扣除付款後的淨額，以及基準日是哪一天。
這只需花費十分鐘，卻能避免最常見的爭議。《Journal of Accountancy》在介紹相同的製作過程時，也同樣強調先做好設定的重要性。
這也決定了您的資料來源。您需要的是包含剩餘餘額的未結項目匯出檔，而不是一張包含所有歷史發票的清單。
這種方法的局限在於，定義本身並不能進行任何計算，它只能防止您算錯。
方案 2：建立區間欄位，然後對總額進行樞紐分析
將基準日減去到期日來計算逾期天數，然後將該數字對應到區間標籤。TODAY 函數可以提供動態的基準日，而 DATEDIF 則能傳回兩個日期之間的天數。
至於標籤本身，使用 IFS 會比半年後回頭看巢狀 IF 語句更具可讀性。接著，使用 SUMIFS 按客戶和區間計算總額，這樣可以保持每一列計算的可稽核性。
在分發報告時，請使用寫死的基準日，而不是 TODAY。因為下週會自動重新計算帳齡的檔案，將會與他人收件匣中已有的版本產生衝突。
這種方法的瓶頸在於資料量和特殊情況。雖然公式行得通，但折讓單、部分付款和爭議款項仍需要手動處理。
方案 3：在數據旁保留一個規則標籤頁
將所有棘手的決定集中在一個地方。例如折讓單如何抵銷、有爭議的發票是要排除還是標記，以及外幣餘額如何折算、採用何種匯率。
這能確保當其他人執行報告時，報告依然能正常運作。然而，這也是在月底時間緊迫時，最容易被忽略的工作表。
局限在於，規則標籤頁只是記錄了判斷標準，卻無法自動執行。每個週期仍然需要有人去手動套用每條規則。我們關於如何在試算表中進行交易對帳的指南，介紹了為此提供支援的核對工作。
共同的瓶頸。這三種方法都假設您是從乾淨的未結項目匯出檔開始。如果資料來源是原始發票匯出檔加上獨立的付款檔案，那麼在開始分類區間之前，真正的挑戰在於如何將它們聯結起來。
如何使用 Powerdrill Bloom 建立帳齡分析表
步驟 1：上傳您的發票與付款資料
上傳未結項目匯出檔，或者同時上傳發票和付款檔案。Powerdrill Bloom 會在資料匯入時分析欄位特徵，因此在計算任何區間之前，就能找出缺失的到期日、空白金額和重複的發票號碼。
步驟 2：用自然語言描述區間規則
直接說明規則，而不需要手動建構。例如，說明您要以特定日期為基準日，並從到期日開始計算帳齡。提供區間界限，並說明金額應為扣除已收付款後的淨額。
接著，在同一個步驟中要求進行檢查。詢問哪些發票的付款金額超過了發票金額，以及哪些發票的到期日早於發票日。最後，詢問區間總額是否與應收帳款餘額一致。
步驟 3：匯出圖表、報告或簡報
匯出每個客戶的帳齡表、區間分佈圖，或是按最舊餘額排序的催收清單。
為什麼這比每個月重新製作更好
| 手動方式 | Powerdrill Bloom | |
|---|---|---|
| 聯結發票與付款 | 每個檔案使用對照公式 | 同時上傳兩者並直接提問 |
| 變更基準日 | 重新計算並重新驗證 | 直接說明新日期 |
| 抵銷部分付款 | 手動建立餘額欄位 | 要求提供扣除付款後的餘額 |
| 將總額與總帳核對 | 每個週期手動檢查 | 直接詢問總額是否一致 |
中間的那些步驟才是最耗費時間的。區間分類只是簡單的算術，整理出乾淨的未結項目清單才是真正的核心工作。
常見錯誤
本想以到期日計算，卻誤用了發票日。對於催收工作，到期日幾乎總是正確的選擇。不論您選擇哪一種，請在報告上註明。
顯示發票金額而非剩餘餘額。部分付款的發票應以其未付餘額歸入區間。顯示全額會高估所有總額。
讓 TODAY 函數重新計算已分發檔案的帳齡。在傳送報告前請凍結基準日，否則兩個人在同一個檔案中會看到不同的數字。
忽略折讓單。未套用的折讓單存在於客戶帳上，會減少其欠款。如果忽略它，會使餘額看起來比實際情況更糟。
按客戶而非按發票進行區間分類。區間應針對每張發票進行分類，然後按客戶加總。將客戶的帳齡平均會隱藏最舊的項目，而那正是您需要關注的項目。
從不與總帳核對。區間總額必須等於應收帳款控制帳戶的餘額。如果省略這項檢查，這份報告就只是個擺設。
每個週期都從頭開始重新製作。規則不會每個月改變，改變的只有資料。保留規則並更換匯出的資料即可，這與製作預算與實際對比報告是相同的原則。
結論
決定帳齡計算日期、使用剩餘餘額、凍結基準日，並將總額與總帳核對。這四個關鍵決定了您的報告是一份能讓人採取行動的工具，還是一張讓人爭論不休的表格。
這種工作之所以耗費成本，是因為所有內容都是相對於今天計算的，因此永遠沒有真正完成的一天。聯結和檢查工作在每個週期都會捲土重來。
如果這正是您月底時間的去處，不妨在您的發票和付款匯出檔上試用 Powerdrill Bloom。另請參閱我們關於如何將 PDF 財務報表轉換為圖表的指南，以及 AI 現金流 analysis 頁面。
常見問題
應收帳款帳齡分析表中的標準區間有哪些？
大多數報告使用 0–30 天、31–60 天、61–90 天以及 90 天以上，通常還會包含一個「未逾期」或「尚未到期」欄位。這些界限只是一種慣例而非硬性規則，因此請註明您使用的是哪些區間。
我應該從發票日還是到期日開始計算發票帳齡？
如果您想知道客戶逾期了多久（這通常是催收的主要目的），請使用到期日。如果您想知道單據本身存在了多久，請使用發票日。
我該如何處理部分付款？
顯示剩餘餘額，而非原始發票金額，並將該餘額歸入一個區間中。從未結項目匯出檔（而非發票清單）開始處理，會自動解決這個問題。
我需要哪些 Excel 函數？
使用 TODAY 或固定日期作為基準日，並使用 DATEDIF 計算逾期天數。用 IFS 分配區間標籤，並用 SUMIFS 按客戶和區間計算總額。這些函數都不複雜，真正困難的是定義。
帳齡分析表應該多久重新製作一次？
至少每個月一次；如果催收工作頻繁，則應每週一次，因為每個區間都是相對於基準日計算的。請在您分發的每個版本中凍結該日期。