Excel如何分析產品別毛利 - 會計

Excel如何分析產品別毛利

網誌圖文版：

http://www.b88104069.com/archives/3149

有讀者工作上必須分析毛利減少，不知如何下手，我簡單指引方向、並且提
供操作方法，沒想到真解決了讀者難題，實在很高興。一方面，會計人常用
的Excel小技巧，之前大多寫過文章介紹，這次算是實戰應用，另方面，這也
是對我會計實務的肯定。於此，分享給各位會計人參考並指教：

一、兩個月的銷貨成本明細，任何管理分析的第一步，都是先拿到原始資料
。

二、二話不說跑樞紐分析表，這部份可參考之前文章：《Excel如何建立樞紐
分析表》（http://www.b88104069.com/archives/2448）。

三、我設計的範例很單純，只有六個料號，實務工作遇到的，通常是好幾十
種甚至上百種的料號，所以會把料號歸屬於幾個大類產品，一般在編碼上就
會講究，在這裡我假設料號首碼是產品類別，所以套個公式：「
=LEFT(C2,1)」，將每個料號的產品別抓出來。

四、再跑一次產品別的樞紐，這裡可以重新架樞紐，也可以直接更新樞紐的
資料，作法參考先前文章：《Excel如何樞紐分析表資料更新》（
http://www.b88104069.com/archives/1581）。

五、兩個月的產品別彙總表，架樞紐和修飾的部份，可以參考：《Excel如何
編製價量分析》（http://www.b88104069.com/archives/2347）。

六、後面加個公式：「=B6/B$12」，第12行是總計欄，所以用「＄」固定起
來，從E欄往右拉到F欄、再往下拉到12行，每個產品當月的佔營收比就出來
了。

七、再跑產品別的毛利彙總，這個等下有妙用。

八、收入跟毛利擺在一起，Excel的樞紐表默認按字母排序，所以兩個表同一
行是同類產品，在毛利表後面套公式：「=H8/B8」，拉完之後，毛利率也好
了。如圖所示，先把佔營收比大的產品標出來，這些產品是造成兩期差異的
主因。例如產品D為出貨主力，八月份佔比減少，但毛利率變化不大，且接近
於當月平均匯率，因此對總毛利率影響不大。產品I雖然也是主力產品，兩期
營收佔比和毛利率變化不大，因此也不會造成總毛利的變化。

產品F為低毛利產品，八月出貨佔比減少，產品H為高毛利產品，八月出貨佔
比增加，這兩個產品標黃色，可以很好地說明為何八月份整體收入減少、毛
利卻是增加的，也就是毛利率提高了。

九、前面所述是產品別毛利差異分析，同類產品成本結構相同、通常兩期毛
利變動不大，所以範例假設是純粹出貨佔比的變化，造成整體毛利率變動。
同樣的架表方式，只要內容改成客戶，便是客戶別毛利分析。因為客戶的銷
貨結構不同，所以可能兩期毛利率變動，分析方式相同，例如標青色的A、B
、C客戶，雖然毛利率變動大，但是出貨佔比不大，標橘色的F、I客戶，出貨
佔比稍有增減，但兩期毛利率差異不大，暫不作特別說明。

標黃色的D、H客戶，很顯然是造成兩期毛利率變動的主因要。其中D客戶兩期
出貨皆佔四成，為主要客戶，八月毛利率驟降，H客戶八月出貨佔比增加，然
而毛利率同樣驟降，這兩個客戶都有必要再詳細瞭解原因，作為差異分析的
說明。

至於單一客戶為何毛利率變動，那就依樣畫葫蘆，把該客戶的兩期料號明細
展開，看是哪個料號佔比大、差異大，不過這樣的分析，都是比較表面的，
到最後還是要看成本分析最核心的部份，也就料工費的分攤情形，這個，如
果要寫的話可能要好幾篇文章仔細說明了。

以後，我打算從成本分攤的設置、ERP系統結算成本、再配合實際案例分析，
分享這個會計工作較為複雜的部份。


延伸閱讀：

Excel如何設置直接部門成本分攤：

http://www.b88104069.com/archives/1403

Excel如何設置間接部門成本分攤：

http://www.b88104069.com/archives/1342

Excel如何彙總營業成本表項目：

http://www.b88104069.com/archives/2827

--


周末，我們繼續Excel：精華區=>21.心得=>5.其他=>3.office

會計人的Excel小教室： https://www.facebook.com/acctexcel


--