借助Excel模型實現(xiàn)財務(wù)比率多維透視分析

【摘 要】 比率分析是財務(wù)分析中最核心的量化分析方法。在比率分析中，更多的是通過比較分析來挖掘財務(wù)比率背后隱含的經(jīng)營信息。比較分析應(yīng)用最多的是橫向比較分析和縱向比較分析。傳統(tǒng)的財務(wù)分析工具往往是通過一張圖表生成一個比率，而無法在同一張圖表上進行靈活的橫向比較分析、縱向比較分析以及綜合分析。Excel的數(shù)據(jù)透視表和數(shù)據(jù)透視圖，可以解決如何在一張圖表中對財務(wù)比率進行橫向分析和縱向分析的問題。文章旨在借助Excel數(shù)據(jù)透視圖表的強大功能，建立一個有效的比率分析數(shù)據(jù)透視模型，從而實現(xiàn)財務(wù)比率多維透視分析。

【關(guān)鍵詞】 Excel； 數(shù)據(jù)透視模型； 財務(wù)比率； 多維透視分析

財務(wù)分析的目標(biāo)是運用財務(wù)數(shù)據(jù)對公司當(dāng)期和過去的業(yè)績及其可持續(xù)發(fā)展的能力進行評價。財務(wù)分析方法主要有比率分析法和現(xiàn)金流量比率分析法。比率分析法是財務(wù)分析中最重要和核心的量化分析方法。在財務(wù)比率分析中，更多的是通過比較分析來挖掘財務(wù)比率背后隱含的經(jīng)營信息。當(dāng)前，比較分析應(yīng)用最多的是橫向比較分析和縱向比較分析。在傳統(tǒng)的報表分析手段和工具中，財務(wù)報表分析者對于比率分析，往往是通過一張圖表生成一個比率進行橫向比較分析或者縱向比較分析。如果建立一個統(tǒng)一的比率分析數(shù)據(jù)透視模型，就可以在同一張圖表上，靈活地進行橫向比較分析、縱向比較分析和綜合分析，將為財務(wù)報表分析者進行有效的財務(wù)分析提供極大的幫助。

Excel是微軟公司辦公軟件Office的重要組成部分，具有功能強大的數(shù)據(jù)處理和繪圖分析功能，能提供豐富的函數(shù)。數(shù)據(jù)透視表是Excel用于進行快速匯總大量數(shù)據(jù)的交互式表格，數(shù)據(jù)透視圖是一種交互式的圖表，它以類似數(shù)據(jù)透視表的方式通過圖形化的方法來查看和重排數(shù)據(jù)。數(shù)據(jù)透視表和透視圖集中展現(xiàn)了Excel強大的數(shù)據(jù)統(tǒng)計分析處理能力，靈活快捷地對數(shù)據(jù)庫表從多個維度（字段）進行統(tǒng)計和分析，是數(shù)據(jù)透視表和透視圖最大的特點。借助數(shù)據(jù)透視圖可以解決如何在一張表或圖中，對財務(wù)比率進行橫向比較分析和縱向比較分析的問題。本文借助Excel數(shù)據(jù)透視圖的強大功能，建立一個有效的比率分析數(shù)據(jù)透視模型，從而實現(xiàn)財務(wù)比率多維透視分析。

一、建立財務(wù)比率分析模型

財務(wù)比率分析始于企業(yè)財務(wù)比率的計算。目前的財務(wù)比率分析主要有盈利能力分析、短期償債能力分析、長期償債能力分析、資產(chǎn)管理能力分析、現(xiàn)金流量分析和市場比率分析等。在財務(wù)比率分析模型建設(shè)中，首先需要借助Excel建立單個企業(yè)的比率分析表。

（一）建立單個企業(yè)的比率分析表

在建立單個企業(yè)的比率分析表時，首先需要建立基礎(chǔ)數(shù)據(jù)表（資產(chǎn)負(fù)債表、利潤表、現(xiàn)金流量表、股票價格表等）。

在基礎(chǔ)數(shù)據(jù)表的基礎(chǔ)上，借助Excel計算公式定義的功能，可以建立財務(wù)比率分析表，如表1所示。在該表中，定義各個財務(wù)比率的公式，如每股收益=凈利潤/股本總額，在本例中，轉(zhuǎn)換為Excel公式即為：D5=利潤表！E35/資產(chǎn)負(fù)債表！E80。類似地可以定義全部的財務(wù)比率計算公式。

在建立單個企業(yè)的基礎(chǔ)數(shù)據(jù)表和財務(wù)比率分析表后，將各個企業(yè)的基礎(chǔ)財務(wù)數(shù)據(jù)輸入或?qū)氲交A(chǔ)數(shù)據(jù)表模板，就可以生成各個企業(yè)的財務(wù)比率分析表。

（二）建立行業(yè)比率分析表

