Grouping iteration type account receivable self-service analysis method

Abstract

The invention relates to the field of financial data analysis methods, and particularly discloses a grouping iteration type receivable account age self-service analysis method, which comprises the following steps of: (1) establishing a bad debt counting and extracting proportion Accrual number bin, establishing a project Project number bin, establishing a project metering number bin and establishing a project collection Receipt number bin; (2) cleaning data; and (3) executing account age analysis. According to the method, a configurable standardized account age receivable analysis model is established, automatic and accurate checking and reduction of negative metering are achieved in the model by means of grouping iteration, forward traversal and reverse checking and reduction, financial staff are helped to achieve automatic account age receivable analysis by means of the data warehouse technology, the account age receivable analysis efficiency is improved, and the accuracy of account age receivable analysis is improved. And the receivable account age analysis accuracy is ensured.

Description

Technical Field

[0001] The present invention relates to the technical field of financial data analysis methods, and in particular to a grouped iterative self-service analysis method for aging of accounts receivable, which is used for aging analysis of accounts receivable containing negative measurements. Background Art

[0002] In the enterprise project management business, the situation of accounts receivable can be reflected according to the measurement of project progress, but the collection of each project may not be collected on time according to the established project progress measurement results. According to the enterprise financial system, if accounts receivable are not collected for a long time, they will be impaired according to a certain provision ratio. The cost of impairment is the accounts receivable that the enterprise determines cannot be recovered, which is called bad debt. Aging analysis is to allocate the accounts receivable of each project to different age groups, and calculate the bad debts of the corresponding age group according to the provision ratio. The business of each project of the enterprise increases year by year, and most projects have a long cycle. The workload of aging analysis is huge. It is difficult for financial personnel to implement aging analysis in a standardized and automated manner regularly through electronic spreadsheets. Especially in actual management, there is a situation of measurement reduction, which makes it more difficult for financial personnel to conduct aging analysis accurately and efficiently.

[0003] There are two traditional methods for aging analysis: (1) Performing aging analysis by using Excel spreadsheets. This involves compiling the data standards required for aging analysis in Excel, collecting the data required for aging analysis based on the receipt standards, compiling aging analysis formulas, and calculating the aging analysis results for each project. However, the biggest drawback of this method is that data collection, data verification, and data calculation are all done manually, so the labor cost is very high and the time cycle is very long. In particular, it is impossible to achieve automated analysis for multi-source financial data, large data volumes, and data containing negative measurements. In addition, it has poor versatility and is difficult to adapt to the aging analysis needs of multiple business types. (2) Directly using business system data analysis: After the business needs are informatized, the system is gradually used to carry out aging analysis in order to achieve the purpose of automated aging analysis. This method mainly uses business data in the source business system and conducts analysis according to the aging analysis calculation rules. Compared with Excel, this method can realize automated analysis, and the data is very timely, which also reduces the time cycle of aging analysis. However, the biggest drawback of this method is that it directly reads business data for calculation. When the data volume is large, it is easy to cause table deadlock, which seriously affects the application of the source business system. In addition, the data standards of various business systems are inconsistent, and the calculation methods are also inconsistent. Once there is a modification in the aging analysis calculation rules, the calculation rules of various business systems must be readjusted. Therefore, data consistency is difficult to ensure, the versatility is not strong, and it cannot negatively measure the reduction process. More importantly, it has a negative impact on the stability of the business system. Summary of the invention

[0004] In order to solve the problems existing in the above-mentioned prior art, the purpose of the present invention is to provide a group-iterative self-service analysis method for accounts receivable aging, by establishing a configurable and standardized accounts receivable aging analysis model, in which group iteration is used to realize automatic and accurate reduction of negative measurement in a forward traversal and reverse reduction manner, and data warehouse technology is used to help financial personnel realize automated accounts receivable aging analysis, improve the efficiency of accounts receivable aging analysis, and ensure the accuracy of accounts receivable aging analysis.

[0005] In order to achieve the above-mentioned purpose, the present invention adopts the following technical measures: a group iterative accounts receivable aging self-service analysis method, comprising the following steps:

[0006] (1) Establish a data warehouse:

[0007] ① Establish an Accrual data warehouse for bad debt provision ratio, including fields such as [Account Nature Code], [Account Nature Name], [Aging Time Period], [Aging Time Period Maximum Boundary Value], and [Provision Ratio]; pre-prepare bad debt provision ratio data in accordance with the requirements of the enterprise's financial management documents;

[0008] ② Establish a project data warehouse, including fields such as [Project Number], [Project Name], [Project Amount], [Main Unit], [Project Region], [Province / City], [Owner Name], [0-6 Months], [7-12 Months], [1-2 Years], [2-3 Years], [3-4 Years], [4-5 Years], and [More than 5 Years];

