Bookkeeping Learned with Python-The Flow of Bookkeeping-

Introduction

The purpose of this article is to code and understand the flow of bookkeeping in Python (mainly Pandas).

Since the purpose is to understand the general flow, some items that appear in bookkeeping textbooks are omitted. For example

--Calculation of cost of sales based on product inventory and purchase amount --Creating a spreadsheet --Carryover to the next term

Matters such as are omitted.

0. What is bookkeeping?

This is a procedure to record transactions made by a company as ** journals ** and create ** balance sheet (B/S) ** and ** income statement (P/L) **.

The image of ** Journal ** is as follows. image.png

The left side of the journal is called the debit ** and the right side is called the credit **. (Unfortunately, this is a promise I have to remember ...)

** Balance Sheet (B/S) ** is the financial position of a company, to put it plainly "How much wealth does a company have now?" It is a table showing.

image.png

** Income Statement (P/L) ** is the business performance of a company, to put it plainly "How much money did the company make (or lose) in a certain period of time?" It is a table showing.

image.png

Roughly speaking, the bookkeeping procedure is performed in the following steps 1 to 3.

** 1. Creating journals **

Record the transaction as a ** journal ** in the ** journal **.

** 2. Creating a trial balance (T/B) **

Create a ** balance trial balance ** by aggregating the debit amount and credit amount for each account from the journal.

** 3. Preparation of balance sheet (B/S) and income statement (P/L) **

Divide the trial balance into ** balance sheet (B/S) ** and ** income statement (P/L) **. (Note 1)

In addition, ** 1. When creating a journal **, it is necessary to follow the following four rules.

--Journal rule (1): Enter ** account ** and ** amount ** in ** debit (left side) ** and ** credit (right side) ** --Journal rule (2): The total amount of debit and credit must match ** --Journal rule ③: Increase ** assets ** on the debit side, and increase ** liabilities ** and ** net assets ** on the credit side --Journal rule ④: ** Expense ** is debited, ** Revenue ** is credited

... So far, terms specific to bookkeeping have appeared one after another. Especially if you are a beginner in bookkeeping, you may be confused by the number of terms.

First of all, in the world of bookkeeping

--Representing corporate transactions in the form of ** journals ** --The left side of the journal is called the debit ** and the right side is called the credit **.

Please check only that.

Other terms will be explained by quoting the rules ① to ④ each time when creating a journal.

(Note 1) In the bookkeeping textbook, it says that a "closing journal" is created between 1 and 2, and a "settlement table" is created between 2 and 3. ** Trial balance → Settlement journal → Balance sheet, Income statement ** The settlement table records the numbers of. However, in the field of accounting work, it seems that there are many cases where closing journals are performed without distinguishing them from ordinary journals. In addition, 2 → 3 is automatically generated on the system, and it seems that there are many cases where you do not bother to create a settlement table, so I will omit it in this paper. (It is a necessary table when preparing financial statements by hand like a bookkeeping test.)

1. Creating journals

【Thing you want to do】 Record the transaction as a ** journal ** in the ** journal **.

[Coding policy] Enter your journals in Pandas DataFrame format into the journal ** df_siwake **. Add and update a row of journal data to ** df_siwake ** by calling a function ** entry ** of the ** Siwake ** class.

Journal rule ①: Describe the account and amount on the debit (left side) and credit (right side)

According to, create a journal ** df_siwake ** as an empty DataFrame for entering journals.

import pandas as pd
df_siwake = pd.DataFrame(index=[],
                         columns=['Journal number', 'date', 'Debit subject', 'Debit amount', 'Credit subject', 'Credit amount'])

For example (Debit) Cash / (Credit) Capital Every time you enter the journal, according to the type of DataFrame

df_siwake.append(pd.Series([1, 20200401, 'cash', 1000, 'Capital', 1000], index=df_kamoku.columns), ignore_index=True)

You can write the code and add a line of DataFrame,

