【MBA流】エクセル 超 基礎講座 - ビジネスに使えるテンプレート配布

海外MBA 海外生活

【MBA流】エクセル "超" 基礎講座 ビジネスに使えるテンプレート配布

更新日:

こんにちは、ゆうたろうです。

今回はMBAで学んだエクセル術シリーズの第一弾をお届けします。みなさん、こんな悩みありませんか?

  • ずっとエクセルに苦手意識があったが、今年こそ初心者レベルから脱却したい!
  • MBAで学ぶエクセルモデリングをビジネスに活かしたい
  • パワークエリ、パワーピボットを活かして、仕事の生産性を爆上げしたい!

この記事は、UCLAで経営学部の学部生向けの『エクセル "超" 基礎講座』を私がまとめたものです。エクセル初心者でもわかりやすいように解説しました。この記事を読むと、エクセルに関する以下のことが理解できるようになります。

  • 相対参照と絶対参照
  • if関数の使い方
  • グラフ作成とスクロールバー
  • 条件付き書式の使い方
  • NPV関数とIRR関数の使い方
  • What if 分析の使い方

実際に受講すると8万円程度の内容になります。

エクセルのテンプレートは以下のツイートから無料配布していますので、合わせてご確認ください。

また、MBAエクセル解説シリーズの第2弾として『MBA流エクセル術【ソルバー編】』も公開しておりますので是非こちらもご参照ください!

第3弾はPower Query、Power Pivotの使い方・テンプレートを配布する予定ですのでお楽しみに♪

それでは早速はじめましょう!

MBAでエクセルを教える理由

私がMBAを取った大学(UC San Diego)では、たかがエクセルの基本操作と簡単なモデリングを教えるためだけにUCLAの有名なアカウンティングの教授をわざわざ呼び寄せて講義をします。しかも、丸々4日間みっちりとやりました。(費用にすると、生徒1人当たり1日8万円 × 4日 = 32万円です!!)

アメリカでは、コンサルや投資銀行に勤めると、睡眠時間を削っても仕事が終わらない人と、睡眠時間をしっかり確保してかつ綺麗なプレゼンを作る人に分かれると言います。その違いはエクセルの使い方だと。

当時は『そんな大げさな』と思いましたが、その後、実際にニューヨークの投資銀行で働いているクラスメイトに聞いてみると『本当にその通り!』だったそうです笑。

ということで、全3回シリーズで実際に『MBAで教えられたエクセル術』を余すところなく解説します!今回の【“超” 基礎編】では、エクセルが苦手な初心者にも分かるように解説しました。身につけるかどうかはあなた次第です!

モジュール1:掛け算表

まずはエクセル操作の基本から紹介します。エクセルで掛け算の九九表をつくってみましょう。

できること:”$” の使い方がわかる(相対参照と絶対参照)

このエクササイズでは、今後のエクセル操作で必須の考え方『相対参照と絶対参照』を学ぶことができます。

まずは、あるセルに『A1』『B2』などの『セル番地』を入力します。このように普通にセル番地を入力した場合、全て『相対参照』になります。相対参照を使った数式をコピーすると、参照するセルそのものではなく、あるセルから見て『左隣のセル』といった位置関係がコピーされます。

例えば下記のように、D2セルに数式『B2*C2』を入力します。

相対参照1

D2セルの数式をコピーしてD3セルに張り付けてみると、『B3*C3』になりますね。これが相対参照です。

相対参照2

一方、絶対参照では、数式をコピーしても参照セルは変わりません。エクセルで絶対参照をするには、行もしくは列の前に”$”マークを使うことになります。

絶対参照

例題1

下記の黄色のマス(B2セル)に数式を記入し、あとは同じ数式を水色にコピー&ペーストするだけで、掛け算の九九表が完成するような数式を入力しましょう。

例題1

みなさんも自分のエクセルを使って数式を記入してみましょう。

解答

いかがでしょうか?答えは、まずB2セルに数式 ”=$A2*B$1” を入力します。

