從財務系統中,撈出過往的銷售資料,並匯出成Excel檔案,面對這堆銷售數據,你的主管想要掌握各項產品的銷售狀況,而不是模擬兩可的答案。
例如:
● 各產品到目前為止的銷售數字?
● 跟去年相比,哪些產品成長、哪些衰退?
● 成長或衰退的比率分別是多少?
● 這幾年每個產品的表現如何?
如何透過Excel 做出這些報表並提供給主管看?
接著,我將分享三個常用來評估營收的財務數據,並說明如何用Excel做出這些報表。
YoY (年營收成長率)
YoY(Year on Year) 年營收年增率,指的今年的總營收金額與去年的總營收金額進行比較,以分析該項目(產品)為成長(衰退)的百分比率。
公式為:YoY = (當年年營收-去年年營收) ÷ 去年年營收 x 100%
舉例而言:
本年度的總營收為2,190萬元;去年度的總營收為1,870萬元
因此,YoY=(2190-1870)/1870*100% = 17.1%
由於數值為正,因此為成長;反而如果為負則是衰退
接著,我們假設你從系統中導出銷售數字,並試著用Excel 產出YoY報表
我們可以用Excel 樞紐分析產出如下的報表,其中 年份 放置在 [欄]的位置,月份 則放置在[列]的位置,銷售金額則放置在[值]並做出以下的報表
此時,我們可以直接用Excel內建的[差異百分比],再選擇計算方式為跟前一年比較,就可以直接做出YoY 分析報表哦!
為了讓主管可以更容易掌握每個產品的營收成長狀況,你還可以在樞紐分析接的選項中,選擇[插入交叉分析篩選器],接著將[產品名稱]設定為篩選器並插入長條圖,以便主管可以根據自己的需求,選擇想要掌握的產品年營收成長率!
YTD (年初至今)
YTD ( Year To Date ),簡單的說便是累計營收。
例如:某一個產品從年初到現在一共帶來收入,也就是累加總金額。
下圖簡單舉一個案例:
其中C3的資料為C2+C3,用公式便是=SUM($B$2:B3);同樣的,C5=C2+C3+C4+C5或是用公式=SUM($B$2:B5)
除了用人工來進行輸入公式外,如果數據是資料庫,也可以用Excel樞紐分析快速解決這個問題
例如上題中,利用Excel樞紐分析將年 以及 月 放置在[列] ,同時將金額 拉兩次放置到[值],產生如下圖
接著在樞紐分析表中,按下滑鼠右鍵選擇[值的顯示方式/計算加總至]
就可以做出金額累加的表格了!
同樣的如果再結合交叉分析篩選器,以業務人員為例,便可以做出讓主管自己選擇想要看哪個業務人員,指定的日期內銷售總利潤,以及業績達成率哦!
CAGR (年複合增長率)
CAGR(Compound Annual Growth Rate) 年複合成長率。
公式為:
[BV]起始價值 [EV]最終價值 [N] 年數
舉例而言:
某項產品2010年營收為190萬元,2015年營收為318萬元
這5年的時間,經計算得到:年複合成長率10.85%,意思是說,產品從2010年起,每年的營收都比前一年度成長10.85%。
計算公式 = (318/190)^(1/5)-1*100% = 10.85%
再舉一個例子:下列表格為各日期的數據
由於公式中的N為年數,因此我們先用Max()以及Mix()函數,分別找出該表格最開始的日期以及最終日期,接著再用Year() 進行最大與最小日期的相減,以找出年數,也就是N值。
公式為:
=Year(MAX(A2:A9))-Year(MIN(A2:A9))
接著,我們便可以利用公式來計算CAGR
公式 = (B9/B2)^(1/E2)-1 CAGR(年複合增長率) = 2.53%
作者:游振昌
●專案管理顧問有限公司 執行長
●中華國際專案經理人協會 理事長
●Project Club 發起人暨資深顧問