タグ別アーカイブ: エクセル分析

5%値引したらどうなる?

昔、あるEC通販の会社にいた時に、よく会議でこんなやりとりを聞きました

 営業「売上が前期より落ちているから、値引きして売上を回復したい」

 管理「値引きをしたら利益が減ってしまうだろう!」

 営業「違う、売上が増えるから利益が増えるんだよ!」

どちら側も正しいのですが、議論をする上で欠けているものが1つあります

 今、利益率は何%か?

ということです

実はこの手の議論でよく勘違いされていることがあります

「5%利益率を減らしても、5%売上を増加すれば前と同じ利益が確保できる」

これは間違いです。値引きをした分、売上を増やせばいいというのはかなり乱暴な考えです

 値引した時に、前と同じ利益を確保するために、どれくらい売上を増やせばいいかは、「今、利益率は何%か?」によります

以下の表を見てください!この表をもとに2つのケース(①②)を見て見ましょう!

➀値引きをしたら売上を増やしても意味が無くなるケース

売上が100として、利益が10の場合、利益率は10%です

では、利益率が10%の時に、10%値引したらどうなるか?

利益率が上の表でいえば、利益率が10%の行の下、0%になり、いくら売上を増やしても利益は出ません!

➁値引きをしたら途方もないぐらい売上を増やさねければなくなるケース

では、利益率が20%の時に10%値引きして、利益率を10%にした場合はどうか?

値引き前は利益は20です

売上が値引き後もそのまま100の場合、値引き後の利益額は10です

つまり、売上を2倍にしないと、値引き前の利益を確保できません

今、➀と②の2つのケースを見ましたが、2つとも実は以下の数式で表すことができます

値引時・必要売上増加率=値引き前・利益率 ÷ 値引き後・利益率

試しに➀と②を上記の数式に当てはめてみましょう

➀の場合、分母が0なのでエラーになります

➁の場合、20%÷10%になり、回答は2になります

どうでしょう?

 この数式で値引き時の必要増加率を計算できる、ということはよくご理解頂けたと思います

そして、この数式では「今、利益率は何%か?」が分子になっています

 とにかく、値引きを考える上では「今、利益率は何%か?」を把握しておくことが重要なのです

 では、次に、この必要増加率を計算する数式を、もっと会議で有効に活用する方法を紹介したいと思います

これは、値引き時の必要増加率の早見表を事前に作成しておくことです

 例えば、表の一番左上に11%という数字がありますが、これは利益率が100%の時に値引きを10%した場合、売上を11%増やさないと値引き前の利益額を確保できないよ、ということです

この表を作成しておけば、冒頭に紹介したような無駄なやりとりをせずに現実的な値引政策を決定していくことが可能になります

ちなみに、この表を見て気づくことがありませんか?

利益率が高いほど、必要売上増加率は低く済むのです

つまり、戦略の自由度が高いのです

 ですから、なるべく利益率は高く保てるよう、早見表を有効活用して値引きの無駄な乱発はなくしていきましょう!

にほんブログ村 資格ブログ ビジネススキルへ
にほんブログ村 IT技術ブログ VBAへ


にほんブログ村

最速集計の為のABC分析テンプレート

ABC分析で問題となるのはランク分けの手間です。IF関数主体でランク分けをするのでなく、VLOOKUP関数とランク表の組み合わせにより、最速でランク分けを実現します

特徴

・データ並べ替えの手間を省きました

・質(上位)と量のバランスを意識して設定できます(従来は集計してみないと質の状況がよくわからなかった)

・ランク分けの数を3から2、もしくは3から4などに手軽に変更できる

仕組み

・Dataシート(後でVLOOKUP関数について解説)とABC設定シート(後でPERCENTILE.INC関数について解説)の2シートで構成している

・”PERCENTILE.INC関数”を使用して引数1(ランク設定対象の数字が入力されている範囲)、引数2(下の画像であれば1-5%)ランク用販売個数を設定する(下の画像ではAランク:4729個以上、Bランク:280個以上、Cランク:7個以上、Dランク:7個未満)