あとはその数式を水色のセル全体にコピーすれば完成です。絶対参照と相対参照のイメージができましたでしょうか?

解答1

モジュール2:価格計算

次の例題では、『紙』のように購入する数量によって単価が変わる商品の価格計算をエクセルでモデリングします。

できること: 『購入する数量によって単価が変わる商品』の価格計算

この例題では、エクセル操作で非常によく使う関数『if関数』の使い方を学ぶことができます。
※if関数の定義についてはUdemyの記事を参考にしてください。

例題2

下記のように購入する枚数によって単価が変化する『紙』の価格を計算しましょう。

例題2-1

紙をX枚購入した時の、トータルの金額を計算するスプレッドシートを作成しましょう。黄色のセル(C9セル)に、購入する紙の枚数(X枚)を記入し、トータル金額が青色セル(C10セル)に算出されるようにしましょう。

例題2-2

解答

いかがでしょうか?答えは、C10セルに以下の数式を入力します。

” =IF(C9<=400, C9*C4 ,IF(C9<=1000,400*0.6+(C9-400)*C5 ,400*0.6+600*0.5+(C9-1000)*C6))”

解答2

モジュール3:ウェブ予測

次の例題では、成熟市場と成長市場の2つの市場における、ある定期購読サービスの『新規カスタマーの獲得数』と『総カスタマー数』の違いを、エクセルでシミュレーションします。

できること: 2つの市場における『定期購読カスタマー数』の予測

この例題では、例題1で学んだ『絶対参照』の考え方も使い、エクセルで『成長市場の5年後の市場規模』の計算、および『新規顧客の獲得数』と『総顧客数』の推移を計算させることができます。

例題3

下記の2つの市場(シナリオ)における、毎月の『市場規模』、『未契約の総顧客数』、『新規顧客の獲得数』、『総顧客数』を、60か月分(5年分)エクセルで計算しましょう。

  • シナリオ1(成熟市場):トータルの市場規模(市場顧客数)は1,000,000人で、5年後も変化なし
  • シナリオ2(成長市場):トータルの市場規模(市場顧客数)は1,000,000人で、毎月1%成長
  • 市場にいる『未契約の総顧客数』のうち、2%が毎月『新規顧客』になる想定(新規顧客獲得率=2%)

1月目の『市場規模』、『未契約の総顧客数』、『新規顧客の獲得数』、『総顧客数』は下記のように与えられています。

例題3

上記の黄色のセルに数式を記入し、各月の『市場規模』、『未契約の総顧客数』、『新規顧客の獲得数』、『総顧客数』を計算しましょう。

解答

いかがでしょうか?答えは以下になります。

解答3

◆成熟市場の場合

  • 市場規模:常に1,000,000(B8セルに ”=B7” を入力)
  • 未契約の総顧客数:月数1は1,000,000でスタート、月数2からは『当月の市場規模 - 前月の総顧客数』で計算(C8セルに “=B8-E7” を入力)
  • 新規顧客数:毎月、『当月の未契約の総顧客数 × 新規顧客獲得率(2%)』で計算(D8セルに “=C8*$E$3” を入力)
  • 総顧客数:毎月、『当月の新規顧客数 + 前月の総顧客数』で計算(E8セルに “=E7+D8” を入力)

あとは月数2の計算式を月数3以降にコピーすれば完成です。

◆成長市場の場合

成熟市場の計算と比べて、『市場規模』の計算式だけが変わります。

  • 市場規模:月数1は1,000,000でスタートし、月数2からは前月の市場規模 × (1+1%)で計算(H8セルに ” =H7*(1+$K$4)” を入力)
  • 未契約の総顧客数:月数1は1,000,000でスタート、月数2からは『当月の市場規模 - 前月の総顧客数』で計算(I8セルに “=H8-K7” を入力)
  • 新規顧客数:毎月、『当月の未契約の総顧客数 × 新規顧客獲得率(2%)』で計算(J8セルに “=I8*$K$3” を入力)
  • 総顧客数:毎月、『当月の新規顧客数 + 前月の総顧客数』 で計算(K8セルに “=K7+J8” を入力)

あとは月数2の計算式を月数3以降にコピーすれば完成です。

【MBA流テクニック】グラフ作成

例題では『新規顧客獲得率=2%』で計算しました。これが2%ではなく、3%になったら『新規顧客の獲得数』や『総顧客数』はどのように変化するでしょうか?

計算式では『絶対参照』を用いているので、E3、K3セルにある『2%』を『3%』に変えるだけで全ての計算式が変わるのがわかります。これが絶対参照が非常に便利な点です。

MBA流テクニック 絶対参照

グラフ作成とスクロールバーの挿入

さらに、この『新規顧客獲得率』が変わった時に『総顧客数』がどのように変わるのか、一目で分かるようにグラフで表してみましょう。

グラフ作成の手順

まずはA6からK66までのすべてのセルを選択します。

グラフ作成手順1

次にExcel上部の『挿入』をクリック、『折れ線グラフの挿入』をクリックすると、さらに折れ線グラフの種類が選べるので、『マーカー付き折れ線』を選びます。

グラフ作成手順2

すると下記のようなグラフが表示されます。

グラフ作成手順3

ここでグラフの余白にカーソルを合わせてマウスの右クリックを押すと、下記のようなメニューが出てきますので『データの選択』をクリック。

グラフ作成手順4

凡例項目の中の『総顧客数』以外の全ての項目で、チェックボックスを外してください。(サイドバーを下にずらして全てのチェックを外します。)

グラフ作成手順5

すると、以下のように2つの市場の『総顧客数』だけのグラフになります。

グラフ作成手順6

詳細は省きますが、グラフのタイトルや背景色、軸の目盛設定は自分の好みに設定してみましょう。

グラフ作成手順7

最後に、スクロールバーを挿入します。まずはエクセル上部の『開発』をクリック、挿入をクリックして、スクロールバーのアイコンをクリックします。

スクロールバー挿入1

するとカーソルが矢印から『+』のマークに変わるので、エクセルの好きな場所にドラッグして『スクロールバー』を作成しましょう。

スクロールバー挿入2

さらにスクロールバーの上でマウスを右クリックし、『コントロールの書式設定』をクリック。

スクロールバー挿入3

『リンクするセル』をクリックし、エクセル上の『新規顧客獲得率』の2%のセルを指定します。(私のExcelテンプレートの解答を見ている場合はD3セル)指定できたら、OKをクリック。

スクロールバー挿入4

そうすると、スクロールバーの上下ボタンをクリックすることで、『新規顧客獲得率』の値が連動して変わり、さらにグラフの推移もリアルタイムに変わることが分かります!

スクロールバー挿入5

これを提案資料などに使うと視覚的にシミュレーションできてインパクトがあります!是非ご活用ください!

モジュール4:損益計算書の試算(データ予測・プロフォーマ)

企業の経営マネジメントに必須の損益計算書のシミュレーションをエクセルでおこないます。あるプロジェクトについて、投資すべきかどうかの意思決定をおこなうための判断材料にします。

できること: 複数のシナリオで損益計算書を作成する

この例題では企業のトップとして投資判断をおこなう際に、5年先の企業の損益がどうなるかをシミュレーションすることができます。具体的には、『if関数』、『条件付き書式』、『NPV関数IRR関数』の使い方と、『What if分析(ゴールシーク)』と呼ばれる手法を学ぶことができます。

例題

あなたは小型コンピューターを製造・販売する会社を設立しました。2.5億円の投資をおこなう前に、投資元であるベンチャーキャピタルから今後5年先までの損益計算書を提出するように求められています。損益計算書には『販売台数』『売上』『変動費』『マーケティング費用』『固定費』『税引前利益』等が含まれています。

