コストテーブルの作り方とは？実例をダウンロード可能なExcelを用いて説明

製造業の調達・購買部門では、コスト管理が重要な役割を果たします。特に、原材料や部品の価格変動が激しい市場では、正確なコスト分析と最適な購買判断が求められます。そんな中、コストテーブルを活用することで、コストの可視化や最適化が容易になり、より精度の高い戦略的調達が可能となります。

本記事では、コストテーブルの基本的な作り方から、実際に使えるExcelフォーマットとその使い方まで、実務に直結する情報を詳しく解説します。コストテーブルを導入・活用することで、調達業務の効率化とコスト削減を実現しましょう。

コストテーブルのおさらい

コストテーブルは、製品や部品ごとの原価構成を体系的に整理し、コスト要素を可視化するための表です。製造業では、原材料費、加工費、労務費などの内訳を明確にし、コスト構造を分析するために活用されます。

別記事「コストテーブルとは？コストテーブルの種類や作り方を分かりやすく解説」で概要を知ることができます。まだお読みでない方は、そちらを先に読んでから本記事をお読みください。

コストテーブルとは

コストテーブルは、コストを決定するための基準となるデータであり、製造業のコスト削減活動を支える重要なツールです。これを活用することで、価格の妥当性評価や工程改善が可能になります。

また、コストテーブルは「原価に影響を与える要因と原価との関係を整理したもの」であり、費用構造の可視化を目的としています。表形式やグラフ、計算式を用いて作成され、コスト要因（変数・コストドライバー）の分析を容易にする点が特徴です。

コストテーブルは単なる価格管理ツールにとどまらず、意思決定を支援する役割も果たし、コスト最適化に貢献します。

コストテーブルの種類

コストテーブルは大きくわけて4つの種類があります。

1. 単位当たりの単価表
  単価比較や簡易的な見積査定に活用されるが、品質や仕様の違いを反映しにくい。
2. コスト見積比較表
  サプライヤーの見積を可視化し、交渉の根拠資料として活用。ただし、設備差などの背景要因を考慮しにくい場合がある。
3. 回帰分析型
  データを基に客観的にコストを予測・算定できるが、モデルの精度やデータ量の確保が重要となる。
4. 原価積算型
  外注品の詳細な見積根拠を提示し、製造原価を算出するが、作成には専門的な技術知識が必要となる。

今回は、その中でもコストの予測が可能となる回帰分析型のコストテーブルの作成方法について説明します。

回帰分析について

データの傾向を理解し、未来の予測や意思決定を行う上で、統計手法の一つである「回帰分析」は非常に有用です。回帰分析を用いることで、ある変数（説明変数）が別の変数（目的変数）にどのような影響を与えるのかを数式で表し、関係性を明確にすることができます。例えば、生産数量と製造コストの関係など、ビジネスにおける多くの分析に役立ちます。

本章では、回帰分析の基本概念やその活用方法について詳しく解説していきます。

回帰分析とは

DATA BIZ LABによると回帰分析とは、ある結果（目的変数）が他の要因（説明変数）によってどの程度影響を受けるのかを数式化し、分析する統計手法です。ビジネスにおいては、売上予測、コスト分析、需要予測など、幅広い分野で活用されています。

回帰分析では、「目的変数」と「説明変数」の関係性を表す回帰式を使用します。

説明変数が1つの場合：「y = ax + b」単回帰式

2つ以上の場合：「y = a₁x₁ + a₂x₂ + …+b」重回帰式

この式の「a」は回帰係数を指し、説明変数が目的変数に与える影響の大きさを示します。

このように、回帰分析を用いることで、データに基づいた意思決定を行い、より精度の高い予測や最適な調達戦略の策定が可能になります。

回帰分析の進め方

Neuによると回帰分析は以下の進め方で行います。

1. 目的変数を決める（目的変数：予測したい数値）
2. 説明変数を見つける（説明変数：影響を与える要因）
3. データを収集する（分析に必要な情報を準備）
4. 数式に当てはめて計算する（統計ツールやExcelを活用）
5. 計算式をもとに因果関係や結果を予測する（意思決定に反映）

