エクセルで財務分析

私は算数数学が得意でありますが、会計(簿記)・経理分野にも精通しているところもあります。数字なら何でも好きですね。

今回エクセルを使って上記の分野のご提供できないかということで、財務諸表を例示作成して分析数値を算出してみました。以下のエクセルファイルになります。

財務分析

流れとしては以下の通りとなります。

1.『財務諸表シート』の貸借対照表の期首の欄に勘定科目に対応する金額を入力する。

2.『当期仕訳シート』に当期に行うべき仕訳を入力する。(200行程入力設定しています)

3.すると自動的に『財務諸表シート』の当期損益計算書と期末貸借対照表の数値等と『経営分析シート』の各種財務分析指標の数値がボンと算出されます。

(上記『経営分析シート』)

事業年度における財務というのはまず期首の貸借対照表から始まります。そこから期中に様々な取引が発生して、仕訳を進めていくことになりますね。そして決算になると決算整理仕訳を行って当期の結果を判断していくわけです。結果というのは主に経営成績(損益計算書)・財政状態(貸借対照表)等、そしてそこから導き出される各種財務分析数値になります。

よって経理担当として行うべきこととしては、期首の貸借対照表(スタート)をはっきりさせること・期中と決算の仕訳をもれなく記帳していくことが大切になります。それができれば後は上記の結果が自動的に算出されることになるのです。

提示のエクセルで使われた主な技術・留意点は次のようになります。

・『当期仕訳シート』は『財務諸表シート』と連動しているので勘定科目を統一させます。また新たな勘定科目を追加させることも可能です。

・『財務諸表シート』の貸借対照表増減欄と損益計算書欄の金額は『SUMIF』関数の技術を用います。

・『財務諸表シート』の貸借対照表期末欄と損益計算書の当期純利益欄は増減させて自動計算されます。

・『経営分析シート』の分析は大きく4つに分けられます。それぞれ『財務諸表シート』の貸借対照表欄と損益計算書欄の該当数値を用いて、公式に当てはめて自動計算されます。

いかがでございましょうか。

『当期仕訳シート』の仕訳を少しでも修正追加させることによって、他の自動計算数値が連動していろいろと変化するので、面白いですね。

実績を入力して分析するだけではなく、次期以降のシュミレーションを立てる意味においても有用性があると思います。

勘定科目や体裁等をご自身の使いやすいように直して、ご利用されてみてください。

 

和算と方程式2(流水算)

問題】(流水算、前回と同じ)

ある川の上流のP地と下流のQ地は120km離れている。
PQ間を船が往復したところ、下りの速さは上りの速さの1.6倍で、下るのにかかった時間は上るのにかかった時間よりも6時間少なかった。
この時、この川の流速は何km/時か。

解説2(方程式的解き方)

上りの速さをxKm/時とおくと、下りの速さは1.6倍より1.6xKm/時となる。
PQ間は120Kmより、 上りにかかった時間は、120÷x=120/x (時間)、下りにかかった時間は、120÷1.6x=120/1.6x (時間)

上りと下りのかかった時間差は6時間より、
120/x-120/1.6x=6

この方程式を解くと、まず両辺を1.6x倍して
120×1.6-120=6×1.6x
192ー120=9.6x
9.6x=72
x=7.5
よって上りの速さは7.5Km/時である。
また下りの速さは1.6倍より、7.5×1.6=12Km/時となる。
よってこの川の流速は、 (12-7.5)÷2=【2.25Km/時】となる。・・・(答え)

いかがでございましょうか。

以上この流水算の問題は、和算的解き方と方程式解き方の二つの解法を提示しました。中学受験分野・SPI試験分野・公務員試験分野などによく出題される問題になります。

この和算的解き方は仮の数値を当てはめてみて修正をかけていく特徴、方程式解き方は求めたい数値をxとおいて式を立てて解を求める特徴になるかと思います。○○算といった特殊算は、この二つの解法で解を求めることがよくあります。

どちらがいいとは一概にはいえませんが、和算的解き方は中学受験生がよく使い味わいがある感じがします。方程式解き方は式を立てることができれば後は機械的に解けば良いので楽なところがあります。

一つの問題に対していろんな解法を身につけておくと、柔軟性が身につき思考力が強くなるので良いことですね。また提示していこうと思います。

 

和算と方程式1(流水算)

問題】(流水算)

ある川の上流のP地と下流のQ地は120km離れている。
PQ間を船が往復したところ、下りの速さは上りの速さの1.6倍で、下るのにかかった時間は上るのにかかった時間よりも6時間少なかった。
この時、この川の流速は何km/時か。

解説1(和算的解き方)

流水算(速さ)の問題になります。まず今回『下りの速さは上りの速さの1.6倍で』とありますので、何か【仮の数値】を 設定してみようと思います。

