BOM表excel優化進貨成本。這張表格最大的優勢在於，只需要修改一處數據，其他的數據也都可以自動完成最新計算，產品數量越多，此表格能發揮的作用就越大。上一篇我們從成本的角度切入，尋找通過產品物料成本結構的分析，了解自己的產品策略：哪些是高成本低售價的產品，這些產品是否屬於戰略性產品，用來導流吸引更多消費者認識品牌；又有哪些是低成本高售價的產品，如果銷量不佳是否可以考慮使用促銷手段，如果銷量很好供不應求是否可以提升該產品產能，等等。諸如此類的許多分析都可以通過了解產品物料成本結構來進行分析，然後將分析結果通過“翻譯”，白話成生產者能接受的語言。用數字翻譯成別人聽的懂的語言，學會講別人的話是解決外行人與內行人溝通的一個非常有效的方法。

大家好，我是Alex，畢業於英國華威大學商學碩士，曾經從事3年建築設計工作。目前在烘焙產業從事運營相關工作，在這裏我會分享工作和生活中最有用的思維邏輯以及一些好用的工具。這篇開始我會分享我是如何運用幾乎免費的工具，為公司的運營和決策提供實用的數據分析支撐。

本篇我會繼續接著上一篇，通過強化BOM表成本自動計算，同時連接其他表格的數據，能使BOM表在展示所有產品的標準配方并自動計算物料成本外，還能自動計算各產品對應該月生產總量的原物料分別使用了多少，也就是自動計算生產進貨量，并且自動計算出該月爲了生產這些產品總共的總進貨金額，讓它成為好用BOM表神器。

準備工作：需要引用的外部表格數據

我們具體需要哪些外部數據來實現聯動？第一個需要使用到的，是我們在第一篇中提到的生產產能表，將每月各產品的總生產量與該產品物料成本結合；第二個需要使用到的，是我們在上一篇中提到的物料進貨成本表，通過引用以上兩個表格工具中的數據，讓我們的物料成本表格能自動計算出當月某物料的總使用量，以及該物料使用量在當月的總進貨成本。這個工具就相當好用了！通過附加上這兩個外部表格數據，我們使用的這個表格工具就可以用來核對原物料的進貨數據，針對進貨數據比對，可以合理的分析出師傅的總產能數據是否真實，甚至與可以用來輔助分析師傅們的生產耗損（如果師傅本來就有將生產耗損記錄下來，就可以用來跟他們提供的數據作比對），甚至於可以通過這個工具預判原物料的預計使用量，來輔助原物料的叫貨。接下來我會先說明如何完善這個工具，然後說明我自己是如何使用這個表格工具來實現上述的分析，并用來與師傅們溝通。

1、當月各產品分別的生產總量

首先，我們先來引用第一個外部表格中的數據——當月各產品生產產能，也就是D欄的生產數量。以菠蘿麵包為例，在D6單元格我們只需要使用一個VLOOKUP函數，查詢并引用B6單元格（也就是菠蘿麵包）的產品品名在當月產量表中總產量，回填到D6單元格即可。我在這里使用的公式如下：“=VLOOKUP(@B:B,’產量表絕對位置地址’!$B:$C,2,FALSE)”，因為VLOOKUP是excel中非常常用且好用的函數，所以建議大家一定要用習慣。之前我有提到這裡我會更針對表格設計和使用邏輯去解釋，而且外面關於excel函數的教學多如牛毛，那麼這裡針對函數的部分就不做過多說明了，相信大家都能很快的學會甚至超過我的所知。

2、所有原物料的進貨單價與生產進貨量計算

接下來，我們再來引用第二個外部表格中的數據——原物料的進貨成本。在引用之前我們先要完成一個函數的計算，在本例中，第8列我們需要先計算原物料的總生產使用量，以G8為例，我們需要知道在當月中，所有產品的生產理想狀態下（無耗損）總共需要使用多少公克的高筋麵粉。這裡我們只需要使用一個簡單的EXCEL函數SUMPRODUCT就可以實現，我的公式如下：“=SUMPRODUCT($D$5:$D$7,G$5:G$7)”，這個公式就等同於“=D5*G5+D6*G6+D7*G7”，這里看起來SUMPRODUCT函數好像并沒有節約到多少時間，可是如果今天有100個品項，就能想象如果不使用這個公式，要計算這個數值的公式該有多長！換言之，SUMPRODUCT跟VLOOKUP一樣，也是EXCEL中非常常用的函數，非常重要，請大家不會的話抓緊學起來。