Excelや統計ツールを使えば実施可能ですが、適切な変数選定やデータの精度が分析結果を大きく左右しますのでまずは簡単な分析からはじめるてみることが重要です。

単回帰分析と重回帰分析

回帰分析には、以下の2種類があります。

単回帰分析：1つの説明変数が目的変数に与える影響を分析する手法（例：製品重量が製品単価に与える影響。）

重回帰分析：複数の説明変数が目的変数に与える影響を分析する手法（例：製品重量、材料単価、プレス工程数が製品単価に及ぼす影響）。

回帰分析型コストテーブルの概要

回帰分析型コストテーブルとは、過去のデータをもとに統計的手法を活用し、コストの変動要因を分析することで、より精度の高いコスト予測を行うためのツールです。ここでは回帰分析型コストテーブルの具体的なイメージを持てるように、ダウンロード可能なExcelファイルで説明します。

回帰分析型コストテーブルのイメージ

従来のコストテーブルは、材料費や労務費、加工費などの標準的な数値を参照するものでしたが、回帰分析を取り入れることで、データに基づいた動的なコスト算出が可能になります。

特に製造業においては、製品重量による製品単価、原材料の価格変動、工程ごとのコスト差、地域ごとの労務費の違いなど、様々な要素がコストに影響を与えます。回帰分析型コストテーブルでは、これらの要素を統計的にモデル化し、最適なコスト推定を行うことで、調達・購買組織の意思決定を支援します。

今回用意した単回帰分析と重回帰分析のコストテーブルのExcelのダウンロードはこちらになります。Excel内で単回帰分析と重回帰分析のシートにわかれており、簡単に試算価格を算出できるシートになっています。具体的なコストテーブルの作り方は「Excelでの回帰分析型コストテーブルの作り方」の章から説明します。

コストドライバーとは

回帰分析型コストテーブルを作成する前に覚えておきたいポイントとしてコストドライバーがあります。コストドライバーとは、製品やサービスのコストに影響を与える要因のことを指します。製造業の調達・購買組織では、コストの最適化を図るために、主要なコストドライバーを把握し、適切なコントロールを行うことが重要です。

下記にコストドライバーの具体例を挙げます。

目的変数をコスト（購入単価）とした場合のコストドライバー（説明変数）の具体例

1. 製品重量：製品が重くなるほど、加工設備が大型化し、加工工数が多く、輸送コストや材料コストも増加する。
2. 製品サイズ：サイズが大きくなると、加工内容も多く保管コストや梱包費、輸送時の制約などがコストに影響する。
3. 加工・組付け工数：製造工程における作業の複雑さや作業時間が増えることで、労務費や設備稼働コストが上昇する。
4. 工程数：製造プロセスが多段階になると、各工程にかかる時間やコストが増加し、全体の製造コストに影響を与える。
5. 型・設備費：生産設備の導入・維持管理費用、設備の稼働率などがコスト影響要因となる。
6. 原材料費：材料が多く使われていたり、高価な材料を用いる製品ほど製品単価は高い。
7. 納入場所までの距離：配送コスト、倉庫保管費用などが製品コストに影響する。
8. 労務費：加工工数や管理工数等の全てにおいて労務費の高さはコストに影響する。
9. 品質管理レベル：不良品の発生率、品質検査の頻度などがコストに影響。

調達・購買組織において、コストを左右する要因であるコストドライバー（説明変数）は多岐にわたります。具体的には製品重量やサイズ、加工・組付け工数、工程数などは、材料費や労務費、物流費と密接に関係し、製品の総コストに影響を与えます。

また、型・設備費や納入距離、品質管理レベルといった要素もサプライヤー側の原価として当然発生するものであり、コスト増減の重要な要因となります。これらのコストドライバーを適切に管理し、回帰分析を活用することで、調達コストの最適化が可能となります。

コストドライバーと説明変数の違い

コストドライバーはコスト構造の分析や管理の観点で捉えられるのに対し、説明変数は統計モデルの中で目的変数の変動を説明するために設定されます。したがって、すべてのコストドライバーが説明変数になるわけではありません。

例えば、サプライヤーの交渉力はコストに大きな影響を与える要因ですが、その影響度を定量的に測定することが難しく、数値データとして回帰分析に直接組み込むのは困難です。同様に、地政学リスクも発生のタイミングや影響度が予測しにくいため、過去データをもとに数値化することが難しく、説明変数として扱うのは困難です。

このように、コストドライバーの中には統計モデルに適用しにくい要因があるため、回帰分析を実施する際には、データとして収集可能で定量的に評価できる変数を適切に選定することが重要です。

Excelでの回帰分析型コストテーブルの作り方

回帰分析型コストテーブルの作成は過去の購入実績データを基に、部品のコストドライバー（説明変数）と価格の関係性を数式化し、将来の価格予測やコスト評価に活用するものです。本記事では、Excelを用いて回帰分析型コストテーブルを作成する手順を詳しく解説します。

「コストテーブルのExcelダウンロードはこちら」

作成対象部品の決定

まず、コストテーブルの作成対象となる部品を選定します。以下のポイントを考慮して、効果的な部品を選びましょう。

・取引額の大きい部品：全体の取引額に占める割合が高い部品は、コストテーブルの活用による効果が大きくなります。

・生産量の多い部品：生産量が多い部品は、コスト削減の影響が大きく、分析の優先度が高まります。

・生産方法の共通性：同じ生産方法や工程を持つ部品は、コストドライバーの特定や分析が容易です。

・安定した生産性：生産工程やコスト構造が安定している部品は、回帰分析の精度が高まります。

コストテーブルの効果を最大化するためには、取引額や生産量が大きい部品など、より効率的な部品を選定することが重要です。これにより、コスト分析の精度が向上し、より効果的なコスト削減施策を講じることができます。

予備調査

コストテーブルを作成する前に、対象部品やその製造工程の概要を把握する必要があります。具体的には、以下の情報を収集します。

・対象部品（製品）の機能：どのような用途で使用されるのかを明確にする。

・生産量：月間・年間の生産数量を確認する。

・加工工程：主な製造プロセス（鋳造、プレス、切削、溶接など）を特定する。

・材料費・部品価格の概要：主要な原材料や購入単価の傾向を把握する。

・発注先サプライヤーの能力、特性：主要サプライヤーの生産能力や技術力を確認する。

購入実績データの整理と絞り込み

新コストテーブルによると、購入実績データの整理と絞り込みは、購入実績データの内容を整理し、コスト分析に適した形に整えます。以下のステップを踏んでデータを準備します。

1.タイプ：コストテーブルを作成するために、部品をタイプ別に分類します。

例：ボールベアリング、ニードルベアリングなど

2.グレード：品質の異なるグレードを分類し、適切なデータを用いる。

例：標準品、特殊品、長寿命品など

3.キャパシティ：部品の大きさや容量に基づいて分類を行う。

例：大型、小型、超小型

このようにデータを整理することで、コストテーブルの精度を高めることができます。

コストドライバー（目的変数）の選定

コストドライバー（目的変数）は新コストテーブルによると、必ず定量的に数字で大小やレベルで表現することが必要です。

主なコストドライバーの例

1. 大きさ（サイズ・径・面積など）例：mm、cm、m、ϕ
2. 重量（質量）例：g、kg、ton
3. 容量（体積・電力容量）例：L、cm³、kW
4. その他（型枠番号・層数・精密度合いなど）例：1, 2, 3, 6

データの細分化が過剰になると作業が滞るため、試行錯誤しながら適切なコストドライバーを見つけることが重要です。

例えば、プレス加工後にカチオン電着塗装を施す部品の場合のコストドライバーは下記が考えられます。

プレス加工

・製品重量（g）

・加工面積（c㎡）

・製品周長（mm）

・材料引張り強度（Mpa）

