通過自動計算物料成本，找出物料成本佔比超高，但銷售狀況不佳的產品，優化產品品項結構

本篇會跟大家介紹一個超級無敵好用的表格，也是我花了半年時間整理歸納出來的BOM表，因爲内容龐大，這里先介紹如何用這張表自動計算產品成本。相信我，我可以，你也一定可以！上一篇分享的心得是關於如何通過簡單的Excel表單建立與師傅有效溝通的第一個指標：人均單日產能。有了人均產能的指標後，我們就能知道師傅在生產工作的內容安排上是不是合理，是否還有提升的空間，比如：生產人數相同，今天產能比上週同期產能少了一半是為什麼？

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

很多人會問：外行人怎麼管理內行人？師傅寧願聽從師傅的建議，而不會願意聽一個外行人在他們的領域“高談闊論”，這幾乎存在於所有行業的現象。可是很多時候作為管理者或者輔助決策者，一定會有需要檢驗師傅們績效的需求，那外行人要如何取得師傅們的信任，讓他們願意相信你不是故意來找茬？從數據分析的角度來說，或許就是一個很好的用武之地——提供真實的數據，建立我們與專業人士之間的相互信任感，因為我們不是無病呻吟來找茬，而是真的有溝通的需要。作為外行，為了與專業人員更好的溝通，我認為有以下2點需要注意：第一是尊重專業，不懂的地方不要裝懂，多聽取專業人員的意見；其次是站在中立的角度，客觀真誠的面對數據，好的地方要讚許，同時需要改進的地方也要提出。記住一個原則，真實的面對數據，拋棄所謂的面子或是其他不必要的考量。

今天我們來聊聊另外一個繞不開的話題——成本。只要有實體產品的銷售，就一定會需要知道產品的成本，這樣在產品定價的時候才能作為一個指標性的因素加入考量。插入一段題外話，在我心中，一個不能通過銷售產品獲取利潤的公司不是一個合格的公司，因為如果企業不能“真正的”盈利，那作為企業就不能“真正的”照顧好員工，一旦員工得不到應有的“照顧”，就沒辦法拋掉後顧之憂全力的參與到公司的進程中，因此企業就會陷入一個不良的循環中，甚至於看到因虧損導致降薪、裁員、改用低成本原物料或添加劑等我們不願看到的結果。因此，企業的產品需要獲利！這個沒什麼好隱晦的，如果一個企業告訴你說他們賣的產品不賺錢，那作為消費者的你反而應該三思，因為如果你能相信你面前這個人告訴你的，那這個產品的生產企業一定有你不為人知的另一面。

產品成本包含了超多面向，諸如物料成本、人事成本、生產場所的固定租金成本、能源成本等等，因為我們當前的目的是用數據來與專業生產人員溝通，因此我們先僅以產品物料成本來展開討論。以我自身所從事的行業舉例，烘焙行業的物料成本就會涉及到像麵粉、雞蛋、奶油等，以這些原物料的使用量作為一個烘焙產品的物料成本組成結構，在財務三大報表的損益表中，也是所謂的毛利率對應需要考量的成本。

要提物料成本結構，就可以同時了解一個概念——BOM (Bill of Material)表，也就是一個產品使用的原物料展開，用來說明生產一個單位數量的產品，需要用到多少量的某種原物料。因此我們今天來討論一下如何利用BOM表的概念來設計一個Excel表，如果原物料的價格產生浮動，它也能自動幫助我們計算出最新的產品原物料成本！聽起來雖然很棒，但是會不會很複雜？No！一點都不會，只要跟著我的思路，就會變得超！簡！單！而且我們需要的不僅僅是一個靜態獨立的表格，我們需要讓它與我們其他表格共同作業，讓外行人也能輕鬆與內行人溝通，為管理決策提供更多關鍵數據分析輔助。請瞭解，我們需要的不是一個超美觀的成本計算表格，而是一個能用Excel搭建的數據系統中的其中一環，如果需要的只是一個美美的物料成本計算表格，也歡迎私訊我，我會針對如何製作一張美美的成本計算表寫另外一篇心得，給大家參考。

那我們開始了喔！以菠蘿麵包為例，首先我們快速梳理結構：A欄一樣是產品類別，B欄是品名，C欄是單價，D欄我會設置為生產數量（這裡的數據會從上一篇的產能表格中提取，如果還沒看過上一篇，快速前往），E欄我們設定為單個產品重量（這裡的重量是配方中的重量，不是最終烤焙完成成品重量喔，作用我們等一會解釋），F欄設定為配方重量（用來核對配方中的成分重量），從G欄到O欄是產品配方中所用到的全部原物料的名稱。

