Excel VBA財報分析,ROUND函數與Sheet.Delete計算存貨周轉次數

存貨周轉天數反映企業經營能力,也是觀察商品競爭力的關鍵指標。本文以Excel VBA網路爬蟲抓取投資資訊,商業數據分析宏碁、華碩、微星三家公司的存貨。

本章第一節在介紹存貨周轉率時,舉的例子為老雜貨店和新便利商店,由於所提供的產品服務較為先進創新,雖然是同樣大小的店舖,新便利商店的存貨周轉率較老雜貨店多了一倍,也就是一年可賣出更多一倍的商品。在所有產業之中,一般人最熟悉的電腦手機等資訊產業就具備這樣的特性,產品推陳出新、周期短、改朝換代很快,因此在瞭解屬於這個產業中的諸多公司的經營能力時,存貨周轉次數便是一個很重要的財務比率指標,它能告訴我們一家公司到底是老雜貨店還是新便利商店。

以下就台灣擁有自己品牌的三家著名電腦公司為例(宏碁、華碩、微星科技),介紹存貨周轉次數這項財務比率:

一、Sheets.Delete確認

上一節的VBA程式碼雖然很方便,但讀者如果自己有操作過,應該會發現有個小麻煩,例如這一節要換一家新公司宏碁,先複製上一節最後特力的檔案,更改檔案名稱,將複製檔案中帶有股票代碼的財務報表刪除。

像這樣會一再重覆的操作,便有自動化提升效率的空間,如圖所示,在VBA程式碼新增一行:「Sheets(WPName).Delete」,表示在取得資料、複製完工作表後立即刪除。實際執行巨集,中間會跳出訊息框提示:「Microsoft Excel將永久刪除此工作表。確定要繼續嗎?」按「刪除」VBA就會確定執行。

這裡以刪除工作表為例,因為這是最貼近Excel操作的流程,也可以用錄製巨集的方式瞭解程式碼。其實像複製檔案、更改名稱、甚至是不要再提示是否確定刪除,VBA都能處理,但是在技術上較為複雜,不像「Sheets(WPName).Delete」很容易理解,實際操作這些步驟也不會增加太多時間,留待以後再作適當分享。

二、銷貨日數逐年增加

巨集執行完成之後,工作底稿會自動更新,這裡毋須任何調整,原本的應收帳款相關比率便會正確計算,只要依照IS和BS的區別,小心新增行列、複製公式,很快便得到「存貨週轉率(次)」和「平均銷貨日數」。在這裡做了兩個小小的優化:一個是將EF、HI、KL、NO等輔助欄位隱藏起來,另一個是用ROUND函數取周轉率小數兩位,亦即將兩位以下的尾數去掉,如此在計算周轉天數較合乎所看到,也更為接近股東會年報的數字,當然避免不了有些非常小、可以忽略的計算差異。

從財務比率來看,存貨周轉率是逐年降低,平均銷貨日數是逐年增加,似乎需要進一步瞭解。

三、營業收入及淨利

和存貨周轉直接攸關的便是銷貨,如同本書第四章第一節所言,營業收入是一家公司賺錢的火車頭,一開始有了收入,中間經過種成本費用,最後才是本期淨利(賺錢)、或者本期淨損(賠錢)。目前在工作底稿為了計算應收款項周轉率,原本已經有了營業收入這一項,只要在底稿再插入一行,稍加整理,便又多了一項「本期淨利(淨損)」。讀者有興趣可以於範例檔案嘗試看看,公式架構已經設計好了,保留擴充性,所以在Excel操作變得很簡單。

由於範例檔案是原始資料、工作底稿、分析報表的結構,當工作底稿多了項目,同樣很方便可以帶到分析報表,變成是財務比率和重要財務報表項目並列,如此很清楚看出在存貨周轉次數下降的背後,其實更值得關心的是營收長期趨勢為減少,火車頭緩速,當然連帶會影響最後面數字,近五年有賺有賠、波動非常大。

將資料都彙總到Excel還有個好處,因為Excel不但是資料處理統計的高手,在圖表呈現方面也非常靈活,所以如圖所示,輕鬆能快速編製一個直條圖,讓變化的趨勢更加一目瞭然。

四、呆帳及跌價損失

接下來是華碩(股票代碼2357)。在取得財務報表之後,如果仔細和股東會年報的財務比率核對,會發現有所差異。經過核對驗算,原來是將備抵呆帳(累計減損)和備抵跌價損失等評價會計科目加回來,以總額而非淨額計算周轉率,所以在工作底稿增加這兩項,修改計算公式,才能得到和股東會年報相同的數字。

在此有三點補充:

(1)上個步驟的宏碁也有備抵呆帳,但金額很小,宏碁以扣掉備抵呆帳的應收帳款淨額計算比率,華碩與宏碁的方法不同,在會計上可能有同一事項不同的處理方法,這是公司的會計政策,不同政策並沒有絶對的對與錯,只是背後的觀念不同。如果仔細閱讀財務報告,附註中有一大段是「重要會計政策之彙總說明」,便是在說明公司所採用的會計政策方法。我們並非查核會計師、也不是會計工作者,毋須在此鑽研,而且其實差異也不大,不會影響到財務比率的分析,這裡只要注意到華碩有這兩個項目即可。