[0009] ③ Establish a metering data warehouse for project measurement, including fields such as [project number], [measurement amount for this period], [measurement time for this period], [bad debt provision base for this period], [0-6 months], [7-12 months], [1-2 years], [2-3 years], [3-4 years], [4-5 years], and [more than 5 years];

[0010] ④ Create a project receipt data warehouse, including fields such as [Project Number] and [Cumulative Receipt Amount].

[0011] (2) Data cleaning: Establish project scheduled tasks, project metering scheduled tasks, and project receipt scheduled tasks; at a fixed time each month, extract the basic information, metering data, and receipt data of all projects generated as of the end of the previous month from the project business, metering business, and receipt business data and clean them into the three data warehouses of Project, Metering, and Receipt.

[0012] (3) Perform aging analysis:

[0013] (3.1) Traverse the Project data warehouse: Traverse all projects in the Project data warehouse and perform the following steps:

[0014] (3.1.1) Read the cumulative amount of receipts A of the current project in the Receipt data warehouse and all metering data of the current project in the Metering data warehouse according to the project number, and proceed to step (3.1.2);

[0015] (3.1.2) Determine whether there is a negative measurement in the current project measurement data. If there is a negative measurement, go to step (3.2); otherwise, go to step (3.3);

[0016] (3.2) Check and reduce the metering load: Traverse all metering data of the current project in the Metering data warehouse in the positive order of metering time and perform the following steps:

[0017] (3.2.1) Define the amount to be reduced: B=0, the cumulative amount to be reduced: C=0, and the remaining amount to be reduced: D=0; take the index from 0 to the first time that the metering amount in this period is negative, and form the sub-array SubMetering to be reduced this time; proceed to step (3.2.2);

[0018] (3.2.2) Traverse the subarray SubMetering in reverse order of metering time, set the amount to be deducted B = SubMetering[0][Current period metering amount]; proceed to step (3.2.3) and deduct B;

[0019] (3.2.3) If the current period metering amount E<0, reset E=0; otherwise, if E<=D, the current period metering amount is completely reduced, set C=C+E, and the remaining reduced amount D=BC, then set E=0; otherwise, it is a partial reduction, set the metering amount after reduction E=ED, C=C+D, then set the remaining reduced amount D=BC; update the reduced E to the metering amount field of the current period in the metering warehouse; proceed to step (3.2.4);

[0020] (3.2.4) Determine whether B has been reduced: If D = 0, B has been reduced, and the SubMetering traversal is exited and the process goes to step (3.2.5); otherwise, the process continues to traverse the SubMetering and repeats step (3.2.3) until the SubMetering reduction is completed and the process goes to step (3.2.5);

[0021] (3.2.5) Repeat step (3.1.2) until there is no negative metering in the current project of the Metering warehouse, and then proceed to step (3.3).

[0022] (3.3) Calculate the accrual base: Define the variable SE of the cumulative metering amount at the end of the current project, initially set SE=0, traverse all metering data of the current project in the Metering warehouse in the positive order of metering time, and perform the following steps:

[0023] (3.3.1) Set SE = SE + E, compare SE with the cumulative received amount A of the current project. If SE - A <= 0, it means there is no uncollected measurement, that is, the accrual base G for this period = 0; if SE - A < E, G = SE - A; otherwise, G = E; update the accrual base G for this period to the accrual base field for bad debts in the Metering data warehouse, and proceed to step (3.3.2);

[0024] (3.3.2) Continue to traverse the next measurement data, and repeat step (3.3.1) until all the measurement data of the current project in the Metering data warehouse has been traversed, then proceed to step (3.4).

[0025] (3.4) Calculate the bad debt amount: Traverse all the measurement data of the current project in the Metering data warehouse in ascending order of measurement time, and perform the following steps:

[0026] (3.4.1) Set the number of months M of the current period's aging as the total number of months between the analysis time and the measurement time of this period. Traverse the Accrual data warehouse in ascending order of the boundary values of the number of months. Set F as the smallest boundary value greater than M, and take the accrual ratio corresponding to F as the accrual ratio R for bad debts in this period, and then exit the traversal of the Accrual data warehouse; set the bad debt amount BadDebt_T = G * R, and update BadDebt_T to the bad debt amount in the corresponding aging period in the Metering data warehouse;

[0027] (3.4.2) Traverse the next measurement data, and repeat step (3.4.1) until all the measurement data under the current project in the Metering data warehouse has been traversed, then proceed to step (3.5).

[0028] (3.5) Aggregate the bad debt amount: According to the project number, count and update the cumulative bad debt amount BadDebt = SUM(BadDebt_T) for each aging period of the current project in the Metering data warehouse, and update it to the bad debt amount for each aging period in the Project data warehouse. Traverse the next project, and repeat step (3.1.1) until the traversal of the Project data warehouse is completed.

[0029] Optionally, the extracted content includes:

[0030] ① The basic project information includes: [project number], [project name], [project amount], [main body unit], [project affiliated region], [province / city], [owner name];

[0031] ② The project measurement data includes: [project number], [measurement amount for this period], [measurement time for this period];