數(shù)據(jù)透視表和圖是對數(shù)據(jù)表的數(shù)據(jù)字段進行匯總分析，數(shù)據(jù)字段是數(shù)據(jù)表中的列名（數(shù)據(jù)表中的每一列同質(zhì)數(shù)據(jù)作為一個字段）。要建立財務(wù)分析多維透視表和圖，首先需要建立一個匯集行業(yè)各企業(yè)財務(wù)比率的Excel文件，將用于實現(xiàn)橫向?qū)Ρ确治龅耐袠I(yè)多家企業(yè)比率數(shù)據(jù)經(jīng)過格式調(diào)整，放在同一張數(shù)據(jù)表中。該過程首先需要確定數(shù)據(jù)表的字段，以便于用來多維透視分析。在筆者建立的多行業(yè)比率分析表中，設(shè)定了5個數(shù)據(jù)字段：指標(biāo)名稱、指標(biāo)類別、股票名稱（公司名稱）、時間、指標(biāo)值，從而建立了如表2所示的行業(yè)比率分析表。

表2“行業(yè)比率分析表”與表1“單個企業(yè)財務(wù)比率分析表”在表格顯示形式上最大的區(qū)別在于表2本質(zhì)是一張二維表（由行和列構(gòu)成的二維表，每行表示一個獨立的數(shù)據(jù)項），而表1本質(zhì)是一張三維表（由行字段——指標(biāo)名稱，列字段——指標(biāo)時間和隱含的頁字段——公司名稱三維構(gòu)成），每個指標(biāo)值所占據(jù)的單元格都是由三維字段來約束的。三維的數(shù)據(jù)表無法在一張表上顯示所有企業(yè)的指標(biāo)數(shù)值，不便于進行數(shù)據(jù)透視和分析，所以必須先將單個企業(yè)三維模式的財務(wù)比率分析表轉(zhuǎn)換為表2所示的二維模式下的財務(wù)比率分析表。然后再將各個企業(yè)二維模式下的財務(wù)比率分析表進行合并，從而完成表2“行業(yè)比率分析表”的建設(shè)工作，為財務(wù)分析多維透視表和圖的建設(shè)提供數(shù)據(jù)來源。

在將單個企業(yè)三維模式的財務(wù)比率分析表（表1所示的格式）轉(zhuǎn)換為二維模式的財務(wù)比率分析表（表2所示的格式）過程中，“指標(biāo)名稱”、“指標(biāo)類別”、“股票名稱”（或公司名稱）、“時間”4個字段的數(shù)值的填充工作比較容易完成，最為繁瑣的是“指標(biāo)值”字段中數(shù)據(jù)的填充。每個公司的比率指標(biāo)值數(shù)量為“指標(biāo)名稱數(shù)量”ד年份數(shù)”。如果每個指標(biāo)值都通過人工來轉(zhuǎn)換，工作量將是非常大的。解決這個問題可以借助Excel中的查找函數(shù)——Vlookup來實現(xiàn)。

在二維格式的“行業(yè)比率分析表”的基礎(chǔ)上，就可以建立用來進行多維透視分析的源數(shù)據(jù)表。

二、運用透視圖建立分析模型

（一）建立數(shù)據(jù)透視圖模型

以“行業(yè)比率分析表”作為源數(shù)據(jù)表，通過借助Excel的“數(shù)據(jù)透視表和透視圖”功能，就可以輕松建立一張數(shù)據(jù)透視圖模型。利用“數(shù)據(jù)透視圖向?qū)А笨梢越⑷鐖D1所示的數(shù)據(jù)透視圖模型。

在“數(shù)據(jù)透視圖模型”中，首先有“數(shù)據(jù)透視表字段列表”，該列表是以源數(shù)據(jù)表的各列字段名（各列第一個單元格的值）生成的列表，選擇任何字段都包含了要在數(shù)據(jù)透視圖中進行分析的數(shù)據(jù)。可以將這些字段按照需要分別拖到“頁字段”、“分類字段”、“系列字段”和“數(shù)據(jù)項”區(qū)域。頁字段是用來篩選在數(shù)據(jù)區(qū)中要處理的源數(shù)據(jù)的。分類字段類似于圖形的X軸，在Excel圖表中是分類軸。系列字段類似于二維圖形的Y軸，是用來建立數(shù)據(jù)系列的，在Excel圖表中是值軸。