--Cannot enter compound journals (journals that do not have a one-to-one correspondence between debits and credits) ... ① --At the time of input, it is not possible to confirm whether the data type of the journal is correct (such as entering a character string in the debit amount) ... ② --At the time of input, it is not possible to confirm whether the journals are balanced and borrowed ... ③

There is a problem with.

  • In the world of bookkeeping, it is very important that ③ is observed. Since the balance is always the same when you enter the journal, you can also make a trial balance later. It is guaranteed that the loan amounts will match.

Therefore, define a class ** Siwake ** for journal entry, and first of all, ① in a form corresponding to the entry of compound journals. Define the function ** entry **.

  • Finally, the functions of (2) data type check and (3) balance check will be implemented in ** Siwake **. Since the code will be long, I will add it as a supplement at the end.
class Siwake:
    def __init__(self):
        self.siwake_no = 0

    def entry(self, df, date, kari, kashi): 
        self.siwake_no += 1 # ...Update journal number
            
        for i in range(len(kari)): # ...To support compound journals[Debit item, debit amount]Turn the loop as many times as
            kari_entry = pd.Series([self.siwake_no] + [date] + kari[i] + ["", 0], index=df.columns)          
            df = df.append(kari_entry, ignore_index=True)
            
        for i in range(len(kashi)): # ...To support compound journals[Credit item, credit amount]Turn the loop as many times as
            kashi_entry = pd.Series([self.siwake_no] + [date] + ["", 0] + kashi[i], index=df.columns)
            df = df.append(kashi_entry, ignore_index=True)
            
        return df

Supplement the comments in class ** Siwake **.

In order to record journals in chronological order in the journal, journal number ** siwake_no ** is defined as a class variable. Every time a journal is created (the function entry is called)

self.siwake_no += 1 # ...Update journal number

The journal number is updated as a serial number.

In addition, although this paper deals with only one-line journals for both debit and credit subjects, in practice it is common to create multi-line journals. In order to support compound journals, loop as follows to generate pd.Series for each debit and credit, and then Adding a line to the journal ** df_siwake **.

for i in range(len(kari)): # ...To support compound journals[Debit item, debit amount]Turn the loop as many times as
    kari_entry = pd.Series([self.siwake_no] + [date] + kari[i] + ["", 0], index=df.columns)          
    df = df.append(kari_entry, ignore_index=True)

for i in range(len(kashi)): # ...To support compound journals[Credit item, credit amount]Turn the loop as many times as
    kashi_entry = pd.Series([self.siwake_no] + [date] + ["", 0] + kashi[i], index=df.columns)
    df = df.append(kashi_entry, ignore_index=True)

That concludes the supplementary explanation of class ** Siwake **.

Enter the function ** entry ** in the following format.

siwake.entry(df_siwake:Journal, date:date,
             kari :[[Credit item 1,Debit amount 1], [Credit item 2,Debit amount 2], ... ],
             kashi:[[Credit subject 1,Credit amount 1], [Credit subject 2,Credit amount 2], ... ])

The following line will be added to the journal ** df_siwake **.

index Journal number date Debit subject Debit amount Credit subject Credit amount
0 siwake_no
(Numeric value)
date
(Numeric value)
kari[0][0]
(Character string)
kari[0][1]
(Numeric value)
""
(Character string)
0
(Numeric value)
1 siwake_no
(Numeric value)
date
(Numeric value)
""
(Character string)
0
(Numeric value)
kashi[0][0]
(Character string)
kashi[0][1]
(Numeric value)

Now create an instance of class ** Siwake ** and

siwake = Siwake()

The most basic cycle of company management

  1. Establish a company
  2. Purchasing products
  3. Sale of goods
  4. Payment of purchase price
  5. Collection of sales price

We will create a journal for each transaction of.

Journal ①: Establish a company

I established a company with 1000 cash.

This source is called ** capital **, and the journal entries are as follows.

