【フリーランス経理】銀行、カードからスプレッドシート集計、弥生会計の決算を超効率化

今回はフリーランスや小規模会社向けに、スプレッドシートと弥生会計を使った経理作業の効率管理の方法をご紹介します。

経理作業で良く使われている方法は、クラウド会計サービスの活用や、提携税理士に外注するケースでしょう。
この時に、銀行・カードのデータ収集や領収書の集計は自分で行わなければならず、データが間違っていたりするとやり直ししたり何かと時間がかかって大変です。

そんな場合に有効なのがオンラインで集計を自動化する仕組みを作ることです。
一度作り上げれば後はデータベース用のスプレッドシートに貼り付ける作業を月に10,20分ほどするだけで済みます。
また、数値もクロスチェックをする事で、仮に間違えてもすぐに気付けるようにする仕組みにできます。

実際に筆者は4年前から取り入れて、今は税理士なしでスタッフに決まった作業を任せるだけで管理できています。
スプレッドシート内の構成フロー図は、下記のとおりです。

順番に説明していきます。

スプレッドシートの初期設定

最初に必要な主な作業は下記です。

  1. 銀行、カード、手元の現金で処理したデータをスプレッドシートに集計する
  2. 取引記録を勘定科目別に分解する。科目、金額、弥生反映有無、領収書保存など経理上必要な項目を1つずつ紐付ける
  3. 合体シートを用意して、1.を1枚のタブ内にまとめる
  4. 弥生会計のアップロード用ファイルに合わせてデータを並べ変える

※3.4は数式を使い自動更新で集計します

1.銀行、カードのダウンロードデータ集計

実際の集計シーイメージは下記です。

タブの数は銀行A、銀行B、・・、カードDと会社ごとに1つのタブを用意して、「現金その他」の記入タブも用意します。
それぞれのダウンロードデータの列タイトルは異なりますが、最終的に経理処理に必要な共通項目を合体タブへ数式出力することを意識します。主な列タイトル一覧は下記です。

  • 口座利用/支払日:銀行口座で出入金、カードの支払い日
  • 摘要内容:弥生会計で記載する勘定科目の詳細。自社内で区別できる名前を記載します。
  • 支払額:支払いなど出金額
  • 受取額:売上など入金額
  • 決済日/取引日:銀行口座取引、カード支払いの計上日

上記項目は銀行やカード会社と弥生会計を参考にしていますが、使いやすいものに適宜統一しましょう。
1シートでの期間の区切りは一期分です。
決算/計上日ベースか口座利用/計上日ベースのどちらで統一しますが、ここでは決算/計上日ベースにしています。

2.経理上必要な項目の記入

それぞれの項目を勘定科目に分解して、経理上必要な項目を各行の右側に記載します。

  • 属性:個人事業、法人○○などの名義
  • 借方1/貸方1:科目名を記載します
  • 借方2/貸方2:計上日と支払/受取日が異なる場合にこちらも記入します
  • 弥生会計反映有無:毎月データを更新していくと、どれを弥生会計に反映したか分からなくなるので「○」「未反映」などと記載して区別します
  • 項目→勘定科目変更:銀行/カード取引をそのままにしている項目と、複数に分解する項目があるので、ここで区別できるようにします
  • 電子領収書保存:オンラインでやり取りして請求書を保存する場合もあるので、ここで「○」「印刷済」「フォルダ保存済」などと区別します
  • 紙領収書保存:同様に紙で領収書をやり取りする場合もあるので、「○」「保存済」など区別できるようにします

(項目→勘定科目変更例)
A社からの入金:15万円→売上:18万円、支払手数料:-3万円

経費にならない項目などもあるので、合体タブに反映させたくない場合は空欄にします。

3.合体シートの設定

個別の集計タブの内容を、合体タブで一つにまとめます。


上から銀行A、銀行B、銀行C、カードA、現金他を順番に並べるのですが、
予め1年分が何行になりそうか推算して、その分の行数を予め確保します。

(各行構成例)銀行A→1〜500行目、銀行B→501〜800行目、銀行C→801〜1500行目・・・
(数式例)=ARRAYFORMULA(IF(‘銀行A’!$B2:$B501=””,””,’銀行A’!D2:D501))

こうすることで後から金額のクロスチェックもしやすくなり、数式OFFSETを使った結び付けもできるようになります。

また、このシート自体をデータベースとしてGoogleデータポータルやTableauに繋げて、表やグラフで可視化することも可能です。
1年ごとに1枚のスプレッドシートのタブにまとめておくことで、データシートを繋ぎかえるだけで過去年度の数値もすぐに確認することができたり、次年度もコピーするだけで同じシートをすぐに用意できたりして便利です。

