感度分析とシナリオ分析

操作編

エクセルを使ったシミュレーションで、是非定番スキルとして習得しておきたいのが感度分析とシナリオ分析です。

※今回使用した元になるエクセルファイルはこちらからダウンロードできます。
https://www.dropbox.com/s/8mg92tn8m7vij3d/20131211_sensitivity.xlsx?dl=0


感度分析とは、アウトプット（たとえば利益や売上高）に影響を与えるインプット要素（たとえば、価格や数量、売上原価など）をモデル化して把握しておいて、それぞれのインプットが変化した時にどのくらいアウトプットに影響を与えるかを分析して把握しておくために行います。これをすることで、経営者として業績に大きな影響を与えるようなインプット要素を把握することができるので、事前に対処法や準備をしておくことが可能になります。たとえば、輸出産業の会社において、為替が一円円高に振れたらどのくらい収益に影響を与えるのかを把握しておく。それが非常に強烈なインパクトになるのであれば、為替予約をしておく、支払いを円建てにしておくなどの対応法がとれますよね。

シナリオ分析とは（感度分析が、各インプットを独立させて、それぞれが単独で変化した時にアウトプットにどのような影響を与えるのかを把握する手法であるのに対し）、あるシナリオのもと、複数のインプット要素が変化した時に、一体アウトプットがどのような数値になるのかを把握するための手法です。

個人的にはエクセルでシミュレーションをかけることが多く、感度分析もシナリオ分析も良く使いますけれども、感度分析はどちらかというと自分自身のため。つまり各インプットがアウトプットに与える影響の大きさを理解し、自分の携わる事業のリスクを把握するために使います。もし誰かにプレゼンテーションなどで感度分析の結果について語ることがあるのであれば、トルネードチャートなどで可視化させてあげたほうがわかりやすいかもしれないですね。

一方シナリオ分析は、説明する相手のため。つまり、想定されるシナリオがいくつかあって、その時にアウトプットがどうなるか、ということを説明するときに使いますね。経験上、プレゼンの場などで感度分析のような「パズルのピース」のような話をしても話が繋がらない事が多い。やっぱりプレゼンは「ピースを組み立てて全体像を見せる」べきだと思ってます。

知っておくと便利なエクセル関数（2）〜数値の編集編

操作編

表計算以外にも数値を扱ったデータの加工修正などにおいてもエクセルは力を発揮します。今回は良く見受けられるシステムなどからダウンロードした生データを加工する際に便利な関数をいくつかご紹介しましょう。

今回の動画はライブ版でお届けします（笑）いつもは一人でPCに向かって画面をキャプチャしながら話しているのですが、今回は聴衆の方がいらっしゃるので心なしかテンション高めです。

日付・時刻データを使いやすいように加工する

日付データや時刻データは実は数値情報なので、この特徴を活かして加工をすることが可能です。下記の動画では、ROUND関数（ROUNDUP関数・ROUNDDOWN関数を含む）を応用して、データを加工編集するテクニックをご紹介しています。

YEAR関数・MONTH関数・DAY関数・WEEKDAY関数・TODAY関数を活用する

日付や時刻データは数値なので、その特性を活用すると計算にも応用できますが、一方細かすぎて大きな枠でとらえる際（年次ごととか月次とか）は使いづらいことがあります。そんなときにちょっと頭を使って応用することでまとめるちょっとしたティップスです。

データを分割したり、半角に変換する

LEFT関数やRIGHT関数を使って、セルの中に入っているデータをうまく分割する方法をご紹介しています。また電話番号や郵便番号などのデータなどで全角が混じっていて一つ一つ変換する手間に困っている人向けに、ASC関数を使った簡易な編集テクニックもご紹介しています。


今回はちょっとティップス傾向が強すぎましたかね。まあ、いろいろこれからも入れていきますので、たまにはということで。

知っておくと便利なエクセル関数（1）〜集計編

操作編

エクセルは「表計算ソフト」とも言われるくらいですから（もう死語？）、数値を集計する際に威力を発揮するわけですけれども、簡単な四則演算だけでなく、これらの便利な関数を知っておくと本当に作業効率が高まります。

今回は基本的だけど頻繁に使える、集計の関数をいくつかご紹介します。