[0032] ③Project collection data includes: [project number], [collection amount for this period], and [collection time for this period].

[0033] Optionally, the cleaning rules include:

[0034] ① Basic project information: First clear the Project data warehouse, and then write the basic information of each approved project into the Project data warehouse, including: [Project number], [Project name], [Project amount], [Subject unit], [Project region], [Province / city], [Owner name]. The number of bad debts in each aging period [0-6 months], [7-12 months], [1-2 years], [2-3 years], [3-4 years], [4-5 years], [5 years and above] is set to 0 as the initial value;

[0035] ② Project metering data: Clear the metering warehouse first, and then write the metering data of each approved project into the metering warehouse. The written content includes: [Project number], [Metering amount for this period], [Metering time for this period]; [Bad debt provision base for this period], and the initial value of bad debts for [0-6 months], [7-12 months], [1-2 years], [2-3 years], [3-4 years], [4-5 years], [5 years and above] is 0;

[0036] ③ Project collection data: First clear the Receipt data warehouse, and use the extracted project collection information as the statistical unit, calculate the cumulative collection amount of each project as of the end of the previous month, and write it into the Receipt data warehouse. The written content includes: [Project Number], [Cumulative Collection Amount].

[0037] From the above, the method of the present invention includes three main steps: establishing a data warehouse, cleaning data, and performing aging analysis. By establishing a data warehouse, multiple independently deployed application service data are collected and integrated to eliminate data islands, unify data standards and calculation calibers; according to the data standards defined in the data warehouse, each application data is cleaned regularly to achieve data consistency and timeliness of data analysis, thereby ensuring the timeliness and accuracy of the aging analysis results in the aging analysis link. The present invention establishes a configurable and standardized accounts receivable aging analysis model, uses a group iteration method, realizes automatic and accurate reduction of negative measurements, and uses data warehouse technology to realize automated analysis of accounts receivable aging. Since it is impossible and prohibited in actual financial business for the cumulative negative measurement amount to be greater than the cumulative positive measurement amount, the present invention does not need to consider the processing of such extreme data.

[0038] Compared with the prior art, the present invention introduces the application of data warehouses, isolates the analysis process from the business system to a certain extent, and can reduce the direct impact on the performance of the business system. In addition, an aging analysis data standard is established based on the database warehouse, and through automated data cleaning and calculation, it can support a variety of aging analysis scenarios. In particular, the revenue data is grouped and iteratively reduced based on the negative measurement index that appears first, which can not only meet the requirements of automatic and nearby reduction of negative measurements, but also clearly understand the specific distribution of the reduction amount, achieving the effect of data traceability.

[0039] The present invention realizes the standardized and automated analysis of the age of financial accounts receivable, which is mainly reflected in:

[0040] (1) The model supports aging analysis of accounts receivable for multiple application scenarios, including “sales and service payments”, “project payments and project warranty deposits”, “security deposits to owners”, and “deposits”.

[0041] (2) Automatic data acquisition and analysis greatly reduce the workload of financial personnel.

[0042] (3) By using a data warehouse approach and performing step-by-step calculations, the effect of data traceability can be achieved.

[0043] (4) The automatic aging analysis process is isolated from the source business system, reducing the frequent reading and high-intensity calculation of the source business system data and ensuring the stable performance of the source business system. BRIEF DESCRIPTION OF THE DRAWINGS

[0044] The drawings described herein are used to provide further understanding of the present application and constitute a part of the present application. The illustrative embodiments of the present application and their descriptions are used to explain the present application and do not constitute improper limitations on the present application.

[0045] Figure 1 This is the overall flow chart of the aging analysis of the present invention;

[0046] Figure 2 The aging analysis sub-process of the present invention - burden reduction measurement;

[0047] Figure 3 The aging analysis sub-process of the present invention - calculation of accrual base;

[0048] Figure 4 This is the aging analysis sub-process of the present invention - calculating the number of bad debts. DETAILED DESCRIPTION

[0049] The present invention will be described in detail below in conjunction with the accompanying drawings and specific embodiments. It should be noted that the described embodiments are only a part of the embodiments of the present invention, rather than all of the embodiments. Based on the embodiments of the present invention, all other embodiments obtained by ordinary technicians in this field without making creative work are within the scope of protection of the present invention.

[0050] A group iterative self-service analysis method for accounts receivable aging includes the following steps:

[0051] (1) Establish a data warehouse

[0052] ① Establish the Accrual data warehouse for bad debt provision ratio, including fields such as [Account Nature Code], [Account Nature Name], [Aging Time Period], [Aging Time Period Maximum Boundary Value], and [Provision Ratio]. Pre-prepare bad debt provision ratio data in accordance with the requirements of the enterprise's financial management documents.