在行業(yè)比率分析多維透視模型中，需要從“數(shù)據(jù)透視表字段列表”中將“指標(biāo)名稱”字段拖動到頁字段區(qū)域，以實現(xiàn)按指標(biāo)進行數(shù)據(jù)篩選。將“時間”字段拖動到分類字段區(qū)域，以建立分類軸。將“股票名稱”（或公司名稱）字段拖動到系列字段區(qū)域，以建立數(shù)據(jù)系列。將“指標(biāo)值”字段拖動到數(shù)據(jù)項區(qū)域，以告知Excel系統(tǒng)用來統(tǒng)計分析的數(shù)據(jù)項。同時需要借助Excel數(shù)據(jù)透視表工具欄的“字段設(shè)置”功能來設(shè)定“指標(biāo)值”的匯總方式為“平均值”、“最大值”或“最小值”[實際上，在本文所建立的行業(yè)比率分析多維透視圖中，由于任何一個數(shù)據(jù)項（某一時間某一公司某一指標(biāo)的值）只有一個，所以無論是使用“平均值”還是使用“最大值”或“最小值”的數(shù)據(jù)匯總方式都是一樣的統(tǒng)計結(jié)果，那就是顯示指標(biāo)值本身]，通過設(shè)定指標(biāo)值的匯總方式可以展示指標(biāo)數(shù)值本身。本文中，筆者采用了“平均值”作為數(shù)據(jù)項的匯總方式。

在設(shè)定完數(shù)據(jù)透視圖的頁字段、分類字段、系列字段和數(shù)據(jù)項字段及匯總方式后，“行業(yè)比率多維透視分析模型”就建立完成，可以通過Excel圖表的一些格式設(shè)置來使得圖表更為直觀。建立完多維透視分析模型后，從頁字段“指標(biāo)名稱”下拉框中選定任何一個指標(biāo)，就可以得到如圖2所示的單指標(biāo)橫向比較和縱向比較分析圖。

（二）運用數(shù)據(jù)透視模型進行財務(wù)分析

本小節(jié)以多維數(shù)據(jù)透視分析模型為依據(jù)，對中醫(yī)藥行業(yè)上市公司（云南白藥、同仁堂、康美藥業(yè)、太極集團、紫鑫藥業(yè)）的盈利能力財務(wù)比率之一——凈資產(chǎn)收益率進行比較分析，說明多維數(shù)據(jù)透視模型在財務(wù)分析中的運用。

圖2列示了近年來上述公司凈資產(chǎn)收益率的變動趨勢。以云南白藥為例，2007—2009年凈資產(chǎn)收益率呈下降的趨勢，從2010年起凈資產(chǎn)收益率開始反彈，并將增長趨勢持續(xù)到2011年。再看太極集團，2007—2011年，該公司的凈資產(chǎn)收益率持續(xù)徘徊在5%以下，2010年跌到了-9%的谷底，而在2011年又開始反彈。

圖2還列示了競爭對手的凈資產(chǎn)收益率，為進行競爭對手之間的財務(wù)比率比較分析提供了基本依據(jù)。從圖2中可以看出，2007—2011年，云南白藥的凈資產(chǎn)收益率遠(yuǎn)高于其競爭對手。然而，太極集團的凈資產(chǎn)收益率卻遠(yuǎn)低于其競爭對手，并且差距逐漸拉大。是什么原因?qū)е略颇习姿幍膬糍Y產(chǎn)收益率一直處于最高水平？是什么原因?qū)е绿珮O集團的凈資產(chǎn)收益率一直處于最低水平？要回答這一問題，我們可以從圖3和圖4得到答案。圖3列示了影響云南白藥凈資產(chǎn)收益率的驅(qū)動因素，不難看出，云南白藥持續(xù)較高的凈資產(chǎn)收益率主要來自于其持續(xù)增長的銷售凈利率和較高的總資產(chǎn)周轉(zhuǎn)率的影響。圖4列示了影響太極集團凈資產(chǎn)收益率的驅(qū)動因素。從圖中數(shù)據(jù)，我們可以發(fā)現(xiàn)，太極集團持續(xù)較低的凈資產(chǎn)收益率主要來自于其較低（甚至為負(fù)）的銷售凈利率和極高的權(quán)益乘數(shù)的影響。

三、結(jié)論

數(shù)據(jù)透視表和透視圖的應(yīng)用，展現(xiàn)了Excel強大的數(shù)據(jù)統(tǒng)計和分析處理能力。通過建立數(shù)據(jù)透視模型，靈活快捷地對數(shù)據(jù)圖表從多個維度進行統(tǒng)計和分析，不僅打破了傳統(tǒng)財務(wù)分析工具的局限性，同時也使財務(wù)分析變得更加立體化，從而實現(xiàn)了財務(wù)比率多維透視分析。

【參考文獻(xiàn)】

[1] [美]佩普，希利，伯納德.運用財務(wù)報表進行企業(yè)分析與估價[M].孔寧寧，等，譯，中信出版社，2004.

[2] 劉繼偉，楊樺.Excel在財務(wù)管理中的應(yīng)用[M].清華大學(xué)出版社，2010.

[3] 中文版 Excel 2000 VBA一冊通[Z].云舟工作室，2000.