(2)VBA程式所取得的資產負債表中,應收帳款和存貨都是淨額,也就是直接扣掉了備抵呆帳和備抵跌價損失,為了要將計算結果與股東會年報一致,如圖所示,是直接翻閱華碩各年度的財務報告PDF檔,手工抄寫數字到Excel工作表上。所以VBA程式雖然可以很有效率取得網路資料,但也有先天限制,例如像這裡只有PDF檔,VBA便發揮不了作用,只能用最原始的方法。所幸如同第五章第三節「XBRL網頁資料」所言,在XBRL的資訊電子化趨勢,大部份、主要的財務資料還是可以在網頁直接取得,因此可以使用VBA。

(3)這裡有點大費周章將備抵呆帳和備抵跌價損失納入計算,只是純粹剛好有範例和讀者分享,實際在執行財務分析時,可以直接財務報表上的數字計算即可,一方面是合乎效率,另一方面採用相同計算方法比較不同公司,反而才具有分析意義。

回到華碩的分析總表,可以看到存貨周轉次數、營業收入、當年淨利大致維持穩定、但似乎有些微下坡的跡象,表示公司業務經營相當穩健,長期而言值得信賴,但仍須關心公司未來的走向。

華碩106年度財務報告附註中的重要會計項目之說明。

五、穩定的微星科技

接下來是微星科技(股票代碼2377)。一樣複製檔案、檔案重新命名、更改股票代碼、執行巨集,取得財報資料和更新工作底稿和分析表之後,各項目金額和公司財報是相同的,不過計算出來的周轉率和股東會年報稍微有些差異。在此不再和上個步驟華碩一樣深入研究,因為一來差異不大,二來Excel是依照MATCH和INDEX函數帶入各項目金額,金額沒有問題,財務比率公式是依照普遍的原則所計算的,本身即具有分析的意義。而且退一步說,本來財務分析便是相當靈活自由,只要確定已取得了財務報表資料,要怎麼作分析並沒有一定規則。

從存貨週轉次數來看,微星科技維持穩定,再搭配營收收和本期淨利來看,收入和淨利都是逐年增加,代表公司經營穩定並且成長。通常在K線技術分析圖喜歡價量齊漲的股票,財務比率分析也是,像這樣五年財務比率穩健成長的公司,基本面非常漂亮。

六、公司經營策略

財務報表的背後,是公司經營團隊幾年來持續努力的耕耘,所以在解讀經營能力相關的財務比率時,最好能更深一層地去看公司的經營策略。

以宏碁為例,其在105年股東會年報第66頁所說明的業務內容,公司以電腦產品為核心事業,其中筆記型電腦佔63%、桌上型電腦15%、顯示器佔12%,顯示宏碁仍然以電腦為主力業務。

再以華碩為例,其於股東會年報的營運概況中沒有特別說明營收佔比,不過稍具規模的上市櫃公司都會有法人說明會,華碩在其官網活動訊息中有每個季度的法人說明會簡報檔,以2017年第四季法人說明會而言,PC(電腦)佔營收比63%,其他主要為Mobile(手機)、Component(零組件),可見得較於宏碁仍固守電腦本業為主,華碩是朝資訊產品多元化經營的綜合品牌策略。

華碩說明會(www.asus.com/tw/Pages/Investor/#IR-Overview):

法人說明會簡報(asus.todayir.com.tw/attachment/201802091729041338111964_tc.pdf):

至於微星科技,稍微有瞭解過這家公司或者接觸這家公司的產品,第一眼印象就是電競電腦,如同微星科技的官網的願景(tw.msi.com/about/brand-story):「 MSI GAMING產品圖騰,是以象徵玩家熱血及狂熱的紅色、創新科技的黑色及持續獲利的銀色為基色,相互呼應。」,所以這家公司的經營策略很清楚,持續專注於電腦產業、而且是專注在電競產品,因為把路走深了,所以也把路走廣了。

七、三家公司財務分析

和上一節最後一個步驟一樣,在Excel可以很輕易將資料整合,彙總三家公司存貨周轉次數和經營績效的個人心得。

財務分析過程重於結果

這一節同時涉及到本書三個重點:Excel強化、財務比率分析、瞭解公司經營,其中Excel的部份也許讀者會有疑問,既然知道這樣設計,為何不一開始就把整套模型搬出來就好?

關於這一點,首先一個產品本來不可能瞬間就是完美,作者也是在書寫過程不斷修修補補、持續強化這套Excel工具,另外網站資料和網站結構是有可能改變、無法全盤長久預期的,所以藉由修改過程,讓讀者可以更熟悉這個工具,不但足以因應變化,而且每個人偏好需求不同,熟悉了之後才能自行客製化。最後,投資股票不也是這樣一個過程,越來越多公司、產業、年度的財務比率分析經驗之後,才能累積深厚的經驗心得,找到屬於自己認為最有投資價值的一家公司。

每天學習,每天充電:VBA財務分析文章

歡迎加入Excel VBA投資分析Line社群!

最新文章: