贊贊小屋

Excel如何以樞紐分析表(透視表),分析產品別銷貨毛利

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

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

兩個月的銷貨成本明細

二、二話不說跑樞紐分析表,這部份可參考之前文章:《Excel如何建立樞紐分析表》

跑樞紐分析表

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

套個公式:「=LEFT(C2,1)」,將每個料號的產品別抓出來

四、再跑一次產品別的樞紐,這裡可以重新架樞紐,也可以直接更新樞紐的資料,作法參考先前文章:《Excel如何樞紐分析表資料更新》

再跑一次產品別的樞紐

五、兩個月的產品別彙總表,架樞紐和修飾的部份,可以參考:《Excel如何編製價量分析》

兩個月的產品別彙總表

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

後面加個公式:「=B6/B$12」

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

再跑產品別的毛利彙總

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

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

在毛利表後面套公式:「=H8/B8」

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

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

只要內容改成客戶,便是客戶別毛利分析

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

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



本網站所有內容皆受版權保護