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

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

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

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

一、切片器功能介紹

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

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

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

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

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

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

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

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

（一）確定分析思路

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

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

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

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

（3）對(duì)比分析。將數(shù)據(jù)進(jìn)行橫向?qū)Ρ取⒖v向?qū)Ρ?，從而找出波?dòng)異常。與上一個(gè)年度的數(shù)據(jù)進(jìn)行對(duì)比，如保費(fèi)收入同比增加10%，銷售費(fèi)用同比增加40%或保費(fèi)收入同比增加10%，綜合賠付同比增加40%，則可以重點(diǎn)分析銷售費(fèi)用或賠付成本;在三級(jí)機(jī)構(gòu)間進(jìn)行對(duì)比，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)變化時(shí)以下鉆的方式，按照一定的維度不斷分解，層層解剖，尋找根本成因，如某分公司綜合成本率高→綜合賠賠付率影響度較高→未決賠款準(zhǔn)備金影響度高→期末未決金額高→車物案件（人傷案件）影響度高→部分機(jī)構(gòu)影響度高，以此原則逐層進(jìn)行分析，進(jìn)行下鉆，找出誘發(fā)問(wèn)題的最底層原因和問(wèn)題機(jī)構(gòu)。

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

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

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

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

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

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

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