[0053] ② Establish a project data warehouse, including fields such as [Project Number], [Project Name], [Project Amount], [Main Unit], [Project Region], [Province / City], [Owner Name], [0-6 Months], [7-12 Months], [1-2 Years], [2-3 Years], [3-4 Years], [4-5 Years], and [More than 5 Years].

[0054] ③ Establish a metering data warehouse for project measurement, including fields such as [project number], [measurement amount for this period], [measurement time for this period], [bad debt provision base for this period], [0-6 months], [7-12 months], [1-2 years], [2-3 years], [3-4 years], [4-5 years], and [more than 5 years].

[0055] ④ Create a project receipt data warehouse, including fields such as [Project Number] and [Cumulative Receipt Amount].

[0056] (2) Cleaning data

[0057] Establish project scheduled tasks, project metering scheduled tasks, and project receipt scheduled tasks. At a fixed time each month, extract the basic information, metering data, and receipt data of all projects generated by the end of the previous month from the project business, metering business, and receipt business data, and clean them into the three data warehouses of Project, Metering, and Receipt.

[0058] Extracted content includes:

[0059] ① Basic project information: [project number], [project name], [project amount], [main unit], [project area], [province / city], [owner name];

[0060] ② Project measurement data: [project number], [measurement amount for this period], [measurement time for this period];

[0061] ③Project collection data: [Project number], [Current period collection amount], [Current period collection time].

[0062] Cleaning rules include:

[0063] ① Basic project information: First clear the Project data warehouse, and then write the basic information of each approved project into the Project data warehouse. The written content includes: [Project number], [Project name], [Project amount], [Subject unit], [Project region], [Province / city], [Owner name]. The number of bad debts in each aging time period [0-6 months], [7-12 months], [1-2 years], [2-3 years], [3-4 years], [4-5 years], [5 years and above] is set to 0 as the initial value;

[0064] ② Project metering data: Clear the metering warehouse first, and then write the metering data of each approved project into the metering warehouse. The written content includes: [Project number], [Metering amount for this period], [Metering time for this period]. [Bad debt provision base for this period] and the bad debt numbers for [0-6 months], [7-12 months], [1-2 years], [2-3 years], [3-4 years], [4-5 years], and [5 years and above] are set to 0 as the initial value;

[0065] ③ Project collection data: First clear the Receipt data warehouse, and use the extracted project collection information as the statistical unit, calculate the cumulative collection amount of each project as of the end of the previous month, and write it into the Receipt data warehouse. The written content includes: [Project Number], [Cumulative Collection Amount].

[0066] (3) Perform aging analysis

[0067] The overall aging analysis process is as follows: Figure 1 As shown. Specifically:

[0068] (3.1) Traversing the Project Data Warehouse

[0069] Traverse all projects in the Project data warehouse and perform the following steps:

[0070] (3.1.1) Read the cumulative amount of receipts A of the current project in the Receipt data warehouse and all metering data of the current project in the Metering data warehouse according to the project number, and proceed to step (3.1.2).

[0071] (3.1.2) Determine whether there is a negative measurement in the current project measurement data. If there is a negative measurement, go to step (3.2); otherwise, go to step (3.3).

[0072] (3.2) Measurement of burden reduction

[0073] Negative measurement reduction process Figure 2 As shown. Traverse all metering data of the current project in the Metering data warehouse in chronological order and perform the following steps:

[0074] (3.2.1) Define the amount to be reduced: B=0, the cumulative amount to be reduced: C=0, and the remaining amount to be reduced: D=0. Read the array SubMetering to be reduced according to the array index of the first current period metering amount E which is a negative number, SubMetering=Metering[0, Min(Index(E<0))]. Go to step (3.2.2).

[0075] (3.2.2) Traverse the subarray SubMetering in reverse order of metering time, and set the amount to be deducted B = SubMetering[0][Current Period Metering Amount]. Go to step (3.2.3) and deduct B.

[0076] (3.2.3) If the current period metering amount E<0, reset E=0; otherwise, if E<=D, the current period metering amount is completely reduced, set C=C+E, and the remaining reduced amount D=BC, then set E=0; otherwise, it is a partial reduction, set the metering amount after reduction E=ED, C=C+D, then set the remaining reduced amount D=BC. Update the reduced E to the metering amount field of the current period in the metering warehouse. Go to step (3.2.4).

[0077] (3.2.4) Determine whether B has been reduced: If D = 0, B has been reduced, and the process exits the SubMetering traversal and proceeds to step (3.2.5); otherwise, the process continues to traverse the SubMetering and repeats step (3.2.3) until the SubMetering reduction is completed and proceeds to step (3.2.5).

[0078] (3.2.5) Repeat step (3.1.2) until there is no negative metering in the current project of the Metering warehouse, and then proceed to step (3.3).

[0079] (3.3) Calculation of the accrual base

[0080] The calculation process of the provision base is as follows: Figure 3 Define the variable SE of the cumulative metering amount at the end of the current project, and set SE to 0 initially. Traverse all metering data of the current project in the Metering warehouse in the positive order of metering time and perform the following steps:

