借助Excel模型實現財務比率多維透視分析

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

【關鍵詞】 Excel； 數據透視模型； 財務比率； 多維透視分析

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

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

一、建立財務比率分析模型

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

（一）建立單個企業的比率分析表

在建立單個企業的比率分析表時，首先需要建立基礎數據表（資產負債表、利潤表、現金流量表、股票價格表等）。

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

在建立單個企業的基礎數據表和財務比率分析表后，將各個企業的基礎財務數據輸入或導入到基礎數據表模板，就可以生成各個企業的財務比率分析表。

（二）建立行業比率分析表

數據透視表和圖是對數據表的數據字段進行匯總分析，數據字段是數據表中的列名（數據表中的每一列同質數據作為一個字段）。要建立財務分析多維透視表和圖，首先需要建立一個匯集行業各企業財務比率的Excel文件，將用于實現橫向對比分析的同行業多家企業比率數據經過格式調整，放在同一張數據表中。該過程首先需要確定數據表的字段，以便于用來多維透視分析。在筆者建立的多行業比率分析表中，設定了5個數據字段：指標名稱、指標類別、股票名稱（公司名稱）、時間、指標值，從而建立了如表2所示的行業比率分析表。

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

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

在二維格式的“行業比率分析表”的基礎上，就可以建立用來進行多維透視分析的源數據表。

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

（一）建立數據透視圖模型

以“行業比率分析表”作為源數據表，通過借助Excel的“數據透視表和透視圖”功能，就可以輕松建立一張數據透視圖模型。利用“數據透視圖向導”可以建立如圖1所示的數據透視圖模型。

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

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

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

（二）運用數據透視模型進行財務分析

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

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

圖2還列示了競爭對手的凈資產收益率，為進行競爭對手之間的財務比率比較分析提供了基本依據。從圖2中可以看出，2007—2011年，云南白藥的凈資產收益率遠高于其競爭對手。然而，太極集團的凈資產收益率卻遠低于其競爭對手，并且差距逐漸拉大。是什么原因導致云南白藥的凈資產收益率一直處于最高水平？是什么原因導致太極集團的凈資產收益率一直處于最低水平？要回答這一問題，我們可以從圖3和圖4得到答案。圖3列示了影響云南白藥凈資產收益率的驅動因素，不難看出，云南白藥持續較高的凈資產收益率主要來自于其持續增長的銷售凈利率和較高的總資產周轉率的影響。圖4列示了影響太極集團凈資產收益率的驅動因素。從圖中數據，我們可以發現，太極集團持續較低的凈資產收益率主要來自于其較低（甚至為負）的銷售凈利率和極高的權益乘數的影響。

三、結論

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

【參考文獻】

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

[2] 劉繼偉，楊樺.Excel在財務管理中的應用[M].清華大學出版社，2010.

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