基本的な集計（SUM関数、AVERAGE関数、COUNT関数）

もうすでに知られている関数なのであまり説明は必要ないかもしれませんが、一応ご紹介。

  • SUM関数・・・足し算ですね。=SUM(H2:H10)というのは、H2セルからH10セルまでの数値を足し算しなさい、というもの。
  • AVERAGE関数・・・その名の通り平均を算出します。=AVERAGE(C2:C10)というのは、C2セルからC10セルまでの数値の平均を算出しなさいというもの。
  • COUNT関数・・・これもその名の通りカウント、つまり件数を数えなさい、というもの。=COUNT(G2:G10)というのは、G2セルからG10セルまでの範囲にある数値が何個あるか数えなさい、というもの。

ちなみにこれら3つの集計数値は（平均、データの個数、合計）画面の下に現れるステータスバーにも表示されます。参照するくらいなら関数を入れなくても出ます。

条件付き集計（SUMIF関数、AVERAGEIF関数、COUNTIF関数）

先ほど述べた基本集計の関数の応用で、条件付きで集計するときの関数です。

  • SUMIF関数・・・条件付き足し算。ある条件を満たしたセルだけ算出します。=SUMIF(E2:E10,"法人",H2:H10)というのは、E2セルからE10セルの範囲で「法人」と入力されたものを見つけてきて、それに対応するH列の数値を足しなさい、というもの。
  • AVERAGEIF関数・・・条件付き平均。ある条件を満たしたセルの数値の平均を算出します。=AVERAGEIF(D2:D10,"男",F2:F10)というのは、D2セルからD10セルの範囲で「男」と入力されたものを見つけてきて、それに対応するF列の数値の平均を出しなさい、というもの。
  • COUNTIF関数・・・条件付きカウント。同じく、ある条件を満たしたセルが何個あるかを数える関数。=COUNTIF(D2:D10,"女")というのは、D2セルからD10セルの範囲で「女」と入力されたものを見つけてきて、何個該当するかカウントするもの。

今は条件の欄にダブルクオーテーション（"）で囲ってますが、エクセルを操作するときの鉄則の一つは「ベタ打ちをせずに参照する」事なので、12行目あたりのセルに入力セルを用意して、そこに男・女とか法人・個人とか入力し、それを参照するほうがいいです。後でそのセルだけ変えてあげれば簡単にシミュレーションできますからね。

配列の積を計算する（SUMPRODUCT関数）

個人的に良く使うのがこのSUMPRODUCT関数。たとえば複数の商品があるときに部門全体の利益率の加重平均を算出するとか、在庫額を計算するときに各商品の単価と在庫数量を掛け合わせるとか、そういったときにこのSUMPRODUCT関数を使います。
今回のケースだと、たとえばこれまで受講したテストすべての平均点を出したい時に、

  1. =SUMPRODUCT(G2:G10,H2:H10)とやって、G列とH列の対応する数値をそれぞれ掛け合わせて合計点を出し、
  2. 出てきた数値をH14の受講回数合計で割ってあげる

と欲しい数値が算出されます。


また詳しくは動画でご確認ください。

時系列グラフの作り方

グラフ編

今回は時系列グラフを作ってみましょう。グラフの作成手順自体は全く難しくありませんが、そのグラフで何を伝えたいのか、メッセージは何か？ということを意識して修飾してみたいと思います。今回のデータは、私の大先輩がたが経営している高島株式会社の売上高および経常利益のデータを使ってみたいと思います。

高島株式会社は平成28年3月期に、売上高1,000億円、経常利益17億円という目標を掲げている会社です。今回のグラフでは、「掲げた目標値に対して、現在の実績トレンドはオントラックなのかどうか」という観点でメッセージ性を出してみたいと思います。

詳しくは下記の動画を見ていただければお分かりいただけますけれども、ポイントとしては、

  • 棒グラフと線グラフを両方使う。かつ、両方のグラフが重なりあったりしないように目盛を調整する。
  • 実績値、目標値、予測値（推移値）を明確に色で区分する。
  • あとはいつものように、フォント（Meiryo UI／14）、メモリ線の削除、棒グラフの太さの調整など、デフォルト設定をそのまま使わずに見やすい形に工夫する