[0081] (3.3.1) Set SE = SE + E, and compare SE with the cumulative received amount A of the current project. If SE - A <= 0, it means there is no uncollected measurement, that is, the provision base G for this period's measurement = 0; if SE - A < E, G = SE - A; otherwise, G = E. Update the provision base G for this period to the [Provision Base for Bad Debts in This Period] field in the Metering data warehouse, and proceed to step (3.3.2).

[0082] (3.3.2) Continue to traverse the next measurement data and repeat step (3.3.1) until all the measurement data of the current project in the Metering data warehouse has been traversed, and then proceed to step (3.4).

[0083] (3.4) Calculate the bad debt amount

[0084] The calculation process of the bad debt amount is as Figure 4 shown. Traverse all the measurement data of the current project in the Metering data warehouse in ascending order of the measurement time. Execute the following steps:

[0085] (3.4.1) Set the number of months of the current aging period M = Month(T - Metering[i][Measurement Time in This Period]). Traverse the Accrual data warehouse in ascending order according to the size of the month boundary value. The boundary value F = Accrual[j][Maximum Boundary Value of the Aging Period in Months]. If M <= F, set the bad debt provision ratio R for this period = Accrual[j][Provision Ratio], and the aging period MT to which this period belongs = Accrual[j][Aging Period], and jump out of traversing the Accrual data warehouse. Set the bad debt amount BadDebt_T = G * R, and update BadDebt_T to the bad debt amount in the corresponding aging period in the Metering data warehouse, that is, Metering[i][MT] = BadDebt_T.

[0086] (3.4.2) Traverse the next measurement data and repeat step (3.4.1) until all the measurement data under the current project in the Metering data warehouse has been traversed, and then proceed to step (3.5).

[0087] (3.5) Aggregate the bad debt amount

[0088] According to the project number, count and update the cumulative bad debt amount BadDebt = SUM(BadDebt_T) of the current project in each aging period in the Metering data warehouse, and update it to the bad debt amount in each aging period in the Project data warehouse. Traverse the next project and repeat step (3.1.1) until the traversal of the Project data warehouse is completed.

[0089] Example 1

[0090] The method of the present invention is used for automatic analysis of the aging of financial accounts receivable. The specific implementation of the present invention is introduced below with reference to a specific example. Assuming that an aging analysis of the accounts receivable of each project before November 30, 2024 (T) is required, the steps are as follows:

[0091] (1) Establish a data warehouse

[0092] ① Establish the Accrual data warehouse for bad debt provision ratio, including fields such as [Account Nature Code], [Account Nature Name], [Aging Time Period], [Aging Time Period Maximum Boundary Value], and [Provision Ratio]. Pre-prepare bad debt provision ratio data in accordance with the requirements of the enterprise's financial management documents.

[0093] Specifically, the Accrual data warehouse standards and data for this example are shown in Table 1, where 9999 represents infinity:

[0094] Table 1

[0095]

[0096] ② Establish a project data warehouse, including fields such as [Project Number], [Project Name], [Project Amount], [Main Unit], [Project Region], [Province / City], [Owner Name], [0-6 Months], [7-12 Months], [1-2 Years], [2-3 Years], [3-4 Years], [4-5 Years], [5 Years or More]. Read the [Project Number] and [Project Name] information of all engineering projects from the business system library and store them in the Project data warehouse.

[0097] ③ Establish a metering data warehouse for project measurement, including fields such as [Project Number], [Measurement Amount for this Period], [Measurement Time for this Period], [Bad Debt Provision Base for this Period], [0-6 Months], [7-12 Months], [1-2 Years], [2-3 Years], [3-4 Years], [4-5 Years], and [More than 5 Years]. According to the [Project Number] of the Project library, read the [Measurement Amount] and [Measurement Date] from the business system library and store them in the metering data warehouse.

[0098] ④ Create a project receipt data warehouse, including fields such as [Project Number] and [Cumulative Receipt Amount]. According to the [Project Number] of the Project library, read the [Cumulative Receipt Amount] from the business system library and store it in the Receipt data warehouse.

[0099] (2) Cleaning data

[0100] Establish project scheduled tasks, project metering scheduled tasks, and project receipt scheduled tasks. On the 1st of each month, extract the basic information, metering data, and receipt data of each project generated by the end of the previous month from the project business, metering business, and receipt business data and clean them into the three data warehouses of Project, Metering, and Receipt.

[0101] Extracted content includes:

[0102] ① Basic project information: [project number], [project name], [project amount], [main unit], [project area], [province / city], [owner name];

[0103] ② Project measurement data: [project number], [measurement amount for this period], [measurement time for this period];

[0104] ③Project collection data: [Project number], [Current period collection amount], [Current period collection time].

[0105] Cleaning rules include:

