#WORKOUTWEDNESDAY 2019/W39解説

WorkoutWednesday 2019/W39の課題を提出したのですが、正直なところ試行錯誤で何となく出来てしまった感が強いので、自分のためにもまとめを書きます。


全て説明すると長くなってしまうので、チャレンジの要所のみ解説します。

チャレンジページは以下です。

WEEK 39: CAN YOU CREATE A BCG GROWTH SHARE MATRIX WITH SALES AND ORDERS?


今回の記事に使用したワークブックは以下からダウンロードできます。

https://tabsoft.co/2lIYVBG

要件について

基本的なところはチャレンジページを見て頂くとして、要点だけ以下の図に書きました。


要は、サブカテゴリなりManufacturerなりでSalesまたはOrder数のGrowthとMarket Shareを計算し、それをもとにしてBCG Matrixという分類にかけましょう、というチャレンジです。


そのためには大まかに言って、以下の数値を計算する必要があります。

  1. 最新年(このデータでは2018年)の数値

  2. 過去〇〇年の平均数値または総数値

  3. 過去〇〇年の全体数値


実際にはもう少し細かい計算がありますが、今回のチャレンジの要旨は上記の通りです。

ちなみに今回のチャレンジ、表計算の使用が禁止されていました。

表計算の方が実装が簡単であろう箇所はありますが、ご了承下さい。

それでは早速はじめていきましょう。

SalesについてBCG MATRIXを作る

チャレンジではSalesとOrderそれぞれに計算を作りますが、まずはSalesから取り組みます。



日付フィルターを作成する

年を指定した計算を多用しますので、先にこのあたりを作っておきます。


パラメータ:Years to Compare

(チャレンジの要件で、計算につかう年数を指定できるようにとあることから)

Year Filter

YEAR([Order Date]) >= {MAX(YEAR([Order Date]))} -[Years to Compare]+1


Latest Year filter

YEAR([Order Date]) = {MAX(YEAR([Order Date]))}


Previous Years Filter

YEAR([Order Date]) >= {MAX(YEAR([Order Date]))} -[Years to Compare]+1

AND

YEAR([Order Date]) <= {MAX(YEAR([Order Date]))} -1


このあたりは算数ですので詳細割愛します。

{}についてだけ言及すれば、こちら粒度を指定しないFIXEDです。

最終的に、それぞれの計算式がどの年をTRUEと返すかを以下に図示します。


Growthに関する計算(Sales)

GrowthとMarket Shareを計算していきますが、まずはGrowthに関連したものから取り扱っていきます。


Latest Sales

SUM(IF [Latest Year Filter] THEN [Sales] END)


Average Sales in n-1 years

SUM(IF [Previous Years Filter]THEN [Sales] END)

/

COUNTD(YEAR(IF [Previous Years Filter] THEN [Order Date] END))


Average Salesについて補足すると、以下2ステップで年平均を出しています。

  1. Previous Years Filterに該当する年の総Salesを計算

  2. 含まれている年数で割り、年平均を算出

ちなみに、別解としては以下です。

Average Sales in n-1 years (別解)

AVG({ INCLUDE YEAR([Order Date]):

SUM(IF [Previous Years Filter]THEN [Sales] END)

})


さて、最新年のSalesと年平均Salesが計算できたので、Growthを計算します。


Sales Growth

([Latest Sales] - ZN([Average Sales in n-1 years]))

/

IIF(ZN([Average Sales in n-1 years])=0,1,[Average Sales in n-1 years])


分母のIIF()は単に0で割る場合を除外するためです。

もう一つ、Growthの最大値を算出する必要がありますが、こちらは後程。

Market Shareに関する計算(Sales)

続いて、Market Shareの計算をしていきます。


Total Sales

{SUM([Sales])}


Sales Market Share

SUM([Sales]) / MIN([Total Sales])


Market Shareの計算について、上述のYear Filter等を計算式に含めていないのは、ワークシート上でコンテキストフィルターに設定することを想定しているからです。

明示的に計算に使用する期間を指定したい場合は、適宜計算式に入れれば良いかと思います。


さて、ここまで出来たので、一旦は散布図を作ってみましょう。

BCG Matrixを作る

とりあえず下記のように配置してみましょう。


ところで、BCG Matrixの4分類のための基準値は、Market Shareが7%、Growthが最大Growthの半分でした。このあたりの数値を作っていきましょう。


Mid Market Share(パラメータ)


Mid Sales Growth

MIN({

MAX({ FIXED [Sub-Category]: [Sales Growth]})/2

})


