fbpx

用Excel 輕鬆搞懂YoY、YTD、CAGR

從財務系統中,撈出過往的銷售資料,並匯出成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 發起人暨資深顧問


請輸入要搜尋的內容

合作洽談 *

任何採訪邀約、異業合作提案,我們將有專人與您聯繫。

現階段專案管理顧問有限公司已經承接了超過上仟場的企業內訓,有絕對的經驗值與專業替您規劃同仁的培育計畫。

歡迎填寫此表單,並詳述你希望的使用方式,我們將會儘速與你聯繫,讓我們替您規劃專屬於您的學習脈絡。

歡迎填寫此表單,並詳述你希望的使用方式,我們將會儘速與你聯繫,讓我們替您規劃專屬於您的學習脈絡。