[0106] ① Basic project information: First clear the Project data warehouse, and then write the basic information of each approved project into the Project data warehouse. The written content includes: [Project number], [Project name], [Project amount], [Subject unit], [Project region], [Project province / city], [Owner name]. The number of bad debts in each aging period [0-6 months], [7-12 months], [1-2 years], [2-3 years], [3-4 years], [4-5 years], [5 years and above] is set to 0 as the initial value.

[0107] Specifically, the Project data warehouse standards and data after cleaning in this example are shown in Table 2:

[0108] Table 2

[0109]

[0110] ② Project metering data: Clear the metering warehouse first, and then write the metering data of each approved project into the metering warehouse. The written content includes: [Project number], [Metering amount for this period], [Metering time for this period]. [Bad debt provision base for this period] and the bad debt numbers for [0-6 months], [7-12 months], [1-2 years], [2-3 years], [3-4 years], [4-5 years], and [5 years and above] are initialized to 0.

[0111] Specifically, the metering data warehouse standards and data after cleaning in this example are shown in Table 3:

[0112] Table 3

[0113]

[0114] ③ Project collection data: First clear the Receipt data warehouse, and use the extracted project collection information as the statistical unit, calculate the cumulative collection amount of each project as of the end of the previous month, and write it into the Receipt data warehouse. The written content includes: [Project Number], [Cumulative Collection Amount].

[0115] Specifically, the Receipt data warehouse standards and data after cleaning in this example are shown in Table 4:

[0116] Table 4

[0117]

[0118] (3) Perform aging analysis

[0119] (3.1) Traversing the Project Data Warehouse

[0120] Traverse all projects in the Project data warehouse and perform the following steps:

[0121] (3.1.1) Read the cumulative amount of receipts A of the current project in the Receipt data warehouse and all metering data of the current project in the Metering data warehouse according to the project number, and proceed to step (3.1.2).

[0122] (3.1.2) Determine whether there is a negative measurement in the current project measurement data. If there is a negative measurement, go to step (3.2); otherwise, go to step (3.3).

[0123] (3.2) Measurement of burden reduction

[0124] Traverse all metering data of the current project in the Metering data warehouse in chronological order and perform the following steps:

[0125] (3.2.1) Define the amount to be reduced: B=0, the cumulative amount to be reduced: C=0, and the remaining amount to be reduced: D=0. Read the array SubMetering to be reduced according to the array index of the first current period metering amount E which is a negative number, SubMetering=Metering[0, Min(Index(E<0))]. Go to step (3.2.2).

[0126] Specifically, for the metering data of the item "P0000001" in Table 3, the subarray SubMetering data is as shown in Table 5:

[0127] Table 5

[0128]

[0129] (3.2.2) Traverse the subarray SubMetering in reverse order of metering time, and set the amount to be deducted B = SubMetering[0][Current Period Metering Amount]. Go to step (3.2.3) and deduct B.

[0130] (3.2.3) If the current period metering amount E<0, reset E=0; otherwise, if E<=D, the current period metering amount is completely reduced, set C=C+E, and the remaining reduced amount D=BC, then set E=0; otherwise, it is a partial reduction, set the metering amount after reduction E=ED, C=C+D, then set the remaining reduced amount D=BC. Update the reduced E to the metering amount field of the current period in the metering warehouse. Go to step (3.2.4).

[0131] (3.2.4) Determine whether B has been reduced: If D = 0, B has been reduced, and the process exits the SubMetering traversal and proceeds to step (3.2.5); otherwise, the process continues to traverse the SubMetering and repeats step (3.2.3) until the SubMetering reduction is completed and proceeds to step (3.2.5).

[0132] Table 5 Sub-array SubMetering negative metering reduction process and results are shown in Table 6:

[0133] Table 6

[0134]

[0135] (3.2.5) Repeat step (3.1.2) until there is no negative metering in the current project of the Metering warehouse, and then proceed to step (3.3).

[0136] Update the data in the [Amount after reduction E] column of Table 6 to the [Amount of current period] of the "P0000001" project in the Metering warehouse. The results are shown in Table 7:

[0137] Table 7

[0138]

[0139] (3.3) Calculation of the accrual base

[0140] Define the variable SE of the cumulative metering amount at the end of the current project, and set SE to 0 initially. Traverse all metering data of the current project in the Metering warehouse in the positive order of metering time, and perform the following steps:

[0141] (3.3.1)Set SE = SE + E, compare SE with the cumulative received amount A of the current project. If SE - A <= 0, it means there is no uncollected measurement, that is, the accrual base G for this period = 0; if SE - A < E, G = SE - A; otherwise, G = E. Update the accrual base G for this period to the [Accrual Base for This Period] field in the Metering data warehouse, and proceed to step (3.3.2).

[0142] (3.3.2)Continue to traverse the next measurement data, and repeat step (3.3.1) until all the measurement data of the current project in the Metering data warehouse has been traversed, then proceed to step (3.4).