下記の条件をもとに、5年分の損益計算書を完成させて下さい。

  • ベンチャーキャピタル投資額は2.5億円
  • 初年度の販売台数は1,600台
  • 初年度の小型コンピューターの単価(販売価格)は1台あたり18万円
  • 市場の成長により、2年目以降の年間販売台数は毎年『前年の販売台数の2倍』になる見込み
  • しかし、競争の激化により販売価格は毎年15%下落する
  • 初年度の1台あたりの製造コストは10万円
  • 技術進歩により、製造コストは毎年6%下がる
  • 年間の固定費は1億円
  • 年間のマーケティング費用は売上トータルの14%
  • もしメリットがあれば、コンピューターの製造に『自動組み立て装置』をリースして導入することも可能
  • 『自動組み立て装置』を導入すると、初年度の1台あたりの製造コストは20%下がる
  • しかし『自動組み立て装置』導入により、年間固定費は2倍になる
  • 『自動組み立て装置』を導入した場合も、技術進歩により製造コストは毎年6%下がる
  • プロジェクトのNPV(正味現在価値)を求めるための割引率は15%とする

※税金等は無視することとします。
下記のフォーマットを参考に、エクセルで今後5年先までの損益計算書を作成してください。

例題4

解答

今回は少し長い問題文でしたが、いかがでしょうか?条件を丁寧に見ていけば、1つ1つの計算はそれほど難しくはないと思います。答えは以下になります。順に解説していきます。

解答4-1

  • 販売台数:条件より、初年度は1600台。2年目からは前年度の2倍(H3セルに ” =G3*(1+$C$6)” を入力)
  • 販売価格:条件より、初年度は18万円。2年目からは前年に対して15%の値引き率なので『前年の価格×85%』で計算(H4セルに “=G4*(1-$C$7)” を入力)
  • 売上:『販売台数 × 販売価格』で計算(G5セルに “=G3*G4” を入力)

◆『自動組み立て機』リースなしの場合

  • 一台あたりの製造コスト(変動費):条件より、初年度は10万円。2年目からは前年に対して6%のコスト削減なので『前年の製造コスト×94%』で計算(H6セルに “=G6*(1-$C$9)” を入力)
  • 変動費合計:『販売台数 × 一台あたりの製造コスト(変動費)』で計算(G7セルに “=G6*G3” を入力)
  • 固定費:条件より、年間の固定費は毎年10,000万円(G8セルに ”=$C$10” を入力)
  • 製造費合計(リースなし):『変動費合計 + 固定費』で計算(G9セルに “=G7+G8” を入力)

◆『自動組み立て機』リースありの場合

  • 一台あたりの製造コスト(変動費):条件より、初年度10万円に対して製造コスト20%削減が見込めるので、『初年度の製造コスト × 80%』で計算(G10セルに “=C8*(1-C12)” を入力);2年目からは前年に対して6%のコスト削減なので『前年の製造コスト×94%』で計算(H10セルに “=G10*(1-$C$14)” を入力)
  • 変動費合計:『販売台数 × 一台あたりの製造コスト(変動費)』で計算(G11セルに “=G10*G3” を入力)
  • 固定費:条件より、毎年の年間固定費10,000万円が2倍になるので(G12セルに ” =$C$10*(1+$C$13)” を入力)
  • 製造費合計(リースあり):『変動費合計 + 固定費』で計算(G13セルに “=G12+G11” を入力)
  • 製造費の最小値:リースなし・ありの製造費を比べて、小さい方を採用する(G15セルに “=MIN(G13,G9)” を入力)
  • マーケティング費用:条件より、売上トータルの14%がマーケティング費用となるので、『売上 × 14%』で計算(G16セルに “=G5*$C$11” を入力)
  • 税引前利益:『売上 - 製造費 - マーケティング費用』で計算(G17セルに “=G5-G15-G16” を入力)

ここまでできれば、あとは2年目の計算式をそれぞれ3年目以降にコピーすれば完成です。

解答4-2