df_siwake = siwake.entry(df_siwake, 20200401,
                         [['cash', 1000]],
                         [['Capital', 1000]])

df_siwake[df_siwake['Journal number']==siwake.siwake_no]
index Journal number date Debit subject Debit amount Credit subject Credit amount
0 1 20200401 cash 1000 0
1 1 20200401 0 Capital 1000

The cash paid to the company belongs to the company, that is, it is an ** asset **, so it is listed as ** debit **.

On the other hand, the capital belongs to the shareholders who paid 1000 cash (if it is a joint-stock company). It is called ** net worth ** and is listed on ** credit **.

It is important that the debit amount 1000 and the credit amount 1000 always match.

Let's move on a little bit, but the credit's net worth is roughly speaking.

** Capital paid at the time of company establishment + Profit earned after company establishment **

It consists of. Accumulate profits by doing business and return it to shareholders (if it is a joint-stock company) as a dividend, That is the basic cycle of the company.

Since the profits earned after the establishment of the company come to the net assets of the credit,

--Generation of revenue → Increase credit profit → Described in credit --Incurred costs → Reduce credit profit → Described in debit

That is, Journal rule ④: Expenses are debited and revenues are credited Is guided.

Journal ②: Purchasing products

I purchased product 500 from an outside vendor. The journals are as follows.

df_siwake = siwake.entry(df_siwake, 20200402,
                         [['Product', 500]],
                         [['Accounts payable', 500]])

df_siwake[df_siwake['Journal number']==siwake.siwake_no]
index Journal number date Debit subject Debit amount Credit subject Credit amount
2 2 20200402 Product 500 0
3 2 20200402 0 Accounts payable 500

The purchased goods are from the company, that is, ** assets **, so debit them. (Rule ③: Increase in assets is debited, and increases in liabilities and net assets are credited)

In transactions between companies, the purchase price is paid with the supplier after XX days, etc. Mostly deferred payment. (Of course, you may pay on the spot)

This debt (obligation to pay the price) until payment is called ** accounts payable **.

Accounts payable will be credited to outsiders in the future, that is, they are ** liabilities **. (Rule ③: Increase in assets is debited, and increases in liabilities and net assets are credited)

Journal ③: Selling products

200 of the purchased products were sold at a price of 300. The journal entries for sales are as follows.

df_siwake = siwake.entry(df_siwake, 20200403,
                         [['accounts receivable', 300]],
                         [['Earnings', 300]])

df_siwake[df_siwake['Journal number']==siwake.siwake_no]
index Journal number date Debit subject Debit amount Credit subject Credit amount
4 3 20200403 accounts receivable 300 0
5 3 20200403 0 Earnings 300

In transactions between companies, the sales price is agreed with the sales destination, such as collection after XX days. Most of them will be collected at a later date. (Mochiran, it may be collected on the spot)

This receivable (right to receive payment) until collection is called ** accounts receivable **.

Accounts receivable will be the company's cash in the future, that is, they are ** assets **, so they will be credited **. (Rule ③: Increase in assets is debited, and increases in liabilities and net assets are credited)

Sales are ** revenue **, which has the effect of increasing the company's ** net assets (credit) **, so it is listed as ** credit **. (Rule ④: Expenses are debited and revenues are credited)

The journal entries for costs corresponding to sales are as follows.

df_siwake = siwake.entry(df_siwake, 20200403,
                         [['Cost of sales', 200]],
                         [['Product', 200]])

df_siwake[df_siwake['Journal number']==siwake.siwake_no]
index Journal number date Debit subject Debit amount Credit subject Credit amount
6 4 20200403 Cost of sales 200 0
7 4 20200403 0 Product 200

