目次
こんにちは。
このブログでは、エクセルを使って定量分析をしたり、プレゼンテーションの資料を作ったりするときのヒントとなるようなティップスをご紹介していきます。これまで私がコーポレートファイナンスの仕事をする中で、またMBAの講義をする中で蓄積した、我流ではありますが知っておくと便利なノウハウを集めております。あくまでも我流ですのでね、そこはご容赦を。
操作編
- 劇的に作業時間を短縮するエクセル操作テクニック集
- 知っておくと便利なエクセル関数
- what-if分析
- 感度分析とシナリオ分析
- ソルバー（制約条件下で最適解を求める）
- 重回帰分析
- ピボットテーブル
類似企業のβを使って自社のWACCを計算する
今回は、企業が債権者・投資家に対してコミットするリターン、すなわち加重平均資本コスト（Weighted Average Cost of Capital：WACC）の算出方法について解説をしてゆきたいと思います。
WACCそのものの概念についてはこのコラムではご説明をしませんので、内容について詳しく理解したい方は、下記YouTubeチャンネルをご参照いただければと思います。
こちらのコラムでは、実務的にどのようなアプローチでもってWACCを計算するのか、特に非上場企業のように自社のβ値が市場データから入手することができないケースを想定して、デモンストレーションをしたいと思います。今回も従来通り、事例を使った解説をしてゆきたいと思います。
非上場企業であるA社のCFOであるあなたは、投資家からの資金調達を行うにあたり、このタイミングで自社のWACCを算出することにした。
株主資本の期待収益率（rE）を算出するために、マクロ経済データ（国債10年利回りやマーケットリスクプレミアムなど）をWebなどから入手するとともに、自社と類似の事業を行っている企業のデータも入手した。これらの情報は下記に記している。
また当社の事業規模は、相対的に小規模のため、専門家のアドバイスによりサイズプレミアムを乗せるべきだとの結論に達した。市場調査会社によると、当社の規模感であれば、サイズプレミアムとして3.5%は上乗せするべきとの意見を得た。
これらの情報を使って、自社のWACCを算出したい。どのようなアプローチでもってWACCを計算したらいいのだろうか。
WACCとは、税引き後の債権者の期待利回り（rD）と株主資本の期待利回り（rE）の加重平均です。よって、まずはrDおよびrEを算出しなければいけません。
rDはすでに1.25%とデータがあるので、これに（1－税率）を掛けてあげれば税引き後の債権者の期待利回りを出すことは簡単にできます。今回のポイントはいかにrEを算出するか、ということになってきます。
rEはCAPMの公式を使って推定することができます。CAPMによるrEの算定方式は、
rE = リスクフリーレート + β値 x マーケットリスクプレミアム
ですので、そのパラメータの中に数字を入れていけばいいですね。リスクフリーレートには国債10年利回りを適用させてあげればよく、マーケットリスクプレミアムは6%とデータがありますので、それを直接当てはめてしまえばいい。ここまでは何ら難しくはないです。
ポイントになるのは、当社のβ値。通常であれば、日経新聞やロイター、東京証券取引所などが発表しているβ値を使えばいいのでしょうが、当社は非上場企業ということでβ値がありません。そこで同様の事業を営む類似企業をピックアップし、「同様の事業であれば事業リスクは同じである」という前提に基づいてβ値を抽出する、ということを行います。
ただし、β値というのは、負債比率によって上下します。つまりβ値とは、「事業リスク」と「株主が背負う財務リスク」の二つを反映しているため、「事業リスク」だけをピュアに取り出そうとするのであれば、「無借金時のβ値」すなわちアンレバードβに算出してあげることが必要となります。
つまりここでやろうとしているのは、類似会社のβ値を、いったん無借金時のβ値（アンレバードβ）に直してあげて、「事業リスク」をピュアに反映させたβ値をいったん抽出する。その後自社の負債比率に応じて、もう一度リレバーしてβ値（レバードβ）を算出する、というプロセスを追うわけです。
このあたりを文章で書くと、とてもややこしくなってしまうので、これらの算出ロジックを動画でまとめてみました。下記YouTube動画でご確認いただければと思います。
将来の事業計画をもとに、PLおよびBSを作り、必要な調達金額を算出する
あけましておめでとうございます。2014年もこつこつとティップスをためていきたいと思います。基礎的なティップスはある程度網羅したと思うので、今後は時事ネタや実務で使うシーンを想定したケーススタディ編を充実させていきたいと思います。
さて今回は、事業計画を立てる際に重要なポイントとなる「必要な調達資金の額」を算出するための考え方およびエクセルテクニックをご紹介。将来の損益計画や投資などの条件をもとに、将来の貸借対照表（BS）を作るとともに、必要な資金の額および調達方法について検討するポイントを解説します。今回もいつものように例題でもってご説明。
ハワイのオーガニック化粧品の輸入販売会社を経営するオカダ社長。創業5年たって売上高2億4千万円、営業利益も黒字化するなど、事業の基盤が固まってきたと思っている。これまでは単年度赤字、資金不足、人不足もあって、積極的な広告宣伝活動ができなかったし、システム基盤も脆いままごまかしながらやってきた。しかし第5期にようやく単年度黒字化を達成したということもあって銀行の融資姿勢も好意的になってきたので、第6期は積極的に広告宣伝やシステムに投資を行い、事業の加速度的成長を狙おうと思っている。
役員会で議論を進めてきたところ、システムへの投資および広告宣伝費に投資を行えば、売上高は今後3年間で飛躍的なペースで成長できそうだという結論に至った（その前提となる詳細数値は下記参照）。
さて、一体この計画を達成するためにはどのくらいの資金を調達すればいいのだろうか。またその調達方法は融資、増資、どちらで行うべきだろうか。ちなみに現在の銀行からの融資は第6期から毎月50万円の元金返済の約束を交わしており、年間の借入利率は3.7％。新規の借入れの利率も同率という条件を出されている（60か月の元金均等返済）。
ケースに関する資料（過去5期の財務諸表および将来計画の詳細条件）はこちらからダウンロードできます。cashflowsimulation_pre.xlsx
必要な資金を算出するための方法はいくつかありますが、ここでは次のステップに沿ってエクセルシートを作成する方法を解説しています。
- 所与の条件で将来のPLを作る
- 売上や売上原価に連動する運転資金（売掛金、買掛金、棚卸資産）の金額を確定させる
- 固定資産投資の金額を入れる
- 必要最低限の現金預金の金額を入れる（何日分の売上高に相当する現金を保有すべきか、と考えると算出しやすいですね）
- BSの残りの項目に数値を入れる
- BSがバランスするために必要になる金額（＝資金調達すべき金額）を算出する
PLとBSは連動していますからね。丁寧に順番を追って入力していけば必ず財務諸表を作ることができます。具体的な手順に関しては下記の動画をご参照ください。
※作成したエクセルシートはこちらからダウンロードすることができます。
cashflowsimulation_post.xlsx 
さて、今回の事業計画を遂行するにあたり必要となる資金は総額で1億6千万円あまりになることがわかりました。ここで考えるべきポイントは二つ。すなわち、
- 増資と融資、どちらの調達方法を採用するか
- どのタイミングでいくら調達するか
について最終的な意思決定を下さなければいけません。
算出した数値に基づいて「解釈」する
増資（資本金）で調達するか、融資（借入金）で調達するかを判断するには、それぞれの調達方法の特徴を押さえておく必要があります。一般論ですが、その二つの調達方法のメリットデメリットをまとめると下記の通りになります。
今回のケースであれば、ポイントになるのは二つになりそうです。すなわち、
- 投資（システムおよび広告宣伝費）に対する売上増加、利益増加の確実度がどのくらい高いのか？
- 仮に資本金による調達を選択するのであれば、投資家の希望に見合う条件になっているのか？
という点で議論を進めて最終的に意思決定をすることになるでしょう。
確実度が高ければ、返済義務があり返済スケジュールが決まっている融資であっても、低い資本コストの融資（借入金）で調達するほうが合理的です。キャッシュの出入りがほぼ見えているような運転資金などの調達は融資で行うのが鉄則です。一方、将来のキャッシュフローの不確実性が高いのであれば、そのリスクを負える人から調達することのほうが望ましい。リスク許容度の高いのは一般的には投資家ですから、資本金による調達を行うことが望ましいでしょうね。一般的に投資家のおカネが「リスクマネー」と呼ばれるのはそういう理由によるものです。
ただし、仮に不確実性が高いのでリスクマネーである増資（資本金）で調達しようと思っても、投資をしたいという投資家がいなければ調達はできません。ベンチャーキャピタルなどはそのような小規模ビジネスに対する投資家の代表格ですが、彼らが期待するビジネスサイズもベンチャーキャピタルによって違いがあります。数百億円、数千億円の事業規模になりそうなビジネスに絞って億単位を投資するベンチャーキャピタルが存在する一方、数千万円規模の投資ですぐにエグジット（投資回収）できる複数の案件に興味を持つベンチャーキャピタルもいます。投資家との交渉や契約締結にも時間がかかりますが、投資家の期待値を確認したうえで増資を行うことが重要です。
この手の話に絶対解はありませんが、個人的な見解としてはこんな感じになるでしょうか。
これまでとは非連続的なビジネス展開をするにあたり将来のキャッシュフローの不確実性が強いので、基本的には増資によるリスクマネーの調達を第一に考える。年度ごと都度調達するというよりは、この3カ年の成長戦略は一連の戦略によるものと考え第6期のタイミングでまとめて調達をする。ただし同時に運転資金などの不確実性の低い資金需要に関しては融資での調達もできるように銀行との折衝を同時並行で行っておく。こちらは必要に応じて都度調達できるような状態にしておくことが望ましい。
「リスクマネー主体の資金調達」というのがオカダ社長の基本路線ではあるが、交渉の相手側の投資家が魅力に感じてもらえるような案件かどうかは事業規模の観点やビジネスモデルの新規性の観点から見るとまだ不透明。交渉をする中でより大きな、より斬新なビジネスを求められることを想定し、ビジネスモデルのファインチューニングができるかどうかを検討する。
最後の最後は、その投資家と長期間「握れる」関係を作れるか・・・というのが重要な観点のような気がします。
いかがでしょう？皆さんのご見解、ご意見もお聞かせいただけると嬉しいです。
様々な制約条件の中から最適解を求める〜ソルバー機能の活用
今回はソルバー機能について解説したいと思います。様々な制約条件がある中で、最適な解を求めるときに活用できる機能です。大変便利なので是非習得してみてください。今回もいつものように事例を使って解説しましょう。
B社は特殊な部品を委託製造する会社。ひとつの機械を活用して様々な部品を製造している。
来月納期の製造依頼を受けたのが全部で5社ある。この5社は長い付き合いなので最低限の要望には応えたいが、すべての要求をのむのは難しいと思っている。下記の制約条件をクリアしながら、粗利額を最大化するには、5つの商品の製造量をどのように決定したらよいだろうか考えたい。
- 製造装置はフル稼働（週7日x24時間）しているが、メンテナンスや製造切り替えのため稼働率は80％と想定される。
- それぞれの商品はすでに最低限の数量は決まっていて、それは確実に製造したい。それ以上は希望数量まで到達できればOKとする。
まずはエクセルでシミュレーション表を作りましょう。今回は「粗利」の最適化が目的なわけだから、製造数量を入力すると粗利額が計算できるようなシートを作ってみる。その際、製造時間に限界があるので（今回のケースだと、30日x24時間x60分だけど、80％の稼働率・・・という条件がある）合わせて製造時間も算出する。これがキャパを超えないように見てあげる必要があるわけですね。ざっとこんな感じのシートになるでしょうか。
このシートさえ作ったらソルバーを立ち上げて、
- 目的セル＝粗利額合計(赤枠で囲ったところ)を指定し、
- 目標値が最大になるようにチェックボックスを入れて指定し、
- 変化させるセルとして各商品の製造数量(黄色で色付けしたセル範囲)を指定し、
- 制約条件を入力してあげて
実行すると最適な製造数量の組み合わせがあっという間に算出されます。いかがですか？簡単でしょう。とても便利な機能です。
詳しいプロセスは下記の動画をご参照ください。今日は病み上がりの上、経堂のPAX Coworking Officeでの収録ということで、照れも入って声のトーンが低めですが、内容は大丈夫です（だと思いますｗ）
ちなみに、ソルバー機能はアドインなので、入っていない場合はOfficeボタン→Excelのオプション→アドインで選んでアクティブにしてあげる必要があります。
モデル化して売上計画を作る
今回は実践編ということで、事業計画を立てるときの売上計画の作成法について考えてみたいと思います。たとえば・・・ということで今回はこんな事例で考えてみましょう。
ホールフーズ社はサプリメントを輸入販売している企業。これまで好評だった「ナチュラルケア」というブランドにグルコサミン配合の新商品が出た。この新商品の事業計画を立てるにあたり、売上高の予測をしていきたい。
「ナチュラルケア」ブランドではこれまで3つの商品群があって、それぞれ年商3億円、2億円、1億円の商品に成長しているが、今度の新商品はそれらを上回りそうな勢いを感じている。
「ナチュラルケア」ブランドは、消費者が実感しやすいという特徴を持ち、一度使ってもらうとかなりの確率でリピート購入してくれていた。これまでの実績値で言うと、サンプル利用者の30％がトライアル購入をし、またトライアル購入者の50％がリピート購入している。この率は業界では特筆すべき好成績である。
さて、一体この商品の3年間の売上はどのようなものになるであろうか。
ここでガッツで作った適当な数値を放り込んで後で大変になる、ということは結構あります。ここで言う「適当」とは、過去一番好感触だから5億円は行くだろう、とか、これまでのトレンドを引っ張って今後数年は年率○○％で成長する、なんてのも含みます。まあ、希望的観測としてはいいですが、それはあくまでも目標値であって計画ではない。
売上高というのはアウトプットであり、単価や顧客数、さらにそれらを分解した一つ一つのインプット要素の積み重ねでできあがるわけです。いくら売上高10億円を目指そう！と意気揚々に言ったところで、所詮アウトプットに直接影響を与えることはとっても難しい。僕ら人間ができることは一つ一つのインプットに効く打ち手を考えて、その結果としてアウトプットがどう変化するかを考える事。これを僕らは「売上高をモデル化して考える」なんて言ったりします。
今回のケースでモデル化すると、おおよそこんな感じになるでしょうか。
見ていただいてお分かりの通り、新商品の売上高を3つの顧客層別に分解しています。すなわち
売上高＝新規客による売上高＋リピート客による売上高＋ロイヤル顧客による売上高
と分けて考える。さらにそれぞれの売上高の内容も「顧客数」と「単価」とに分解し、さらに顧客数も構成要素に分解していく。いわゆるMECEに（モレなくダブリなく）分解していくわけですね。あとは、この各要素（今回は薄い青に色付けした要素）に数値を当てはめていけば、最終的な売上高計画を立てることができる、というわけです。
どうですか？ざっくりと「年商5億円！」なんてやるよりも、具体的にどの要素の数値はこれくらいを目標にして、そのためにはこういう施策を打って・・・とやったほうが具体的に目標を達成させるイメージがわいてくるんじゃないですかね。もちろん、計画ですからこの通りに行くことのほうが少ないですが、実際にやってみて当初計画とギャップが出たら、その構成要素を見て、もともとの数値と大きくかい離しているものが何かを把握すると、より的確な問題分析・問題発見ができるようになります。新しい施策を考えてそれを実行した時に、売上高がどのように変化するか？なんていうシミュレーションもこのモデル式を使うとできるようになりますよね。いいことづくし。修得しない手はない。
動画では上記の考え方に基づいて、具体的にエクセルシートで計画を作る工程について説明しています。シートの修飾法も含めてご参照してみてください。今回作成したエクセルシートはこちらからダウンロードできますので、合わせてご参照ください。sales_simulation_1.xlsx
二つ目の動画では、月次で集計した売上高をSUMIF関数を使って年次でまとめる方法、およびライフタイムバリュー（LTV）の考え方を使って「顧客一人獲得するためにねん出できるコストの算出方法」などについても説明しています。ちょっと説明がグダグダですので、わかりにくければフィードバックください（この辺がすでにグダグダ・・・ｗ）
ピボットテーブル
今回は大量のデータを有したデータベースを、容易に編集加工するツールであるピボットテーブルの操作法について説明します。ピボットテーブルを使うとクロス集計が簡単にできます。
クロス集計とは何か？クロス集計とは、複数のデータ項目を縦・横にとって、データを掛け合わせて集計する分析手法のことです。
今回の事例では、システムからダウンロードした受注情報の生データを使って、
- ブランド別の月次売上高をまとめる
- 中でも最も売れているブランドの月次売上を、取引先別に集計する
手順について説明をしています。今回の事例説明で使用したエクセルについてはこちらからダウンロードできますので合わせてご参照ください。pivot_table.xlsx
言うまでもなく、ツールはツール。分析のクオリティは本質的には「切り口のセンス」によります。ピボットテーブルが使えたからといって、達成できるのは時間の短縮（まあ、これも重要な要素ですが）、分析アウトプットの質が上がるとも限りませんのであしからず・・・
重回帰分析
エクセルには本当に多くの機能が搭載されていますが、今日はその中でも覚えておくと便利な重回帰分析のやり方について説明をします。
そもそも重回帰分析とは何か？事例を使って説明してみましょう。（僕は統計の専門家ではないので、初歩的に知っておいたほうがいいという点に絞って解説します。もし詳しく知りたい、アカデミックに内容を押さえたいと言う人は、wikipedia先生かgoogle先生に聞いてみてください。）
ある町のワンルームマンションの賃料を調べてみたところ、一番安いところだと4万4千円、最も高い物件だと11万円する。この賃料の差は、どうやら駅からの距離（徒歩での所要時間）、広さ、築年数といった要素が賃料に影響を与えている事は想像できるのだが、一体具体的にどのような法則性があるのか考えたい。ファイルダウンロードはこちら：apartment.xlsx
別に賃貸物件でなくてもいいです。過去の事例やすでにあるファクトなどから法則性を導き出すのが重回帰分析。ちょっと抽象的に言えば、何かしらのアウトプットの変数（「目的変数といいます）と、それに対して影響を与えるであろう複数の変数（「説明変数」といいます）とが、一体どんな関係にあるのか数式で示そうというのが重回帰分析。この数式が導き出されれば、説明変数さえ入手すれば容易に予測することが可能になりますよね。エクセルでの操作方法、分析結果で見るべき基本となるポイントについて動画でまとめたのでご参照ください。
重回帰分析を行うと、その分析結果がチャートで出てきます。着眼点は主に二つ。
- 数式がどんなものかを把握する
- 数式の精度を確認する
以下順を追って説明します。
数式がどんなものかを把握する
「係数」というところを見てください。これが目的変数を説明する数値になります。今回のケースでいえば、
賃料＝徒歩での所要時間ｘ（-674）＋広さｘ（1,758）＋築年数ｘ（-797）＋49,575
という式が導き出されます。この町のワンルームマンションの賃料は、この式で算出することができる、ということですね。とっても簡単。
数式の精度を確認する
数式は必ず算出されますが、精度が高いかどうかは別問題。精度が低ければ、その数式は出したものの使えない、ということになります。
この精度を確認するときに良く見るのが下記3点です。
- 重決定R2／補正R2・・・決定係数と呼ばれるものです。導き出した数式がどのくらい事象を説明できるか、その「あてはまり度合い」を示したものです。重決定R2と補正R2の違いは、説明変数が増えれば増えるほど決定係数であるR2は上昇するので、その変数の増減によるR2への影響分を修正したのが補正R2。なので、一般的には補正R2を使います。今回のケースで言うと、補正R2が0.80なので「80％この数式で事象が説明できる」といった感じになります。
- P-値・・・平たく言うと（平たく言いすぎ？）仮のこの変数がゼロだったとしてもこの式が成り立つ可能性がどのくらいあるか？というのを示したのがP-値。つまりP-値が小さいということは、この変数の影響は大きい、この変数は外せない、ということを意味します。逆にP-値が大きいということは、その変数は影響がないので外したほうがいい、ということを意味します。一般的にはP-値は0.05未満だとOKと言われてますね。ちなみにP-値の「P」とはProbability（起こりうる確率）のこと。その要素がなかったとしても数式が成り立つ「確率」ということですね。
- t・・・tは係数を標準誤差で割ったものです。重回帰分析をすれば係数はバシっとシングルナンバーで出るけれども、実際にはブレがあるわけですよね。それが簡単に言うと標準誤差なわけですが、このブレが大きいと係数自体の信頼性がなくなってしまう。その信頼性を図るためにtというのがあります。P-値とは違って、tは大きいほうが望ましい（標準誤差＝ブレが小さければtは大きくなる）。一般的には2以上でOKとしています。
繰り返しになりますが、今回の重回帰分析の説明はかなり砕いた形で説明をしていますので、より正確に把握したい。細かく内容を知りたいという方は、別のサイトを参考にしてみてください（笑）