Mid Sales Growthについて少し補足すると、最初の

MAX({ FIXED [Sub-Category]: [Sales Growth]})/2

の計算で、目的の「サブカテゴリ間の最大Growthの半分」を取得しています。


これを粒度指定なしのFIXEDにより、全サブカテゴリ(厳密に言えば全レコード)に同一の数値をアサインしている形になります。


ちなみに後ほどManufacturerについてのGrowthも使い、ManufacturerをBCG Matrixの分類に当てはめるわけですが、その際の基準Growthは上記のサブカテゴリ基準のものを使用するようです(再現対象Vizとの比較検算で確認)。


さて、2つの基準値が作成できたので、先ほどの散布図にリファレンスラインで追加します。


そして、この4象限のどこに含まれるかで分類していきます。


Sales Portfolio Category

IF [Sales Market Share] > [Mid Market Share]

AND [Sales Growth] > [Mid sales Growth]

THEN 'Stars'


ELSEIF [Sales Market Share] > [Mid Market Share]

AND [Sales Growth] <= [Mid sales Growth]

THEN 'Cash Cows'


ELSEIF [Sales Market Share] <= [Mid Market Share]

AND [Sales Growth] > [Mid sales Growth]

THEN 'Question Marks'


ELSEIF [Sales Market Share] <= [Mid Market Share]

AND [Sales Growth] < [Mid sales Growth]

THEN 'Dogs'

END


画像は以下サイトから適当に探しました。

Noun Project


さて、これでSalesに関するBCG Matrixの骨子は出来ました。

ここでは細かいフォーマット設定については割愛します。

ORDERについてBCG Matrixを作る

これからOrderについても作っていくわけですが、基本的には同じ計算を辿ります。

特にGrowthについては同一の計算ロジックのため、以下に計算式のみ記載しておきます。


Latest Orders

COUNTD(IF [Latest Year Filter] THEN [Order ID] END)


Average Orders in n-1 years

COUNTD(IF [Previous Years Filter]THEN [Order ID] END)

/

COUNTD(YEAR(IF [Previous Years Filter] THEN [Order Date] END))


Orders Growth

([Latest Orders] - ZN([Average Orders in n-1 years]))

/

IIF(ZN([Average Orders in n-1 years])=0,1,[Average Orders in n-1 years])


Mid Orders Growth

MIN({

MAX({ FIXED [Sub-Category]: [Orders Growth]})/2

})

OrderのMarket Shareについて

さて、ここからがちょっとだけ厄介です。

上述の通り、Order数にはCOUNTD([Order ID])を使用しますが、ちょっと以下の図を見てください。

当たり前と言えば当たり前なのですが、各Order IDに対して、複数サブカテゴリが含まれている場合があります。

(要は1注文で複数製品を注文した場合です)


この場合、Salesの場合のように{COUNTD([Order ID])}という計算で、Market Share算出のための全体Order数を算出することはできません。

したがって、Orderについては以下のように、サブカテゴリ単位でのOrder数を算出してから、その和を取る形で全体Order数を定義します。

(そうしないとMarket Shareの和が100%を超えます)


Total Orders

{SUM(

{FIXED [Sub-Category]: COUNTD([Order ID])}

)}


Orders Market Share

COUNTD([Order ID])

/

MIN([Total Orders])


ということで、めでたく同様にOrderのBCG Matrixを作成することが出来るようになりました。


Orders Portfolio Category

IF [Orders Market Share] > [Mid Market Share]

AND [Orders Growth] > [Mid Orders Growth]

THEN 'Stars'


ELSEIF [Orders Market Share] > [Mid Market Share]

AND [Orders Growth] <= [Mid Orders Growth]

THEN 'Cash Cows'


ELSEIF [Orders Market Share] <= [Mid Market Share]

AND [Orders Growth] > [Mid Orders Growth]

THEN 'Question Marks'


ELSEIF [Orders Market Share] <= [Mid Market Share]

AND [Orders Growth] < [Mid Orders Growth]

THEN 'Dogs'

END




最後に

今回の記事はここまでで留めておきますが、チャレンジ完成までの80%はここまででカバーされています。

興味ある方は、ぜひ残りの部分にも取り組んでみて下さい。


ちなみにWorkoutWednesday参加方法については以下にまとめました。ご参考になれば幸いです。

WORKOUT WEDNESDAY 参加方法ガイド


ご質問等はTwitterまたはLinkedinまでよろしくお願いします。 それでは。


© 2023 by Actor & Model. Proudly created with Wix.com