abechan
年が明ければ
明けましておめでとうございます。
弊社ではフリーランスのパートナーの方とお仕事させて頂く機会が多いです。かく言う私も入社する前は、半年ほどフリーランスのパートナーとしてKRAYで業務をしていました。
さて、この時期！フリーランスの方に待っているのは確定申告という厄介な強敵です。私も昨年、初めて確定申告をしました。そして、昨年の１〜２月の事業所得があるので今年も確定申告しに行ってきます。
今日は、フリーランスの確定申告をテーマにブログを書きます。会社員としてお勤めの方には少々退屈かもしれませんが、雰囲気だけでも掴んでいただける様に頑張りますので、どうか最後までお付き合い下さい。
昨年の私
もともとExcelをこよなく愛する人間だったため、昨年の確定申告ならびに帳簿作成にはExcelを使いました。
こんな感じです。
ですが、Excelで頑張るのも結構無理があり。。。
=IF($H$2=$K7,IF($L7="借方",$B7+$D7-$C7-$E7, 0),"")
こんなのや
=IF($B8="借方",INDIRECT($A8&"!$G$1"),0)
=VLOOKUP($A4,試算表!$A:$F,5,FALSE)
こんな数式が、びっしりとセルに埋め込まれることになります。メンテナンス性も悪いうえ、処理も少し重いのです。
今年の私
最近思うのですが、Excelの起動時間よりGoogle Spredsheetのレスポンスの方が速かったりします。ましてや、普段Gitにお世話になりっぱなしだと、テキストベースのバージョン管理に慣れてしまいます。そろそろExcelを使い続けるのも苦しくなってきました。
どうにかならないものか？データの集合を扱うのに便利なツールは無いものか？ありました！ソフトウェアエンジニアならお馴染みのデータを効率よく扱えるツール。リレーショナルデータベースです。
そんな訳で、今年の確定申告はMySQLでやってみます。
やってみよう！
さて、MySQLで確定申告と勇み立ってみたものの、作ろうとしているものが曖昧なままでは、良いプロダクト？は作れません。本節では作ろうとしているものに必要な背景知識について簡単に解説します。また、本節の最後ではどこまでMySQLでやるかのスコープを定めたいと思います。
青色申告制度
デキるフリーランスの方であれば青色申告をご存知と思います。
青色申告制度の概要
我が国の所得税は、納税者が自ら税法に従って所得金額と税額を正しく計算し納税するという申告納税制度を採っています。
1年間に生じた所得金額を正しく計算し申告するためには、収入金額 や必要経費に関する日々の取引の状況を記帳し、また、取引に伴い作成したり受け取ったりした書類を保存しておく必要があります。
ところで、一定水準の記帳をし、その記帳に基づいて正しい申告をする人については、所得金額の計算などについて有利な取扱いが受けられる青色申告の制度があります。
青色申告をすることができる人は、 不動産所得、事業所得、山林所得のある人です。
有利な取扱いってなんでしょう？
続く4 青色申告の特典 (1) 青色申告特別控除 に書いてあります。
不動産所得又は事業所得を生ずべき事業を営んでいる青色申告者で、これらの所得に係る取引を正規の簿記の原則、一般的には複式簿記により記帳し、その記帳に基づいて作成した貸借対照表を損益計算書とともに確定申告書に添付して確定申告期限内に提出している場合には、原則としてこれらの所得を通じて最高65万円を控除することとされています。
また、それ以外の青色申告者については、不動産所得、事業所得及び山林所得 を通じて最高10万円を控除することとされています。
65万円貰えるわけではありません。ですが、課税所得が330万円の人が65万円の所得控除を受けた場合、還付される(※1)税金はざっくり(※2)以下となります。
控除前の課税所得 = 3,300,000
所得税額 = 1,950,000 x 0.05 + (3,300,000 - 1,950,000) x 0.10
= 232,500
控除後の課税所得 = 3,300,000 - 650,000
= 2,650,000
所得税額 = 1,950,000 x 0.05 + (2,650,000 - 1,950,000) x 0.10
= 167,500
還付される税金 = 232,500 - 167,500
= 65,000
参考) 国税庁HP No.2260 所得税の税率
※1 還付されるのは源泉徴収されている場合です
※2 簡略化のため復興特別所得税については考慮していません
その差額、なんと￥65,000！ 馬鹿にできません。日本の所得税は累進課税ですので、例えば課税所得が695万円のバリバリのフリーランスの方であれば、20%分の￥130,000が還付されます。Wow!!
続いて、￥65,000を奪還するために 65万円の所得控除を受けるために必要な事を説明します。
提出するのは損益計算書と貸借対照表
上で引用した 4 青色申告の特典 (1) 青色申告特別控除 では以下の記述がありました。
所得に係る取引を正規の簿記の原則、一般的には複式簿記により記帳し、その記帳に基づいて作成した貸借対照表を損益計算書とともに確定申告書に添付して確定申告期限内に提出
実際に提出が必要なのは所得税青色申告決算書という書類になるのですが、その大部分は損益計算書と貸借対照表が占めると言ってしまって良いと思います。
損益計算書と貸借対照表は以下の様な感じです。
(国税庁HP 所得税青色申告決算書（一般用）【平成25年分以降用】 より)
経理の知識が無いとぱっと見よく解らないと思います。ひとまず、どちらの用紙にも出てくる科目という言葉に着目して下さい。後で説明する複式簿記では所得に係る取引を科目に分けて記録します。これらの科目ごとの記入欄には、その年の取引について科目ごとに集計した金額を記入することになります。
記帳義務
提出する書類は貸借対照表と損益計算書があればよいのですが、実は提出書類以外にも記帳義務というものが存在します。
青色申告者に対しては種々の特典がありますが、その一つに所得金額から最高65万円又は10万円を控除するという青色申告特別控除があります。
1 65万円の青色申告特別控除
この65万円の控除が受けられるための要件は、次の様になっています。
(1) 不動産所得又は事業所得を生ずべき事業を営んでいること。
(2) これらの所得に係る取引を正規の簿記の原則(一般的には複式簿記)により記帳していること。
(3) (2)の記帳に基づいて作成した貸借対照表及び損益計算書を確定申告書に添付し、この控除の適用を受ける金額を記載して、法定申告期限内に提出すること。
（注）
1 現金主義によることを選択している人は、65万円の青色申告特別控除を受けることはできません。
2 不動産所得の金額又は事業所得の金額の合計額が65万円より少ない場合には、その合計額が限度になります。ただし、この合計額とは損益通算前の黒字の所得金額の合計額をいいますので、いずれかの所得に損失が生じている場合には、その損失をないものとして合計額を計算します。
3 不動産所得の金額、事業所得の金額から順次控除します。
2 10万円の青色申告特別控除
この控除は、上記1の要件に該当しない青色申告者が受けられます。
「正規の簿記の原則(一般的には複式簿記)により記帳していること」これが、65万円の青色申告特別控除を受けるための記帳義務になります。
記帳義務違反における罰則
日本税理士会連合会の資料によると、記帳義務を怠っていたとしても法律的な罰則は無いとのことです。
ですが、以下の一文があります。
(日本税理士会連合会 – 青色申告制度のあり方について より)
記帳義務違反がある場合には、青色申告の特典を不適用とするペナルティーがある。
つまりは￥65,000持ってかれる青色申告特別控除が受けられなくなるかもしれないということです。
少なくとも65万円の控除を受けるための要件である
(2) これらの所得に係る取引を正規の簿記の原則(一般的には複式簿記)により記帳していること。
が満たされなくなるので￥55,000は持ってかれます65万円の控除額が10万円になってしまうことは十分に考えられます。ですので、しっかりと記帳義務を果たしましょう。
詳細は国税庁HP 個人で事業を行っている方の記帳・帳簿等の保存についてを参照下さい。
さて、次はいよいよ複式簿記について説明します。これが出来なければ損益計算書と貸借対照表を作成するのは現実的ではありません。
複式簿記
複式簿記には必ず作成しなければならない帳簿があります。仕訳帳と総勘定元帳です。
勘定科目とはお金の出入りの種別を表します。借方/貸方については慣れないとややこしいのですが、ポイントがあります。基本的に、入ってくるものは左側(借方)、出て行くものは右側(貸方)と覚えましょう。
総勘定元帳は以下の様な感じになります。
総勘定元帳は勘定科目ごとに作成します。別の言い方をすると、仕訳帳に記載されている内容を勘定科目ごとに整理し、転記したものと言えます。また、今現金がいくらあるのか？未収の売上はいくらあるのか？などがすぐに解るように残高も記入します。手書きの帳簿であれば、仕訳帳に記入して総勘定元帳に都度転記していかなければいけません。ですが、基のデータは仕訳帳です。そのため、仕訳帳さえあれば計算で出せるのです。
最終的には年末に総勘定元帳に記入されている残高を基に、試算表を作成します。そして、決算処理を行った後、試算表と決算整理仕訳から精算表を作成します。貸借対照表と損益計算書には精算表にまとめられた勘定科目ごとの金額を記入します。
この精算表まで出来てしまえば、あとは損益計算書と貸借対照表に写していくだけです。
その他の帳簿について
青色申告の解説サイトなどを見ると、各種補助簿について説明があります。大抵、どこのサイトも必要に応じて作成すると但し書きがあります。こちらはケースバイケースなのでしょうが、参考までに私のケースを基に必要性をまとめてみました。
| 補助簿 | 要否 | 私のケース |
|---|---|---|
| 現金出納帳 | 不要 | 経費に計上する出費は全て仕訳帳の摘要に内容を記入している。 レシートと突き合せられる状態にしたいのでまとめて記入することは無い。 |
| 預金出納帳 | 不要 | 個人用の口座と事業用の口座が一緒。 銀行取引明細の全てを仕訳帳に記入している。 |
| 仕入帳 | 不要 | 物を売る仕事じゃないので仕入れない。 |
| 買掛帳 | 不要 | 物を売る仕事じゃないので買掛けない。 |
| 売掛帳 | 不要 | 請求書を出したら仕訳帳に売掛金として記入する。 請求書の控えは別途保管。 |
| 経費帳 | 不要 | 経費に計上する出費は全て仕訳帳の摘要に内容を記入している。 レシートと突き合せられる状態にしたいのでまとめて記入することは無い。 |
| 固定資産台帳 | 必要 | 10万円以上のソフトウェア・PCを持っていないが、 開業費償却で10万円ほどまとめて計上したのでそれだけ記録している。 |
ソフトウェア開発のフリーランスにとって、必要な補助簿はごく僅かと思います。私は仕訳帳・総勘定元帳以外では固定資産台帳しか記帳していません。
スコープを定める
本節の最初でMySQLでどこまでやるかのスコープを定めると書きました。
本稿ではMySQLで以下を実現しようと思います。
- 仕訳帳の作成
- 総勘定元帳の作成
- 試算表の作成
- 精算表の作成
- 税務調査の際に税務署の方に提示出来るようにプリントアウト出来る形に出力
今度こそやってみよう！
通常の業務では考えられませんが、解りやすさのためにMySQLの各テーブル、およびカラム名には日本語を使用します。以降のSQLではMySQLのcharsetがutf8に設定されている前提です。
それではガンガン行きましょう。
仕訳帳
前節で複式簿記の帳簿は仕訳帳と総勘定元帳で、総勘定元帳の基データは仕訳帳ということを書きました。仕訳帳については日々の記録が必要です。MySQLで仕訳帳を記録するにはどうするか？いちいちINSERT INTO 〜とか書きたくありません。そのため、日々の仕訳はCSVに記録してMySQLにはCSVからテーブルにインポートします。
仕訳帳テーブル
CREATE TABLE 仕訳帳 (
id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
日付 DATE,
借方勘定科目 VARCHAR(8),
借方金額 DECIMAL(10,0),
貸方勘定科目 VARCHAR(8),
貸方金額 DECIMAL(10,0),
摘要 VARCHAR(255),
備考 VARCHAR(8) -- 備考は後ほど精算表の整理記入で使用します。
-- ひとまず気にしないでください。
);
仕訳帳.csv
※サンプルデータです
〜
2014-05-30,売掛金,300000,売上,300000,株式会社○○ 4月分 請求
2014-06-01,事業主貸,100000,現金,100000,生活費
2014-06-01,通信費,10000,普通預金,10000,携帯電話
2014-06-01,現金,50000,普通預金,50000,引き出し
2014-06-05,新聞図書費,1470,現金,1470,きたみりゅうじ フリーランスを代表して申告と節税について聞いてきました
2014-06-15,現金,50000,普通預金,50000,引き出し
2014-06-15,支払手数料,108,普通預金,108,引き出し手数料
2014-06-25,普通預金,270000,売掛金,270000,株式会社○○ 4月分 入金
2014-06-25,事業主貸,30000,売掛金,30000,株式会社○○ 4月分 源泉徴収
2014-06-27,地代家賃,60000,普通預金,60000,家賃/事務所費
2014-06-27,水道光熱費,4000,普通預金,4000,東京電力
2014-06-27,水道光熱費,3000,普通預金,3000,都市ガス
2014-06-27,通信費,4000,普通預金,4000,インターネット接続使用料
2014-06-28,事業主貸,6270,普通預金,6270,カード引き落とし キラキラ 飲み代
2014-06-28,消耗品費,3348,普通預金,3348,カード引き落とし 東急ハンズ SHOT NOTE Nuboard
2014-06-28,消耗品費,9800,普通預金,9800,カード引き落とし アップル アイチューンズストア OmniGraffle for Mac
〜
複合仕訳の記入について
上記、カード引き落としの例など、一度の入金・支出に対して相手方の勘定科目が複数となるケースがあります。こういった場合、通常は以下の様に仕訳を書きます。
MySQLで簡易に処理しようとした場合、これだとややこしくなってしまいます。また、総勘定元帳に転記した際も、相手方の勘定科目が”諸口”となってしまうため、仕訳帳を見ないと取引の内容が把握できません。一般の経理ソフトなどでは、この辺りは自動的によろしくやってくれると思いますが、今回は以下の様に1回の引き落としを3つに分割して記録します。
インポート
インポートはMySQLで LOAD DATA LOCAL INFILE を使って以下の様に処理します。
LOAD DATA LOCAL INFILE '仕訳帳.csv'
INTO TABLE 仕訳帳
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\n'
(@date, 借方勘定科目, 借方金額, 貸方勘定科目, 貸方金額, 摘要, @remarks)
SET 日付 = STR_TO_DATE(@date, '%Y-%m-%d'),
備考 = IF(@remarks IS NOT NULL, @remarks, '');
総勘定元帳
仕訳帳をテーブルに入れるのは簡単に出来ました。続いて複式簿記に必須のもう一つの帳簿である総勘定元帳を作成します。ここが関門です。
総勘定元帳は各勘定科目ごとに全ての取引が記入された帳簿です。また、勘定科目ごとに貸借の区別があり、貸借によって残高の計算方法が逆になります。例えば、”売上”は貸方勘定科目になり、残高は 貸方金額の合計 – 借方金額の合計 です。一方、”普通預金”や”現金”は借方勘定科目となり、残高は 借方金額の合計 – 貸方金額の合計、といった具合です。
まずは、勘定科目のマスタテーブルを作ります。
勘定科目マスタ
CREATE TABLE 勘定科目マスタ (
id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
科目名 VARCHAR(8),
貸借区分 VARCHAR(2),
精算表 VARCHAR(5) -- 精算表の作成で使用します。
);
勘定科目マスタ.csv
現金,借方,貸借対照表
普通預金,借方,貸借対照表
事業主借,貸方,貸借対照表
事業主貸,借方,貸借対照表
ソフトウェア,借方,貸借対照表
工具器具備品,借方,貸借対照表
売掛金,借方,貸借対照表
未払金,貸方,貸借対照表
元入金,貸方,貸借対照表
開業費,借方,貸借対照表
売上,貸方,損益計算書
消耗品費,借方,損益計算書
接待交際費,借方,損益計算書
支払手数料,借方,損益計算書
新聞図書費,借方,損益計算書
水道光熱費,借方,損益計算書
地代家賃,借方,損益計算書
通信費,借方,損益計算書
旅費交通費,借方,損益計算書
減価償却費,借方,損益計算書
上記は私が使っている勘定科目を基に作成しています。精算表カラムについては、各科目ごとの合計が貸借対照表と損益計算書のどちらに記入されるかの定義です。
インポート
LOAD DATA LOCAL INFILE '勘定科目マスタ.csv'
INTO TABLE 勘定科目マスタ
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\n'
(科目名, 貸借区分, 精算表);
まずは残高なしの総勘定元帳
残高計算は特殊なので別で処理します。ですが、計算で出せるものについて途中結果をワークテーブルに保存するのもナンセンスです。かといって、副問い合わせで全部書こうとすると破綻するので、Viewを使います。
CREATE VIEW 総勘定元帳 AS
SELECT 仕訳帳.id AS 仕訳帳_id,
仕訳帳.日付 AS 日付,
仕訳帳.借方勘定科目 AS 勘定科目,
仕訳帳.貸方勘定科目 AS 相手勘定科目,
仕訳帳.摘要 AS 摘要,
仕訳帳.借方金額 AS 借方金額,
0 AS 貸方金額,
仕訳帳.備考 AS 備考
FROM 仕訳帳
UNION
SELECT 仕訳帳.id AS 仕訳帳_id,
仕訳帳.日付 AS 日付,
仕訳帳.貸方勘定科目 AS 勘定科目,
仕訳帳.借方勘定科目 AS 相手勘定科目,
仕訳帳.摘要 AS 摘要,
0 AS 借方金額,
仕訳帳.貸方金額 AS 貸方金額,
仕訳帳.備考 AS 備考
FROM 仕訳帳;
仕訳帳から総勘定元帳に転記する際は借方勘定科目・貸方勘定科目それぞれの帳簿に記入するためレコード数は２倍になります。それぞれ取得してUNIONで結合しています。
残高も付いた総勘定元帳
残高はいわゆる累計です。さて、累計ってどうやって出すんだろう？ 商用DBのOracleでは分析関数のoverで累計値を求められるそうです。ですが、MySQLにそんなものはありません！そのため、少し工夫が必要です。
CREATE VIEW 総勘定元帳_残高付き AS
SELECT A.仕訳帳_id AS 仕訳帳_id,
A.日付 AS 日付,
A.勘定科目 AS 勘定科目,
A.相手勘定科目 AS 相手勘定科目,
A.摘要 AS 摘要,
A.借方金額 AS 借方金額,
A.貸方金額 AS 貸方金額,
CASE WHEN 勘定科目マスタ.貸借区分 = '借方'
THEN SUM(B.借方金額 - B.貸方金額)
ELSE SUM(B.貸方金額 - B.借方金額) END AS 残高,
A.備考 AS 備考
FROM 総勘定元帳 AS A
JOIN 総勘定元帳 AS B ON A.勘定科目 = B.勘定科目 AND A.仕訳帳_id >= B.仕訳帳_id
JOIN 勘定科目マスタ ON A.勘定科目 = 勘定科目マスタ.科目名
GROUP BY A.仕訳帳_id,
A.日付,
A.勘定科目,
A.相手勘定科目,
A.摘要,
A.借方金額,
A.貸方金額,
A.備考
ORDER BY 勘定科目, 仕訳帳_id;
累計は同じViewをJOINし、Aのレコードの仕訳帳_id以下の仕訳帳_idを持ったBのレコードの金額を集計して算出します。注意事項として、仕訳帳_id(仕訳帳テーブルのid = 仕訳帳.csvの行)は時系列に並んでいなければいけません。はい。少々無理はありますが、気にしません！税金が還ってくれば青色申告できれば良いのですから。
また、CASE文は前述した様に、勘定科目の貸借により残高の計算方法が異なるためです。
総勘定元帳を勘定科目ごとに出力出来るように
上記、総勘定元帳_残高付きのViewのレコードイメージは以下の様な形です。
mysql> SELECT * FROM 総勘定元帳_残高付き;
+------------+------------+-----------+---------------+-------------------------------------+--------------+--------------+---------+--------+
| 仕訳帳_id | 日付 | 勘定科目 | 相手勘定科目 | 摘要 | 借方金額 | 貸方金額 | 残高 | 備考 |
+------------+------------+-----------+---------------+-------------------------------------+--------------+--------------+---------+--------+
| 3 | 2014-01-01 | 事業主借 | 開業費 | 開業前の書籍、PC、PCソフト購入費用 | 0 | 100000 | 100000 | |
| 32 | 2014-03-25 | 事業主貸 | 売掛金 | 株式会社○○ 1月分 源泉徴収 | 30000 | 0 | 30000 | |
| 44 | 2014-04-25 | 事業主貸 | 売掛金 | 株式会社○○ 2月分 源泉徴収 | 30000 | 0 | 60000 | |
| 56 | 2014-05-25 | 事業主貸 | 売掛金 | 株式会社○○ 3月分 源泉徴収 | 30000 | 0 | 90000 | |
| 1 | 2014-01-01 | 元入金 | 普通預金 | 個人口座をそのまま事業用に | 0 | 500000 | 500000 | |
| 2 | 2014-01-01 | 元入金 | 現金 | 事業用資金 | 0 | 50000 | 550000 | |
| 12 | 2014-01-27 | 地代家賃 | 普通預金 | 家賃/事務所費 | 60000 | 0 | 60000 | |
| 21 | 2014-02-27 | 地代家賃 | 普通預金 | 家賃/事務所費 | 60000 | 0 | 120000 | |
| 33 | 2014-03-27 | 地代家賃 | 普通預金 | 家賃/事務所費 | 60000 | 0 | 180000 | |
| 45 | 2014-04-27 | 地代家賃 | 普通預金 | 家賃/事務所費 | 60000 | 0 | 240000 | |
| 57 | 2014-05-27 | 地代家賃 | 普通預金 | 家賃/事務所費 | 60000 | 0 | 300000 | |
| 25 | 2014-02-28 | 売上 | 売掛金 | 株式会社○○ 1月分 請求 | 0 | 300000 | 300000 | |
| 37 | 2014-03-28 | 売上 | 売掛金 | 株式会社○○ 2月分 請求 | 0 | 300000 | 600000 | |
| 49 | 2014-04-28 | 売上 | 売掛金 | 株式会社○○ 3月分 請求 | 0 | 300000 | 900000 | |
| 61 | 2014-05-28 | 売上 | 売掛金 | 株式会社○○ 4月分 請求 | 0 | 300000 | 1200000 | |
〜
ひとつのViewに全ての勘定科目のデータが含まれていて、勘定科目はカラムとして存在しています。いざ税務調査の際にこれを見せても「見にくいよ〜」なんて言われかねませんね。各勘定科目ごとに見れるようにそれぞれのViewを作成します。Viewは勘定科目マスタのレコード分だけ必要で、Viewの名前は科目名に応じて変わります。ここではMySQLのストアドプロシージャを使います。
DELIMITER //
CREATE PROCEDURE 科目ごと総勘定元帳作成()
BEGIN
DECLARE account_title VARCHAR(8);
DECLARE _cursor CURSOR FOR SELECT 科目名 FROM 勘定科目マスタ;
SET @idx = 0;
SELECT COUNT(*) INTO @count FROM 勘定科目マスタ;
OPEN _cursor;
WHILE @idx < @count DO
FETCH _cursor INTO account_title;
SELECT CONCAT('CREATE VIEW 総勘定元帳_', account_title,'_印刷用 AS
SELECT 仕訳帳_id,
日付,
相手勘定科目,
摘要,
借方金額,
貸方金額,
残高,
備考
FROM 総勘定元帳_残高付き
WHERE 勘定科目 = "', account_title, '"
ORDER BY 仕訳帳_id') INTO @s;
PREPARE stmt FROM @s;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
SET @idx = @idx + 1;
END WHILE;
CLOSE _cursor;
END
//
DELIMITER ;
CALL 科目ごと総勘定元帳作成;
出来上がった、勘定科目ごとのViewは以下の様になります。
mysql> SELECT * FROM 総勘定元帳_売上_印刷用;
+------------+------------+--------------+--------------------------+-----------+-----------+---------+--------+
| 仕訳帳_id | 日付 | 相手勘定科目 | 摘要 | 借方金額 | 貸方金額 | 残高 | 備考 |
+------------+------------+--------------+--------------------------+-----------+-----------+---------+--------+
| 27 | 2014-02-28 | 売掛金 | 株式会社○○ 1月分 請求 | 0 | 300000 | 300000 | |
| 41 | 2014-03-28 | 売掛金 | 株式会社○○ 2月分 請求 | 0 | 300000 | 600000 | |
| 55 | 2014-04-28 | 売掛金 | 株式会社○○ 3月分 請求 | 0 | 300000 | 900000 | |
| 69 | 2014-05-28 | 売掛金 | 株式会社○○ 4月分 請求 | 0 | 300000 | 1200000 | |
+------------+------------+--------------+--------------------------+-----------+-----------+---------+--------+
はい。見やすくなりましたね。
試算表
貸借対照表と損益計算書を記入する前に、仕訳帳の決算処理を行います。通常の決算処理では、決算整理前の仕訳帳・総勘定元帳を基に各勘定科目ごとの合計値と残高を出して試算表を作成します。
CREATE VIEW 試算表 AS
SELECT 総勘定元帳.勘定科目 AS 勘定科目,
SUM(総勘定元帳.借方金額) AS 借方合計,
SUM(総勘定元帳.貸方金額) AS 貸方合計,
CASE WHEN 勘定科目マスタ.貸借区分 = '借方'
THEN SUM(総勘定元帳.借方金額 - 総勘定元帳.貸方金額)
ELSE 0 END AS 借方残高,
CASE WHEN 勘定科目マスタ.貸借区分 = '貸方'
THEN SUM(総勘定元帳.貸方金額 - 総勘定元帳.借方金額)
ELSE 0 END AS 貸方残高
FROM 総勘定元帳
JOIN 勘定科目マスタ ON 総勘定元帳.勘定科目 = 勘定科目マスタ.科目名
WHERE 総勘定元帳.備考 != '決算整理'
GROUP BY 勘定科目
ORDER BY 勘定科目マスタ.id;
再びCASE文が出てきましたが、総勘定元帳と同様で勘定科目の貸借により残高の計算方法が異なるためです。WHERE句の 総勘定元帳.備考 != '決算整理' については、この後、仕訳帳に決算整理仕訳を追記したいためです。追記後も集計対象から外せるように条件を加えています。
印刷用View
総勘定元帳と同様に印刷用のViewを作成します。
CREATE VIEW 試算表計 AS
SELECT '計',
SUM(借方合計) AS 借方合計,
SUM(貸方合計) AS 貸方合計,
SUM(借方残高) AS 借方残高,
SUM(貸方残高) AS 貸方残高
FROM 試算表;
CREATE VIEW 試算表_印刷用 AS
SELECT * FROM 試算表 UNION
SELECT * FROM 試算表計;
まず、合計値用のViewを作り、試算表と合計値ViewをUNIONで結合しています。ここで、合計値の 借方合計 = 貸方合計 かつ 借方残高 = 貸方残高 とならない場合は仕分帳の記入、もしくは勘定科目マスタの貸借に誤りがあります。
精算表
さあ、残す帳簿は精算表のみとなりました！ですが、ここが最終関門です。一般に試算表を作成した後は、試算表を基に決算整理を行い決算整理仕訳を出した後、精算表を作成します。また、精算表は試算表の内容も含み、その他に整理記入・損益計算書・貸借対照表・当期純利益が含まれます。一気に作りたいところですが、まだ情報が足りていません。一つ一つ作っていきます。
整理記入
整理記入とは決算整理仕訳を表します。決算整理仕訳の具体例としては家賃や光熱費の経費計上の按分や、入金前の売上に対する未納付の源泉徴収税を未払金として計上したり、減価償却費の経費計上などです。
まずは、仕訳帳.csvに決算整理仕訳を追記します。
※サンプルデータです
〜
2014-12-31,減価償却費,100000,開業費,100000,開業前の書籍、PC、PCソフト購入費用,決算整理
2014-12-31,事業主貸,58800,水道光熱費,58800,事業使用比率30%,決算整理
2014-12-31,事業主貸,216000,地代家賃,216000,事業使用比率30%,決算整理
2014-12-31,事業主貸,84000,通信費,84000,事業使用比率50%,決算整理
2014-12-31,事業主貸,1101,支払手数料,1101,事業使用比率15%,決算整理
2014-12-31,事業主貸,30000,未払金,30000,未納付の源泉徴収税額,決算整理
仕訳帳の最後のカラム(見た目上は備考としていますが)を決算整理のフラグとして使っています。
続いて、精算表に出力する整理記入欄のViewを作成します。
CREATE VIEW 整理記入 AS
SELECT 総勘定元帳.勘定科目 AS 勘定科目,
SUM(総勘定元帳.借方金額) AS 借方合計,
SUM(総勘定元帳.貸方金額) AS 貸方合計
FROM 総勘定元帳
JOIN 勘定科目マスタ ON 総勘定元帳.勘定科目 = 勘定科目マスタ.科目名
WHERE 総勘定元帳.備考 = '決算整理'
GROUP BY 勘定科目;
損益計算書
損益計算書のViewを作成します。追加した決算整理仕訳も含み、この内容が青色申告で提出する損益計算書の記入欄の数字になります。
CREATE VIEW 損益計算書 AS
SELECT 勘定科目マスタ.科目名 AS 勘定科目,
CASE WHEN 勘定科目マスタ.貸借区分 = '借方'
THEN IFNULL(試算表.借方合計, 0) - IFNULL(試算表.貸方合計, 0)
+ IFNULL(整理記入.借方合計, 0) - IFNULL(整理記入.貸方合計, 0)
ELSE 0 END AS 借方合計,
CASE WHEN 勘定科目マスタ.貸借区分 = '貸方'
THEN IFNULL(試算表.貸方合計, 0) - IFNULL(試算表.借方合計, 0)
+ IFNULL(整理記入.貸方合計, 0) - IFNULL(整理記入.借方合計, 0)
ELSE 0 END AS 貸方合計
FROM 勘定科目マスタ
LEFT JOIN 試算表 ON 勘定科目マスタ.科目名 = 試算表.勘定科目
LEFT JOIN 整理記入 ON 勘定科目マスタ.科目名 = 整理記入.勘定科目
WHERE 勘定科目マスタ.精算表 = '損益計算書';
IFNULL文が出てきましたが、税務調査の際に「NULLってなんすか？」と言われてしまうことを避けるためです。
貸借対照表
貸借対照表のViewを作成します。追加した決算整理仕訳も含み、この内容が青色申告で提出する貸借対照表の記入欄の数字になります。
CREATE VIEW 貸借対照表 AS
SELECT 勘定科目マスタ.科目名 AS 勘定科目,
CASE WHEN 勘定科目マスタ.貸借区分 = '借方'
THEN IFNULL(試算表.借方合計, 0) - IFNULL(試算表.貸方合計, 0)
+ IFNULL(整理記入.借方合計, 0) - IFNULL(整理記入.貸方合計, 0)
ELSE 0 END AS 借方合計,
CASE WHEN 勘定科目マスタ.貸借区分 = '貸方'
THEN IFNULL(試算表.貸方合計, 0) - IFNULL(試算表.借方合計, 0)
+ IFNULL(整理記入.貸方合計, 0) - IFNULL(整理記入.借方合計, 0)
ELSE 0 END AS 貸方合計
FROM 勘定科目マスタ
LEFT JOIN 試算表 ON 勘定科目マスタ.科目名 = 試算表.勘定科目
LEFT JOIN 整理記入 ON 勘定科目マスタ.科目名 = 整理記入.勘定科目
WHERE 勘定科目マスタ.精算表 = '貸借対照表';
精算表
精算表のViewを作成します。
CREATE VIEW 精算表 AS
SELECT 勘定科目マスタ.科目名 AS 勘定科目,
IFNULL(試算表.借方残高, 0) AS 試算表借方,
IFNULL(試算表.貸方残高, 0) AS 試算表貸方,
IFNULL(整理記入.借方合計, 0) AS 整理記入借方,
IFNULL(整理記入.貸方合計, 0) AS 整理記入貸方,
IFNULL(損益計算書.借方合計, 0) AS 損益計算書借方,
IFNULL(損益計算書.貸方合計, 0) AS 損益計算書貸方,
IFNULL(貸借対照表.借方合計, 0) AS 貸借対照表借方,
IFNULL(貸借対照表.貸方合計, 0) AS 貸借対照表貸方
FROM 勘定科目マスタ
LEFT JOIN 試算表 ON 勘定科目マスタ.科目名 = 試算表.勘定科目
LEFT JOIN 整理記入 ON 勘定科目マスタ.科目名 = 整理記入.勘定科目
LEFT JOIN 損益計算書 ON 勘定科目マスタ.科目名 = 損益計算書.勘定科目
LEFT JOIN 貸借対照表 ON 勘定科目マスタ.科目名 = 貸借対照表.勘定科目;
まだこれで終わりでは無く、当期純利益と合計値が不足しています。
当期純利益
当期純利益は損益計算書を基に算出します。
CREATE VIEW 当期純利益 AS
SELECT '当期純利益',
0,0,0,0,
SUM(貸方合計 - 借方合計),
0,0,
SUM(貸方合計 - 借方合計)
FROM 損益計算書;
この後作成する合計値Viewと印刷用Viewのため、固定値の科目名を入れ、カラム位置の調整をしています。
合計値View
精算表がややこしいのは、合計値に当期純利益が含まれることです。
CREATE VIEW 精算表＋当期純利益 AS
SELECT * FROM 精算表 UNION
SELECT * FROM 当期純利益;
CREATE VIEW 精算表計 AS
SELECT '計',
SUM(試算表借方) AS 試算表借方,
SUM(試算表貸方) AS 試算表貸方,
SUM(整理記入借方) AS 整理記入借方,
SUM(整理記入貸方) AS 整理記入貸方,
SUM(損益計算書借方) AS 損益計算書借方,
SUM(損益計算書貸方) AS 損益計算書貸方,
SUM(貸借対照表借方) AS 貸借対照表借方,
SUM(貸借対照表貸方) AS 貸借対照表貸方
FROM 精算表＋当期純利益;
一度、当期純利益を結合したViewを作り、そこから合計値を求めています。ここで、合計値の試算表・整理記入・損益計算書・貸借対照表それぞれで借方合計と貸方合計が一致しない場合は仕分帳の記入、もしくは勘定科目マスタの貸借に誤りがあります。
印刷用View
お疲れ様でした！ついに最後です。精算表を印刷できる形にします。
CREATE VIEW 精算表_印刷用 AS
SELECT * FROM 精算表 UNION
SELECT * FROM 当期純利益 UNION
SELECT * FROM 精算表計;
UNIONで結合しているだけですね。
出来上がりの精算表は以下の様になります。
※基のデータはサンプルデータです
mysql> SELECT * FROM 精算表_印刷用;
+---------------+-------------+-------------+---------------+---------------+-----------------+-----------------+-----------------+-----------------+
| 勘定科目 | 試算表借方 | 試算表貸方 | 整理記入借方 | 整理記入貸方 | 損益計算書借方 | 損益計算書貸方 | 貸借対照表借方 | 貸借対照表貸方 |
+---------------+-------------+-------------+---------------+---------------+-----------------+-----------------+-----------------+-----------------+
| 現金 | 108530 | 0 | 0 | 0 | 0 | 0 | 108530 | 0 |
| 普通預金 | 937286 | 0 | 0 | 0 | 0 | 0 | 937286 | 0 |
| 事業主借 | 0 | 100000 | 0 | 0 | 0 | 0 | 0 | 100000 |
| 事業主貸 | 1406270 | 0 | 677901 | 0 | 0 | 0 | 2084171 | 0 |
| ソフトウェア | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
| 工具器具備品 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
| 売掛金 | 300000 | 0 | 0 | 0 | 0 | 0 | 300000 | 0 |
| 未払金 | 0 | 0 | 0 | 30000 | 0 | 0 | 0 | 30000 |
| 元入金 | 0 | 550000 | 0 | 0 | 0 | 0 | 0 | 550000 |
| 開業費 | 100000 | 0 | 0 | 100000 | 0 | 0 | 0 | 0 |
| 売上 | 0 | 3300000 | 0 | 0 | 0 | 3300000 | 0 | 0 |
| 消耗品費 | 13148 | 0 | 0 | 0 | 13148 | 0 | 0 | 0 |
| 接待交際費 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
| 支払手数料 | 1296 | 0 | 0 | 1101 | 195 | 0 | 0 | 0 |
| 新聞図書費 | 1470 | 0 | 0 | 0 | 1470 | 0 | 0 | 0 |
| 水道光熱費 | 84000 | 0 | 0 | 58800 | 25200 | 0 | 0 | 0 |
| 地代家賃 | 720000 | 0 | 0 | 504000 | 216000 | 0 | 0 | 0 |
| 通信費 | 168000 | 0 | 0 | 84000 | 84000 | 0 | 0 | 0 |
| 旅費交通費 | 110000 | 0 | 0 | 0 | 110000 | 0 | 0 | 0 |
| 減価償却費 | 0 | 0 | 100000 | 0 | 100000 | 0 | 0 | 0 |
| 当期純利益 | 0 | 0 | 0 | 0 | 2749987 | 0 | 0 | 2749987 |
| 計 | 3950000 | 3950000 | 777901 | 777901 | 3300000 | 3300000 | 3429987 | 3429987 |
+---------------+-------------+-------------+---------------+---------------+-----------------+-----------------+-----------------+-----------------+
おまけ
出力用シェル
印刷用に出力する際、MySQLのデフォルトのテーブル表示でも良いのですが、CSVとして出したかったり、Excelで見たい場合もあるかもしれません。本稿のタイトルからすると反則な気もしますが、シェルを使って各フォーマットで書き出せるようにします。
仕訳帳テーブルだけ印刷用Viewを作っていないのでSQLに追加します。
CREATE VIEW 仕訳帳_印刷用 AS SELECT * FROM 仕訳帳;
出力用シェルです。
※Excel用出力にはnkfが必要です
#!/bin/bash
mkdir -p out/
SHEETS=(`mysql -u root 複式簿記 -N -s -e 'SHOW TABLES LIKE "%印刷用"' | tr '\n' ' '`)
for sheet in ${SHEETS[@]}; do
mysql -u root 複式簿記 --table -e "SELECT * FROM $sheet" > out/${sheet%_印刷用}.txt
mysql -u root 複式簿記 -e "SELECT * FROM $sheet" | tr '\t' ',' > out/${sheet%_印刷用}.csv
mysql -u root 複式簿記 -e "SELECT * FROM $sheet" | tr '\t' ',' | nkf -s > out/excel_${sheet%_印刷用}.csv
done
推薦図書
青色申告の際に提出が必要な損益計算書と貸借対照表については本稿でカバーできたのでは無いでしょうか？ですが、実を言うと確定申告にはもう一つ「確定申告書B」という強敵がいるのです。こちらについては、昨年、私が参考にした書籍をお勧めさせていただきます。
フリーランスの教科書 - 見田村 元宣 / 内海 正人
そして、もう１冊。こちらは説明不要と思います。良書です。
フリーランスを代表して申告と節税について教わってきました - きたみ りゅうじ
最後に
いかがでしたでしょう？今回はMySQLを使った複式簿記に挑戦してみました。作成したSQLとシェルは以下のリポジトリに置いています。
https://github.com/noriyoshiabe/double-entry-bookkeeping
MITライセンスですので、ご使用にあたっては自己責任でお願いしますm(__)m
それでは、今年もどうぞよろしくお願い致します。
確定申告はお早めに！
このエントリーに対するコメント
日本語が含まれない投稿は無視されますのでご注意ください。（スパム対策）
- トラックバック
最近の記事
人気の記事
「いいね!」で応援よろしくお願いします！