ことを意識しています。

ちなみに、平成26年度および27年度の計算の方法は、CAGR（Compound Annual Growth Rate：平均年間成長率）を算出して、前年の売上高および経常利益に掛け合わせています。この計算の方法は下記の通り。

CAGR＝（H28年度の数字÷H25年度の数字）^（1/3）

^というのは乗数（2乗とか3乗とかのアレ。2の3乗は8ですが、エクセルで計算するときは2^3という形で計算できます）を表します。つまり^(1/3)というのは、3分の1乗なのですが、これはいわゆる3乗根（ルートといったほうがわかりやすいですかね？たとえば8の3乗根は2になりますが、エクセルでは8^(1/3)という形で計算できます）を表します。

ちょっとわかりにくければ逆数を考えればいい。H25年度の数字がある一定の成長率で伸びていくとH28年度の数値になる。これが成り立つような「成長率」を方程式で算出してあげればいいわけですね。

劇的に作業時間を短縮するエクセル操作テクニック集

操作編

エクセル作業で最も時間を要するのは、実はマウス操作です。マウスを動かす、左クリックする、右クリックする、選択肢を選ぶ・・・ちょっと意識すると、この操作だけで結構な時間を使っている事がわかります。逆に言うと、このマウス操作を簡略化させれば、エクセル作業に要する時間は劇的に短縮することができます。

ここでは知っておくと本当に便利な、作業時間短縮のためのショートカットやキーボード操作のテクニックをご紹介します。是非、皆さんのエクセル作業の際にご活用ください。

エクセルシート／ファイルを移動する時間を劇的に短縮するテクニック

これを知っているだけで、マウスを使って右端や下にあるスクロールバーを動かしたりする必要がなくなります。これだけで作業時間の70％は短縮（※時間は筆者の感覚値）。

  • Enter / Shift + Enter　・・・セルの入力を確定して下のセルに移動する（Shiftを押しながらだと上のセルに移動する）
  • Tab / Shift + Tab　・・・セルの入力を確定して右のセルに移動する（Shiftを押しながらだと左のセルに移動する）
  • Page Up / Page Down　・・・上下にスクロールする
  • Ctrl + カーソル（←↓→↑）　・・・入力されている連続するセルの端までジャンプする、もしくは空白を飛び越して入力されているセルまでジャンプする

エクセルファイルの中に複数シートがある場合の移動や、複数ファイル間を移動する場合、またアプリケーション間を移動する場合にも、画面下のタスクバーを操作することなく移動することができるテクニックもあります。仕事とは関係ないアプリケーションをあけているときに誰かが後ろを通った時、これを知っていると何気ない操作で画面を切り替えることができます（笑）

  • Ctrl + Page Up / Ctrl + Page Down　・・・ファイル内にあるシートを移動する。Page Upで左に、Page Downは右に移動。
  • Ctrl + Tab　・・・　複数のエクセルファイルを順に移動
  • Alt + Tab　・・・　アプリケーション間を移動。画面にアクティブなアプリケーションが表示されるので、Alt + Tabを複数回押して、移動したいアプリケーションおよびファイルを選択する

シートの編集にかかる時間を劇的に短縮するテクニック

エクセルシートにセルや行列を挿入したり、印刷範囲を指定したり、とかくシートの編集をすることも多いかと思いますが、これも上記同様、知っておくと便利なショートカットがたくさんあります。

  • Ctrl + スペース　・・・列を指定
  • Shift + スペース　・・・行を指定。ただし半角状態なっていないとできません。
  • Ctrl + A　・・・（エクセルが自動判別して）データベースの範囲を指定。繰り返すと全ページ指定
  • Ctrl + C　・・・コピー
  • Ctrl + X　・・・カット（切り取り）
  • Ctrl + V　・・・貼り付け
  • Alt →　E →　S　・・・選択して貼り付け

文字やセルの修飾をする時間を劇的に短縮するテクニック

会議やプレゼンテーションに、エクセルで作ったチャートなどを提出する際に、文字やセルを修飾するのは不可欠ですが、これをするのも結構時間がかかりますよね。これを知っておくと、その就職にかかる時間も大幅に短縮できます。

  • Ctrl + B　・・・太字にする
  • Ctrl + I　・・・イタリック（斜体）にする
  • Ctrl + U　・・・アンダーラインを引く

