基于Excel的產品流向優(yōu)化盈利分析模型

PAGEPAGE9基于Excel的產品流向優(yōu)化盈利分析模型創(chuàng)新成果報告銷售部2014年11月基于Excel的產品流向優(yōu)化盈利分析模型創(chuàng)新成果報告前言寶鋼集團新疆八一鋼鐵有限公司，目前產能達到1000萬噸，產品結構調整接近完成，企業(yè)實現了質的飛躍。八鋼不但生產優(yōu)質的建筑鋼材、優(yōu)特鋼、金屬制品，還生產高質量、高技術含量、高附加值的板帶產品。鋼鐵產品種類的豐富，產品結構的優(yōu)化，增強了企業(yè)的競爭活力，拓展了企業(yè)的發(fā)展空間。2014年疆內鋼鐵行業(yè)競爭激烈，鋼鐵“微利時代”已經形成，為確保公司目標的實現，開展產品流向優(yōu)化盈利分析模型建設工作?，F階段，產品與服務差異越來越小、顧客需求日趨多樣化、市場競爭越來越激烈，企業(yè)保持長久競爭力的重要手段是實施按照效益優(yōu)先、利潤最大化原則優(yōu)化八鋼產品流向結構。保持企業(yè)的資源優(yōu)勢；在為客戶提供更大價值的同時提升企業(yè)的利潤和價值。本創(chuàng)新成果：通過產品流向優(yōu)化盈利分析模型的創(chuàng)立，西北市場鋼材需求每年以較高的速度遞增。西北五省鋼材市場主要是靠投資拉動，這期間的政府投資增長，必然使得鋼材需求增長。然而，在區(qū)內鋼鐵產量嚴重過剩的情況下，八鋼公司考慮在西北地區(qū)銷售部分鋼材品種，銷售品種結構、流向優(yōu)化，并考慮重點戰(zhàn)略用戶、渠道維護，通過產品流向優(yōu)化——DSP需求優(yōu)化，測算產品效益最大化為原則。幫助企業(yè)完成產品盈利經營分析，進行銷售計劃管理，并跟蹤市場產品銷售狀況。一、成果產生背景2014年疆內鋼鐵行業(yè)競爭激烈，市場環(huán)境變化，鋼鐵“微利時代”已經形成，為確保公司目標的實現，促使銷售部轉變觀念、創(chuàng)新營銷、決戰(zhàn)2014，銷售部圍繞著夯實營銷基礎工作開展產品流向優(yōu)化分析模型建設工作。集團審計部建議八鋼公司建立產品流向優(yōu)化盈利經營分析模型，通過開展產品盈利分析模型，收集和產品有關的銷售量、制造成本、銷售單價、運費等基礎數據，上月盈利分析及次月產品流向規(guī)劃，優(yōu)化產品流向結構。傳統經營模式是以產品為競爭基礎的，隨著市場的變化和競爭的加劇，企業(yè)將關注的重點由產銷率轉向注重產品盈利流向分析。產品流向優(yōu)化盈利分析模型兩部分：數據歸納：在上個月實際發(fā)生的數據進行整理、歸納，在進行初步分析、歸納、整理。項目負責人收集和保存好原始記錄和資料。規(guī)劃求解法：整理各產品的（制造成本、銷售單價、物流運費）數據，輸入公式按照產品利潤最大化原則，分配各產品資源流向工作。二、成果的基本內涵產品盈利預測模型是指：基于EXCEL將公司生產經營成本和產品結構的各種假定通過各種關系轉化對公司的產品結構調整和銷售流向報表預測的一種方法。基于產品盈利預測模型，通過計算公式下月的產品流向，并利用各種產品估值模型確定公司預算值。產品盈利預測模型中收集和產品有關的鋼材銷售、制造成本、銷售單價、運費、等基本的信息，幫助企業(yè)完成產品盈利經營分析，確定主要市場，進行銷售計劃管理，并跟蹤市場產品銷售狀況。2.1產品流向優(yōu)化盈利分析模型建立的目的通過開展產品流向優(yōu)化盈利分析模型，收集和產品有關的銷售量、制造成本、銷售單價、運費等基礎數據，上月盈利分析及次月產品流向調整，優(yōu)化產品流向結構。（1）按照效益優(yōu)先、利潤最大化原則優(yōu)化八鋼產品流向結構；（2）制定產品盈利模型建立x要素，形成產品流向優(yōu)化固化模型；（3）預測下月產品流向，為八鋼產品結構調整和銷售流向提供有效支撐。企業(yè)的任務不僅是向客戶提供產品，還需要產品盈利分析經營模式，在實施產品盈利經營管理過程效果不僅能對公司下月計劃有限制定，而且更能促進企業(yè)利潤最大化原則體現。三、成果的具體實踐和主要做法3.1產品流向優(yōu)化盈利分析模型流程圖3.2模型的具體實踐及主要做法精細銷售管理先從銷售計劃的制定與管理開始，如果計劃中的銷售目標與實際差異巨大，將直接影響到企業(yè)的生產和庫存。因此必須盡量準確地確定每個月的銷售目標。計劃制定只是管理的開始，市場往往會以企業(yè)意想不到的變化來考驗企業(yè)的管理能力。如何調整資源流向計劃的速度會成為企業(yè)成功的核心競爭能力。要把這種能力轉化成企業(yè)的知識管理，把營銷人員的經驗積累下來，降低企業(yè)運營的成本和風險。3.2.1模型建立條件為解決區(qū)外資源如何流向，最大滿足疆內銷售要求，疆外銷售品種結構、流向必須要優(yōu)化；并考慮重點戰(zhàn)略用戶、渠道維護，以產品效益最大化為原則。通過建立規(guī)劃求解模型求出區(qū)外產品流向效益最大值。規(guī)劃求解模型建立條件：區(qū)外產品各品種需求量制造成本、銷售單價、運費等下月預測數據所有數據來源均以財務數據為準（全部是不含稅計算）假設：（1）公司下月制造成本由財務預測提供，不受其他因素影響；（2）區(qū)外運費為財務提供理論值，不受其他因素影響；3.2.2基本Excel的SUMPROOUCT函數模型的規(guī)劃求解（1）選取2014年4月數據模型分析求解1）為了預測下月區(qū)外不同品種的銷售收益，首先將公司財務數據收集到Excel統計下來并計算各區(qū)域品種銷售收益情況，通過作圖發(fā)現各品種的銷售收益呈現一定周期性關系，所以選用SUMPROOUCT函數，建立規(guī)劃求解模型。SUMPROOUCT函數基本功能：函數SUMPRODUCT的功能返回相應的區(qū)域或數組乘積的和。基本格式SUMPRODUCT（數據1，數據2，……，數據30）示例數據表A列B列C列D列E列數據1數據2數據3數據4數據區(qū)域計算要求：計算A、B、C三列對應數據乘積的和——取最大值公式：=SUMPRODUCT(A2:A6,B2:B6,C2:C6)計算方式：=A2*B2*C2+A3*B3*C3+A4*B4*C4+A5*B5*C5+A6*B6*C6即三個區(qū)域A2:A6,B2:B6,C2:C6同行數據積的和。返回值可取（最大值、最小值、目標值）。2）銷售收益=銷售毛利×品種噸位，據此建立了SUMPROOUCT的目標函數。同時依據區(qū)外各區(qū)域的要求，可以得出對目標函數的約束條件可分為：各產品的銷售單價、制造成本、運費的約束各產品在各區(qū)域的最大需求量各產品的銷售毛利在各區(qū)域需求的特定比例約束流向區(qū)外的總資源量的約束然后根據這4種約束條件可得出各個約束式，因此，已知目標函數與約束條件，再通過SUMPROOUCT函數規(guī)劃求解求出最優(yōu)解，最終得出區(qū)外產品銷售的最大收益。（2）求解過程1)銷售價格單位：元/噸品種西北公司寶鋼公司西安分公司寶鋼西部公司寶鋼華中公司建材2737000優(yōu)鋼3250003498熱軋2794340532803094冷軋3462367503419酸洗卷0337633763291中厚板30253045324830512）制造成本單位：元/噸品種西北公司寶鋼公司西安分公司寶鋼西部公司寶鋼華中公司建材2801280128012801優(yōu)鋼3124312431243124熱軋2786278627862786冷軋3690369036903690酸洗卷3083308330833083中厚板29042904290429043）銷售運費單位：元/噸品種西北公司寶鋼公司西安分公司寶鋼西部公司寶鋼華中公司建材285475545313優(yōu)鋼285475545313熱軋285475545313冷軋285475545313酸洗卷285475545313中厚板2854755453134）“銷售毛利”=銷售單價-制造成本-運費單位：元/噸品種西北公司寶鋼公司西安分公司寶鋼西部公司寶鋼華中公司建材-349優(yōu)鋼-15961熱軋-277144-51-5冷軋-513-490-584酸洗卷-182-252-105中厚板-164-334-201-166（3）區(qū)外各鋼種約束條件西北銷量6.87＞=6.87西北中厚板銷量1.74＜=1.74寶鋼華中熱軋銷量0.40＜=0.4西安公司中厚板銷量0.19＜=0.5寶鋼西部銷量3.82＞=3.82寶鋼西部建材銷量0.00=0寶鋼華中冷軋銷量0.00=0寶鋼華中銷量0.80＞=0.8寶鋼西部優(yōu)鋼銷量0.00=0寶鋼華中酸洗銷量0.35＜=0.35西安公司4.12＞=4.12寶鋼西部熱軋銷量2.70＞=1.8寶鋼華中中厚板銷量0.05＜=0.05西北建材銷量2.00＜=2寶鋼西部冷軋銷量0.00＜=0.7西安公司建材銷量0.00=0西北優(yōu)鋼銷量1.09＜=1.09寶鋼西部酸洗銷量1.12＞=1.12西安公司優(yōu)鋼銷量2.90＜=2.9西北熱軋銷量2.00＜=2寶鋼西部中厚板銷量0.00＜=0.2西安公司熱軋銷量1.00＜=1西北冷軋銷量0.04＜=0.04寶鋼華中建材銷量0.00=0西安公司冷軋銷量0.00＜=0.3西北酸洗銷量0.00=0寶鋼華中優(yōu)鋼銷量0.00=0西安公司酸洗銷量0.03＜=0.03區(qū)外資源通過約束條件規(guī)劃求解分配資源流向，是區(qū)外品種銷售獲得最大收益。求解得出區(qū)外各區(qū)域流向量單位：萬噸品種區(qū)外合計西北公司寶鋼公司西安分公司寶鋼西部公司寶鋼華中公司合計15.616.873.820.84.12建材2200.000優(yōu)鋼3.991.0900.002.9熱軋6.122.70.401冷軋0.040.0400.000酸洗卷1.501.120.350.03中厚板1.981.7400.050.19（5）規(guī)劃求解演算過程運算結果報告MicrosoftExcel14.0運算結果報告工作表:[2014年銷售分析排產、流向建議模型4.10.xls]4月區(qū)外分大品種報告的建立:2014-11-0518:47:18結果:規(guī)劃求解找到一解，可滿足所有的約束及最優(yōu)狀況。規(guī)劃求解引擎引擎:單純線性規(guī)劃求解時間:.062秒。迭代次數:27子問題:0規(guī)劃求解選項最大時間100秒,迭代100,Precision0.000001最大子問題數目無限制,最大整數解數目無限制,整數允許誤差5%,無整數約束的求解,假設為非負數目標單元格(最大值)單元格名稱初值終值$A$55西北酸洗銷量-1526-1526可變單元格單元格名稱初值終值整數$C$61建材西北公司22約束$D$61建材寶鋼西部公司00約束$E$61建材寶鋼華中公司0.000.00約束$F$61建材西安分公司00約束$C$62優(yōu)鋼西北公司1.091.09約束$D$62優(yōu)鋼寶鋼西部公司00約束$E$62優(yōu)鋼寶鋼華中公司0.000.00約束$F$62優(yōu)鋼西安分公司2.92.9約束$C$63熱軋西北公司22約束$D$63熱軋寶鋼西部公司2.72.7約束$E$63熱軋寶鋼華中公司0.400.40約束$F$63熱軋西安分公司11約束$C$64冷軋西北公司0.040.04約束$D$64冷軋寶鋼西部公司00約束$E$64冷軋寶鋼華中公司0.000.00約束$F$64冷軋西安分公司00約束$C$65酸洗卷西北公司00約束$D$65酸洗卷寶鋼西部公司1.121.12約束$E$65酸洗卷寶鋼華中公司0.350.35約束$F$65酸洗卷西安分公司0.030.03約束$C$66中厚板西北公司1.741.74約束$D$66中厚板寶鋼西部公司00約束$E$66中厚板寶鋼華中公司0.050.05約束$F$66中厚板西安分公司0.190.19約束約束單元格名稱單元格值公式狀態(tài)型數值$N$44西安公司中厚板銷量西安分公司0.19$N$44<=$P$44未到限制值0.31$J$50西安公司熱軋銷量西北公司1.00$J$50<=$L$50到達限制值0$J$51西安公司冷軋銷量西北公司0.00$J$51<=$L$51未到限制值0.3$J$52西安公司酸洗銷量西北公司0.03$J$52<=$L$52到達限制值0$B$49西北優(yōu)鋼銷量區(qū)外合計1.09$B$49<=$D$49到達限制值0$J$49西安公司優(yōu)鋼銷量西北公司2.90$J$49<=$L$49到達限制值0$B$51西北冷軋銷量區(qū)外合計0.04$B$51<=$D$51到達限制值0$B$48西北建材銷量區(qū)外合計2.00$B$48<=$D$48到達限制值0$F$48寶鋼西部冷軋銷量西安分公司0.00$F$48<=$H$48未到限制值0.7$F$50寶鋼西部中厚板銷量西安分公司0.00$F$50<=$H$50未到限制值0.2$B$50西北熱軋銷量區(qū)外合計2.00$B$50<=$D$50到達限制值0$J$48西安公司建材銷量西北公司0.00$J$48=$L$48到達限制值0$J$46寶鋼華中酸洗銷量西北公司0.35$J$46<=$L$46到達限制值0$J$47寶鋼華中中厚板銷量西北公司0.05$J$47<=$L$47到達限制值0$J$45寶鋼華中冷軋銷量西北公司0.00$J$45=$L$45到達限制值0$J$44寶鋼華中熱軋銷量西北公司0.40$J$44<=$L$44到達限制值0$F$51寶鋼華中建材銷量西安分公司0.00$F$51=$H$51到達限制值0$F$52寶鋼華中優(yōu)鋼銷量西安分公司0.00$F$52=$H$52到達限制值0$B$60合計區(qū)外合計15.61$B$60<=15.61到達限制值0$B$52西北酸洗銷量區(qū)外合計0.00$B$52=$D$52到達限制值0$F$44西北中厚板銷量西安分公司1.74$F$44<=$H$44到達限制值0$F$45寶鋼西部建材銷量西安分公司0.00$F$45=$H$45到達限制值0$F$46寶鋼西部優(yōu)鋼銷量西安分公司0.00$F$46=$H$46到達限制值0$F$47寶鋼西部熱軋銷量西安分公司2.70$F$47>=$H$47未到限制值0.90$B$44西北銷量區(qū)外合計6.87$B$44>=$D$44到達限制值0.00$B$45寶鋼西部銷量區(qū)外合計3.82$B$45>=$D$45到達限制值0.00$B$46寶鋼華中銷量區(qū)外合計0.80$B$46>=$D$46到達限制值0.00$B$47西安公司區(qū)外合計4.12$B$47>=$D$47到達限制值0.00$F$49寶鋼西部酸洗銷量西安分公司1.12$F$49>=$H$49到達限制值0.00敏感性報告MicrosoftExcel14.0敏感性報告工作表:[2014年銷售分析排產、流向建議模型4.10.xls]4月區(qū)外分大品種報告的建立:2014-11-0518:47:19可變單元格終遞減目標式允許的允許的單元格名稱值成本系數增量減量$C$61建材西北公司20-348.82051281E+30164.6410256$D$61建材寶鋼西部公司0001E+301E+30$E$61建材寶鋼華中公司0001E+301E+30$F$61建材西安分公司0001E+301E+30$C$62優(yōu)鋼西北公司1.090-159.01960321E+30354.4419352$D$62優(yōu)鋼寶鋼西部公司0001E+301E+30$E$62優(yōu)鋼寶鋼華中公司0001E+301E+30$F$62優(yōu)鋼西安分公司2.9061.407910171E+30227.1258589$C$63熱軋西北公司20-276.81196581E+30236.6495726$D$63熱軋寶鋼西部公司2.70143.55840461E+30143.5584046$E$63熱軋寶鋼華中公司0.40-51.085470091E+30200.8461538$F$63熱軋西安分公司10-4.9829059831E+30160.7350427$C$64冷軋西北公司0.040-513.4615385164.64102561E+30$D$64冷軋寶鋼西部公司0-633.3447293-489.7863248633.34472931E+30$E$64冷軋寶鋼華中公司0001E+301E+30$F$64冷軋西安分公司0-418.4786325-584.1965812418.47863251E+30$C$65酸洗卷西北公司0001E+301E+30$D$65酸洗卷寶鋼西部公司1.120-181.9316239325.49002851E+30$E$65酸洗卷寶鋼華中公司0.350-251.931623950.794871791E+30$F$65酸洗卷西安分公司0.030-105.40170941E+3060.31623932$C$66中厚板西北公司1.740-163.65369881E+30349.8078397$D$66中厚板寶鋼西部公司0-477.6866097-334.1282051477.68660971E+30$E$66中厚板寶鋼華中公司0.050-201.13675211E+3050.79487179$F$66中厚板西安分公司0.190-165.717948760.31623932418.4786325約束終陰影約束允許的允許的單元格名稱值價格限制值增量減量$N$44西安公司中厚板銷量西安分公司0.1900.51E+300.31$J$50西安公司熱軋銷量西北公司1160.735042710.190.31$J$51西安公司冷軋銷量西北公司000.31E+300.3$J$52西安公司酸洗銷量西北公司0.0360.316239320.030.190.03$B$49西北優(yōu)鋼銷量區(qū)外合計1.09354.44193521.090.040$J$49西安公司優(yōu)鋼銷量西北公司2.9227.12585892.90.190.31$B$51西北冷軋銷量區(qū)外合計0.0400.041E+30-3.46945E-17$B$48西北建材銷量區(qū)外合計2164.641025620.040$F$48寶鋼西部冷軋銷量西安分公司000.71E+300.7$F$50寶鋼西部中厚板銷量西安分公司000.21E+300.2$B$50西北熱軋銷量區(qū)外合計2236.649572620.040$J$48西安公司建材銷量西北公司0165.717948700.190$J$46寶鋼華中酸洗銷量西北公司0.3500.351E+300$J$47寶鋼華中中厚板銷量西北公司0.0550.794871790.050.350$J$45寶鋼華中冷軋銷量西北公司0251.931623900.350$J$44寶鋼華中熱軋銷量西北公司0.4200.84615380.40.350$F$51寶鋼華中建材銷量西安分公司0251.931623900.350$F$52寶鋼華中優(yōu)鋼銷量西安分公司0251.931623900.350$B$60合計區(qū)外合計15.61143.558404615.611E+300$B$52西北酸洗銷量區(qū)外合計0513.461538500.040$F$44西北中厚板銷量西安分公司1.74349.80783971.740.040$F$45寶鋼西部建材銷量西安分公司0-143.558404600.90$F$46寶鋼西部優(yōu)鋼銷量西安分公司0-143.558404600.90$F$47寶鋼西部熱軋銷量西安分公司2.701.80.91E+30$B$44西北銷量區(qū)外合計6.87-657.0199436.8700.04$B$45寶鋼西部銷量區(qū)外合計3.8203.82-2.05391E-151E+30$B$46寶鋼華中銷量區(qū)外合計0.8-395.49002850.800.35$B$47西安公司區(qū)外合計4.12-309.27635334.1200.19$F$49寶鋼西部酸洗銷量西安分公司1.12-325.49002851.120.91.12四、實施效果4.1典型案例分析建材結構優(yōu)化——案例分析2014年鋼材銷售由產品銷售向產品經營轉變，鋼材品種經營從銷售單價、制造成本、單位運費3個方面為基礎按照產品盈利模型優(yōu)化產品結構。上半年建材結構調整通過2014年上半年度各主要產品盈利對比，建材產品中，線材盈利水平低于螺紋鋼，加大螺紋鋼產量及銷量，特別是小螺紋、大螺紋價格高150元/噸，上半年大小規(guī)格螺紋鋼生產比例由計劃33%調整到41%。2014年計劃大小規(guī)格螺紋鋼生產比例較2013年42%調整到44%。名稱2012年所占螺紋鋼比例2013年所占螺紋鋼比例2014年總量所占螺紋鋼比例上半年實際螺紋鋼224256322小螺紋6529%8132%10633%34.3%大螺紋198%2510%4011%10.8%大小螺紋合計8437%10742%14644%45.1%能夠合理的降低八鋼庫存，減少存貨的倉儲及物流成本。改變以往區(qū)域庫房布庫銷售的傳統模式，提高資源的周轉率，優(yōu)化流向。淡季低庫存運行，旺季結合產品結構、盈利水平控制適當的庫存。在保證建材產品整體低庫存的情況下，提高各區(qū)域點庫庫存周轉率，減少調撥布庫，已達到資源流向的均衡性。五、主要創(chuàng)新成效及體會5.1經濟效益產品流向優(yōu)化盈利模型應用于公司各銷售部門的產品流向資源優(yōu)化進一步提高銷售額、增加產品盈利能力，進而提高銷售收入。降低銷售成本。對產品具體甄別和區(qū)域分類進行分析，使產品推銷和銷售計劃的制定與執(zhí)行避免了盲目性資源配置。效益：7193.6萬元（依據見計算公式）銷售毛利=“銷售單價”-“銷售成本”-“運費”產品盈利額=“銷售毛利”×噸位公式：效益=∑產品銷售毛利（使用該模型）×噸位（優(yōu)化流向銷售量）成果經濟效益情況表單位：萬元經濟效益銷售毛利=“銷售單價”-“銷售成本”-“運費”月產品盈利額=“銷售毛利”×噸位公式：效益=∑產品銷售毛利（使用該管理系統）×噸位（優(yōu)化流向銷售量）月份2月3月4月5月6月7月8月9月10月小計預計銷售收益-2906.1-592.1-1648.7252.7-59.1-3618.8213.2-403.1-3923.5-12685.5流向優(yōu)化實際銷售收益-2401.8-152.6665.9423.3-46.9-1311.9941.1265.2-3874.2-5491.9流向優(yōu)化差收益額（實際收益-預計收益）504.3439.52314.6170.612.22306.9727.9668.349.37193.620