予算達成率の計算式

マイナスの予算に対して実際にはマイナスが少なくて済んだ場合の達成率の計算の仕方についてお教え願えませんでしょうか。

予算　▲１８９　　実際　▲１５０　の時
予算　▲１８９　　実際　　　３０　の時

よろしくお願い致します。

No.4 ベストアンサー 回答者： gootaroh 回答日時： 2007/08/08 14:03

No.4です。

再び失礼します。
ちょっと違うような気がして、あれから少し考え直しました。
次の式は、質問者様の意図を反映していると思います。

達成率=ROUNDDOWN(IF(予算=0,"－",(過不足額/ABS(予算)+1)*100),1)

パターン　予算　実績　過不足額　達成率
(1) 100 150 50 150.0
(2) 1,000 1,000 0 100.0
(3) 1,000 0-1,000 0.0
(4)-1,000 900 1,900 290.0
(5)-1,000 -1,000 0 100.0
(6)-1,000 0 1,000 200.0
(7) 0 900 900-
(8) 0 900 900-
(9) 0 0 0-
(10) -189 -150 39 120.6
(11) -189 30 219 215.8

上の試算は、予算がプラス、マイナス、ゼロの場合に分け、
それぞれ実績がプラス、マイナス、ゼロに分けたものです。
ご質問は、パターン（10）と（11）に計算してあります。

No.1様への「お礼」では、
>▲１８９÷▲１５０＝１．２６として計算してしまうのもおかしいような気がします。
・「予算/実績」ですから、これはおかしいですね。
赤字、黒字、トントンのどのパターンの場合でも同じ式が使えないと意味がありません。

この回答へのお礼

前回頂いた回答だとしっくり来なかったのですが、この回答ですと自分の思っていたことと同じだと思います。
質問ではエクセルの計算式のことは伝えていなかったのですが、計算式まで教えて頂き大変重宝致しました。
ありがとうございました。

お礼日時：2007/08/08 18:33

No.3 回答者： gootaroh 回答日時： 2007/08/07 11:00

要するに、予算が赤字の場合でも、予実差異額と達成率の正負を一致させたい、ということですよね。

以下、Excelで説明します。一番左の列を「予算」として予算額を入力、その隣に「実際」として実際発生額を入力、その隣に（これがキモですが）「予実差異額」として（実際額－予算額）の数式を入力しておきます。そして、更にその隣の列には「達成率」として次の数式を入力してください。
=IF(予実差異額=0,0,(IF(予算=0,"―",ROUNDDOWN(予実差異額/ABS(予算)*100,3))))
（数式内の日本語部分は説明用であり、実際は対応するセル番地を入力してください。念のため）

これでいかがでしょうか？

通常達成率とは「予実差異額÷予算」で計算しますが、予算が赤字の場合、予実差異額と達成率の正負が逆になります。
予算が赤字の場合、数学上は「赤字額」が主語になってしまい、数字の解釈に混乱が生じてしまいます。
そこで、分母を予算の絶対値とすることで、この問題を解消させます。

先にご紹介した数式では、予算0円で実際は発生した場合の表示方法、そして、予算・実際ともに0円の場合の達成率の表示方法という例外規定も盛り込んでいます。

予算が0円とは、分母が0ということなので、数学上、答えの無い「不能」です。これはどうしようもないので、「－」（横棒）と表示します。

予算・実際ともに0円の場合については、Excelでは、分母が0の場合、直ちに「0除算エラー」と扱いますので、予算・当期ともに0円の場合、このままでは(2)の「－」（横棒）と表示されてしまいます。
しかし、たとえ分母が0であっても、「0÷0」だけは「不能」ではなく、答えが一つに定まらない「不定」です。
Excelでは、「不能」も「不定」も「計算できない」という意味では同じなので、数学上の意味が異なるにもかかわらず、扱いを同じにしているのです。
この場合の分母の意味は「予実差異額」ですから、数学上「不定」であっても、他の予実差異額0円の場合と同様に「達成率0％」と表示するのが適切です。

あと、端数処理は「切り捨て」が適切です。この計算式では、小数点第2位以下を切り捨てています。
理由は、例えば、予算10000円、実際19999円、予実差異額9999円の場合、四捨五入では「100.0％」となります。
しかし、達成率の計算において「100.0％」とは、予実差異額が「予算と同額」という意味です。
この場合の予実差異額は予算額より1円不足しており同額ではないので、厳密には「100.0％」という表現は適切ではありません。
「四捨五入」と断っている場合は許容範囲なのでしょうが、感覚的にも間違えないように、切り捨てて「99.9%」と表示するのが適切だと思います。

なお、蛇足ですが、専門家の中には「赤字同士を比べても意味がない」、「比較は黒字が前提」という方もいらっしゃいますが、私はそうは思いません。
例え赤字であっても、赤字が減少していれば、それはそれで企業努力であり、「達成した」あるいは「好転した」といってもよいはずです。
赤字を計算から除外すると、適切な経営判断ができなくなるおそれがあります。赤字は赤字としてきちんと比較計算し、分析するべきだと思います。

この方法ですと、ご質問の場合の達成率は、
予算▲189　実際▲150　の時→達成率20.6％
予算▲189　実際　 30　の時→達成率115.8％
となります。
この方が、実感とも合致するのではないでしょうか。

No.2 回答者： okame7237 回答日時： 2007/08/06 22:33

　基本的に△前提の場合ですと、達成率という考えはなしだと思います。

もちろん費用が100かかるのを80となれば費用の20％減といいますが。（ご質問のとおり）利益の達成率－150％とか話をしてもぴんと来ないのが実態ではないでしょうか。
　

No.1 回答者： m_inoue222 回答日時： 2007/08/06 15:58

＞予算　▲１８９　　実際　▲１５０　の時

予算＝「１８９だけ損をしなさい」で
実績＝「１５０しか損が出せなかった」ですから
達成率７９％

＞予算　▲１８９　　実際　　　３０　の時

予算＝「１８９だけ損をしなさい」で
実績＝「３０儲けてしまった」ですから
達成率▲１６％

元々目標をマイナスに設定する事が特殊処理ですから何らかの＋目標にすべきでしょう

最大マイナス金額が１８９とか

紙の消費を２００枚削減しましょう＝▲２００では無く、削減目標２００でしょうね

この回答への補足

質問の仕方が悪いですね。研究開発部門で売上に対し、経費がかかり利益がマイナス予算になってしまった。
でも、努力して製品を開発し、売上が上がったもしくは経費の削減努力をしたという前提で。

そうしましすと、予算が▲１８９に対して▲１５０で済んだので、達成率が７９％だと努力をしていないように見えてしまいます。
マイナスを少なくしたご褒美という考え方で達成率を１００％以上としたいのです。
計算式的には予算対比▲１５０÷▲１８９＝０．７９である意味正しいとは思うのですが、▲１８９÷▲１５０＝１．２６として計算してしまうのもおかしいような気がします。
よろしくお願い致します。

補足日時：2007/08/06 16:22