if関数、条件付き書式の使い方

次に『自動組み立て装置』をリースすべきかどうかを、例題2で学んだ『if関数』を使って判断してみましょう。

◆リースすべきか?

リースすべきかどうかは、『自動組み立て装置』リースなしの場合とありの場合の製造費を比較し、どちらが安いかを比べることで判断できます。リースありの場合の製造費の合計が、リースなしの場合の製造費合計より安かった場合、『リースすべき』という判断になります。

これをエクセルで表現するには、『if関数』を使います。具体的には、G14のセルに “=IF(G13<G9,"YES!","NO!")” を入力してみてください。

これは、G13(リースありの場合の製造費合計)が、G9(リースなしの場合の製造費合計)より小さければ “YES!” と表示し、そうでなければ “NO!” と表示せよ、という関数になります。

if関数

上図のように『YES!』『NO!』が表示されましたでしょうか?If関数の定義についてはWeb上にもたくさん情報があるので、Udemyの記事を参照してください。

◆条件付き書式

次に『YES』『NO』を見やすくするために『条件付き書式』を設定します。まずは条件付き書式を設定したいセルを選択し、エクセル上部の『ホーム』→『条件付き書式』→『新しいルール』をクリックします。

条件付き書式1

『新しい書式ルール』のウィンドウが開きますので、『指定の値を含むセルだけを書式設定』をクリック。

条件付き書式2

さらに、『次のセルのみを書式設定』の欄のドロップダウンから、『特定の文字列』を選びます。

条件付き書式3

右の欄に “NO!” と入力し、『書式』をクリック。

条件付き書式4

『フォント』タブの『色』を赤に設定してください。

条件付き書式5

さらに、『塗りつぶし』タブの『その他の色』から薄い赤を選んでみましょう。(サンプルに選んだ色が表示されます)その後、『OK』→『OK』を押します。

条件付き書式6

下図のように、『NO!』のセルが赤字、塗りつぶしも薄い赤色になりましたでしょうか?

条件付き書式7

同様にして、『YES!』の方のセルも条件付き書式を設定します。再度条件付き書式を設定したいセルを選択し、エクセル上部の『ホーム』→『条件付き書式』→『新しいルール』をクリックします。

条件付き書式8

『新しい書式ルール』のウィンドウで、『指定の値を含むセルだけを書式設定』をクリック。さらに、『次のセルのみを書式設定』の欄のドロップダウンから、『特定の文字列』を選択。右の欄に “YES!” と入力し、『書式』をクリック。

条件付き書式9

『フォント』タブの『色』を緑に設定します。

条件付き書式10

さらに、『塗りつぶし』タブの『その他の色』から薄い緑を選びます。(サンプルに選んだ色が表示されます)その後、『OK』→『OK』を押します。

条件付き書式11

下図のように、今度は『YES!』のセルが緑色に、塗りつぶしが薄い緑色になりました。

条件付き書式12

◆NPV(正味現在価値)とIRR(内部収益率)の求め方

最後に、5年分のキャッシュフローからNPV(正味現在価値)とIRR(内部収益率)を求めてみましょう。

◆キャッシュフローの現在価値を計算

ファイナンスを学んだ方にはお馴染みですが、投資判断をするには今後5年間にわたって得られるキャッシュフローを『現在の価値』に計算し直す要があります。

これを計算させる関数が『NPV関数』です。関数の使い方についてはネットにも情報がたくさんありますので、参考までにこちらをご確認ください。

今回の例題ではNPV関数の使い方を理解するために、シンプルに『税引前利益』を『キャッシュフロー』とみなします。下図のように、NPV関数を用いて『割引率を15%』、『初年度~5年目までの税引前利益』を引数に指定すると、キャッシュフロー合計の現在価値が27,546万円になりました。

NPV1

これをベンチャーキャピタルからの投資額25,000万円と比較すると、NPVは以下のように計算できます。

NPV2

NPVがプラスの為、今回のプロジェクトには『投資すべき』という意思決定になりました。