・VLOOKUP関数(参照方法・近似値)にてPERCENTILE.INC関数にて設定した値を参照してランクを抽出する

アイコン

ABC分析テンプレート 109.00 KB 13 downloads

...
にほんブログ村 資格ブログ ビジネススキルへ
にほんブログ村 IT技術ブログ VBAへ


にほんブログ村

ABC分析をエクセル力で改善する6

今回はこれまでのまとめから始めましょう

①実用的なエクセル力とは

ビジネスに修正はつきものです。エクセル機能を組み合わせて、修正にも即時に対応できるような能力を身に付けましょう!

ABC分析をエクセル力で改善する2

②分析にも修正はつきものです

分析の肝の一つは区分です。ABC分析は区分を最少単位で行います

但し、試行錯誤はつきものです。どこからどこまでを1つの区分とするかを変える度に、エクセルの処理を最初からやり直していたら効率が悪すぎます。VLOOKUP関数とPERCENTILE.INC関数をうまく組み合わせて修正作業を自動化しましょう

ABC分析をエクセル力で改善する4

③表への追加にも対応できるようにしておきましょう

従来のABC分析の場合にはデータを追加した場合には、並べ替え作業からやり直しです。②とあわせてテーブル機能も組み合わせて自動化しておきましょう

ABC分析をエクセル力で改善する5

④ABC分析は質と量の公式であらわせます

ABC分析において重要なのはビジネス上優先すべき質の高い区分(グループ)を特定する観点です

ところが、従来の方法だと質と量の公式の観点が分かりにくいのです

②で述べたVLOOKUP関数とPERCENTILEINC関数をうまく組み合わせて、質と量の双方向からランク付けを行っていきましょう

ABC分析をエクセル力で改善する5

 

さて、今回からこれまでとは違う方向の内容に取り組んでいきます

まず、下の画像を見てみてください

13

Aランクがどの位の質と量のなのか、理解できましたか?

それに質の78%と98%は手で入力しています

これはとても非効率です

次の回からはピボットテーブルと連動した双方向グラフについて解説します

14

にほんブログ村 資格ブログ ビジネススキルへ
にほんブログ村
にほんブログ村 IT技術ブログ VBAへ

ABC分析をエクセル力で改善する5

前回は再び、ABC分析の肝に触れました

9

今回はこの点をもう少し詳細に解説するところからはじめましょう

 

1.分析の公式の一つに”質x量”があります

9

上記を質x量の公式にあてはめると

上位20%の商品⇒量は全体の20%(量)だが、売上の80%を占める(質)

となります

従来のABC分析の手法ですと、この質と量の関係が見えてこないのです

5

では、ここからVLOOKUP関数とPERCENTILEINC関数とテーブル機能を組み合わせると、この量と質の関係が見えてくるのかを実際の使用例でみてましょう

2.VLOOKUP関数とPERCENTILEINC関数とテーブル機能を実践的に組み合わせる

まず、”Data”のシートに加えて”ABC設定”の状況を管理するシートを用意しておきます

10

そして、前もってDataシートにある表はテーブル化しておきましょう

テーブル化しておくことで、表に値を追加しても数式が自動的にセットされます

(詳細は以下のページなどを参照してください)

テーブルを作成しよう~数式を自動入力~

ABC設定のシートは以下の様な構成になっています

11

そして、DataシートのVLOOKUP関数(近似値で検索)と連携させておきます

12

ここまで設定しておけば”量x質”の公式による観点でのランク設定が自由自在に行えます

(下の表はDataシートの内容ですが、上から2行目/販売個数:2,2136行目:1,876のランクが上の操作と連動して変わっています)

Gif5

データを追加した時もランク付けが自動的に行われます

Gif8

もし、ABC、3つのランクでなく、ABCD、4つのランクにしたい時もCランクの設定個数を変えれば自動的に作成できます

Gif10

