贊贊小屋

VBA迴圈取得8季度財報,Excel公式分析淨利毛利率

VBA利用兩家公司和八個季度的迴圈事件取得財報之後,Excel設計INDEX、MATCH、CONCATENATE、HLOOKUP函數公式彙總計算財務比率,自動分析台積電大立光兩年的毛利率及營益率。

上一節介紹營業收入的分析比較,在本書第四章第一節《綜合損益表》中,已經分析過損益表的結構,簡而言之,收入必須再扣掉成本及費損失,最後才是真正賺到的淨利。以火車作比喻,營業收入是火車頭沒錯,但這列火車能跑多快,還要取決拉於多少節車廂、車廂裡坐多少人。以開一家泡沬紅茶店為例,來客數多現金收到手軟,但是要扣買珍珠買鮮奶的原料成本、雇用兼差學生的用人成本,每月還要再扣除跑不掉的水電帳單和店面租金,最後口袋剩下的錢才是真正賺到的。

本節介紹觀察企業賺錢能力的兩項重要指標:毛利率和營益率 (營業利益率)。

一、損益表重要科目

再複習一次簡單損益表的大項科目,如列表所示說明如下:

收入是一切利潤的源頭,毛利是販賣商品的差價,賣的產品越多、毛利越多,例如大家都知道iPhone手機的毛利率非常高。

營業利益則是毛利再減掉銷管研費用,是經營主要業務的利潤所得,重點在於成本控管、有效管理各項費用,所以這是衡量管理團隊績效最佳的綜合數字,例如王永慶是經營之神,營益率就是在看一家公司離經營之神還有多遠。

營業利益再扣掉業外、所得稅等其他非業務支出,最後才是能留在口袋裡、能分配給股東的盈餘。在股市中歷任能當上股王股后,亳無疑問在淨利率絶對是高人一等。

營業毛利、營業利益、稅後淨利各除以營業收入,便是毛利率、營益率、淨利率,等於是以公司經營的各個層次評估公司獲利能力。

VBA迴圈取得8季度財報,Excel公式分析淨利毛利率 VBA財務分析 第1張

二、分析總表INDEX函數

架設分析總表(工作表「Analysis」)。上一節提到上市櫃公司依照《證券交易法》規定,每個月都要公告營收,除此之外,《證券交易法》同時也規定每個季度必須公告經會計師認可的財務報告,在這裡便以季度為單位,想整理出兩家公司五個季度的毛利率、營益率、淨利率等獲利指標。同樣在輸入兩家公司代碼和開始年度季度之後,工作表上標黃色部份會立即自動更新,大部份都是用到簡單的Excel操作,例如儲存格參照、加減計算、Vlookup函數,其中在D5到H5是設計較為複雜的公式:「=INDEX(Calculation1!$B$15:$I$15,,$F$4+COLUMN()-4)」,作用是配合開始年度和季度產生恰當的五個期間,這裡和上一節相同,工作表「Analysis」(分析總表)和「Calculation1」(工作底稿)是交互作用的,另外由於這一節較為複雜,所以會用到兩張工作底稿,亦即「Calculation1」和「Calculation2」。

VBA迴圈取得8季度財報,Excel公式分析淨利毛利率 VBA財務分析 第2張

三、工作底稿CONCATENATE函數

第一張工作底稿「Calculation1」的任務有兩項,其一是和上一節相同,引用工作表「Analysis」資料,計算轉換得到各期間作為標題,其二是得到兩家公司各個期間的財務報告網址,這網址不但是方便參考比對,同時會作為VBA程式取得網頁資料的重要參數。

工作表上標黃色部份,仍然都是會自動化更新的,除了網址之外的公式相對較為簡單,利用到的技巧和上一節相同,網址部份的公式:「=CONCATENATE(“mops.twse.com.tw/server-java/t164sb01?step=1&CO_ID=”&$B$10&”&SYEAR=20″&LEFT(B15,2)&”&SSEASON=”&RIGHT(B15,1)&”&REPORT_ID=C”)」,看起來似乎複雜,其實就是借助CONCATENATE函數拆分公開資訊觀測站的網址結構,將其中的股票代碼、年度、季度都設定為儲存格參照,引用其他地方已經處理好的資料。

四、VBA程式碼

30-40:定義這個巨集程式所需要用到的變數

60-70:在正式執行程式取得網頁資料之前,先跳出一個對話方塊,提醒操作者將上次執行所產生工作表予以刪除。

90-340:設定兩家公司輪流執行的迴圈。

120-150:新增工作表,依照股票代碼命名。

170-290:依序取得八個季度的損益表網頁資料。

310-320:由於原始損益表資料欄寛很大,為避免影響所取得資料,預先設定工作表欄寛。

