今回はExcelを使って貸借対照表（BS）を作図する方法をご紹介します。
BSは借方に資産、貸方に負債と純資産があり、それぞれ並び順も決まりがあるので、Excelの棒グラフで作図しようとしてもなかなかうまくいかないですよね。
ExcelでBSを作図する方法はいくつかあるのですが、今回は分析に便利な「ピボットグラフ」を使って作図していきたいと思います。
このような時系列のBSを作っていきましょう。
BS分析の元データの準備
まずは、分析の元となるBSデータを準備しましょう。
今回はこのような「年」「DrCr」「科目１」「科目２」「金額」の5つの列を用意します。
「DrCr」「科目１」「科目２」はこちらの画像の記載のまま使ってください。
このとき、科目1と科目2についている頭の番号は必ずつけるようにしてください。科目の並び順を設定するのに必要です。
複数年のBSを作図する場合は、行の下に追加していきます。
ExcelピボットグラフでBSを作成
元データが用意できたら、早速ピボットグラフを作成していきましょう。
「挿入」タブの「ピボットグラフ」をクリックします。
範囲が正しく選択されていることを確認したら、OKを押します。
すると、このようなピボットグラフの箱ができます。右側にフィールドリストが表示されていない場合は、ピボットグラフ分析のタブから追加しましょう。
次に、以下のように、軸に「年」「DrCr」、フィルターに「科目１」、凡例に「科目2」、値に「金額」を追加しましょう。
するとこのような棒グラフができます。これではまだBSからは程遠いですね。
次に、グラフの種類を変えていきます。「デザイン」タブの「グラフの種類の変更」をクリックします。
「積み上げ縦棒」を選択してOKを押しましょう。
少しBSらしくなってきました。ここからさらに調整していきます。
横軸を見ると、Cr（貸方）が左、Dr（借方）が右に来てしまっているので、入れ替えていきます。
グラフの左下あたりにある「DrCr」というボタンをクリックし、「降順」を選択したらOKを押します。
これでDrを左に、Crを右に置くことができました。
次に、科目の並び順を修正していきます。
BSでは流動→固定の順番に科目が並ぶのがルールです。現状だと固定→流動の順番に並んでしまっているので、直したいと思います。
グラフの右にある「科目２」のボタンをクリックして、「降順」をクリックしたらOKを押しましょう。
流動→固定の順番に科目が並び変わりました。純資産も右下に来ていますね。
さらに調整を加えていきます。このままだとグラフが細すぎてBSらしくないので、グラフの幅を太くしていきましょう。
棒グラフのどこでもいいので選択した状態で右クリックします。
「データ系列の書式設定」をクリックしましょう。
系列のオプションの「要素の間隔」が、デフォルトだと100%以上になっているので、ここを20%くらいにしましょう。
これでバランスが整いました。BSの形できれいに時系列で並べることができました。
このままでもよいのですが、グラフ内に金額も表示させてみましょう。
「デザイン」タブの「グラフ要素を追加」をクリックして、「データラベル」→「中央」をクリックしましょう。
するとこのように、グラフ内に金額を表示させることができました。
BSの資産側だけの時系列グラフの作成方法
次に応用編です。今作ったBS時系列をもとに、「資産」側だけの時系列グラフを作ってみましょう。
グラフの左上の「科目1」のボタンをクリックして、「資産」を選択しOKを押します。
すると、資産側だけの時系列推移が簡単に作図できました。
同じ方法で、負債だけや純資産だけのグラフも作ることができます。いろいろ試してみてください。
まとめ
今回はExcelのピボットグラフを使ってBSを作図する方法をご紹介しました。
ピボットグラフを使うと、さまざまなBS分析に応用できるのでおすすめです。
ぜひご参考にしてみてください。