この1枚のタブによって銀行、カードの全合計の収入、売上、経費を把握することが可能なのです。

4.弥生会計反映有無を分別表示したタブ

次に合体タブから弥生出力タブへ、弥生会計反映有無を分別して出力します。
ここでは数式FILTERを使います。

(数式例)=ARRAYFORMULA(SORT(UNIQUE((FILTER(‘合体タブ’!L2:V,’合体タブ’!L2:L<>”○”,’合体タブ’!Q2:Q>0))),2,TRUE,1,TRUE))

これによってまだ弥生に入力してない項目だけが一目で分かります。
すべて「弥生会計反映済」になったら完了ですね。

ここで出力するのは合体タブのすべての項目ではなく、最低限のものに絞ります。
支払日、決済日、属性、借方1,2、貸方1,2、金額、摘要内容が最低限揃えば弥生会計にアップロードできるので、このシートから各行のデータを収集します。
ここでも弥生会計の仕様にあわせてスプレッドシートの細部の項目名などを変更すれば、次回以降の処理も楽になります。

通常時の処理作業

初期段階の準備はできたので、次は毎月の更新作業についてです。

銀行、カードのrawデータの最新月分を追加

これは各社の閲覧サイトからダウンロードして、スプレッドシートに追加更新分を上書きしていく作業です。

各タブごとで項目名が異なるので、合体タブ、弥生会計反映用の作業時に利用しやすいようにカスタマイズしましょう。また、現金取引についてもお店などで支払った直後にスマホでスプレッドシートを開いて入力できます。
すぐに所定箇所に記録する癖がつけば、後で作業しなくて済みミスも減るので効率的です。

経理上必要な項目を入力

月に一度上記の更新データを元に勘定科目などを入力します。
毎月同じ項目がある場合はコピペで済むので、予め頻度(毎月、年1回など)に応じてリスト化しておくと良いですね。数式やGoogle Appsで公式のように勘定科目を自動入力できるようになるとさらに時間短縮できます。


この作業を行うことで、合体タブ、弥生反映用シートは自動更新されます。
弥生会計への反映は、必要でなければ2,3ヶ月に1回でも大丈夫でしょう。

年度締め、決算資料提出時の処理作業

弥生会計上の作業、提出書類の作成

1年が終わり12カ月分のデータが揃ったら、次年度用のスプレッドシートをコピーしてそちらの作り込みに移ります。
一方、既存年度分は弥生会計にすべてアップロードして、弥生会計内で期末処理の作業を行います。
弥生会計は毎年進化して初心者にも簡単に使えるようになっています。
必要事項を記入するだけで、後は印刷ボタンを押せば提出に必要な資料が出力されるようになっています。

提出書類の保存

領収書、各種書類を保存しないといけませんが、これもスプレッドシートの「○」「保存済」などを見ながら一個ずつ確認すれば大丈夫です。
法人の場合はもう少し込み入った作業が必要ですが、個人事業の方は普段の月とあまり変わらないくらいの時間で済みます。
普段から記録する癖がついていれば、全く焦ることなく決算処理を終わらせることができるようになります。

税理士に頼りたくもなる時もあります。
しかし、そのような専門家が必要なときは、
・年々更新される情報が欲しいとき
・合法的に節税をするアイディアが欲しいとき
であり、これは毎月顧問契約しなくても何とかなります。
単発での相談に対応してくれる事務所もあるので、それを利用しましょう。

その他のタブ

その他にあると便利なタブを記載します

内容別メモ

定常的に発生する記帳内容を頻度別にリスト化したものです
頻度:毎月、隔月、年4回、年1回
項目名:記録上の名前
更新タイミング:毎月のサービスであれば「○日に更新」と記載することで備忘録にもなります。属性、勘定科目、領収書有無なども項目も必要に応じて載せましょう。

やり取りメモ

社内でスタッフに指示するためのタブです。
日付、ステータス(完了、連絡中、アクション依頼、認識共有のみ)、内容分類、担当分類などの項目を加えて見やすくして、連絡漏れ、齟齬や時間ロスを防ぎます。

まとめ

この方法によって、毎月と決算期の経理作業もほとんど時間をかけずに処理できて、経理作業を行なうことが楽しくもなってきました。
当ブログでは、WEBマーケティングデータをデータポータルやTableauを使って高度、高効率に管理する方法をお伝えしていますが、今回のように経理作業にも応用することができました。

スプレッドシート自体も思いついた時に手を加えて改善しており、これからもどんどん使いやすくなっていきます。
さらに続けていく内に経理や節税の知識やアイディアにも詳しくなれます。
特にフリーランスや小規模会社の方にお勧めの方法ですので、是非試してみましょう。

最新情報をチェックしよう!