これで大分、実践的になってきました。次回はこれまでの話しを少しまとめた上で、次の段階にいきたいと思います

ちなみに実際にサンプルを動かしてみたいという方は以下をご利用ください

アイコン

ABC分析 258.23 KB 44 downloads

...

にほんブログ村 資格ブログ ビジネススキルへ
にほんブログ村
にほんブログ村 IT技術ブログ VBAへ

ABC分析をエクセル力で改善する4

前回はABC分析のデメリットに触れました

5

今回から何故、この2つのプロセスが問題かについての解説と、克服方法について解説します

1.並び変えは時間の浪費を生む

下の図は”販売個数”に従って

⇒構成率を計算⇒累計・構成率を計算⇒ランク付け(IF関数でランク付け)

を行っています

この表に販売個数/5,000を付け加えたとします

(商品別に行が構成されているという前提です)

そうするとどうなるでしょう?

6

再び同じ作業を繰り返した後に、Aランクが増えます

Aランクが微妙に多すぎたりします

すると、Aランクの基準を変えたくなります、つまり、IF関数の条件を変更するということです、

そしてBランクも・・・

これでは時間の浪費です

もっといい方法がないものでしょうか?

ここがエクセル力の見せどころです

<エクセルの基本機能を組み合わせて実務的な活用をする力>

2.VLOOKUP関数とPERCENTILE.INC関数、とテーブル機能を組み合わせる

ここで違和感を感じる人がいるかもしれません

”何故VLOOKUP関数”なのか

”PERCENTILE.INC関数”とは?

では次のファイルで上記2つの関数のエクササイズをしてみましょう!疑問が解けると思います

アイコン

ABC分析 258.23 KB 44 downloads

...

目次から”VLOOKUP関数&Percentile関数”のシートに飛んでください

7

まず、VLOOKUP関数ですが、70を検索値としてEからF列にある表を参照していますが、エラーになっています

何故なら、表に70が無いからです

では参照方法を0から1に指定し直したらどうでしょう?

Gif3

検索値を超えない範囲(今回で言えば70を超えない範囲)で最大の値を参照します(今回は10)

つまり、近似値で検索をするのです

このVLOOKUP関数の近似値検索を利用すれば、ランク付けの基準を変える時に、

数式を変更しなくても、

並び変えを行わくても、

表を変えさえすれば、ランク付けは変更されます

他の言い方をすると、累計構成率ではなく、直接的に値(今回の事例では”販売個数”)を参照してランク付けが行えます

VLOOKUP関数の詳細な使い方は以下をぜひ参照してください

VLOOKUP関数とIF関数だけを使い倒す#1

次にPercentile関数です

下のGIF画像の右側にある値の一覧の中から、指定した基準の値を拾ってくくれます

例えば50%と指定すれば1から10の間で上位50%に位置する値を拾います

Gif4

数式の指定の仕方自体はとてもシンプルです

参照する値の範囲を指定し、加えて基準を指定します

(ここで80%を20%に置き換えていることにご注意ください)

8

PERCENTILEINC関数の概要が明確になってきたと思います

VLOOKUP関数とPERCENTILEINC関数を組み合わせることで、

ABC分析の肝・・・例:上位20%の商品で売上の80%を稼いでいる

この肝に準じて表作成を自動的に行っていけるのです

 

次回は再びこの肝から解説をはじめて、より詳細に解説を行っていきます
にほんブログ村 資格ブログ ビジネススキルへ
にほんブログ村
にほんブログ村 IT技術ブログ VBAへ

ABC分析をエクセル力で改善する3

ところABC分析とは何でしょう?

辞書を見ると下記のように記述がありました

4
https://kotobank.jp/word/ABC%E5%88%86%E6%9E%90-1360
要はビジネスの対象を3つに分け、対策が効率良く打てそうなところを抜き出すということです

背景には20-80%ルールがあり(売上の8割は2割の顧客で稼いでいるなど)、とにかく対象を3つに分けてしまうのがポイントです

分析の本質はこのグループ分けです