在完成第8列的計算之後，我們要開始第9列的引用——計算當月使用原物料的總進貨成本，計算的話就是用第8列的“總使用物料重量x該物料的進貨單價”。因為我們物料進貨價格在上一篇中有提到，所以這邊我們同樣先寫出G9單元格的公式用來對照：“=G8*VLOOKUP(G$4,’產量表絕對位置地址’!$A:$C,3,FALSE)”,這裡的邏輯很簡單，大家對照一看就明白。最後，我們需要加總全部物料的進貨總金額，在本例中是在P9單元格，使用SUM函數加總“=SUM(G9:O9)”即可。

如何優化原物料進貨量，以精簡進貨成本

到這裡為止，所有的引用和計算的部分都完成了！現在我們再來分析，可以如何使用這些自動計算出來的數據，去跟專業的生產人員溝通。

首先，我們知道了當月各種生產原物料對應各種產品生產總量的總重量，比如高筋麵粉在當月總生產量下的使用量是53KG，那麼當月總麵粉進貨量就應該落在這個數值的附近，比如一包麵粉是25KG，那麼我們高筋麵粉當月進貨就應該不應該超過3包（2*25<53<3*25），加入我們在月底跟廠商結算時發現進貨量大於3包，就可以將當月月底高筋麵粉庫存盤點與上月數據以及當月進貨量做比對，計算出當月實際使用的高筋麵粉數量，假設這樣計算出來的當月使用量遠超過53KG，就可以詢問師傅是不是有生產耗損，原因為何，是否有記錄下來等等後續的管理措施。

如果計算出來的原物料使用量與53KG接近，但是當月進貨量卻遠大於3包，比如當月進貨10包，就可以詢問師傅為何進貨量這麼大，是否存在業務塞貨的情況等等。對於這部分，涉及到財務思維里的“快收慢付”，將來有機會也會介紹如何使用財務思維幫我們做精實管理。其次，我們可以利用P9單元格自動計算出來的當月總生產物料使用成本，與當月生產物料總進貨成本做比對，看總生產物料使用成本是否與叫貨成本匹配，浮動範圍是否合理，通常會比對當月生產物料庫存盤點與上月盤點數據來計算當月實際使用物料成本，這樣會取得更精準的匹配。庫存盤點比對計算的部分，將來也會在之後的文章中說明。

BOM表自動計算生產進貨量與總進貨金額後言

有了這張表，就能完成以下諸多數據的統一運用：全產品的標準配方表（統一原物料使用單位）、各產品分別的原物料成本價格、分別對應各產品的售價的物料成本佔比、對應當月各產品生產總量的原物料分別使用量、以及當月各原物料的生產進貨成本。這張表格最大的優勢在於，衹需要修改一處數據，其他的數據也都可以自動完成最新計算，產品數量越多，此表格能發揮的作用就越大。比如說，今天的原物料廠商因爲進口成本增加而調漲價格，衹需要修改原物料進貨表中的進貨單價，此表格就能自動將所有使用到該原物料的產品成本重新計算，讓表格數據保持最新的狀態。除此之外，如果下個月預計要增加產量，這張BOM表還能通過修改產品總產量來預估下個月各原物料需要的最低進貨量，針對叫貨周期長的物料，能提前做好叫貨的準備降低臨時可能缺貨的風險。當然這張表還有非常多的用途，衹要梳理自己的邏輯，以終爲始，還會發現更多BOM表能幫助解決的問題。

這樣，這個超級好用的BOM表範例就設定完成了，它能根據外部數據的變動，自動幫助大家做最新的計算，不論是產品的物料成本，還是原物料使用狀況，亦或是當月總進貨成本，一張表格通通搞定！有了這張表的數據，就能準確、有理有據的與專業人員溝通，幫助在生產管理上提升效率，朝實現精實生產管理的方向努力。如果大家有任何想討論的地方，也歡迎隨時留言給我，有看到會盡快回覆喔。

數據在互聯網和5G萬物互聯網的蓬勃發展下，已經儼然成爲世界新的名片。在善用數據爲人們生活帶來的便利下，學會收集數據、分析數據、并將思辨客觀數據帶來的結論加以利用，將會是人類繼工業大革命、科技大革命之後的又一個風口。有效使用數據分析，不僅能減少不同個人直觀感受上的差異，更能深挖現象直抵問題根本。本篇作爲開題，衹作爲抛磚引玉，工具衹是手段，能回答自己爲什麽要做才能避免形而上學。希望能引發大家在自己工作領域的思考，數據分析真正能有所幫助的衹會是自己的努力和思維的轉變。下方會放上所有數據分析系列文章連接，歡迎大家根據需要繼續閲讀我的數據分析心得報告，謝謝！Alex

（內容儘供交流，請勿商用）

BOM表Excel常見疑問整理