VBA迴圈取得8季度財報,Excel公式分析淨利毛利率 VBA財務分析 第3張

五、執行巨集

執行「QuarterlyProfit」巨集,VBA會新增「3008」、「2330」兩張工作表,分別取得各公司兩個年度八個季度的損益表。仔細看各季度內容,一到三季度都是當季三個月的金額,到第四季便是年度,也就是一整個年度的金額,如此雖然合乎通常看財報的習慣,由於是希望整理出最近五個季度的資料,方便作跨期間趨勢比較,想得到單獨第四季的金額,需要以年度金額減掉前三季的變通方法,從這裡讀者應該可以理解,為何從17Q2開始五個季度,在Excel和VBA設計卻要一次從17Q1取得八個季度的資料。

VBA迴圈取得8季度財報,Excel公式分析淨利毛利率 VBA財務分析 第4張

六、MATCH與INDEX函數

工作底稿沿用先前章節一貫的架構,以MATCH和INDEX函數搭配,關鍵字的概念擷取財務報表中特定會計科目的金額。這裡因為有兩家公司、會有兩張工作表,而且工作者的名稱隨著選擇不同公司更新,不再像之前原始資料的工作表名稱固定不變,所以需要再引進INDEX函數:「=MATCH(“*”&$B9,INDIRECT(“‘”&$B$1&”‘!A:A”),0)」,作用是把公式中所引用的工作表設定為動態參照,只要B1儲存格內容變更,MATCH對應的工作表也會隨之變更。

如圖所示,擴充了「Calculation」工作底稿,不但有兩張工作表,而且在「Calculation2」複製成兩家公司的計算底稿,方便於分析總表同時呈現兩家公司的財務比率。

沿續上一步驟關於一到三季度和全年度的差別,這裡在工作表最下面「(三)計算財務比率」的公式設計也要作調整,「17Q1」到「17Q3」是單季度金額,和以前一樣引用單季度的資料即可,到了第四季「17Q4」因為是全年度金額,要先引用資料再減掉前三季,舉例而言,儲存格J20的函數公式為:「=INDEX(INDIRECT(“‘”&$B$1&”‘!K:K”),J9)-SUM(D20,F20,H20)」。「18Q1」到「18Q4」同樣如法炮製。「公司1:3008」如此,複製的「公司2:2330」也是如此。

VBA迴圈取得8季度財報,Excel公式分析淨利毛利率 VBA財務分析 第5張

七、HLOOKUP函數

工作底稿有八個季度,分析總表只有五個季度,而且工作底稿是固定從Q1開始,分析總表則是四個季度自由設定,所以沒辦法再像先前一樣僅僅是簡單引用,必須讓Excel聰明地依照標題期間去查找適合的獲利率,要達到此目的剛好可利用HLOOKUP函數:「=HLOOKUP(D$6,Calculation2!$D$27:$R$30,2,0)」。

Excel函數是頭過身就過,花費心思設計好了之後,簡單下拉右移便可以複製公式,成功帶入兩家公司五個季度各三項獲利率,下面並且編製折線圖,清楚呈現趨勢走向。

從獲利分析結果來看,大立光不愧是股王,淨利率保有四成以上!而長期領先的資優生台積電,淨利率保有三成以上!以台灣主流產業電子業來說,通常毛利率能達到三成已經是非常驕傲,大立光和台積電能把淨利率拉到這麼高,確實是值得投資的好公司。

VBA迴圈取得8季度財報,Excel公式分析淨利毛利率 VBA財務分析 第6張

Excel佈置好網址供VBA程式使用

這一節取得財務報表資料的技術和先前不太一樣,先前是在VBA程式碼利用變數決定網址,在執行完巨集程式的過程中和結束之後,都不會看到實際所取得資的網址。這一節則是在Excel已經利用函數公式,先行羅列出所需的網址清單,再由VBA依序參照Excel網址取得資料。這麼做的好處除了很清楚一目瞭然,毋須研究編寫複雜的VBA程式碼,而且既然是Excel,不一定要用函數公式,也可以直接輸入或貼上網址,在應用上會更加靈活,合乎不熟悉VBA使用者的需求。

另外這裡的範例是兩家公司五個季度,但在Excel工作表和VBA程式碼的設計中,同樣都保留了擴充的空間,野心大一點可以更多公司更多季度。不過這裡還是建議維持兩家公司五個季度即可,因為在看獲利的時候,沒有必要作歷史學家,我們只關心目前經營狀況。在公司數量的部份,這本書強調的是深入瞭解一家公司的財務數據,在於深不在廣,選擇了兩家公司之後,依照本書所分享的,可以分析的東西就很多了。一家公司一家公司用Excel作筆記研究,如此累積下來的分析功力才會札實。

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

博客來網路書店網址:

https://www.books.com.tw/products/0010801639