2016年6月15日星期三

內審通識:如何利用Excel進行數據驗證

不論外審還是內審,Excel是Auditor經常使用的分析工具。除了一些基本功能如常用函數 ( SUM、COUNT、PMT等 )、排序、篩選,以及繪圖外,其實還有不少有用的功能,幫助我們進行數據檢查和分析。這篇文章將介紹如何利用Excel進行簡單的數據驗證 ( Data Validation )。

數據驗證是一個程序,確保應用程式所用的數據全是準確無誤。例如年齡,數值必定大於0。又例如出生月份,數值必定在1至12之間。一個設計完善的應用程式,在數據輸入前會為數值進行驗證。不過,當從資料庫取出數據,數據未必百分百準確無誤。特別是Auditor向被審單位拿取數據作分析時,這些數據能否直接使用,往往取決於數據的有效性。例如零售店的季度銷售紀錄,交易數量隨時數以千計,若要一一檢查才使用,於Auditor來說既費時又失事。不過,若通過Data validation這個功能,我們就可以快速地找出潛在問題。

舉個例子,以下的季度銷售紀錄屬於今年第一季,因此所有銷售交易的發票日期應在2016年1月1日至3月31日的範圍內。要進行有效性和完整性檢查,我們首先要為日期欄設定範圍。步驟如下:

1.  點選日期欄
2.  在「Data tab」中點擊「Data Validation」
3.  在「Settings」中為數據有效性設定條件,揀選「Data」,取消「Ignore blank」( 即不可留空 ),以及設定日期範圍為2016年1月1日至3月31日。


4. 之後點選「Circle Invalid Data」。


5. Excel就會將不符合條件的儲蓄格圈出來,包括5號發票沒有日期,以及8號發票的日期並非在2016年第一季。



再舉個例子,假設所有產品的單價只會在30至100元之間,設定的步驟如下:
1. 點選單價欄
2. 在「Data tab」中點擊「Data Validation」。
3. 在「Settings」中為數據有效性設定條件,揀選「Whole number」,取消「Ignore blank」( 即不可留空 ),以及設定數值範圍為30至100。


4.  點選「Circle Invalid Data」後,因8號發票的單價在設定範圍以外,所以被Excel圈出來。



另一個auditor經常做的檢查是序號是否重覆和遺漏,例如發票編號,只要幾個簡單的步驟就能查出來。步驟如下:
1. 先為發票編號進行排序 ( sorting ),排序設定是由小至大。
2. 在H5儲存格輸入公式「=IF(A5-A4=1,"","missing")」。
3. 再將此公式複製到H6至H15的儲存格內。
4. 若發票編號存在遺漏,如7號發票,儲存格就會出現「missing」字樣。


若果發票編號包括字串而非全數字,在此情況下就不能進行檢查,應怎辦?不用怕,我們可以先利用「文字函數」如LEFT、MID、RIGHT等公式把數字抽出來,然後再用上述方法檢查重覆和遺漏。
最後一提,Excel雖然能進行數據驗證,但始終不及審計軟件方便和全面。若果需要經常分析數據,而且數據量龐大,還是使用審計軟件為佳。

&&&&&&&&


3 則留言:

C2 說...

謝謝分享。這篇文章十分實用。

除了Excel,我在blog的其他文章也知道ACL和IDEA能為Excel補不足。請問ACL和IDEA在內審方面,現時有多普及?

另外,由於今時今日不少公司的記錄都以電子方式儲存,加上審計軟件的便利,我得悉將來的方向會是乾脆不用sampling,而傾向用軟件去audit整個population。請問這會是將來的趨勢嗎?

Bitter Melon 說...
此留言已被作者移除。
Bitter Melon 說...

謝謝C2君
於外國如美加,ACL在ia field中較普及,以且有好多 training 可以上。

你說得對,其實現在已經利用審計軟件進行 100% sampling, 先對所有 data 進行分析,找出 exceptions 並加以 follow up.

一個簡單例子,例如中procurement,正常係出咗po先會從供應商處收貨。利用軟件找出所有收貨日期比 po發出日期還要早的 po,然後幾單來跟進及找出原因。

回覆刪除

LinkWithin

Blog Widget by LinkWithin