[0143] Specifically, use the metering amount E for this period in Table 7 after write-off, and calculate it with the cumulative received amount of 13,446,000 yuan for the "P0000001" project in Table 4. The calculation results of the accrual base are shown in Table 8:

[0144] Table 8

[0145]

[0146] (3.4)Calculate the bad debt amount

[0147] Traverse all the measurement data of the current project in the Metering data warehouse in ascending order of the metering time. Execute the following steps:

[0148] (3.4.1)Set the number of months of the current account age M = Month(T - Metering[i][Measurement Time for This Period]). Traverse the Accrual data warehouse in ascending order according to the size of the month boundary value. The boundary value F = Accrual[j][Maximum Boundary Value of the Number of Months in the Account Age Period]. If M <= F, set the bad debt accrual ratio R for this period = Accrual[j][Accrual Ratio], the current account age period MT = Accrual[j][Account Age Period], and jump out of traversing the Accrual data warehouse. Set the bad debt amount BadDebt_T = G * R, and update BadDebt_T to the bad debt amount corresponding to the account age period in the Metering data warehouse, that is, Metering[i][MT] = BadDebt_T.

[0149] (3.4.2)Traverse the next measurement data, and repeat step (3.4.1) until all the measurement data under the current project in the Metering data warehouse has been traversed, then proceed to step (3.5).

[0150] Specifically, for the data in Table 8, use the [Maximum Boundary Value of the Number of Months in the Account Age Period] field in Table 1 for calculation. The calculation process, account age distribution, and bad debt amount results are shown in Table 9:

[0151] Table 9

[0152]

[0153] According to the [Number of bad debts in this period] column in Table 9, update the number of bad debts in the corresponding aging period in the metering data of the "P0000001" project in the Metering warehouse. The results are shown in Table 10:

[0154] Table 10

[0155]

[0156] (3.5) Summary of bad debts

[0157] According to the project number, count and update the cumulative bad debt number BadDebt=SUM(BadDebt_T) of the current project in each aging time period of the Metering data warehouse, and update it to the bad debt number of each aging time period of the Project data warehouse. Traverse the next project and repeat step (3.1.1) until the traversal of the Project data warehouse is completed.

[0158] Specifically, the number of bad debts in each aging period is summarized according to the [Item Number] field in Table 9. The results are shown in Table 11:

[0159] Table 11

[0160]

[0161] The above is only a preferred specific implementation of the present invention, but the protection scope of the present invention is not limited thereto. Any changes or substitutions that can be easily thought of by any person skilled in the art within the technical scope disclosed in the present invention should be included in the protection scope of the specification. Therefore, the protection scope of the present invention should be based on the scope of the claims.

Claims

1. A self-service analysis method for accounts receivable aging based on group iteration, characterized in that: The following steps are involved: (1) Establish data warehouses: including establishing the Accrual data warehouse for bad debt provision ratio, establishing the Project data warehouse, establishing the Metering data warehouse for project measurement, and establishing the Receipt data warehouse for project receipts; (2) Data cleaning: Establish project scheduled tasks, project metering scheduled tasks, and project receipt scheduled tasks; at a fixed time each month, extract the basic information, metering data, and receipt data of all projects generated by the end of the previous month from the project business, metering business, and receipt business data and clean them into the three data warehouses of Project, Metering, and Receipt respectively; (3) Perform aging analysis: (3.1) Traverse the Project data warehouse: Traverse all projects in the Project data warehouse, including: (3.1.1) Read the cumulative amount of receipts A of the current project in the Receipt data warehouse and all metering data of the current project in the Metering data warehouse according to the project number, and proceed to step (3.1.2); (3.1.2) Determine whether there is a negative measurement in the measurement data of the current project. If there is a negative measurement, go to step (3.2); otherwise, go to step (3.3); (3.2) Check and reduce the metering load: Traverse all metering data of the current project in the Metering data warehouse in the positive order of metering time, including: (3.2.1) Define the amount to be reduced: B=0, the accumulated amount to be reduced: C=0, and the remaining amount to be reduced: D=0; take the index from 0 to the first time that the current period's metering amount is negative, form the sub-array SubMetering to be reduced, and proceed to step (3.2.2); (3.2.2) Traverse the subarray SubMetering in reverse order of metering time, set the amount to be deducted B = SubMetering[0][Current Period Metering Amount], and proceed to step (3.2.3) to deduct B; (3.2.3) If the current period metering amount E<0, then the reduced amount E=0; otherwise, if E<=D, the current period metering amount is completely reduced, set C=C+E, and the remaining reduced amount D=BC, then set E=0; otherwise, it is a partial reduction, set the reduced amount E=ED, C=C+D, then set the remaining reduced amount D=BC, update the reduced amount E to the current period metering amount field in the metering warehouse, and go to step (3.2.4); (3.2.4) Determine whether B has been reduced: If D = 0, B has been reduced, and the SubMetering traversal is exited and the process goes to step (3.2.5); otherwise, the process continues to traverse the SubMetering and repeats step (3.2.3) until the SubMetering reduction is completed and the process goes to step (3.2.5); (3.2.5) Repeat step (3.1.2) until there is no negative metering under the current project in the Metering warehouse, and then proceed to step (3.3); (3.3) Calculate the accrual base: Define the variable SE for the cumulative measurement amount at the end of this period of the current project, initially set SE = 0, and traverse all measurement data of the current project in the Metering data warehouse in ascending order of measurement time, including: (3.3.1) Set SE = SE + E, compare SE with the cumulative received amount A of the current project. If SE - A <= 0, it means there is no uncollected measurement, that is, the accrual base G for this period of measurement = 0; if SE - A < E, G = SE - A; otherwise, G = E. Update the accrual base G for bad debts in this period to the bad debt accrual base field in the Metering data warehouse, and go to step (3.3.2); (3.3.2) Continue to traverse the next measurement data, repeat step (3.3.1) until all measurement data of the current project in the Metering data warehouse are traversed, and then go to step (3.4); (3.4) Calculate the bad debt amount: Traverse all measurement data of the current project in the Metering data warehouse in ascending order of measurement time, including: (3.4.1) Set the number of months M of the current period's aging as the total number of months between the analysis time and the current period's measurement time. Traverse the Accrual data warehouse in ascending order of the month number boundary value, set F as the smallest boundary value greater than M, and take the accrual ratio corresponding to F as the bad debt accrual ratio R for this period. Jump out of traversing the Accrual data warehouse, set the bad debt amount BadDebt_T = G * R, and update BadDebt_T to the bad debt amount in the corresponding aging time period in the Metering data warehouse; (3.4.2) Traverse the next measurement data, repeat step (3.4.1) until all measurement data under the current project in the Metering data warehouse are traversed, and then go to step (3.5); (3.5) Aggregate the bad debt amounts: According to the project number, count and update the cumulative bad debt amount BadDebt = SUM(BadDebt_T) for each aging time period of the current project in the Metering data warehouse, and update it to the bad debt amount in each aging time period of the Project data warehouse. Traverse the next project and repeat step (3.1.1) until the traversal of the Project data warehouse is completed.

