エクセルを活用した、特別積合せ貨物運賃（以下、特積み運賃）委託契約のシミュレーション例
新規で運送会社と運送委託契約する際には、月間の支払運賃がどれくらいになるかを事前に計算し、コストを想定しておくことが重要です。今回は、マイクロソフト社のエクセルを活用した、特別積合せ貨物運賃（以下、特積み運賃）委託契約の場合のシミュレーション方法について触れていきます。
特積み運賃は、1伝票でまとめられる重量とその運送距離（契約距離）に基づいて計算されることが基本です。重量と運送距離は一定の区間（ex.10kg単位、50km単位）ごとに区切られていて、その区間を超えるごとに運賃が段階的に高くなる仕組みになっています。
では、さっそくエクセルを活用したシミュレーション方法の一例をご紹介します。今回のシミュレーションは下記4つのデータがあることを前提とします。
(1) 出荷実績データ（いつ、どこからどこへ、どの商品を、どの重量等で出荷したかを記録した明細データ）
(2) 距離程
(3) 特積み運賃の料金データ（料金表をエクセルのピボットテーブル等でデータベース化したもの）
(4) 重量と距離の計算値マスタ（料金表から作成します）
STEP1：販売実績データの1伝票単位での集約・重量計算
まずは、出荷実績データ(1)をもとに、１伝票単位に集約する処理が必要となります。一般的には、1伝票を示す伝票ナンバーというものがあり、その伝票ナンバーで集計した合計重量を運賃計算に使用します。伝票ナンバーがない場合は、同一出荷日・同一発地・同一配送先を1つの取引とみなし、合計重量を算出します。ただし、集約単位は契約内容によって異なる場合があるため、事前に契約条件を確認するようにしてください。
ピボットテーブルを使用して1伝票単位にデータを集計することで、簡単に合計重量を求めることができます。この時、後のステップで必要となる発地・着地の情報もデータにもってきておきましょう。
STEP2：発地着地で決められた距離の参照
次に、各出荷の発地と着地（配送先）の距離を距離程(2)から参照します。エクセルのVLOOKUP関数等を使用し、契約距離を自動的に引き出すことができます。
STEP3：重量と距離を計算値に置き換える
運賃は重量や距離が一定の区間を超えるごとに運賃が段階的に変わる仕組みの為、計算値に置き換える必要があります。VLOOKUP関数等を使用し、STEP1で算出した重量とSTEP2で取得した距離に、計算値マスタ(4)からそれぞれの計算値を反映します。
STEP4：契約運賃の計算
STEP3で参照した計算値をもとに、契約運賃を計算します。契約運賃表(3)に基づいて、計算値に対応する運賃をVLOOKUP関数等で参照できるよう設定します。
STEP5：集計と確認
最後に、すべての出荷データを集計し、月間の予想運賃を算出します。ピボットテーブルを使って複数の取引を一括で集計することで、全体のコストを迅速に見積もることができます。
今回のシミュレーションは特積み運賃を例に解説しましたが、貸切運賃でも同様に計算できます。
また、“物流事業者からの請求運賃が本当に正しいのか？”、“物流拠点を変えた場合運賃がどう変化するか？”等の試算にも活用できますので是非一度試してみてください。
（文責：高橋）
（ロジ・ソリューション（株） メールマガジン/ばんばん通信第551号 2024年10月16日）
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
★掲載された記事の内容を許可なく転載することはご遠慮ください。
ロジ・ソリューションでは、物流に関するいろいろなご支援をさせていただいております。何かお困りのことがありましたらぜひお声掛けください。（お問い合わせはこちら）