Selling a product reduces the number of ** products (debits) ** that belong to the company, so list it in the opposite ** credit **. (Rule ③: Increase assets are debited, and increases in liabilities and net assets are creditedRule'③: Decrease in assets is credited, and decrease in liabilities and net assets is debited)

As the number of products decreases, the ** cost ** corresponding to the sales of the product is recorded as ** cost of sales **. (Note 2)

Expenses have the effect of reducing the company's ** net worth (credit) **, so the opposite is stated in ** debit **. (Rule ④: Expenses are debited and revenues are credited)

(Note 2) In this paper, for the sake of simplicity, the cost of sales is recorded at the same time as the product is sold. Actually, the cost of sales is based on the "Financial Statement Journal" mentioned at the beginning. ** Cost of sales = total purchase amount + product inventory amount at the beginning of the period-product inventory amount at the end of the period ** It is calculated by the formula.

Since a product with a cost of 200 is sold at a selling price of 300, the profit of the transaction is 100.

However, the bookkeeping process does not recognize profits every time you sell a product. The purpose of bookkeeping is ** to calculate profits for a certain period (accounting period) **.

For profits, the total amount of income and expenses for the current period is totaled at the end of the period. By creating a journal entry called ** Profit and Loss Transfer ** for the profit that is the difference, Recognize at once at the end of the accounting period (see below).

Journal ④: Pay the purchase price

We paid an outside contractor for 300 of the 500 purchases. (The remaining 200 are assumed to be paid next month) The journals are as follows.

df_siwake = siwake.entry(df_siwake, 20200420,
                         [['Accounts payable', 300]],
                         [['cash', 300]])

df_siwake[df_siwake['Journal number']==siwake.siwake_no]
index Journal number date Debit subject Debit amount Credit subject Credit amount
8 5 20200420 Accounts payable 300 0
9 5 20200420 0 cash 300

By paying the purchase price, the company's assets ** cash ** (** debit **) will be reduced. The opposite is stated in ** Credit **. (Rule ③: Increase assets are debited, and increases in liabilities and net assets are creditedRule'③: Decrease in assets is credited, and decrease in liabilities and net assets is debited)