分かりやすい数字・計算しやすい数字であれば何でも大丈夫です! 最初ぱっと思いつくのは、下り16Km/時・上り10Km/時あたりですかね。
もちろんこれでも大丈夫です。ただもう少し数字を小さくできないか考えますと
それぞれを2で割っても整数のままになりますので、『下り8Km/時・上り5Km/時』で 設定した方がよろしいでしょう。その後の計算が楽になりますからね。 実際『下り8Km/時・上り5Km/時』は、8÷5=1.6なので1.6倍の速さの関係になっていますよね。

【仮の数値】をこれで設定できました。それではこの【仮の数値】の『下り8Km/時・上り5Km/時』で、問題を解き進めてみましょう。

速さの公式はいかがですか。 『時間=距離÷速さ』ですよね。

下りと上りにかかった仮の時間を求めてみます。 下りは120÷8=15時間、上りは120÷5=24時間となりますね。

それでは仮の時間差はどれくらいかといいますと、 24-15=9時間と出てきます。
あれ、問題文では6時間と書かれていますよね。ここで誤差が発生いたします。
つまり6÷9=2/3倍の違いが出てしまいますね。。 速さと時間というものは、反比例(逆の変化の関係)いたします。
例えば先ほどの仮の数値を2倍の速さの『下り16Km/時・上り10Km/時』で設定し直すと、 かかった時間は下り120÷16=7.5時間・上りは120÷10=12時間・時間差12-7.5=4.5時間となり、 『下り8Km/時・上り5Km/時』の場合の1/2倍の時間に変化しています。 つまり反比例の関係ですね。

今回最初の設定では2/3倍の時間の違いが出ましたので、仮と実際の速さは反比例より その逆の3/2倍の違いになるわけです。
それでは正しい速さに修正をいたします。 『下り8Km/時・上り5Km/時』の3/2倍にすればよいので、 下りの正しい速さは8×3/2=12Km/時 上りの正しい速さは5×3/2=7.5Km/時 となります。

このように最初に仮の数値を設定して、後から修正をかけ正しい数値を求めるという手法は 数的分野においてときどき使えますので、マスターしておくとよろしいかと思います。

あとはこの川の流速を求めることになりますね。 この流水算の公式はいかがですか。
『川の流速=(下りの速さ-上りの速さ)÷2』

それでは、上記に当てはめてみますと
川の流速=(12-7.5)÷2=4.5÷2=2.25 よって【2.25Km/時】が答えとなります。

 

数検解説2(2級2次第5問)

前回の解説は数学検定の過去問からの引用(一部改題)でした。2級2次の第5問からになります。

数学検定はご存知でしょうか。以下のHPになります。

https://www.su-gaku.net/suken/

私が数学検定の中で注目している問題の一つとして、この2級2次の第5問があります。検定過去問題のページの中にサンプル問題として公開されています。

   

この第5問は、試験範囲の高校2年までの分野とは異なり『数検特有問題』して位置付けられています。またHPの模範解答を見て他の6問は解法過程の記述解答が求められていますが、この問題だけは解答のみの形となっています。(毎回概ね)

選択問題のため受検者は解答されても解答されなくともどちらでもいいのですが、毎回思考力を要する面白い良問が出題されている印象を受けます。

試験は解答のみで正解になってしまうので、ヤマ勘で解答でも正解になる可能性はありますが前回の解説にもあるようになかなかそうはいかないようです。解法過程がきちんとされていないと正解にはたどり着かない感じですね。

解法過程もまともに書くと分量が多くなり解答用紙には収まりませんので、それも踏まえ解答のみの要求になっているのだと思います。(解答ができていれば解法もしっかりしていることの裏返しですからね。)

そうはいっても解答した受検者はどのような解法過程になっているのか気になる方も多いのではないかと推測されます。せっかく思考力を要する面白い良問が出題されているので、分からないままで終わるともったいない感じがいたします。。

数学検定は指導経験多く自分自身もお世話になってきていて、縁のある検定です。

もし受験された方で要望あれば、お問い合わせください(他の級も可)。解説いたします。一緒に考えてみましょう。

 

数検解説1(論理的に解くということ)

問題】(数学検定2級2次過去問一部改題)

次の等式が成り立つとき,A~Eの各文字が表す数字の組をすべて求めなさい。ただし,各文字は1,2,3,4,5のどれかを表し,それらはすべて異なります。 この間題は解法の過程を記述せずに,答えだけを書いてください。(複数解答あり)
A×B+C-D÷E=6

解説

まずまともにAからEの中に数字1から5を一つ一つ入れていくとどうでしょうか。

異なる数字ということで、

Aに5通りの数字、Bには残りの4通りの数字、Cには残りの3通りの数字、Dには残りの2通りの数字、Eには最後の1通りの数字になるので

5×4×3×2×1=120(通り)の組み合わせの数を入れていかなければなりません。

