按分・配賦計算をGASでおこなう。直接コスト・間接コストを求める手順

事業別採算の実績集計

事業部別コストの算出において、間接費の集計計算がなかなか厄介な実務です。
こちらも実際のシートが手元にあったほうが理解が進めやすいので先にリンクを張っておきます。

按分配賦計算をおこなう（直接コスト・間接コスト）

直接費と間接費

一つの会社が一つの事業しか行ってない場合、事業部別採算という管理概念は存在しなくてもいいです。規模が大きくなる中で、複数の事業（店舗ビジネスでは「店舗」という単位になるかもしれません）を展開した場合に、「総合的に」コストを使うケースが発生します。

直接費

今回のスプレッドシートでは、A、B、C、D　４つの事業を想定して枠を作成しています。
A事業部のために使った人件費、仕入やイベント費、請求書の額面をそのまま集計すればいいのではっきりしているものがこれに該当します。

間接費

たとえばテレビでのイメージ広告や、カスタマーセンターの運用費、特定の事業部のためだけとは限らない費用です。
しかしながら、結果としてはA事業であったり、B事業部の発展や利益に対して寄与しているので、一定の配賦基準を設定し合理的に按分する必要があります。

配賦基準の設定

これもさまざまですよね。一番乱暴だけど単純なのは、売り上げの総額でバーッとわっちゃうとかです。でもそれだと、A事業部は電話問い合わせがないのに売り上げが高いからカスタマーサービスのコストを割り当てられた。
とかなってしまうと「実態」が欲しいはずなのに、なんだかなあってなってしまいます。
「売上費・人頭割・工数割・稼働数等々、実態に紐づいて設定したらいい」大胆さだけではなく緻密さも兼ねそろえた理想的な経営者や、クレバーな会計士の皆様からありがたーくご教授いただく日々ですよね。で、どんどん管理工数だけが上がっていくと。みなさんの電卓の耐久度もそうして限界を超えて行っていると思います。
実務家として、GASでやってしまいましょうということで次に続けます。

スプレッドシートの構成

シートは二つに分かれています
「work」費用実績を入れる＋按分結果を表示する
「set」按分ﾛｼﾞｯｸを入れる
この構成です。

結果表示

黄色のセルに、按分結果が表示され、「合計」が直接・間接を含めた費用合計となります。

スクリプト

function anbun() {
  
   var wsheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("work")
   var ssheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("set")

   var cost1 = wsheet.getRange("B6").getValue()  // 宣伝部　コスト
   var cost2 =  wsheet.getRange("B7").getValue()  // カスタマー　コスト

   Logger.log(cost1)
   Logger.log(cost2)


  for(i=2;i<=5;i++)
{

   var bunsshi = ssheet.getRange(i,2,1,1).getValue()
   var bunbo = ssheet.getRange("B9").getValue()
   
   var valueA = Math.floor(cost1*bunsshi/bunbo)

   Logger.log(valueA)

   wsheet.getRange(i,5,1,1).setValue(valueA)

}

  for(i=2;i<=5;i++)
{

   var bunsshi = ssheet.getRange(i,3,1,1).getValue()
   var bunbo = ssheet.getRange("C9").getValue()
   
   var valueB = Math.floor(cost2*bunsshi/bunbo)

   Logger.log(valueB)

   wsheet.getRange(i,6,1,1).setValue(valueB)

}

}

プログラムの推移

・シートをそれぞれ定義する
・按分したいコスト２件を定義する
・宣伝部コストを宣伝部按分基準で分配する。そのたびに、結果を反映する
・カスタマーコストをカスタマーコスト按分基準で分配する。そのたびに、結果を反映する

スクリプト内での割り算

var valueA = Math.floor(cost1*bunsshi/bunbo)
cost1*bunsshi/bunbo　だと、整数にならないので、Math.floor()　でくくることで、切り捨てしています。関数だとROUNDDOWNです。

基本的な構成

getValue()とsetValue() 　と　繰り返し　で構成しております。
関数に手慣れた方にとっては関数で簡単に作れるシートだと思います。

応用と発展

１．二つの　for　構文が入っています。これを一つの構文内に収めてみましょう。
２．cost1,cost2をセル番地指定にしています。このままでは「宣伝部」「カスタマー」だけならいいですが、「宣伝部B」（宣伝部内でも役割がわかれたりすると増える）「外注費」など、項目が増えると発展させることができません。項目を増やしたときに対応できるようにしてください。
※当然、配賦基準・結果反映のセルも修正しなければいけません。

GASは二次配列

前項の課題、「１」はチャレンジしてほしいですが、「２」をやるぐらいだったら、関数でやったほうが早いですよね。今回のシートでも、関数を併用しまくっています。
二次配列をマスターした後の理想は、
・「SUM」を使わない
・部署増加に柔軟に対応できる
この拡張性まで対応できるのがGASを使うメリットですので、二次配列へのチャレンジのきっかけにどうぞ