・材料単価（kg / 円）

・必要加圧力（ton）

・工程数（回)

・生産量（個/月）

・絞りの深さ（mm）etc

カチオン電着塗装

・表面積（c㎡）

・製品体積（cm³）

・ハンガー掛け数（個）etc

Excelに単価とコストドライバーのデータを取り込む

ここからは実際に回帰分析シートを使用していきます。この分析が完成し、黄色部セルにコストドライバーの値をインプットすると試算単価が表示されます。

まずは、前章で集めた単価とコストドライバーのデータをファイル内の「購入実績データ」の部分にインプットします。

回帰分析の実施

①データ分析から回帰分析を選択します。

②データの対象範囲や条件を指定し実行します。

③実行後、26行目より下の部分に回帰分析結果が表示されます。

コスト試算用の数式を作成

単回帰直線の数式はy=ax+bで表され、今回の場合はy：コスト、x：コストドライバー(重量)、a：係数、b：切片となります。

分析の結果から、H5とH6セルに係数（a）と切片（b）の値が入り、コストドライバーである重量をH2セルにインプットすると、H3セルに試算単価が表示されます。

・係数（aの値）：コストドライバー（例: 重量や材料単価）が1単位増加したときに価格がどれだけ変動するかを示します。

・切片（bの値）：コストドライバーがゼロのときの基準価格を示します。

回帰分析型コストテーブルの例：プレス品の重回帰分析コストテーブル

ここでは回帰分析シートを用いた重回帰分析の事例を説明します。単回帰分析と重回帰分析の方法の違いはデータの範囲選択の違いのみとなります。

①データ分析から回帰分析を選択します。

②データの対象範囲や条件を指定し実行します。単回帰分析の場合と異なる箇所は入力のX範囲が1列→3列と変更となった点のみです。

重回帰分析の結果がa1、a2、a3となります。

・a1：材料単価の係数

・a2：製品重量の係数

・a3：プレス回数の係数

そして、黄色部に各コストドライバーの値をインプットすると、試算単価が計算されます。

調達組織が回帰分析型コストテーブルを用いる具体的な業務例

1. 価格交渉の準備：サプライヤーとの交渉前に、適正価格を算出。
2. 新規サプライヤーの評価：既存データと比較し、コスト競争力を評価。
3. 予算策定：来年度の部品調達コストを試算し、予算計画を立案。
4. 異常価格の特定：実績データと比較し、異常値を発見する。
5. コスト削減施策の立案：データ分析を基に、最適なコスト削減策を提案。
6. 開発段階のコスト予測：新製品開発時に、過去データを活用してコストを見積もり、適切な設計判断の支援やフロントローディングに活用。

これらのように、調達・購買部門での回帰分析型コストテーブルは、幅広い業務で有効に活用することができます。サプライヤーに見積もり依頼をすることが難しい価格交渉の準備や、開発段階でサプライヤーに見積もり依頼をしている時間がない場合など、回帰分析型コストテーブルを用いた価格予測が調達判断に大きな役割を果たすことでしょう。

まとめ

回帰分析型コストテーブルは、過去の購入実績データを基に、部品の特性と価格の関係を数式化し、将来の価格予測やコスト評価に活用する手法です。Excelを用いることで、比較的簡単に回帰分析型コストテーブルを作ることができ、その結果を用いて調達戦略の高度化を図ることが可能です。

回帰分析型コストテーブルの作成手順のまとめ

・対象部品を適切に選定し、コストテーブルの効果を最大化する。

・購入実績データを整理し、適切なコストドライバーを選定。

・Excelで回帰分析を実施し、価格算出のための数式を導き出す。

・試算テーブルを作成し、価格交渉やコスト削減に活用する。

この手法を活用することで、より精度の高い調達コスト管理を実現できるでしょう。

投稿者プロフィール

A1A編集部: A1Aブログは製造業向け調達データプラットフォーム「UPCYCLE」を提供するA1A株式会社が運営するメディアです。製造業の調達購買業務に役立つ情報を発信しています。