On the other hand, ** accounts payable ** (** credit **), which is the debt to pay the supplier, also disappears when the payment is completed. The opposite is stated in ** Debit **. (Rule ③: Increase assets are debited, and increases in liabilities and net assets are creditedRule'③: Decrease in assets is credited, and decrease in liabilities and net assets is debited)

Journal ⑤: Collect sales price

The sales price of 200 was collected in cash from the seller. (The remaining 100 are assumed to be collected next month) The journals are as follows.

df_siwake = siwake.entry(df_siwake, 20200430,
                         [['cash', 200]],
                         [['accounts receivable', 200]])

df_siwake[df_siwake['Journal number']==siwake.siwake_no]
index Journal number date Debit subject Debit amount Credit subject Credit amount
10 6 20200430 cash 200 0
11 6 20200430 0 accounts receivable 200

By collecting the sales price, the company's assets ** cash ** (** debit **) will increase. List in ** Debit **. (Rule ③: Increase in assets is debited, and increases in liabilities and net assets are credited)

On the other hand, ** accounts receivable ** (** debit **), which is a receivable that receives payment from the seller, also disappears when the collection is completed. The opposite is stated in ** Credit **. (Rule ③: Increase assets are debited, and increases in liabilities and net assets are creditedRule'③: Decrease in assets is credited, and decrease in liabilities and net assets is debited)

So far

  1. Establish a company
  2. Purchasing products
  3. Sale of goods
  4. Payment of purchase price
  5. Collection of sales price

I created a series of journals. Let's check the contents of the journal ** df_siwake **.

df_siwake
index Journal number date Debit subject Debit amount Credit subject Credit amount
0 1 20200401 cash 1000 0
1 1 20200401 0 Capital 1000
2 2 20200402 Product 500 0
3 2 20200402 0 Accounts payable 500
4 3 20200403 accounts receivable 300 0
5 3 20200403 0 Earnings 300
6 4 20200403 Cost of sales 200 0
7 4 20200403 0 Product 200
8 5 20200420 Accounts payable 300 0
9 5 20200420 0 cash 300
10 6 20200430 cash 200 0
11 6 20200430 0 accounts receivable 200

2. Creating a trial balance

【Thing you want to do】 Create a ** balance sheet ** (T/B: Trial Balance) by summing up the debit amount and credit amount for each account from the journal.

[Coding policy] Trial balance ** df_TB ** is now generated from the journal ** df_siwake ** In the class ** TrialBalance **, define the function ** create_tb ** and the account DataFrame ** df_kamoku **.

In anticipation of finally preparing financial statements, give the following attributes to each account in the trial balance Define ** df_kamoku ** (Note 3).

(Note 3) In practice, in addition to journals and trial balances, prepare an account master and define attributes such as item code and balance classification. It is common to enter the journal entry with the item code and quote the account name from the master.

--Course code: Display order of subjects --Category classification: Whether it belongs to assets, liabilities, or net assets --BS/PL: B/S course or P/L course --Debt classification: Whether it belongs to a debit (asset) or a credit (liability, net asset)

df_kamoku = pd.DataFrame({'Loan items': ['cash', 'accounts receivable', 'Product', 'Accounts payable', 'Capital', 'Retained earnings carried forward', 'Earnings', 'Earnings原価', 'Net income'],
                          'Subject code': ['100', '110', '120', '200', '300', '310', '400', '500', '600'],
                          'Subject classification': ['Assets', 'Assets', 'Assets', 'liabilities', 'liabilities', '純Assets', 'Revenue', 'cost', 'Profit'],
                          'BS/PL': ['BS', 'BS', 'BS', 'BS', 'BS', 'BS', 'PL', 'PL', 'PL'],
                          'Leasing classification': [1, 1, 1, -1, -1, -1, -1, 1, 1]})

And a table that aggregates the amounts of ** df_kamoku ** and the journal ** df_siwake ** for each account.

df = df_siwake.groupby('Loan items').sum()[['Lending amount']]

To combine.

It is easier to handle the debit as a plus and the credit as a minus for the convenience of counting in Pandas (Note 4).

--Credit amount: Debit amount-Credit amount

Create a column with.

(Note 4) I wrote "for convenience of aggregation", but the concepts of debit and credit, plus and minus can be said to be equivalent. ――The opposite of plus is minus, the opposite of minus is plus --The opposite of the debit is the credit, the opposite of the credit is the debit --The sum of the positive and negative numbers gives zero. --The debit and credit amounts match The incomprehensible words "debit" and "credit" are obstacles to learning bookkeeping for the first time. If you are familiar with mathematics, it may be easier to understand if you replace it with the concept of plus and minus.
class TrialBalance:
    
    df_kamoku = pd.DataFrame({'Loan items': ['cash', 'accounts receivable', 'Product', 'Accounts payable', 'Capital', 'Retained earnings carried forward', 'Earnings', 'Earnings原価', 'Net income'],
                              'Subject code': ['100', '110', '120', '200', '300', '310', '400', '500', '600'],
                              'Subject classification': ['Assets', 'Assets', 'Assets', 'liabilities', 'liabilities', '純Assets', 'Revenue', 'cost', 'Profit'],
                              'BS/PL': ['BS', 'BS', 'BS', 'BS', 'BS', 'BS', 'PL', 'PL', 'PL'],
                              'Leasing classification': [1, 1, 1, -1, -1, -1, -1, 1, 1]})
     
    def create_tb(self, df_siwake):
        df = df_siwake.copy()
        df['Loan items'] = df['Debit subject'] + df['Credit subject'] 
        df['Lending amount'] = df['Debit amount'] - df['Credit amount'] 
        df = df.groupby('Loan items').sum()[['Lending amount']]
        
        df_merge = pd.merge(df, self.df_kamoku, on='Loan items').sort_values('Subject code').reset_index(drop=True)
        
        return df_merge

Instantiate the ** TrialBalance ** class and pass the journal ** df_siwake ** to the function ** create_tb ** Create a trial balance ** df_TB **.

TB = TrialBalance()

df_TB = TB.create_tb(df_siwake)
df_TB
index Loan items Lending amount Subject code Subject classification BS/PL Leasing classification
0 cash 900 100 Assets BS 1
1 accounts receivable 100 110 Assets BS 1
2 Product 300 120 Assets BS 1
3 Accounts payable -200 200 liabilities BS -1
4 Capital -1000 300 liabilities BS -1
5 Earnings -300 400 Revenue PL -1
6 Cost of sales 200 500 cost PL 1

The credit amount for each subject (+ for debit,-for credit) is totaled, and attributes such as subject code and subject classification are added.

All you have to do is divide the trial balance into the balance sheet (B/S) and the income statement (P/L). ** Simply splitting will cause the problem that the debit and credit amounts on the balance sheet do not match **.

For confirmation, only those whose subject classification is "BS" are taken out from the balance trial balance.

df_TB[df_TB['BS/PL']=='BS']
index Loan items Lending amount Subject code Subject classification BS/PL Leasing classification
0 cash 900 100 Assets BS 1
1 accounts receivable 100 110 Assets BS 1
2 Product 300 120 Assets BS 1
3 Accounts payable -200 200 liabilities BS -1
4 Capital -1000 300 liabilities BS -1

At first glance it looks fine, but this balance sheet (B/S) is Rule ②: The total amount of debits and credits always match Does not meet.

Let's add up the debit amount (the credit amount is +) and the credit amount (the credit amount is-).

df_TB[(df_TB['BS/PL']=='BS') & (df_TB['Lending amount'] > 0)]
index Loan items Lending amount Subject code Subject classification BS/PL Leasing classification
0 cash 900 100 Assets BS 1
1 accounts receivable 100 110 Assets BS 1
2 Product 300 120 Assets BS 1
df_TB[(df_TB['BS/PL']=='BS') & (df_TB['Lending amount'] < 0)]
index Loan items Lending amount Subject code Subject classification BS/PL Leasing classification
3 Accounts payable -200 200 liabilities BS -1
4 Capital -1000 300 liabilities BS -1
print('Total debit amount:',df_TB[(df_TB['BS/PL']=='BS') & (df_TB['Lending amount'] > 0)].sum()['Lending amount'])
print('Total credit amount:',df_TB[(df_TB['BS/PL']=='BS') & (df_TB['Lending amount'] < 0)].sum()['Lending amount'])
Total debit amount: 1300
Total credit amount: -1200

You can see that the total debit amount on the balance sheet (B/S) and the total credit amount do not match.

The difference on this balance sheet (B/S) matches the difference between income and expenses on the income statement (P/L) for the current period, that is, ** net income **.

The procedure for calculating net income from the difference between income and expenses is called ** profit and loss transfer **.

The procedure for transferring ** net income ** on the income statement (P/L) to the ** retained earnings carried forward ** account on the balance sheet (B/S) is called ** capital transfer **. ..

The journal entries for capital transfers are as follows:

df_siwake = siwake.entry(df_siwake, 20200430,
                         [['Net income', 100]],
                         [['Retained earnings carried forward', 100]])

df_siwake[df_siwake['Journal number']==siwake.siwake_no]
index Journal number date Debit subject Debit amount Credit subject Credit amount
12 7 20200430 Net income 100 0
13 7 20200430 0 Retained earnings carried forward 100

Net income is the difference between sales of 300 and cost of sales of 200, which is 100.

Let's take a look at the journal ** df_siwake ** and the trial balance ** df_TB ** after the income statement and capital transfer.

df_siwake
index Journal number date Debit subject Debit amount Credit subject Credit amount
0 1 20200401 cash 1000 0
1 1 20200401 0 Capital 1000
2 2 20200402 Product 500 0
3 2 20200402 0 Accounts payable 500
4 3 20200403 accounts receivable 300 0
5 3 20200403 0 Earnings 300
6 4 20200403 Cost of sales 200 0
7 4 20200403 0 Product 200
8 5 20200420 Accounts payable 300 0
9 5 20200420 0 cash 300
10 6 20200430 cash 200 0
11 6 20200430 0 accounts receivable 200
12 7 20200430 Net income 100 0
13 7 20200430 0 Retained earnings carried forward 100
df_TB = TB.create_tb(df_siwake)
df_TB
index Loan items Lending amount Subject code Subject classification BS/PL Leasing classification
0 cash 900 100 Assets BS 1
1 accounts receivable 200 110 Assets BS 1
2 Product 300 120 Assets BS 1
3 Accounts payable -200 200 liabilities BS -1
4 Capital -1000 300 liabilities BS -1
5 Retained earnings carried forward -1000 300 liabilities BS -1
6 Earnings -300 400 Revenue PL -1
7 Cost of sales 200 500 cost PL 1
8 Net income 100 500 Profit PL 1
print('Total debit amount:',df_TB[(df_TB['BS/PL']=='BS') & (df_TB['Lending amount'] > 0)].sum()['Lending amount'])
print('Total credit amount:',df_TB[(df_TB['BS/PL']=='BS') & (df_TB['Lending amount'] < 0)].sum()['Lending amount'])
Total debit amount: 1300
Total credit amount: -1300

By performing profit and loss transfer and capital transfer, we were able to confirm that the debit amount and credit amount on the balance sheet (B/S) match.

3. Preparation of balance sheet (B/S) and income statement (P/L)

【Thing you want to do】 Divide the trial balance into the balance sheet (B/S) and the income statement (P/L).

[Coding policy] Divide the balance sheet ** df_TB ** according to whether the "BS/PL" column is'BS'or'PL'.

By multiplying the "credit amount" by the "credit classification" (+1 and -1), the amount of each debit (+) and credit (-) is returned to the absolute value. Store it in the column labeled "Display Lending".

BS = df_TB[df_TB['BS/PL']=='BS'].reset_index(drop=True)
BS['Show loan'] = BS['Lending amount'] * BS['Leasing classification']
BS[['Subject code','Subject classification', 'Loan items', 'Show loan']]
index Subject code Subject classification Loan items Show loan
0 100 Assets cash 900
1 110 Assets accounts receivable 100
2 120 Assets Product 300
3 200 liabilities Accounts payable 200
4 300 liabilities Capital 1000
5 310 Net worth Retained earnings carried forward 100
PL = df_TB[df_TB['BS/PL']=='PL'].reset_index(drop=True)
PL['Show loan'] = PL['Lending amount'] * PL['Leasing classification']
PL[['Subject code','Subject classification', 'Loan items', 'Show loan']]
index Subject code Subject classification Loan items Show loan
0 400 Revenue Earnings 300
1 500 cost Cost of sales 200
2 600 Profit 当期Profit 100

The balance sheet (B/S) and income statement (P/L) have been created. The image diagram is shown below.

image.png image.png

Looking back again,

** 1. Creating journals **

Record the transaction as a ** journal ** in the ** journal **.

** 2. Creating a trial balance (T/B) **

Create a ** balance trial balance ** by aggregating the debit amount and credit amount for each account from the journal.

** 3. Preparation of balance sheet (B/S) and income statement (P/L) **

Divide the trial balance into ** balance sheet (B/S) ** and ** income statement (P/L) ** according to the item.

In the flow of, ** totaling daily transactions and preparing financial statements ** is the procedure for one round of bookkeeping.

Supplement: Error check when creating journals

Define the functions of (2) data type check and (3) balance check in the journal entry class ** Siwake **.

--Check when entering whether the data type of the journal is correct ... ② --Check when entering whether the journals are balanced and borrowed ... ③

Implement as follows.

class Siwake:
    def __init__(self):
        self.siwake_no = 0

    def entry(self, df, date, kari, kashi):             # ...① Supports compound journals
            if self.check_keishiki(date, kari, kashi):  # ...② Data type check
                if self.check_taisyaku(kari, kashi):    # ...③ Balance check
                    self.siwake_no += 1                 # ...Update journal number
            
                    for i in range(len(kari)): #To support compound journals[Debit item, debit amount]Turn the loop as many times as
                    
                        #Convert journal number, date, debit item, debit amount, credit item, credit amount to Sereis and store in DataFrame
                        #The credit subject is"", Credit amount is 0
                        kari_entry = pd.Series([self.siwake_no] + [date] + kari[i] + ["", 0], index=df.columns)          
                        df = df.append(kari_entry, ignore_index=True)
            
                    for i in range(len(kashi)): #To support compound journals[Credit item, credit amount]Turn the loop as many times as
                    
                        #Convert journal number, date, debit item, debit amount, credit item, credit amount to Sereis and store in DataFrame
                        #The debit item is"", The debit amount is 0
                        kashi_entry = pd.Series([self.siwake_no] + [date] + ["", 0] + kashi[i], index=df.columns)
                        df = df.append(kashi_entry, ignore_index=True)
            
                    return df
            
                else:
                    print("Error: Match loan amounts")
                    return df

            else:
                pass

    
    def check_keishiki(self, date, kari, kashi):# ...② Data type check
        for i , k in zip(range(len(kari)),range(len(kashi))):
            if type(date) != int or len(str(date)) != 8:
                print("Error: Enter the date as an 8-digit integer yyyymmdd")
                return False                       
            
            elif len(kari[i]) != 2:
                print("Error: Please enter only the debit item and debit amount")
                return False                
            
            elif type(kari[i][0]) != str or type(kari[i][1]) != int:
                print("Error: Data type should be debit item → character string, debit amount → number")
                return False
            
            elif len(kashi[k]) != 2:
                print("Error: Please enter only the credit item and credit amount")
                return False                
            
            elif type(kashi[k][0]) != str or type(kashi[k][1]) != int:
                print("Error: The data type should be credit item → character string, credit amount → number")
                return False
            
            else:
                return True                    
                                        
    def check_taisyaku(self, kari, kashi):# ...③ Balance check
        kari_sum = 0
        kashi_sum = 0
        
        for i in range(len(kari)):
                kari_sum += kari[i][1]
        
        for i in range(len(kashi)):
                kashi_sum += kashi[i][1]       
        
        if kari_sum != kashi_sum:
            return False
        
        else:
            return True

Create an instance of the Siwake class and create an invalid journal.

siwake = Siwake()

Try entering an invalid value for "Date".

df_siwake = siwake.entry(df_siwake, 1201,
             [['cash', 500]],
             [['Capital', 1000]])
Error: Enter the date as an 8-digit integer yyyymmdd

Let's enter a journal that does not match the loan amount.

df_siwake = siwake.entry(df_siwake, 20200401,
             [['cash', 500]],
             [['Capital', 1000]])
Error: Match loan amounts

I will try to enter a journal with extra items.

df_siwake = siwake.entry(df_siwake, 20200401,
             [['cash', 500, 'Trader A']],
             [['Capital', 1000, 'Trader A']])
Error: Please enter only the debit item and debit amount

Try to enter a journal with an incorrect data type.

df_siwake = siwake.entry(df_siwake, 20200401,
             [['cash', 500]],
             [['Capital', '1000']])
Error: The data type should be credit item → character string, credit amount → number

In practice, check if the input value is empty, check if the account is in the item master, etc. Various check items can be considered, but in this article, only the minimum checks are carried out.

in conclusion

I posted an article on Qiita for the first time.

When I actually output it, it was a very interesting experience, as it came to light one after another that I didn't understand Python or bookkeeping.

From now on

--Evaluation of product inventory and cost of sales --Depreciation of fixed assets --Procedure to carry over to the next term

I would like to see if it can be implemented.

Recommended Posts