道  具  箱



『企業システム戦略』の構築・実践や人材育成に役立つ道具を紹介します。

◆簡易原価積上げ計算(Excel/VBA)の説明
 製品構成展開されたツリーデータを使用して、製品を構成する部材の原価を積上げ、 製品原価を計算するExcel/VBAの要点を説明します。
 必要な方は、Excelファイルを自分のパソコンにダウンロードして下さい。


 BOMを使用することで原価や重量の
 積上げ計算をすることができます。

 最下層の部品から最上位の製品に
 系列を遡って数値を積み上げます。


 それでは、実際にExcel/VBAを使用して
 原価積上げをしてみましょう。


 まず、Excelには以下のように
 データが入力されている前提です。


 レベル,品名,費目,標準原価,数量,積上げ原価
 0,EndAssy,組立費,10,1,
 1,SubAssy1,組立費,10,2,
 2,Part11,購入費,10,10,
 2,Part12,材料・加工費,10,10,
 1,SubAssy2,組立費,10,2,
 2,Part21,購入費,10,10,
 2,Part22,材料・加工費,10,10,


 計算結果は、積上げ原価の列にセットされます。

 レベル,品名,費目,標準原価,数量,積上げ原価
 0,EndAssy,組立費,10,1,850
 1,SubAssy1,組立費,10,2,420
 2,Part11,購入費,10,10,100
 2,Part12,材料・加工費,10,10,100
 1,SubAssy2,組立費,10,2,420
 2,Part21,購入費,10,10,100
 2,Part22,材料・加工費,10,10,100


 最終行から1行目まで上に遡って
 原価を積上げます。

 各階層の積上げ原価は配列に保持します。
 (ここが、ミソです。)


 Part22の実際原価は、
 標準原価x数量で、10x10=100

 この実際原価100を一つ上のSubAssy2
 の配列に保持します。

 Part21の実際原価は、10x10=100
 この実際原価100を一つ上の
 SubAssy2の配列に加算します。
  
 これでSubAssy2の構成品である
 Part21とPart22の合計原価は、200

 SubAssy2(自身)の組立費10と
 構成品の合計原価200を加算して

 SubAssy2の原価は、210になります。

 SubAssy2は2個必要なので、
 210x2=420

 この積上げ原価420を一つ上のEndAssy
 の配列に保持します。



 同様に、Part12とPart11の
 合計原価は、200

 SubAssy1の原価は、210になります。

 SubAssy1は2個必要なので、
 210x2=420

 この積上げ原価420を一つ上のEndAssy
 の配列に加算します。



 これで、EndAssyの構成品である、
 SubAssy1とSubAssy2の
 合計原価は、840



 最後に、

 EndAssy(自身)の組立費10と
 構成品の合計原価840を加算して

 EndAssyの原価は、850
 
 EndAssyは1個必要なので、
 積上げ原価は、

 850x1=850



 以下が、VBAのサンプルです。

Sub SetCost()

  Dim CT, CTx, CTx_Pre(20), QTY As Single
  Dim Level, Level_Pre, Row, Row_End As Integer
  
  Set strShtName = ActiveSheet
  
  '最終行を取得
  Row_End = Cells(Rows.Count, 1).End(xlUp).Row
  Row = Row_End
  
  '1行目まで繰り返す。
  Do Until (Row = 1)
  
     'シートから各値を取得
     Level = strShtName.Cells(Row, 1).Value
     CT = strShtName.Cells(Row, 4).Value
     QTY = strShtName.Cells(Row, 5).Value

    '階層が上がった場合
     If Level_Pre > Level Then
  
        '実際原価に
       「自身の原価 + Σ構成品の積上げ原価 × 数量」
         をセットする。
        CTx = (CT + CTx_Pre(Level)) * QTY
     
    '階層が同じか下がった場合
     Else
  
       '実際原価に「自身の原価x数量」をセットする。
        CTx = CT * QTY
    
        For i = Level_Pre To Level
            CTx_Pre(i) = 0
        Next i
    
     End If
  
     If Level > 0 Then
  
       'ひとつ上の階層(親)の積上げ原価に、
        自身の実際原価を加算する。
        CTx_Pre(Level - 1) = CTx_Pre(Level - 1) + CTx
     
     End If

     strShtName.Cells(Row, 6) = CTx
  
     Level_Pre = Level
     Row = Row - 1
  
  Loop
  On Error GoTo 0
End Sub



 いかがでしたか。
 重量積上げも同じロジックで可能です。

 ERPやPLMが無くても、
 数千行くらいのBOMであればこれで十分です。

 見積に使用したり、
 コスト削減、重量軽減の

 設計改善に活用できます。


 例えば、
 
 部品を安価なものに変更したら
 製品の原価がどうなるか、

 どのSubAssyに原価や重量の
 比重がかかっているか等


 しかし、この積上げ計算を手作業や
 関数で行うのは結構大変ですよね。