今回は、 What-if 分析の ゴールシーク について解説しようと思います。
ゴールシーク の 使い方 については、こちらの記事を参照してください。
ゴールシークで、支給額から控除額を計算し減算して手取額を計算する計算式を使って、目標の 手取額 から 支給額 を計算することができます。 通常使っている計算式と目標の計算結果から、逆算ができるので、会計の分析にも使えます。
EXCEL エクセル で計算といえば、 入力データ を元にして、 計算式 入力すると、結果が出るだけと思っていました。
ですから、 給与 の 支給額 から 健康保険 厚生年金 を計算して、それらを 控除 した 手取額 を計算する。という計算式をEXCELで入力していました。
ところが、期末に、 臨時ボーナス を支給することになったのですが、 手取り額 を きりの良い 金額 にしたいので、 控除前 の 金額 をいくらにすれば良いのか、計算して欲しいと言われたのです。いわゆる逆算というやつです。
いつも給与計算に使っている 「支給額から手取額を算出する計算式」 はありますが、逆計算の計算式はありませんでした。
一人ずつ、支給額の数字を入れては、結果を見て、あげたり下げたりを地道に繰り返して答えを出す。
これを100人分やるとすると、気の遠くなる作業で、1時間などでは到底終わりそうもありません。
そんな時に、ゴールシークを思い出して、初めて使ってみました。
いつもの計算式を使います。
そして例えば手取額を100,000円にするときは、計算式が入ったセルは手取額のセル、その目標値が100,000、変化させるセルは支給額、と指定してOKを押すと、Excelが地道に計算を繰り返して、支給額を出してくれました。
その時間は、あっという間もなく一瞬です。
人数分ゴールシークを繰り返す、マクロを組んで実行したら、1時間で終わらない処理が、あっという間に、しかも正確にできました。
このように、ハマるとその威力は絶大ですから、使えるところはないかと、いつも探しています。
以下に、使い方の例を解説していますので、最後まで読んでいただければ嬉しいです。
それでは、順に説明します。
BMI を 22にする 体重を ゴールシーク で探す
BMIの算出する計算式は、BMI＝体重÷（身長m×身長m）です。
今、身長が1.75m（175cm）、体重68kgですから、BMIは22.20です。
BMIを22にする（目標結果）ためのゴールシークを実行します。
結果の計算式は、BMI値のC4のセルに入っています。
身長は固定で、体重を変化させて目標になるように計算させます。
計算を実行すると、体重の表示が68から67.375に変更されました。
ゴールシークダイアログでOKを押すと、確定。キャンセルを押すと元に戻ります。
目標 の 営業利益 から 必要な 売上高 を ゴールシーク で求める
ちょっと、難しい専門用語かもしれません。管理会計が分からなくても大丈夫。
要するに、 売上高 から材料などの 変動費 を 減算 して、 限界利益 を出す。
次に、 限界利益 から家賃などの 固定費 を 減算 して 経常利益 を出します。
限界利益＝売上高ー変動費
経常利益＝限界利益ー固定費
売上高100,000から変動費60,000を減算して、限界利益40,000
限界利益40,000から固定費15,000を減算して、経常利益25,000です。
ゴールシーク で シミュレーション
では、ここから、分析を始めます。
経常利益が25,000では少なかったので、30,000に利益アップするには、売上高をいくらにすれば良いか？をゴールシークで求めます。
結果が出ました。経常利益を30,000にするには、①売上高を112,500にする必要がある。となりました。
目標の 営業利益 から、必要な 変動費率 を求める
先ほどは、売上高を上げて経常利益をアップしました。
今度は、経常利益25,000の状態から、売上高を固定して変動費率を変化させ、何％にすれば、経常利益を30,000にすることができるのか、分析します。
数式入力セル C6 と 目標値 は同じで、変化させるセルを 変動費率 のF2として計算を実行します。
変動費率が55％に変更され、経常利益が30,000になりました。
当然ですが、売上高は100,000のままです。
これは、経常利益を5,000改善するには、変動費率（すなわち、材料比率）を5％下げることでも、達成できるということが分析から分かりました。
まとめ
- 目標を掲げて、何をどう変化させれば、目標が達成できるかを検討する時に、ゴールシークを使うと計算の手間や時間が節約され、効率よく分析ができます。
- 経常利益の改善には、売上高を増やす方法、変動費率（材料比率など）を下げる方法など色々なアプローチがありますが、ゴールシークで計算できます。
- 計算式が複雑であればあるほど、人が計算すると時間が掛かりますから、ゴールシークを使用する高価が高まります。
今回は、以上です。
最後まで、有り難うございました！
コメント