グループ分けをすることで比較が行い易くなり、優先順位が決めやすくなります

ABC分析を行うメリットは”優先順位を決めやすい”というところにあると思います

ビジネスにはコストと時間がつきものです

いくら時間をかけて分析して立派な実行計画を経てても実行できなかったら意味がありません

その点、ABC分析では、量と質の観点からシンプルに優先順位を決めることができます

一方、ABC分析のデメリットとは何でしょう

それはABC分析を実施する過程にあります

ABC分析を実施するに当たっては、以下の2つのプロセスを踏みます

1.データを降順(売上金額など)で並べ替える

2.データにランクを付ける

この2つのプロセスが何故、デメリットになるのかを次回、詳細に解説します

にほんブログ村 資格ブログ ビジネススキルへ
にほんブログ村
にほんブログ村 IT技術ブログ VBAへ

ABC分析をエクセル力で改善する2

前回、エクセル力という言葉に触れましたが、今回はある実例をもとにしてエクセル力とは何かを考えてみたいと思います

2

例えば、以下のようにA、B列に10万行データがあったとします

そして、あなたは上司からこれから毎月、A列、B列ともに値が”A”の時、

解の列に◯を入力するように言われたとします

1

しかも、VBAでの処理なしです

あなたはもしかして、目でA列とB列にAがあるのを確認して解の列に◯を入れますか?

ちなみにこれは実際に体験した話しで、上司が毎月、人力で毎月処理していたのを引き継ぐことになったのです

 

詳細は他の記事に譲りますが、ここで下のようにIF関数で処理するのももちろん悪くありませんし、

IF(A2=”A”,IF(B2=”A”,”◯”、”✕”),”✕”)

上のIF関数の中にANDを入れるのも悪くありません

 

ここから、エクセル力という話が出てきます

もし、上司に

「やっぱりA列、B列、ともに”B”の時に◯にしてくれ」

とルールを変更された場合にはIF式を変えなくてはなりません

(ちなみに過去との比較の為、やり直さなくてはいけない、という想定です)

そうなると、もの凄い莫大な時間がかかってしまいます

 

では、どうするかというと、複合キーとVLOOKUP関数とテーブル機能を組み合わせておくのです

3

もう少し詳細を解説するとまず、テーブルを作成した上で

1.AAに◯という対応表を作成しておき

2.C列に列を挿入し、A列とB列を&で組み合わせた複合キーを作成します

3.そして、解の列にVLOOKUP関数を挿入し、

複合キーを対応表の中から検索するようにします

GIF4

(上の図ではIFERROR式でエラーになった時は空白が入るようにしてます)

こうしておけば、対応表を変えるだけで過去の修正が効きます

しかも、テーブル機能を活用すれば、1行に数式を入れれば全ての行に数式が入ってしまいます

 

これが本篇で定義するエクセル力です

ビジネスの実務では変更や修正はつきものです

しかも、時間が無いと嘆いても誰も待ってはくれないのです

そこで、

エクセルの活用方法も”変更”や”修正”などに備えた実践的な活用方法が必要なのです

<エクセルの基本機能を組み合わせて実務的な活用をする力>

この力を次回から、実際のABC分析の例をもとにして身に着けていきましょう!

にほんブログ村 資格ブログ ビジネススキルへ
にほんブログ村
にほんブログ村 IT技術ブログ VBAへ

ABC分析をエクセル力で改善する1

https://analytic-vba.com/%e3%82%a8%e3%82%af%e3%82%bb%e3%83%ab%e5%88%86%e6%9e%90/post-2018/世の中に山とある分析方法の中で、一番多く有効活用されているのはABC分析だと思います

但し、何事にもメリット・デメリットがあります

この講座ではABC分析のメリットを生かしつつ、デメリットを克服し、実践的な活用を目指します

そして、

”この”実践的な活用ができるように手法を改善していく過程を通じて本質的な”エクセル力”を習得することもあわせて狙っていきます

ちなみにこの講座は”ストアカ”で実際に実施した講座のWeb版になります

1

受講者の方のフィードバックを基にしてエッセンスが凝縮された講座にしたいと思っています

今回は1点、先行してポイントを1点、解説したいと思います

ABC分析に限らず、他の分析でも同様に”如何に効率よくデータを区分するか”というのが大きなポイントになります

そして、必ず、”どの範囲で区切るか”という試行錯誤が起こります

その試行錯誤が簡単に行えるとと、ても分析作業の効率が飛躍的に向上します

下の例はデータの全体をある指標を基にして上位から区分し、各データにA、B、Cとラベルを付けているところです

詳細は後の回で後述しますが、従来のABC分析の手法だとラベルをつけるのに複数のステップが必要で、データが1つ追加されただけでも最初からやり直しになります(下の例では販売個数10,000のデータが2つ追加されていますが、自動的にランクAのラベルが付いています)

Gif1

この講座では、この試行錯誤を簡単な3つの仕組みを組み合わせるだけで簡単に行えるようにします

そして、最終的には下のようなエクセルダッシュボードを作成します

GIF2

従来のパレード図より、ABCランクの違いが明確に分かるようになり、実践的な情報を提供できるようになります!

それではまた次回

にほんブログ村 資格ブログ ビジネススキルへ
にほんブログ村
にほんブログ村 IT技術ブログ VBAへ

グラフを魅せるエクササイズ_5~画像でグラフ~

白黒でも”これ何?”と言わせないグラフにするのに究極な方法が画像で表示することです

まずは表示にふさわしい”画像”を用意します

44

実は、画像上で<Ctrl+C>、該当するグラフ上で<Ctrl+V>とするだけでグラフに画像が反映されます

GIF9

画像が反映されたら、下のように指定しましょう

45

すると画像の数で数字を表すようにできます

46

最後に、上記の方法の他に画像をインポートする方法もあることを付け加えておきます

47

エクセルで弾丸チャートを作成しよう 6回目

前回では弾丸チャートの前期実績のグラフを誤差範囲を使用して作成しました

今回は、予算を以下の画像の横棒の形にしてグラフ自体を完成させます!

キャプチャ29

なぜ、こんな横棒の形になるのか?

答えを最初に言うと、折れ線グラフの棒の形のマーカーの種類と大きさを変えることでこんな形になります!

キャプチャ30

加えて解説を加えると、折れ線グラフの折れ線を見えなくするのです!

<今回行うこと>

1.予算を折れ線グラフに変える

2.折れ線グラフのマーカー種類、大きさを変える

3.凡例を修正する

では、早速1と2、そして3と順に行いましょう!

1.予算を折れ線グラフに変える

グラフを右クリックして”グラフの変更”画面を呼び出してください

キャプチャ31

予算を折れ線グラフに変えましょう!

2.折れ線グラフのマーカー種類、大きさを変える

折れ線グラフを右クリックして、マーカーの変更が面を呼び出してください(書式設定を指定)

キャプチャ32

種類を横棒にして、サイズを大きくすれば、上の画像の左側のグラフの形になります

キャプチャ33

次に折れ線グラフの線を”線なし”に指定しましょう

線が消えると一見グラフ自体は完成に見えますが、凡例が正しく表示されていません

キャプチャ33

キャプチャ34

これは、前年がグラフを消しており、予算は棒グラフにしてあるので、マーカーの形を示しているのです

3.凡例を修正する

実は3回目で、作成した棒グラフをコピーしておいてください

とお願いしました

キャプチャ36

これは、凡例だけ切り取って、今回作成したグラフの凡例として貼り付けたいからです

Snipping toolなどで切り取って、貼り付けましょう!

キャプチャ35

これで終了です

<まとめ>

今回、予算のグラフの形を変えたので、グラフ自体は完成です。次回はこれまでのおさらいをしましょう!

ではまた次回
にほんブログ村 資格ブログ ビジネススキルへ
にほんブログ村
にほんブログ村 IT技術ブログ VBAへ