◆IRR(内部収益率)の計算

最後に、IRR(内部収益率)も求めます。内部収益率は正味現在価値とともに、投資の採算性を評価するために使われる指標です。詳細についてはこちらの記事をご確認ください。

エクセルのセルに『初期投資額』と、5年分のキャッシュフローを順番に記載します。

IRR1

IRR関数を用いて『初期投資』、『初年度~5年目までの税引前利益』を引数に指定すると、IRRが17.601%と計算されました。

IRR2

この17.601%って、ファイナンス的にはいったいどんな意味?という疑問に簡単にお答えすると、『割引率が17.601%だったときに、このプロジェクトのNPVがちょうど0になる』という意味になります。

もっと深く知りたい!ファイナンスに興味があるという方は、
3度海外在住した私がMBA留学前に買っておくべき持ち物を紹介!』の記事で紹介している『コーポレートファイナンス入門』を学んでみることをお勧めします。

コーポレートファイナンス入門〈第2版〉 (日経文庫)

◆What if 分析(ゴールシーク)

最後にWhat if 分析(ゴールシーク)を紹介します。この分析からは、『NPVがゼロになるためには初年度に何台売ればいいの?』ということが分かります。

What if分析をするには、エクセル上部の『データ』→『What-If分析』→『ゴールシーク』をクリックします。

What-If1

すると、下記のように小さいウィンドウが開きます。

What-If2

数式入力セルには『NPV(正味現在価値)=G21セル』、目標値は『0』、変化させるセルは『初年度の販売台数 = C4セル』をセットして、OKを押しましょう。

What-If3

すると、解答が見つかりましたという下記のウィンドウが開き、『初年度の販売台数 = C4セル』の値が自動で計算されます。

What-If4

つまり、初年度に小型コンピューターを『最低1,548台』以上販売することができれば、このプロジェクトはプラスのNPVになるということが分かりました。

おまけ【MBA生・ビジネスマン必須】便利なショートカット集

最後に、ビジネスマン必須の便利なエクセルショートカット集を紹介します。全部で200種類以上、Windows版とMac版の両方に対応しております。(英語になりますのでご了承ください。)

こちらも以下のツイートから配布しておりますので、興味のある方はご参照ください。

【MBA流】エクセル "超" 基礎講座 まとめ

今回はエクセル “超” 基礎講座として、UCLAの経営学部の学生向けにレクチャーされた内容をまとめました。いかがでしたでしょうか?改めて紹介したものをまとめると以下になります。

  • モジュール1:掛け算表(相対参照と絶対参照)
  • モジュール2:価格計算(if関数)
  • モジュール3:ウェブ予測(グラフ作成・スクロールバー)
  • モジュール4:損益計算書の試算(条件付き書式、NPV関数、IRR関数、What if分析)

この記事では4つの例題をもとに解説しましたが、『もっと練習問題を解きたい!』という方には以下の参考書がおすすめです。練習問題8問、総合問題が全10問あり、繰り返し復習することでエクセルの基本操作を確実にマスターできます!

Microsoft Excel 2019 基礎 (よくわかる)

この記事が少しでもみなさんのお役に立つことを願っております!

エクセルの次のステップとして、分析ツール『ソルバー』を使いこなせるようになりたいという方には以下の記事がオススメです!ビジネスに活かせるエクセルテンプレートも無料配布しております!

MBA留学を目指されている方は、以下の記事をぜひ参考にしてみて下さいね。

【海外MBA受験】TOEFL68点から合格までの全ステップまとめ

MBA社費留学:倍率60倍の社内選考を突破した小論文を公開

MBAまでの道のり(TOEFL全般、Reading編)

Twitterでも定期的にMBA関連・Eコマースの情報配信をしているので、ぜひフォローをお願いします!

-海外MBA, 海外生活
-,

Copyright© ゆうたろう の ツブログ , 2021 All Rights Reserved Powered by STINGER.