Ｅｘｃｅｌ ＶＢＡ在自行車制造行業鋼管采購優化決策中的應用
2008-01-01 00:00:00李林娟李躍宇
現代商貿工業 2008年5期
摘 要：在自行車制造行業鋼管采購決策中結合鋼管裁減工藝，應用Excel VBA尋找鋼管采購的最優長度組合，達到減少鋼管裁減浪費的目的。以某自行車企業為實例闡述Excel VBA的應用過程，以及應用前后該企業鋼管裁減浪費情況對比來證明Excel VBA在采購優化決策中應用的價值。
關鍵詞：Excel VBA；采購；鋼管長度；浪費金額
中圖分類號：TH文獻標識碼：A文章編號：1672-3198（2008）05-0355-02
1 引言
企業進行生產經營活動，就不可避免地要采購原材料。采購作為物流的第一個環節，其成本高低對于整個生產的總成本有著舉足輕重的作用。采購成本主要包括三部分：材料成本、采購管理成本、存儲成本。材料成本是采購成本中最基礎的，其采購數量的大小將直接影響到存儲成本。如何確定最佳采購數量一直是學術界研究的熱點問題。現有確定采購量的方法都是從庫存控制角度考慮采購數量，沒有結合實際生產工藝，然而現實中很多企業在使用這些方法時都必須考慮其加工工藝。例如自行車制造行業，由于企業是按一定規格長度采購鋼管的，而多個自行車零部件要共用這些鋼管，若不考慮零部件的尺寸和加工工藝就會造成鋼管浪費。
鋼管是自行車制造行業必不可少的物料，其采購是按長度計算。鋼管浪費現象主要是因為采購的鋼管的長度不合理引起的。理論上來講，如果按照各種車型量身采購各種型號鋼管，則可以完全消除鋼管的浪費，不過這樣做將會引起管理費用大量增加。因此對于該行業，在改進過程中，如何在不增加鋼管的采購種類數量，僅通過改變鋼管采購的長度來減少鋼管浪費的研究還很少，也沒有一套適用的工具。對這方面的研究之所以較少，是因為自行車各個零部件加工工藝有差異，對多品種企業其不同產品之間加工工藝也有所不同，這些都給研究帶來了困難。
目前中國大多數自行車企業根據經驗確定鋼管采購長度，由于沒有結合加工工藝，加工過程中鋼管裁減的浪費是巨大的。為了減少這種物料浪費，除了改善加工工藝還可以在采購數量中增加加工工藝的考慮來減少浪費。本文就應用Excel VBA研究自行車制造行業鋼管采購，應用科學的方法替代經驗管理的方式減少鋼管裁減所造成的浪費。
2 問題描述
在自行車企業鋼管裁減現場，可以非常直觀地看出鋼管在使用過程中存在著較嚴重的浪費。鋼管的裁減余料只能作為廢品回收處理，在MRP系統中，對鋼管的管理相當薄弱，鋼管的單位為根，即使只截取很短的一段，在系統中也是認為使用一根，而現場又是按照長度來進行管理的。這樣造成的后果就是鋼管的各種管理缺陷被掩蓋。本文主要研究在加工工藝不變的前提下，如何通過科學的計算來確定鋼管的最優采購長度，以達到減少裁減余料浪費的目的。各符號的含義及計算公式如下所示：
計算某種鋼管部件在裁減時每根鋼管浪費的長度Wi = Li - li×Ni，其中：Ni = ［Li/li］。Li表示第i種車型現用鋼管型號長度；li表示第i種車型鋼管現截長度；Ni表示第i種車型每根鋼管可截數量。［…］ 在此處的含義為向下取整。例如：［3.8］ = 3。
計算該種鋼管部件在一定時期內浪費的總長度TWi = Wi×Si×ni/Ni。Si表示第i種車型的銷售量；ni 為一輛自行車使用該類部件的數量。對于ni這一常數，因為一輛自行車需要使用2根后上叉和2根后下叉。因此在計算該種管材時，需要數量應為車輛銷售量的2倍。
統計橫截面類型相同的鋼管浪費的總長度TTW = ∑TWi。公式的含義即統計所有使用該類型鋼管的部件，并將所有部件在裁減過程中浪費的總長度相加，即可得到該類型鋼管的浪費的總長度。
統計橫截面類型相同的鋼管浪費的總金額V=TTW×p。p表示所用鋼管單位長度的價格。
本文的目的就是要尋找在加工工藝不變即l不變的情況下使得鋼管浪費總金額V最小時的L值。
3 Excel VBA
Excel中使用的編程語言叫做Visual Basic for Applications，簡稱為VBA。其基本形態是Excel的宏語言通過特定語法（VBA）來控制Excel內置對象，來完成一些自動化過程，而這些過程通常都用來完成一些重復性的工作。Excel中的宏可以稱為Excel應用程序編程的起點，它可以展示基本的Excel VBA程序工作的步驟。
應用Excel VBA的目的是在給定條件下尋求總浪費金額最小值所對應的鋼管長度，這樣得到的鋼管長度即是我們鋼管采購的最優長度。尋優過程的原理：由于采購時精度可以達到10mm，所以適合使用計算機窮舉所有采購組合。根據采購時最短長度要求和集裝箱長度限制，讓計算機在最短長度和最長長度之間的數據中遍歷各種組合，以獲得浪費最小的組合，即Min_i，Min_j。尋求Min_i，Min_j的計算步驟如下：
（1）鋼管總浪費長度Waste，因為是要尋求總浪費長度最小值，所以Waste的初始值應當是無窮大的。可以根據公司具體情況設定Waste的初始值。
（2）鋼管長度i，j。鋼管長度種類越多管理費用越高，公司為節約管理費用一般采購兩種長度類型即可。初始化i值，j=i+10，鋼管長度取i，j時的總浪費長度TTW與初始化Waste相比較。Waste
（3）應用嵌套循環：i值不變，j以10mm增加繼續步驟2直到j值達到上限跳出該循環。
（4）i值增加10mm，j=i+10，繼續步驟3。
（5）i值達到上限程序結束，所得到的Min_i，Min_j就是要尋找的鋼管長度最優組合。
4 實例說明
本文以某自行車廠的鋼管采購長度優化為例來闡述Excel VBA在自行車制造行業鋼管采購優化決策中的應用過程。自行車的鋼管主要分為上管、下管、中管、上叉、下叉。該廠現采購的主要的鋼管型號如表1所示：
由于在鋼管的裁減過程中，并不區分鋼管的用途，只確認鋼管的型號和裁減長度。因此將鋼管的數據分類進行調整，如表2所示。
從表中可以看出當采購長度為4660與6070時鋼管的浪費達到最小。改善前浪費長度為293824，浪費金額為758.94739元，改善后浪費長度為36726.13元，浪費金額為94.86元。改善后浪費金額僅為原有的12.50%。浪費程度減少了87.50%。可見改善效果是相當明顯的。
改善后基本上都可以減少70%-95%的浪費，至少可以減少50%以上的浪費。浪費金額也從改善前的15萬多減少到現有的4萬多。改善總幅度達到73.06%。改善效果是比較令人滿意的。
對鋼管的管理優化過程中，由于改進過程所用方法簡單，易于標準化。管理鋼管的工作人員有章可詢，不需要工作人員根據經驗自行計算，既避免了經驗和計算過程中可能出現的各種錯誤，又提高了工作效率。并且即使遭遇人員流失，新來的員工能對鋼管的管理迅速上手。
5 結論
電子表格并不能提供所有問題的解決方案，使用Excel VBA程序來處理自動重復計算任務是很有效的。根據此特點本文就嘗試將Excel VBA應用在自行車制造行業鋼管采購優化決策中，并用實際的案例說明了其可行性以及應用的價值。本文提出的方法對于自行車制造行業具有普適性，所有自行車企業均可應用此方法決策鋼管最優采購長度。
參考文獻
［1］郝淵曉，王茜草等.現代物流采購管理［M］.中山大學出版社，2003，（5）.
［2］馬士華等.供應鏈管理［M］.機械工業出版社，2000.
［3］杰克遜，斯湯頓著.基于Excel和VBA的高級金融建模［M］.中國人民大學出版社，2006.
［4］黃睿，馬然.Excel VBA應用程序專業設計實用指南［M］.電子工業出版社，2006.
［5］閆宏印等.VB程序設計［M］.電子工業出版社，2001.
注：本文中所涉及到的圖表、注解、公式等內容請以PDF格式閱讀原文。