2. The group-iterative self-service analysis method for accounts receivable aging according to claim 1 is characterized in that: The established Accrual data warehouse for bad debt accrual ratios contains fields such as payment nature code, payment nature name, aging time period, maximum boundary value of the number of months in the aging time period, and accrual ratio field, and prefabricates bad debt accrual ratio data according to the requirements specified in the enterprise's financial management documents.

3. The group-iterative self-service analysis method for accounts receivable aging according to claim 1 is characterized in that: The established Project data warehouse contains fields such as project number, project name, project amount, main unit, project area, province / city, owner name, 0 - 6 months, 7 - 12 months, 1 - 2 years, 2 - 3 years, 3 - 4 years, 4 - 5 years, and over 5 years.

4. The group-iterative self-service analysis method for accounts receivable aging according to claim 1 is characterized in that: The established Metering data warehouse for project measurement contains fields such as project number, measurement amount in this period, measurement time in this period, accrual base for bad debts in this period, 0 - 6 months, 7 - 12 months, 1 - 2 years, 2 - 3 years, 3 - 4 years, 4 - 5 years, and over 5 years.

5. The group-iterative self-service analysis method for accounts receivable aging according to claim 1 is characterized in that: The established Receipt data warehouse for project collections contains fields such as project number and cumulative received amount.

6. The group-iterative self-service analysis method for accounts receivable aging according to claim 1 is characterized in that: The extracted content in step (2) includes: ① Basic project information includes: project number, project name, project amount, main unit, project area, province / city, and owner's name; ② Project measurement data includes: project number, current measurement amount, and current measurement time; ③Project collection data includes: project number, current period collection amount, and current period collection time.

7. The group-iterative self-service analysis method for accounts receivable aging according to claim 1 is characterized in that: The cleaning rules in step (2) include: ① Basic project information: First clear the Project data warehouse, and then write the basic information of each approved project into the Project data warehouse. The written content includes: project number, project name, project amount, main unit, project region, province / city, owner name; the number of bad debts in each aging time period is 0-6 months, 7-12 months, 1-2 years, 2-3 years, 3-4 years, 4-5 years, and more than 5 years, and the initial value is 0; ② Project metering data: First clear the metering warehouse, then write the metering data of each approved project into the metering warehouse. The written content includes: project number, current period metering amount, current period metering time; current period bad debt provision base and bad debts from 0 to 6 months, 7 to 12 months, 1 to 2 years, 2 to 3 years, 3 to 4 years, 4 to 5 years, and more than 5 years. The initial value is 0; ③ Project collection data: First clear the Receipt data warehouse, and use the extracted project collection information as the statistical unit with the project number. Count the cumulative collection amount of each project as of the end of the previous month and write it into the Receipt data warehouse. The written content includes: project number and cumulative collection amount.

Images