時間が無制限にあればそれでも答えは出ますのでこのやり方でよろしいでしょう。ところが今回は、数学の試験ということで制限時間があり1問あたりに費やせる時間は20分程になります。120通り入れていくやり方は難しい形となります。

それでは他のやり方を模索していくことになります。

AからEの5つの文字がありますが、ある程度候補が絞れそうな文字があるようです。問題の式 A×B+C-D÷E=6の中に注目すべき部分があります。

…『D÷E』ですね。ここは割り算になりまして入れる数字によって整数になったり小数になったりします。他の部分が全て整数となりますので、ここの部分も整数とならなければなりません。よって『D÷E』は整数となります。

D÷Eが整数となるためには、1から5の数字のどれを入れていけばよろしいでしょうか。

『÷E』のEは3以上になることはありませんね。2か1になります。よってそれに対応するDを決めていくとDとEの組み合わせは、

(D,E)=(4,2)、(2,1)、(3,1)、(4,1)、(5,1)の5通りと絞られます。

(つまり『D÷E』の候補は、2,3,4,5の4つになります。)

次に『A×B+C』を考えていきましょう。分かりやすくA×B+C-D÷E=6を移項処理して、A×B+C=6+D÷Eとします。

(D,E)の組み合わせ候補は5通りでありますので、以下場合分けして考えてみましょう。

(ⅰ) (D,E)=(4,2)のとき

D÷Eが2になりますので、右辺の6+D÷Eは8になります。つまり左辺のA×B+C=8ですね。また残りの数字は、1と3と5になります。ここでAとBとCにまともに入れてみると3×2×1=6通りあるので、このくらいであれば全て確かめても良いかもしれません。

でももう少し絞り込みができます。もしAやBに3や5を入れると15になり、既に8を超えてしまうので不適になります。よってAかBに必ず1がくることになります。

それを踏まえると(A,B,C)の組み合わせは(1,3,5)(1,5,3)(3,1,5)(5,1,3)の4通りとなります。

よって(D,E)が(4,2)のときの(A,B,C,D,E)の組み合わせは、(1,3,5,4,2)(1,5,3,4,2)(3,1,5,4,2)(5,1,3,4,2)の4通りになります。

(ⅱ) (D,E)=(2,1)のとき、

これもD÷Eが2になりますので、右辺の6+D÷Eは8になります。つまり左辺のA×B+C=8ですね。また残りの数字は、3と4と5になります。

この残りの数字をどのようにいれても左辺のA×B+Cが8になることはありえません。よって (D,E)=(2,1)は不適になります。

(ⅲ) (D,E)=(3,1)のとき、

D÷Eが3になりますので、右辺の6+D÷Eは9になります。つまり左辺のA×B+C=9ですね。また残りの数字は、2と4と5になります。

これまた残りの数字をどのようにいれても左辺のA×B+Cが9になることはありえません。よって (D,E)=(3,1)は不適になります。

(Ⅳ) (D,E)=(4,1)のとき

D÷Eが4になりますので、右辺の6+D÷Eは10になります。つまり左辺のA×B+C=10ですね。また残りの数字は、2と3と5になります。

これまた残りの数字をどのようにいれても左辺のA×B+Cが10になることはありえません。よって (D,E)=(4,1)は不適になります。

(Ⅴ) (D,E)=(5,1)のとき

D÷Eが5になりますので、右辺の6+D÷Eは11になります。つまり左辺のA×B+C=11ですね。また残りの数字は、2と3と4になります。

(A,B,C)の候補にもう少し絞り込みをします。もしAやBに3や4を入れると12になり、既に11を超えてしまうので不適になります。よってAかBに必ず2がくることになります。

Aに2を入れるとBが4、Cが3のとき11になります。Bに2を入れるとAが4、Cが3のとき11になります。

それを踏まえると(A,B,C)の組み合わせは(2,4,3)(4,2,3)の2通りとなります。

よって(D,E)が(5,1)のときの(A,B,C,D,E)の組み合わせは、(2,4,3,5,1)(4,2,3,5,1)の2通りになります。

以上(ⅰ)から(Ⅴ)の場合分けにより、求めるA~Eの各文字が表す数字の組は

(1,3,5,4,2)(1,5,3,4,2)(3,1,5,4,2)(5,1,3,4,2)(2,4,3,5,1)(4,2,3,5,1)の6通りになります。・・・(答え)

いかがでございましたでしょうか。一見問題分はシンプルにも見えますが、組み合わせを複数過不足なく挙げていくのは絞り込みと細かい場合分けが必要なことがわかると思います。そこには論理的に手順を追って考えていく思考が存在します。

・全て入れようとすると120通りあって大変だ。→・『D÷E』の割り算で絞り込みができる。→・『A×B+C』は5パターンある。→『A×B』の大きさでより絞り込みをして残りの数字を決めていく といった流れになります。

これから数学・数理を学んでいく際には、常に論理の思考を大切に問題を解き進めていきたいですね。