接下來我們來解釋為什麼要設定E欄——單位產品配方重量。因為在麵包產品中，許多產品會使用同一種麵團，通過添加不同的餡料并改變外形裝飾而成為新的產品。同理，因為我們的菠蘿麵包需要使用的麵團暫且命名為甜麵包麵團，在師傅的配方中，最基本的配方就是麵團的配方，因此我們需要在第5列將師傅給我們的甜麵包麵團配方將各成分重量依次輸入，通過SUM函數在F5單元格加總配方總重量2060公克，例子中F5的公式是“=SUM(G5:O5)”。

因為師傅告訴我們，單個菠蘿麵包麵團的重量在55公克，因此在E6單元格我們輸入“55”，作為參考配方重量。那這55公克的菠蘿麵包麵團中不同的原物料分別占多少公克呢？只需要使用簡單的公式“麵團中A物料重量/麵團總重量*單個菠蘿麵包麵團重量”即可知道55公克中有多少公克A物料，以高筋麵粉為例，在G6單元格的公式即為“=$E6/$F$5*G$5”（關於符號“$”的使用在上一篇中有詳細說明，這裡不作贅述，點我查看上一篇）。同理我們完成第6列中單個菠蘿麵包麵團其他原物料所佔的重量，通過F6單元格中的SUM公式加總，與E6單元格的55公克比較，就能得知是否全部的物料都有正確計算進來，理論上F6要等於E6數值。

接下來是重點本表格重點。首先R欄我們設定為配方總成本價格，S到AA欄複製G到O欄的物料名稱，作為成本配方物料成本索引，最後一欄AB設定為原物料成本佔比。以高筋麵粉為例，我們先講邏輯不講具體公式，S6單元格需要得到單個菠蘿麵包麵團中高筋麵粉使用量的成本價，因此需要用“使用量*該每單位該物料價格”。假設已知所有物料的單位重量價格，就可以完成S欄到AA欄的使用物料成本價格，R欄只需要使用SUM公式就可以計算該配方的總物料成本，同時，AB欄的成本佔比就可以使用R欄的物料總成本/C欄的產品單價，并以“%”格式顯示即可。

如果想讓原物料進貨成本浮動後，本表格中的所有使用該物料的成本都將跟隨著一同改變，則需要單獨做一個物料進貨成本表格，結構可以簡單的使用為A欄——物料名稱（需與配方表中的物料名稱完全相同，方便引用），B欄——物料單位包裝重量（盡量將單位統一為公克），C欄則為該包裝的進貨成本（單價統一為新台幣台幣元）。建立好後，只需要使用Excel中最常用的函數Vlookup即可引用這邊的數據進成本表格，并使得一旦進貨成本改變後，成本會自動重新計算所有使用該物料的產品新物料成本，一勞而永逸！比如S6單元格中的公式就可以寫為“=G6*VLOOKUP(G$4,’物料進貨成本文件路徑’!$A:$C,3,FALSE)/ VLOOKUP(G$4,’物料進貨成本文件路徑’!$A:$C,2,FALSE)”，這邊就不詳細說明VLOOKUP的公式使用了，只需要簡單的在google中輸入vlookup，就可以得到超級多的使用方法教程，當然如果有任何疑問也歡迎直接留言給我，看到後會儘快回覆大家。

到了這裡，只需要凍結G5單元格（點我前往上一篇中查看具體凍結窗格視角的方法），產品的物料生產成本表格的基本框架就搭好了，通過這樣的結構，無論今天師傅有多少種產品需要計算，都可以在R欄一覽無遺。同時，只要簡單的通過隱藏S到AA欄的原物料分別配方成本欄，就可以在查看R欄的各產品物料成本時同時查看AB欄的物料成本成本佔比了，有沒有超級方便的！而且所有的成本還會在因為進貨成本改變，修改完原物料進貨成本表後，全自動的幫你核算所有使用該物料的產品新成本！

到這裡為止，所有產品的原物料成本就能在同一張表格內自動更新為最新的版本，並且通過凍結視窗功能，讓產品品名後面就跟著產品的物料成本價格以及成本佔比（成本/售價），是不是超簡單的！有了各產品的物料成本佔比數據，就能通過比對公司的損益表，標明超過損益表中平均物料成本佔比的產品，將數據提供給專業生產人員，并溝通尋求改善的方法，甚至於還能通過各產品的銷售狀況，找出物料成本佔比超高，但銷售狀況不佳的產品，通過80/20法則，對產品的品項結構做出調整（這些內容具體要怎麼做，我會在之後的文章中將我自己的方法分享給大家）。下一篇中，我會延續使用這個成本自動計算表格的檔案，給它增加更多的“插件plugin”，讓它的功能變的更加強大！

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

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

請在下方輸入你的電子郵件地址並訂閱我們的電子報