調剤薬局業務をExcelで快適に PharmaDataLabo

調剤薬局業務をExcelで効率化しよう

薬価改定に向けて分割品購入にすべき?Excelで考えるとこうなる!

 

sponsored Link

 

先日の記事で、薬価改定を控えて通常包装品を発注すべきか、分割品で発注すべきかを考えてみました。

 

pharmalabo.hatenablog.com

 

この記事の中では特定の医薬品の特定の状況(現在庫数、使用見込み)においての検証だけでしたが、様々なケースを検証するためにはExcelを利用するのが効果的です。

 

ここで押さえておくべきパラメータには、

  1. 各薬局で固定される項目
  2. 各薬品で固定される項目
  3. 各薬品の状況を示す変動項目

があります。

 

1には、卸ごとの通常包装品の仕入れ率(納入価率)があります。薬価に対し何%の価格で納入されるか、という値です。多くの薬局は総加重で妥結していることと思いますが、単品単価契約の場合は2に分類されることになります。

 

2には、各薬品の薬価、包装容量、及び今回の薬価引下げ率(=1‐新薬価/現薬価)があります。

 

3には、その薬品の現在庫数と3月末までの使用見込み数(使用量)があります。

 

これらのパラメータを入力できるようなExcelシートを作成します。

f:id:ashomopapa:20180225180401p:plain

※薄オレンジの網掛けセルにパラメータ入力

上図のようにA列のセルに項目名を入力し、通常包装品と分割品に対応したセルをそれぞれB列、C列に用意します。

C5セル「=B5」

B6セル「=B5*B3」、C6セル「=C5*C3」

C7セル「=B7」

B8セル「=B5*(1-B7)」、C8セル「=B8」…新薬価は恐らく3月5日に告示されます

C9セル「=B9」

C10セル「=B10」

B11セル「=IF(B10>B9,CEILING(B10-B9,B4),0)」

C11セル「=IF(C10>C9,CEILING(C10-C9,C4),0)」 ※B11セルを右にフィルドラッグ

B12セル「=B9+B11-B10」、C12セル「=C9+C11-C10」※B12セルを右にフィルドラッグ

 

 B11,C11セルの解説

使用見込み>在庫数の場合には、CEILING関数を使用してB4(またはC4)に入力した包装単位での仕入数を算出するため上記の式になります。

使用見込み<=在庫数の場合には発注する必要がないので、0、とします。

 

B12,C12セルの解説

期末在庫数を求めるため、「現在庫数+仕入数-使用数」という計算式にします。

 

 

 次に通常包装品と分割品で薬価差益の差を求めます。

f:id:ashomopapa:20180228185207p:plain

【薬価差益】

現在庫を使い切った分の薬価差益①と、期末までの使用のために仕入れた分から期末までに使用した分の薬価差益②の合計、とします。

今回の例では、現在庫数50錠、使用見込み70錠となっていますので、現在庫数の50錠を使い切った分の薬価差益①と、使用見込み70錠まであと20錠足りないので通常包装で100錠仕入れて20錠使用した時の薬価差益②または、分割品で20錠仕入れて20錠使用した場合の薬価差益②の合計①+②となります。

B15セル「=B10*B5*(1-B3)」

C15セル「=IF(C10>=C9,(1-B3)*C9+(C10-C9)*(1-C3),C10*(1-B3))*C5」

※C15セルの解説

  • 使用見込みが在庫数以上なら、通常包装の薬価差益率×在庫数と(在庫数~使用見込み数)×分割品の薬価差益率を合計して、
  • そうでない(使用見込みより在庫数が大きい)なら使用見込み×通常包装の薬価差益率 を求めて、最後に現薬価をかける。

 

【改定後薬価差益】

3月末に残った「期末在庫数」分が4月以降に新薬価で使用されて薬価差益が発生することを考えます。

今回の例では現在庫50錠から100錠仕入れて70錠使用したので、期末在庫は30錠となります。分割品の方ではたまたま期末在庫が残らないという状況です。

B16セル「=B12*(B8-B6)」

 ※期末在庫数×(新薬価-通常包装品の3月までの原価)

C16セル「=IF(C10>C9,C12*(C8-C6),B16)」

 ※使用数が現在庫数以上なら、期末在庫数×(新薬価-分割品の3月までの原価)

 ※使用数が現在庫より少なければ、B16セルと同じ値

 

【改定後仕入 薬価差益】

 3月中に通常包装品を仕入れた場合の期末在庫数に合わせるように、4月以降に分割品を仕入れ、それぞれが使用された場合の薬価差益を考えます。

今回の例では通常包装で仕入れた場合の期末在庫が80錠のため、3月に分割品を仕入れた場合の期末在庫との差である80錠を通常包装品で仕入れた場合の差を考えます。

B17セル「=0」通常包装品で仕入れた場合の期末在庫数の方が多い場合を考えるため

C17セル「=(B12-C12)*C8*(1-B3)」

 ※(通常包装品仕入れた場合の期末在庫数-分割品を仕入れた場合の期末在庫数)×新薬価×通常包装品の薬価差益率

 

【合計薬価差益額】

現在庫を使用した分と、3月末までに仕入れて3月末までに使用した分と、4月以降に使用した分の薬価差益の合計を算出します。

B18セル「=B15+B16+B17」

C18セル「=C15+C16+C17」

 

【差額】

合計薬価差益額の、通常包装品を仕入れた場合と分割品を仕入れた場合の差額を出します。

C19セル「=B18-C18」

※通常包装品を仕入れた場合が得なら+プラスに、分割品が得なら-マイナスになる。

 

 

これで各パラメータを入力した場合に、通常包装品で仕入れるべきか分割品で仕入れるべきかが、客観的に判断することが可能になります。

ただし、このままでは各薬品の状況に応じてパラメータ入力をしないといけません。これはとっても非現実的です。

そこで、Excel2010以降の標準機能である「データテーブル」機能を利用します。(リボンのデータ>予測>What-If分析>データテーブル を利用)

f:id:ashomopapa:20180228213756p:plain

 ワークシートに上図のような表の枠組みを作成します。

  1. 縦に現在庫数として0~100まで5ずつ増やして入力します。この時も楽するために、初めの2つだけ(0,5)を入力したら、そのF3~F4セルを選択してフィルドラッグします(フィルハンドルを下方向にドラッグ)
  2. 同様に横方向に期末まで使用見込み数として10~190くらいまで10ずつ増やして入力します(初めの2つを入力してフィルドラッグする)
  3. F2セル「=C19」と入力する

F2~Y23を選択して、リボンの「データ」タブ>「予測」グループ>「What-If分析」>「データテーブル」を選択し、開いた次のウインドウで、

f:id:ashomopapa:20180228215815p:plain

行の代入セル「=$B$10」

 ※期末までの使用見込みのB10セルに行方向のG2~Y2セルの値を代入する

列の代入セル「=$B$9」

 ※現在庫数のB9セルに、列方向のF3~F23セルの値を代入する

OKをクリックすると、G3~Y23のセルに一気に値が表示されます!

これで、在庫数と使用見込みに応じた判断が可能になりました。(下図)

f:id:ashomopapa:20180228221318p:plain

 上図で赤字で表示されるのは、分割品を購入した方が薬価差益が大きいということなので、この範囲になる現在庫:期末までの使用数の組合せでは「分割品」を仕入れた方がお得になります。

 

図では赤の範囲が規則正しく斜めに位置していることに気づきます。

期末までの使用数が現在庫より+40までは赤字、つまり分割品での仕入れがお得となっています。

ということは、通常包装で100錠仕入れておよそ半分(45錠)使えるなら、通常包装で発注すべきということになります。

 

薬価及び薬価引下げ率の影響

 

では、薬価の大小、薬価引下げ率の大小ではその違いはどうなるのでしょうか?

1)薬価の大小

上図ではセルセプトカプセル250mgの薬価269.4円の場合ですが、これを薬価100円にしてみると、次の図になります。

f:id:ashomopapa:20180302215335p:plain

 薬価が小さくなる分、数字(薬価差益の差)自体は小さくなりますが赤字の範囲には変化がありません。

つまり、薬価が低ければそれほど大きな差にはならないが、100錠包装の半分を使用するなら通常包装で仕入れた方が一応お得になる、ということですね。

 

2)薬価引下げ率の大小

それでは薬価を基に戻して、薬価引下げ率を大小変化させてみます。

・薬価引下げ率「3%」の場合

f:id:ashomopapa:20180302220353p:plain

・薬価引下げ率「10%」の場合f:id:ashomopapa:20180302220427p:plain

薬価引下げ率が大きくなれば、分割品を仕入れた方がいいという赤字の範囲が広くなることが分かります。

これを1~10%でまとめると、次のようになります。

薬価引下げ率 100錠包装中通常包装が適する使用見込み数
1% 15
2% 15
3% 25
4% 35
5% 35
6% 45
7% 45
8% 45
9% 55
10% 55

薬価引下げ率が小さければ、そもそも薬価差益の差は小さくなることも併せて考えると、例えば「薬価引下げ率が3%以下なら通常包装品で仕入れる」と薬局長が決断したり本部主導で決定してもいいかもしれません。 

 

仕入れ率の影響

 

通常包装の仕入れ率によってどのように変化するか見てみましょう。

分割品は、納入価×1.08(消費税分)=薬価、となるように納入価が決まることがほとんどですので、約92%で固定で考えます。

通常包装の仕入れ率 100錠包装中通常包装が適する使用見込み
70% 15
75% 25
80% 35
85% 45
90% 75

仕入れ率が大きくなれば分割品との差も小さくなります。ただ仕入れ率自体は薬局と卸の間で値は固定されるので、皆さんの薬局での値を確認しておくといいでしょう。

包装容量の影響

 

通常包装:分割包装が、100錠:10錠の場合が多いですが、140錠:14錠、56錠:14錠、などいくつかのパターンがありますので、その関係を確認します。

通常包装 分割品 通常包装が適する使用見込み数
100 10 45
50 10 25
140 14 60
56 14 15

3つ目のパターンまでは概ね通常包装の4~5割の使用見込みがあれば通常包装品が適しているという結果です。

4つ目のパターンでは14錠1シート(25%)以上使用するのであれば、通常包装品が適しているということになります。

 

 

まとめ

 

いかがでしたでしょうか?

いくつかのパラメータによって、通常包装品が適しているかどうかということが、薬価差益の差をベースにして考えることができる、というお話でした。

この考え方を発注システムに組み込めば、システムで最適な包装を割り出すことも可能です。(実際そんなシステムは見たことありませんが、、、笑)

最後に、今回のシュミレーションに使用したExcelファイルを、クラウドストレージを活用して、読者の皆様に利用いただけるようにしてみました。興味のある方はファイルを開いてみて下さい。

 

 サンプルファイルはこちらのページから(新薬価対応済み) H30.3.21修正しました

pharmalabo.hatenablog.com