Create reminder letter with Excel combined with financial software

At the beginning of the accounting to carry out arrears clean-up work, those who do not collect back in the New Year's initial recovery, then we will make reminders letter, urge the other party to remit the arrears. Next, I'll talk about how I make collection letters with Excel in combination with financial software .

　　Export Arrears Data

Objective: To derive the relevant data of arrears from the financial software

Now most of the units are using financial software, therefore, when the basic data of the arrears, from the financial software directly exported, such as in the wave of financial software, into the "Account processing" module, the implementation of the "account Sheet" → "balance table" command, in the pop-up "Account Balance Table" dialog box, Select the fiscal period for December 2010 (Figure 1), the ledger account is "Accounts receivable", click the "OK" button, and in the accompanying account balance table, export the "accounts receivable" detail data as an Excel table, and name it "the end of 2010 accounts receivable schedule. xls".

Organize the list of derived receivables, retain only the account name, summary, date, debit, credit data in which the account name is the unit of arrears, add the Balance column name of the receivable (the receivable is a professional term, in fact, the arrears), and in the first G2 to calculate the balance of each unit of arrears, Enter the array formula "{=sum (B$2:B2=B2) * (e$2:e2))-sum ((B$2:B2=B2) * (F$2:F2))}" to automatically calculate the balance of arrears for each unit of arrears.

　　Time to analyze arrears

Objective: To understand what companies with overdue arrears

Know that the number of each unit owed is not enough, but also to know that their arrears of time (the general arrears of more than 6 months can be counted as overdue), the selection of overdue arrears of the company. Create a new "Accounts Receivable aging Analysis Table" (Aging is a professional term, is owed time).

Enter the base date in the G2 of this table, such as December 31, 2010, enter the column account name in the F4:l4 area, 0-2 months, secondary 1, 2-6 months, auxiliary 2, 6 months, total, and copy the account name (that is, the arrears unit) that needs to be analyzed into column F.

Then in the 0-2 months of G5 input array formula "{=if (H5>0,sum) (Accounts receivable schedule! b$2:b$100= Accounts receivable aging Analysis Table! F5) * ($G $2-accounts receivable schedule! D$2:D$100<=60) * (Accounts receivable schedule!) e$2:e$100)), SUM (account receivable schedule!) b$2:b$100= Accounts receivable aging Analysis Table! F5) * ($G $2-accounts receivable schedule! D$2:D$100<=60) * (Accounts receivable schedule!) e$2:e$100)) +h5)} ".

In H5 (auxiliary 1), I5 (2-6 months), J5 (auxiliary 2), J5 (6 months or more), L5 (total), enter the corresponding formula to know the time of each amount owed, and which arrears overdue.

　　Print collection letter

Purpose: To generate collection letters automatically

When you know which companies need reminders, they should send a collection letter (official name is called reconciliation letter), create a new worksheet in which to enter the basic collection information, place the arrears in the C3, and enter the formula in the G5 that stores the amount owed =if ($C $3= "", "", INDEX (should Account Receivable Aging Analysis Table! $L $: $L $202,match ($C $, Accounts receivable aging Analysis Table!) $F $: $F $202,0)) "So when you enter the name of the unit of arrears in C3, for example Qingdao run Hao, in the Reconciliation collection letter will automatically show the amount of arrears of 1, 215,934.24 Yuan, then print and cover the financial special chapter can be sent to the other company (Figure 2).