Public debt is the money the business needs to collect from customers or paid to suppliers after the transaction is complete. Use file debt management in Excel freefrom AccNet, help track details the account public debt, reminders when a payment is due, given the public warning overdue.
1. File debt management, what to include?
File management public debt in Excel, this free 3 sheet key:
- THE CATEGORY CUSTOMER – Save customer information/suppliers.
- PUBLIC DEBT DETAILS – Keep track of each transaction specific debt.
- THE DEBT AGGREGATE – Aggregate public debt according to the customer/supplier.
Ngoài ra, file theo dõi công nợ còn có thể bổ sung báo cáo công nợ bằng PivotTable, biểu đồ trực quan để phân tích dữ liệu dễ dàng hơn.
Read more:
- Software debt management help control the debt quickly
- Short-term debt, what is the? Concepts, examples and how to effectively manage
- Public accounting what is debt? Process, service and management efficiency
2. Tutorial details how to create a file management public debt in Excel, free
Create lists of customers, suppliers
Step 1: Create sheet "LIST of CUSTOMERS"
- Open Excel, click the + sign to create a new sheet.
- Change the sheet name to "LIST of CUSTOMERS".
Step 2: Enter the column header
Enter the following headers in the first row:
| STT | Code KH/ NCC | Name KH/ NCC | Type | Figures when you start using PM | Address | PHONE number | .... | Notes | |
| Accounts Payable | Accounts Receivable | ||||||||
Step 3: format data in the file management of public debt in Excel, free
B1: Column Code KH/NCC should only enter letters, numbers (EX: KH001, NCC001).
B2: Column Type (KH/NCC) enter KH as a customer, enter the NCC if is a provider.
B3: Column receivable and payable: Select column → To Format Cells → Select the Number or Accounting to show the exact amount.
B4: Create a list of options available for the column Type (KH/NCC) to avoid entering wrong:
- Select the column Type (KH/NCC).
- To Data → Data Validation → Select List.
- Enter: KH,NCC → Press OK.
Create table track debts and detailed file management public debt in Excel, free
Step 1: Create sheet "LIABILITIES DETAILS"
- Click the + sign to create a new sheet.
- Rename the sheet to "PUBLIC DEBT DETAILS".
Step 2: Enter the column header
Enter the following headers in the first row:
| Date | CONTENT | Pay increase | Pay up | Balance | Notes | |
| The top states | ||||||
Step 3: Enter formulas calculate automatically in file manager, public debt in Excel, free
Select the "pay period end" (column I) of the first line. Enter the formula: =F2+G2-H2
This formula calculates the balance of the debt by:
- Plus have To pay the first states to arise in the states.
- Minus paid.
- Press Enter, then drag the formula down for all the lines.
Step 4: data format
- Date column: Select the column → Format Cells → Date.
- Column Must pay the first states arose in the period, the payment Must be paid at the end of the period: Select the column → Format Cells → Number or Accounting.
Create table aggregate public debt in the file management of public debt in Excel, free
Step 1: Create sheet "LIABILITIES SYNTHETIC"
- Click the + sign to create a new sheet.
- Rename the sheet to "PUBLIC DEBT synthesis".
Step 2: Enter the column header
| STT | Item code | Name KH/ NCC | Type | Pay start | Pay first | Pay increase | Pay up | Pay period end | Notes |
| TOTAL |
Step 3: Use the formula for synthetic data
- Calculate the total "To pay the": =SUMIF('PUBLIC DEBT DETAILS'!B:B, B2, 'PUBLIC DEBT DETAILS'!F:F)
- Calculate the total "arising in": =SUMIF('PUBLIC DEBT DETAILS'!B:B, B2, 'PUBLIC DEBT DETAILS'!G:G)
- Calculate the total "paid": =SUMIF('PUBLIC DEBT DETAILS'!B:B, B2, 'PUBLIC DEBT DETAILS'!H:H)
- Calculate the "final balance": =D2+E2-F2
After entering the formula is complete, pull down to apply to all lines.
Create liabilities reported by PivotTable
- On sheet "LIABILITIES DETAILS".
- Select the entire data table.
- On Insert → PivotTable → Select "New Worksheet".
In the PivotTable of file management of public debt in Excel for free:
- Pull Code KH/NCC into Rows.
- Drag closing balance Values (select Sum).
- Click OK to view consolidated report on public debt of each customer/supplier.
Create the chart debt
- On sheet "LIABILITIES SYNTHETIC" in the file manager, public debt in Excel, free
- Select the data from the Name column KH/NCC, final balance.
- On Insert → Select the Column Chart (column chart).
- Book title is "Synthetic Debt"Customers.
3. Download file debt management in Excel free
DOWNLOAD THE EXCEL FILE TO MANAGE PUBLIC DEBT FREE - FORM NO. 1
DOWNLOAD THE EXCEL FILE TO MANAGE PUBLIC DEBT FREE - FORM NO. 2
Benefits when using this file:
- File theo dõi công nợ chi tiết, tự động cảnh báo khi sắp đến hạn.
- Integrated calculation formula helps to check the debt quickly.
- Support to manage cash flows efficiently, avoid bad debt, overdue.
- The interface is easy to use, can customize according to business needs.
Lưu ý: File theo dõi công nợ này có thể chỉnh sửa linh hoạt để phù hợp với từng doanh nghiệp. Nếu muốn bổ sung tính năng nâng cao, hãy liên hệ với AccNet để được tư vấn, hỗ trợ!
File debt management in Excel free is a useful tool to help businesses track not too complicated. With recipe automatic calculation, visual reports, you can control the flow of money better, to avoid overdue. Let's immediately download the Excel file is to start debt management more scientific!
CONTACT INFORMATION:- ACCOUNTING SOLUTIONS COMPREHENSIVE ACCNET
- 🏢 Head office: 23 Nguyen Thi huynh, Ward 8, Phu Nhuan District, ho chi minh CITY.CITY
- ☎️ Hotline: 0901 555 063
- 📧 Email: accnet@lacviet.com.vn
- 🌐 Website: https://accnet.vn/
Theme: