Excel設計VBA數列變數及迴圈事件取得遠東百貨財務報表,分析收現日數




本書前面章節已經介紹了財務結構和償債能力財務比率,依照先前章節範例所示,這兩類分析項目都是以單一報表(資產負債表或現金流量表)中某些科目作計算。這一章的重點是經營能力財務比率,如同上一節較為生活化的範例所示,它是以損益表和資產負債表各自的某些科目綜合計算。因此如果想將財務報表下載到Excel,自行核算並方便筆記紀錄存檔,勢必要同時取得這兩份財務報表,先前章節分享如何利用VBA取得單一財務報表,這一節因應需要,分享如何以VBA一次取得三份最重要的財務報表。

一、以遠東百貨為範例,其股票代碼為2903,在該公司105年股東年報中(公開資訊觀測站網址:https://doc.twse.com.tw/pdf/2016_2903_20170620F04_20200131_191101.pdf),第78頁和第80頁分別有財務率分析和各指標的計算方式,可利用這機會複習一下截至目前為止所介紹過的財務比率。

Excel設計VBA數列變數及迴圈事件取得遠東百貨財務報表,分析收現日數 VBA財務分析 第1張

Excel設計VBA數列變數及迴圈事件取得遠東百貨財務報表,分析收現日數 VBA財務分析 第2張

二、依照本書先前格式,先建立該公司的財務比率分析表,目標是以此作為彙總表,將VBA和Excel計算過的資料都整理到這裡。

Excel設計VBA數列變數及迴圈事件取得遠東百貨財務報表,分析收現日數 VBA財務分析 第3張

三、編寫VBA程式碼如下:

Excel設計VBA數列變數及迴圈事件取得遠東百貨財務報表,分析收現日數 VBA財務分析 第4張

此VBA程式碼主要引用自《人人做得到的網路資料整理術》第八章第五節「檔案資料建立」,配合這裡的需要做了些修改,依照綠色的段落註解簡略說明說下:

(1):變數定義:定義此程式會用到的變數,其中Stock為股票代碼、Company為公司名稱、Year為開始年度(程式設為一次取得五年資料)。

為提高靈活度,這裡不再將股票代碼直接寫死的程式裡,而是去抓取Excel工作表「Analysis」B1儲存格的資料(VBA程式碼以「Cells(1,2)」表達),用意是如果要改取得另一家公司資料,只要在Excel更改再執行巨集即可,這也是《人人做得到的網路資料整理術》書中一再強調Excel與VBA相輔相成的概念。

另外雖然定義的公司名稱Company(遠百),實際上在VBA生成工作表時,並沒有用到,這是考慮到VBA為原始程式,不像Excel應用程式有比較強的語言相容性,為避免不必要錯誤,在VBA程式碼原則還是使用英文書寫命名,讀者如真有興趣及需求,可試著修改將書本所附檔案。

(2)設定數列變數:配合報表在原始網頁的順序,分別設定FR2、FR3、FR4為資產負債表、損益表、現金流量表。

(3)集合迴圈取得財報:按照設定好的報表數列變數,依序新增三張Excel工作表,同時取得該公司五年度的三大財務報表。

(4)調整取得資料報表格式:簡單將取得內容統一設定欄寛為15。

四、執行結果如圖所示,成功取得最近五個年度三大財務報表。

Excel設計VBA數列變數及迴圈事件取得遠東百貨財務報表,分析收現日數 VBA財務分析 第5張

五、工作底稿除了引用財報關鍵字,在最後的「計算財務比率」設定公式:「E =D18/(SUM(C19,C20,C21,D19,D20,D21)/2))」,算法如圖所示。

Excel工作表中標黃色部份有三點補充:

(1):由於現在的比率計算需要用到不同的財務報表,各個報表的使用情形可能不同,所以新增加一個「財報」欄位,註明此行資料是屬於哪一份財務報表,BS為資產負債表簡稱、IS為損益表簡稱、CF為現金流量表簡稱。

(2)以2017年來看最近五年,第一年是2013,但由於周轉率中的平均餘額是以期初期末的平均估算,所以需要2012年末、亦即2013年初的餘額。這裡仍然取2013到2017,因為2013又剛好是台灣上市上櫃公司導入IFRSs元年,這裡為避免麻煩,不去抓取2012年的資料,而是直接使用2013年的去年同期數字,如此雖然沒有問題,但是相鄰公式會有不一致情形,沒辦法直接用複製公式的方法,必須手工調整。不過這是剛開始設定時才要考慮,一旦公式模型固定了,以後也毋須再調整。

(3)R欄到V欄把前面的各年度資料再引用過來,這是方便在「Analysis」參照可以複製拉公式,純粹提高開發設定時的效率,供有心想自己嘗試的讀者參考。

Excel設計VBA數列變數及迴圈事件取得遠東百貨財務報表,分析收現日數 VBA財務分析 第6張

Excel設計VBA數列變數及迴圈事件取得遠東百貨財務報表,分析收現日數 VBA財務分析 第7張

六、將計算結果帶到分析表。從指標來看,應收款項週轉率在2017年為37.64,平均收現日數為9.70,和先前幾年數據比起來,有明顯的變化。光看指標還不無法下判斷,參考上一步驟的計算底稿,可看到2017的營收偏少、應收帳款淨額偏高,值得瞭解公司營運和銷售市場是否有哪些原因所導致,作為投資參考。

Excel設計VBA數列變數及迴圈事件取得遠東百貨財務報表,分析收現日數 VBA財務分析 第8張

七、從最近年度來看,應收帳款財務比率值得瞭解,另外從絶對值來看,長期應收款項週轉率一年有30到50次,平均收現日數10天以內,表示產品銷售和現金流動非常快速。在股東年報第71頁「市場及產銷概況」中,公司會提供「最近二年度生產量值」和「最近二年度銷售量值」資料,遠東是百貨業、並非製造業,所以不適用生產量值,其主要收入為「商品銷售收入」及「專櫃抽成收入」。依照我們到百貨公司消費的經驗,百貨業的平均收現日數應該就是這麼短。

Excel設計VBA數列變數及迴圈事件取得遠東百貨財務報表,分析收現日數 VBA財務分析 第9張

這一節所計算的應收帳款財務比率,其實在第一步驟的股東年報就好了,之所以要分享如何以VBA取得資料進而自行計算,是因為看單一家公司往往還不夠。以本節的遠百為例,雖然單看會發現2017年有顯著的變化,但想必資訊仍然不足,例如想知道在百貨業是否收現天數大略如此?遠百在相關行業裡的經營能力是否優秀?其他行業的應收帳款收現日又是如何?像這樣的同業分析比較,便很需要借助Excel和VBA了,留待以後的章節繼續分享。

本文內容取自《讓上班族狠賺的EXCEL財報分析術》,書本仍然是最好的學習方法,今天就買本書吧!

金石堂網路書店網址:https://www.kingstone.com.tw/basic/2014950120822






當前文章延伸閱讀:
VBA財務分析