利用Excel切片器建立財務(wù)分析模型

任澤華 馬曦 黃平 趙發(fā)金

[摘要]本文在介紹切片器功能及優(yōu)勢的基礎(chǔ)上，重點(diǎn)分析利用Excel切片器建立財務(wù)分析模型的路徑，并剖析了建立財務(wù)分析模型的成效。

[關(guān)鍵詞]Excel切片器 ? ?分析路徑 ? ?可視化

一、切片器功能介紹

“切片”是指將物質(zhì)切成極微小的橫斷面薄片，以觀察其內(nèi)部的組織結(jié)構(gòu)。Excel切片器便是利用這一原理建立的數(shù)據(jù)篩選和圖片切換的分析工具，不僅可以通過“排序和組合”篩選達(dá)到目的，還可以實現(xiàn)多條件篩選、多數(shù)據(jù)透視表聯(lián)動、圖形可視化，即用一種圖形化的篩選方式，單獨(dú)為數(shù)據(jù)透視表中的每個字段創(chuàng)建一個選取器或控制標(biāo)簽，浮動于數(shù)據(jù)透視表之上，通過對選取器中字段項的篩選，實現(xiàn)比字段下拉列表篩選按鈕更加方便且靈活的篩選功能。

二、運(yùn)用切片器建立財務(wù)分析模型的優(yōu)勢

一是操作簡單便利。切片器是Excel自帶的工具命令，無需另行安裝軟件，且操作簡單，主要依靠篩選、鼠標(biāo)拖拽就可實現(xiàn)目標(biāo)。

二是實現(xiàn)多維度全覆蓋分析。切片器可以將來自多個源的數(shù)據(jù)進(jìn)行統(tǒng)一，創(chuàng)建能提供可操作見解并驅(qū)動實現(xiàn)業(yè)績的交互式、沉浸式儀表板和報表。

三是可視化的快捷便利。利用切片器建立好數(shù)據(jù)模型之后，可以通過控制標(biāo)簽進(jìn)行任意篩選，實現(xiàn)多視角動態(tài)展示。

四是可重復(fù)利用。分析模型建成后，再次使用只需導(dǎo)入數(shù)據(jù)源即可自動刷新數(shù)據(jù)、自動生成新的圖表。

三、財務(wù)分析模型的構(gòu)建路徑

利用Excel切片器功能建立財務(wù)分析模型，是指依靠MS-Excel切片器控制動態(tài)標(biāo)簽功能，將整體財務(wù)數(shù)據(jù)進(jìn)行可視化分析和智能展示，主要在賠付支出、手續(xù)費(fèi)及傭金、業(yè)務(wù)及管理費(fèi)、稅金支付等領(lǐng)域?qū)⒇攧?wù)報表數(shù)據(jù)進(jìn)行可視化分析，智能化展示數(shù)據(jù)邏輯及波動異常，鎖定檢查重點(diǎn)、疑點(diǎn)。

（一）確定分析思路

1.以KPI指標(biāo)為評估基礎(chǔ)，重點(diǎn)關(guān)注PCI監(jiān)控類數(shù)據(jù)。保險行業(yè)內(nèi)，財險分公司的動態(tài)監(jiān)測指標(biāo)體系主要是“KPI+PCI”模式，即經(jīng)營類+監(jiān)控類，故建立分析體系時以KPI指標(biāo)為分析基礎(chǔ)，重點(diǎn)關(guān)注過程監(jiān)控類數(shù)據(jù)。

2.進(jìn)行多維度業(yè)務(wù)評價。通過對指標(biāo)本身、指標(biāo)趨勢、指標(biāo)連續(xù)性、表內(nèi)異常結(jié)構(gòu)比例、邏輯校驗異常等進(jìn)行業(yè)務(wù)評價，實現(xiàn)非現(xiàn)場分析重點(diǎn)化，主要數(shù)據(jù)分析思路如下：

（1）趨勢分析。通過對財險分公司各項過程監(jiān)控類數(shù)據(jù)在連續(xù)時間序列內(nèi)的分析，確定其增減變動的方向、數(shù)額及幅度，對增幅異常、數(shù)額異常的科目進(jìn)行重點(diǎn)分析，并鎖定該科目下存在異常拐點(diǎn)的機(jī)構(gòu)及時間。

（2）相關(guān)性分析。財險分公司過程類指標(biāo)往往不是孤立的，觀察指標(biāo)不僅要看單個指標(biāo)的變化，還需觀察指標(biāo)之間的相關(guān)性，包括正相關(guān)和負(fù)相關(guān)，通過與之存在相關(guān)關(guān)系的指標(biāo)變化趨勢來判斷異常，如某分公司綜合成本率為96.5%、綜合賠付率為56.09%、銷售成本率為40.41%，其中非車險銷售成本率為61.02%、車險銷售成本率為39.4%，非車險與車險費(fèi)用率明顯異常，故將其列入檢查重點(diǎn)。

