從財務系統中,撈出過往的銷售資料,並匯出成Excel檔案,面對這堆銷售數據,你的主管想要掌握各項產品的銷售狀況,而不是模擬兩可的答案。

例如:

  • 各產品到目前為止的銷售數字
  • 跟去年相比,哪些產品成長、哪些衰退?
  • 成長或衰退的比率分別是多少?
  • 這幾年每個產品的表現如何?

如何透過Excel 做出這些報表並提供給主管看?

接著,我將分享三個常用來評估營收的財務數據,並說明如何用Excel做出這些報表。

 

YoY (年營收成長率)

YoY(Year on Year) 年營收年增率,指的今年的總營收金額與去年的總營收金額進行比較,以分析該項目(產品)為成長(衰退)的百分比率。

公式為:YoY = (當年年營收-去年年營收) ÷ 去年年營收 x 100%

舉例而言:

本年度的總營收為2,190萬元

去年度的總營收為1,870萬元

因此,YoY=(2190-1870)/1870*100% = 17.1%

由於數值為正,因此為成長;反而如果為負則是衰退

接著,我們假設你從系統中導出銷售數字,並試著用Excel 產出YoY報表

YOY rawdata

此時,我們可以用Excel 樞紐分析,產出如下的報表

其中,年 放置在 [欄]的位置,月份 則放置在[列]的位置

銷售金額則放置在[值]

並做出以下的報表

 YOY information

 

此時,我們可以直接用Excel內建的[差異百分比],再選擇計算方式為跟前一年比較,就可以直接做出YoY 分析報表哦!

Year on Year

 

為了讓主管可以更容易掌握每個產品的營收成長狀況,你還可以在樞紐分析接的選項中,選擇[插入交叉分析篩選器],接著,將[產品名稱]設定為篩選器,並插入長條圖,以便主管可以根據自己的需求,選擇想要掌握的產品年營收成長率!

 

YOY dashboard

 

YTD (年初至今)

YTD ( Year To Date ),簡單的說便是累計營收。

例如:某一個產品從年初到現在一共帶來收入,也就是累加總金額。

下圖簡單舉一個案例:

其中C3的資料為C2+C3,用公式便是=SUM($B$2:B3);同樣的,C5=C2+C3+C4+C5或是用公式=SUM($B$2:B5)

YTD rawdata

 

除了用人工來進行輸入公式外,如果數據是資料庫,也可以用Excel樞紐分析快速解決這個問題

例如上題中,利用Excel樞紐分析將年 以及 月 放置在[列] ,同時將金額 拉兩次放置到[值],產生如下圖

 

YTD information

接著在樞紐分析表中,按下滑鼠右鍵選擇[值的顯示方式/計算加總至]

 

Year To Date

就可以做出金額累加的表格了!

Year To Date

同樣的,如果再結合交叉分析篩選器,以業務人員為例,便可以做出讓主管自己選擇想要看哪個業務人員至指定的日期內銷售總利潤以及業績達成率哦!

YTD dashboard

 

CAGR (年複合增長率)

CAGR(Compound Annual Growth Rate) 年複合成長率。

公式為:

EVBV

BV起始價值

EV最終價值

N  年數

舉例而言:

某項產品

2010年營收為190萬元

2015年營收為318萬元

這5年的時間,經計算得到:年複合成長率10.85%

意思是說,產品從2010年起,每年的營收都比前一年度成長10.85%。

計算公式 = (318/190)^(1/5)-1*100% = 10.85%

再舉一個例子,下列表格為各日期的數據

Compound Annual Growth Rate

由於公式中的N為年數

因此,我們先用Max()以及Mix()函數

分別找出該表格最開始的日期以及最終日期

接著再用Year() 進行最大與最小日期的相減,以找出年數,也就是N值

公式為:

=Year(MAX(A2:A9))-Year(MIN(A2:A9))

接著,我們便可以利用公式來計算CAGR

公式 = (B9/B2)^(1/E2)-1 

CAGR(年複合增長率) = 2.53%

 

CAGR

 

推薦文章

用Excel從被動分析到預測分析?

有資訊系統了,需要學數據分析嗎?

學會預測分析前?先學YOY嗎!

髒數據是什麼?

VBA真的好難...

 

 

延伸課程

大數據商業分析力

圖表儀表板

 

Excel 商業數據分析師(Power Query)

excel專家橫幅307x150

 

 

 

gibson001

作者:游振昌
 ●專案管理顧問有限公司 執行長
 ●中華國際專案經理人協會 理事長 
 ●Project Club 發起人暨資深顧問

 

comments
標籤:

登入

會員消息

🔵免費註冊會員可以換範本!

🔵累積三堂課可免費複訓一堂!

🔵怎麼註冊?怎麼輸入點數序號?

🔵忘記密碼嗎??快點來信

🔵找文章請善用搜尋功能!

🔵加入我的最愛每天登入有一點!

🔵FB註冊的朋友,帳號是你的FB姓名

🔵點我搶點數!

🔵點我去範本軍火庫