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系统结算成本、再配合实际案例分析,分享这个会计工作较为複杂的部份。


有系統、全面性的學習Excel職場應用,:會計人的Excel小教室PressPlay頻道


當前文章分類:
数据透视表