（3）對比分析。將數(shù)據(jù)進(jìn)行橫向?qū)Ρ取⒖v向?qū)Ρ龋瑥亩页霾▌赢惓！Ｅc上一個年度的數(shù)據(jù)進(jìn)行對比，如保費(fèi)收入同比增加10%，銷售費(fèi)用同比增加40%或保費(fèi)收入同比增加10%，綜合賠付同比增加40%，則可以重點(diǎn)分析銷售費(fèi)用或賠付成本;在三級機(jī)構(gòu)間進(jìn)行對比，A機(jī)構(gòu)固定人事費(fèi)用約占保費(fèi)規(guī)模的4%，B機(jī)構(gòu)固定人事費(fèi)用約占保費(fèi)規(guī)模的10%，則可以重點(diǎn)分析B機(jī)構(gòu)的固定人事費(fèi)用。

（4）漏斗分析。在分析指標(biāo)變化時以下鉆的方式，按照一定的維度不斷分解，層層解剖，尋找根本成因，如某分公司綜合成本率高→綜合賠賠付率影響度較高→未決賠款準(zhǔn)備金影響度高→期末未決金額高→車物案件（人傷案件）影響度高→部分機(jī)構(gòu)影響度高，以此原則逐層進(jìn)行分析，進(jìn)行下鉆，找出誘發(fā)問題的最底層原因和問題機(jī)構(gòu)。

（5）矩陣分析（象限分析）。將兩類因素組合成矩陣，劃分象限，定義好壞的方向，如對全省三級機(jī)構(gòu)賠付率進(jìn)行分析，將賠付率與保費(fèi)收入定義成矩陣對機(jī)構(gòu)進(jìn)行分類：第一類（保費(fèi)占比高且賠付率高的機(jī)構(gòu)）、第二類（保費(fèi)占比高且賠付率較低的機(jī)構(gòu)）、第三類（保費(fèi)占比低且賠付率低）、第四類（保費(fèi)占比低且賠付高）。

（二）確定分析內(nèi)容

財險分公司主要KPI指標(biāo)有保費(fèi)達(dá)成率、承保利潤、綜合賠付率、綜合成本率、銷售成本率、邊際貢獻(xiàn)率、百元現(xiàn)金流等，過程監(jiān)控指標(biāo)主要有費(fèi)用攤回率、應(yīng)收率、費(fèi)用投產(chǎn)比、未決偏差、結(jié)案率等。結(jié)合財險分公司經(jīng)營管理及數(shù)據(jù)提取，確定以整體費(fèi)用、銷售費(fèi)用、運(yùn)營費(fèi)用、后援費(fèi)用等費(fèi)用類為主，保費(fèi)、賠款及稅金等為輔的19個維度進(jìn)行分析及評估，并確定其所需的風(fēng)險字段，共計88個。

（三）構(gòu)建分析模型

分析模型主要包括三個部分：第一部分為切片器區(qū)域，審計人員可根據(jù)需要從上述19個維度進(jìn)行數(shù)據(jù)篩選;第二部分為圖像可視化區(qū)域，根據(jù)分析思路，按餅狀圖、柱狀圖、散點(diǎn)圖等形式進(jìn)行展示，點(diǎn)擊圖中相應(yīng)位置還可穿透查詢相關(guān)數(shù)據(jù);第三部分為基礎(chǔ)數(shù)據(jù)區(qū)域，可隨時根據(jù)篩選條件顯示基礎(chǔ)數(shù)據(jù)。

1.清洗并形成基礎(chǔ)數(shù)據(jù)。將事先采集完成的數(shù)據(jù)清單，去除表頭和表尾，取消合并單元格，對不能直接使用的原始數(shù)據(jù)進(jìn)行清洗或替代，并在首行增加分析需要的字段，如“時間”“機(jī)構(gòu)”等。將清洗完成的數(shù)據(jù)，根據(jù)分析項目導(dǎo)入Excel表中，生成基礎(chǔ)數(shù)據(jù)區(qū)域。在完成數(shù)據(jù)導(dǎo)入后，務(wù)必校驗導(dǎo)入后數(shù)據(jù)與導(dǎo)入前數(shù)據(jù)的一致性。

2.實現(xiàn)數(shù)據(jù)分析可視化。基于前述分析思路和分析內(nèi)容，依托Excel切片器控制動態(tài)標(biāo)簽功能，通過篩選、拖拽等操作，將機(jī)構(gòu)、險種、期間等與上述19個維度進(jìn)行自由組合，實現(xiàn)交互，給于不同結(jié)果展示，最后形成簡略版的數(shù)據(jù)集市，并完成數(shù)據(jù)的可視化（以條形圖、雷達(dá)圖為主），從而將財險分公司的“KPI+PCI”類數(shù)據(jù)直觀展示，見圖1。