後はAltキーを使ってツールバーのメニュー選択を手元のキーボードで行うこともできます。よく使う修飾としては次のようなものがあげられます。

  • Alt → H → 0 （または9）　・・・小数点表示を一桁増やす（9を押した場合は、一桁減らす）
  • Alt → H → P　・・・パーセンテージ表示にする
  • Alt →　H → K　・・・桁区切り記号のコンマを付けて表示する
  • Alt →　H → FF　・・・フォントの指定をする
  • Alt →　H　→ FG（またはFK）　・・・フォントを大きくする（FKの場合は小さくする）
  • Alt →　H →　FC　・・・フォントの色を変える
  • Alt →　H →　O　・・・フォントの書式自体を変える

ツールバーの中によく使う操作キーがある場合、Altキーを使って操作する方法を覚えておくと、時間短縮になりますよ。

回帰分析を活用して損益分岐点売上高を計算する

ケーススタディ編

今回はエクセルの回帰分析の機能を用いて、実際の企業の損益分岐点売上高を計算してみましょう。題材としては日産自動車の財務諸表を使います。（損益分岐点の考え方に自信がない方は、こちらをご参照ください）

損益分岐点売上高を計算するための手順は、下記の3ステップになります。

  1. 変動比率、固定費額を算出する
  2. 算出された変動比率、固定費額をもとに、事業の収支モデルを作る
  3. 収支モデルにおいて損益トントンになるような売上高をゴールシークで算出する

※ゴールシークの使い方についてはこちらをご参照ください。

プロセスとして文字に書くと簡単そうなのですが、一体変動費率はいくらなのか、固定費はいくらなのかはなかなか分かりにくいものです。

一般的に使われるのは、損益計算書上の勘定科目を見ながら、変動費要素、固定費要素を分けてみるというアプローチ。右のチャートは、平成24年度および25年度の日産自動車の連結損益計算書ですが、たとえばこれを参考にして、売上原価、サービス保証料、販売諸費などのコスト項目は売上成績によって連動することが想像されるので「変動費」とする、というやり方ですね。ただし、製造原価と言っても、製造機械などは固定費的要素も少なからず含まれていますから、なかなか正確に把握することができません。そこでもう一つのアプローチとして、回帰分析を使って損益分岐点売上高を算出する方法をご紹介します。

回帰分析とは、簡単に言えば過去の事象の中から法則性を見つけ数式化すること。つまり過去の業績から変動比率、固定費を算出するアプローチです。
つまり【コスト＝売上高ｘ変動比率＋固定費】という式に過去の売上高、コストを当てはめて、帰納的に変動比率、固定費を算出するやり方ですね。具体的には平成17年から平成25年にかけての9年間にわたっての売上高・コストを二つの変数として取り上げ、散布図を描き、そこに近似直線をひいてあげると、それが売上とコストとの関係性になってきます。その方程式（Y=aX+b）にあてはまるaの部分が変動比率、bの部分が固定費になります（上記赤字で書いた式の通り）。

ここまでできれば後はステップ2と3を踏んであげれば損益分岐点売上高は計算できます。具体的な操作方法については下記の動画でご紹介してますので、ご参照ください。

What-If分析

操作編

エクセルの素晴らしいところはシミュレーションをかけられることです。ロジックを組んでおくと、各インプットを動かすとどのようにアウトプットが変化するか、簡単に把握することができます。

このシミュレーションをさらに便利にするのがWhat-If機能。特にゴールシークやデータテーブルという機能は知っておくととても便利。経営者が気にしがちな「こういう場合は・・・」という質問にも簡単に対応できます。

  • 3年後の利益目標を達成するには、新規顧客何人を今年確保すればいいか？
  • 売上高と広告宣伝費の関係、いくらだったらどのくらいの利益率になるのか？
  • NPVをプラスにするには、あとどれだけの売上高アップ（もしくはコスト減）が必要なのか？

といったようなシミュレーションをするときに効果を発揮します。

What-If分析は、エクセルのツールバーの「データ」の中にあります。このWhat-If機能の使い方を下記の動画でご紹介していますので、ご参照してみてくださいね。