はじめに
本記事は、「問題解決のためのデータ分析」を読み、参考になった部分を自分のために抜粋・整理したものです。
誰にとっても学びがあるような汎用的なものではないことを予めお伝えしておきます。
免責
ですます調の不統一等、文章として適当な部分が多いのですが、私の読書メモとして多めに見てやってもらえると助かります。
売上変動に係る外部要因を知るためのデータ
省庁のデータが１次データとして参考になるそうです。
人口統計
市場規模の把握
消費動向
経済指標データ
政府により、商業の実態を明らかにすることを目的につくられたデータ。自社の売上高は業界平均より上なのか下のかなど、自社のポジショニングを確認する上で活用できる。調査自体が4年に1度しか行われていないため、参考程度に見るのがよい。
国会図書館
株式会社富士経済など市場調査会社が出している資料など、価格は高いが国会図書館で見ることできるものもある。
その他、情報源
アンとケイト
インターネット調査サービス。
シェア理論
船井総合研究所が策定したもの。まずは最低限の存在感を表す存在シェアを目指す。
ただし、商圏の広さや目指すシェアは業種業態によって異なる。例えば量販店であれば商圏に対してシェア7％程度、地域の繁盛店やショッピングモール内の専門店では商圏に対してシェア15％程度が基準。
1社だけで見ると業績が悪く見えても、市場規模推移や他社との比較をとおしてどうか、を見ることが大切。
分析方法
まずは大きな傾向から掴むべし！
複数の観点からロジックツリーを使って課題を抽出し、検証する仮説を決定する。
データ分析結果は事実であっても一旦は疑う。そうでないと、仮に分析方法や計算式が誤っていた場合にミスに気づけない。
分析をしたうえで関係者ヒアリングをするとより正確で説得力のあるデータとなる。関係者にヒアリングする際はデータからわかる事実は分析したうえで仮説をたててヒアリングしないと、忙しい担当者をいらつかせることになる。
分析に必要なスキルは３つだけ
- VOOKUP
- ピボットテーブル
- ソルバー
VLOOKUP
異なるデータ同士をつなぎ合わせるときに頻繁に使うもの。DBでいうところのテーブルのINNER JOINみたいなことができる。
=vookup(検索値、検索対象範囲、列番号、[検索方法])
vookupは指定した表の左端の列を縦方向に検索する。左端しか検索できないので注意。列番号は左端から数えて何列目かを指定する。検索方法は、左端列に該当する値が見つからなかった場合にどうするかを指定する。見つからなかった場合にエラーを表示させる場合は0を入力する。1を入力すると、見つからなかった場合に、そのデータを超えない最大値を検索する。
#NAME? の意味
- 関数名に誤りがあるとき
- 引数に文字列を指定するときにダブルクォーテーションで囲まないとき
- 関数の引数にセルの参照を指定するときに、存在しないセル名やセルの範囲に：を入れるのを忘れたとき
検索方法に1を指定する場合、累積購入額を一定の範囲でセグメントして価格帯別の傾向を分析するような場合に活用できる。
ピボットテーブル
シートの表データや外部データ（他のExcelファイルやDB等）を情報源とし、クロス集計をすることができる。これは特に難しいことはないので、まず触ってみることをおすすめする。
大まかな集計表の作り方としては、右上の「ピボットテーブルのフィールド」に表示される各属性を右下の「フィルター」「列」「行」などにドラッグ・アンド・ドロップする。
ソルバー
予算計画や予定管理などにとても役立つ機能。使用するにはアドインを有効化する必要がある。
有効化には、
[ファイル]→[オプション]をクリックし、「Excelのオプション」ウィンドウを表示させる。次に[アドイン]をクリックして、アドインの表示と管理の画面に切り替え、「ソルバーアドイン」にカーソルを合わせて「設定」をクリックする。そして、表示されたウインドウの「ソルバーアドイン」にチェックをつけてOKを押す。
P249に管理部門コスト配賦後（はいふご）利益率という言葉があるが、これはどうゆう意味なのだろう。
まず、配賦について。
複数の部門・製品を横断して発生する本社経費や共通経費を、各店舗や事業ごとに割り当てて処理することを「配賦」といいます。ある程度規模が大きくなった会社では、収益を直接的には生まない費用を配賦することによって、各部門の責任者に「会社全体の利益」を意識させることができます。
要するにこの書籍で言う「管理部門コスト配賦後利益率」とは、共通経費として管理部門に計上されたコストを関連店舗に割り当てて処理した後の全店舗合計の利益率を言っているようです。
P251の記載として覚えておくべきは、
変動費は「売上✕変動費率」
管理部門コスト配賦後利益率は「管理部門コスト配賦後利益率÷売上」ですので、店舗CのセルD8には、
=iferror(D7/D3, "")
iferrorとはなんぞや
エラーの場合に指定した値を返す関数。エラー値を「0」と表示したり、または非表示（空白セル）にするときなどに使われる。上記の場合はエラーの場合に非表示（空白セル）となる。
ソルバーは、[データ]を押して一番右にある（と思われる）[分析]のなかの「ソルバー」をクリックすることで使うことができる。
上図のような画面がひらくので、「目的セルの設定」には、y=ax+bでいうところのyを、「変数セル」にはxを、制約条件にはその名の通り成約としたい条件を（例えばx1>0, x2>0, x3<-1など）入力し、最後に「解決」ボタンを押すと、最適なxを制約条件のもとで決定してくれるというもの。
正しく収益管理する
企業の利益＝売上ーコストで計算される。このコストを最適化することが大切。コストを最適化するには、まずは収益構造の把握が大切。収益構造を把握するうえで大切なのが、
-
セグメント分析
企業が任意で定めたセグメントにおいて収益管理する方法。財務会計ではわからないミクロな視点でも改善すべき部分を把握することができる。
-
損益分岐点分析
売上高とコストの関係が明確になるので、取るべき戦略や施策が定まる。
セグメント分析
この分析を行ううえで大切なことは、コストの適正配賦。事業別（店舗別）損益ではコストを振り分けて管理していることが多いのに対し、商品別損益や顧客別損益では、事業部をまたがってコストが発生していることが多く、普段から適正にコスト配賦している企業はめったにない。
配賦基準
コストを配賦するルールを作ること。配賦基準は、売上高の比率や社員数の比率、あるいは使用時間の比率や売り場面積の比率などを用いる。
損益分岐点分析
損益分岐点売上 = 固定費 ÷ {1 - (変動費 ÷ 売上高)} = 固定費 ÷ (1 - 変動費率) = 固定費 ÷ 限界利益率
限界利益率とは、売上高が１増えたときに、利益がどの程度増えるのかを表す。
損益分岐点分析によって収益構造を明らかにすることで、収益悪化の原因が次の３つのうちどれにあるかが明確になる。
- 売上高が足りない
- 固定費が高い
- 変動費率が高い
1が課題であれば、販売単価を高めたり、販売数量を増やしたりするための施策として、アップセルやクロスセル、新商品開発や販路拡大などが具体的な打ち手として考えられる。
2が課題であれば、固定費を構成している費目を分析し、どの費目が悪化要因になっているかを明確にする必要がある。悪化要因となっている費目を削減するために、業務プロセス飲み直しやコスト削減が具体的な打ち手になる。
3が課題であれば、原価率や販売手数料などの変動費率を下げることが必要になる。返品や値引きを圧縮し、仕入先への交渉や材料の見直しなど原価を下げる取り組みや、問屋などへの卸販売から直接顧客へ販売するダイレクト・マーケティングへのスイッチなどが打ち手となる。
本書では、各店舗別のP/Lを用い、その中の管理部門コスト配賦後利益率とその他の費目をCORREL関数を使って相関係数を算出し、管理部門コスト配賦後利益率と相関が高い費目について、収益悪化の要因としています。
DMの効果測定（P158）
DMからどの程度、実際の購入につながったかという反応率であるCPO（Cost Per Order）を計測することが大事。
CPOが低ければ低いほど効率の良い広告ということになる。
CPO＝広告の総費用÷注文数
CVR（Conversion Rate）も大切。これは成約率を意味する。DMを送った、あるいはウェブサイトを訪れた人数に対して、何人が成約に至ったかを測定する。
P160では、収益性の悪化の要因をロジックツリーを用いて売上高の伸び悩みとコストの増加に分解し、更にコストの増加を商品原価の増加と販管費の増加に分解しています。
販管費の増加は、
- 広告宣伝費の増加
- 人件費の増加
- 物流費の増加
- その他コストの増加
と分解されています。分析のなかで、広告費に対する売上効果を分析し、コスト効率を明確化しています。
よい顧客の見分け方（RFM分析） P163
RFM分析（Recency Frequency Monetary Analysis）が便利。「一番最近に購入した顧客は誰か」「頻繁に購入する顧客は誰か」「一番お金を使ってくれている顧客は誰か」という３つの側面から分析する。DBに購買履歴があることが前提。
Recency：最新購買日のこと。ある顧客が最後に商品を購入した日を判断材料とするもので、最近購入した顧客のほうが何年も前に購入した顧客よりよい顧客と考える。つまり、すべての顧客の最後の購買日だけを拾い出し、新しい順番に並べれば一番上から順によい顧客となる。
Frequency：購買頻度のこと。過去に何回購入したかを抽出して多い順に並べれば、一番上にくる顧客が最もFrequencyが高いよい顧客となる。期間については、商品の平均的な購買頻度で定めればよいが、アパレルや化粧品は1年、家電製品や車は10年で見ることが多い。
Monetary：購買金額の合計。この金額が多ければ多いほどよい。これもFrequencyと同様に業種や商品を考えながら期間を定めて分析する必要がある。
RFM分析のみかたをまとめると、
- Rが高いほど将来の企業収益に貢献してくれる可能性が高い
- Rが低ければFやMが高くても他社に顧客を奪われている可能性が高い
- Rが同じならFが高いほど常連顧客
- Rが同じならFやMが高いほど購買力がある顧客
- RやFが高くてもMが少ない顧客は購買力が低い
- Fが低くMが高い顧客は、Rの高いほうがよい顧客
- Fが上がらないか下がっている顧客は他社に奪われている可能性が高い
最も重要な指標はR。FやMがいくら高くても最近の購買実績がない顧客は既に競合他社に奪われている可能性が高い。
在庫管理
適切な在庫を把握するのに必要なデータ分析のひとつが在庫回転率。
在庫回転率とは、一定期間（１年や半期、四半期など）に在庫が何回入れ替わったかを示す数値。
この数値が大きいほど、仕入れてから販売に至るまでの期間が短く、効率よく売上に繋がっていることになる。ただし、商品特性によって値は大きく変わるため、それを考慮する必要がある。
在庫回転率 ＝ 売上原価 ÷ 平均在庫高
例えば、１年間を期間とした場合、売上原価はその１年間に売れた商品の売上原価になる。そして、平均在庫高は、その１年間のスタート時における在庫（期首在庫）と終わりのときの在庫（期末在庫）を足して２で割った金額になる。
平均在庫高 ＝ （期首在庫 ＋期末在庫）÷ ２
同様に在庫の効率性を分析する指標に在庫回転期間というものがある。これは在庫を何ヶ月分持っているかを示す指標で、以下の通り在庫回転率の逆数になる。
在庫回転期間（月数） ＝ 在庫高 ÷ （売上原価 ÷ 12）
上記のように月数で表すこともあれば、週ベースや日ベースで表すこともある。週ベースでは52、日ベースでは365で割ることになる。
他に重要な指標として消化率がある。
消化率 ＝ 売上数量 ÷ 仕入れ数量
100％に近ければ仕入れた数量が十分に売れていることになるが、欠品を起こしている可能性もある。消化率が低すぎるのは論外だが、高すぎても良いわけではない。
その他
いくつか覚えておきたい関数を記します。
ROUND関数
四捨五入する関数。使い方は、
ROUND(数値, 桁数)
用例は以下のような感じ。（抜粋：エクセルで四捨五入をするROUND関数の使い方｜Office Hack https://office-hack.com/excel/round/）
| 数値 | 桁数 | 四捨五入の場所 | 結果 |
|---|---|---|---|
| 123,456.789 | 2 | 小数点第3位 | 123,456.79 |
| 123,456.789 | 1 | 小数点第2位 | 123,456.8 |
| 123,456.789 | 0 | 小数点第1位 | 123,457 |
| 123,456.789 | -1 | 1の位 | 123,460 |
| 123,456.789 | -2 | 10の位 | 123,500 |
併せてCEILING／FLOOR関数も覚えておくと良いかもしれません。
それぞれ、天井／床という意味なので、切り上げ／切り捨てを行う関数になります。
CEILING（数値,基準値）
FLOOR（数値,基準値）
終わりに
本書では、ここで紹介していないような様々なケーススタディで分析を学ぶことができます。
初めは難しいかと思いますが、ここでのケーススタディを踏まえ、読者自身、自身の見のまwリで発生している問題に置き換えて実践してみることでスキルが定着するのではないかと思います。
「問題解決」というと、まず問題を定義することがスタートであり、そこを間違えると道を踏み外してしまうとても重要な部分かと思います。従って、その問題の発見・定義に重点を置いた書籍等を併せて読み、実践してみることでもスキルアップできると思います。