Unit C: Analysing data characteristics

主要參考資料來源 : KPMG ACL課程講義資料PriceWaterHouseCooper ACL課程講義資料

ACL Training Materials

2

Situations when we would use data analysis

• ACL commands that allow us to perform data analysis:

• Data validity commands;• Analysis of numeric fields and values;• Analysis of non-numeric fields and values.

Analysing data characteristics

Numeric fields

4

Analysing data characteristics ( 數值 )

• Count

• Total

• Statistics

• Profile

• Stratify

5

Count

• Count - counts the number of records that meet the specified condition;

6

Total

• See if you can derive the total value of the invoices (ie voucher type ‘IN’) that were billed before 1997

• 可驗證資料之完整性

7

Total

8

Total

PricewaterhouseCoopers

9

Statistics and Profile ( 統計分析 )

• Statistics - returns a statistical summary of one or more numeric fields;

• Profile - returns similar but slightly less detailed information;

• Run the statistics command on the value field.

10

Statistics and Profile ( 統計分析 )

Total value of receivables$468,880.69

Average value$865.81

Positive values609 recordswith value of$527,277.55

Negative values(probably credit notes)

161 recordswith value of$(58,396.86)

11

Stratify ( 數值分類 )

可自行輸入 Min 及 Max ，亦可由系統帶出， Interval 預設值為10 ，亦可由 Free 中自行設定區間 。

可將欄位為數字型態之資料分類彙總。

12

Stratify ( 數值分類 )

13

Workshop C1

•開啟 ap_trans•執行 Analyze/Stratify•若先執行 Statistic 則 Min 及 Max 會自動帶出•請問 InvoiceAmount 高於 10,000 元之筆數共有幾筆•區分 0,1000,3000,5000,7000 之區間

14

Workshop C2

• 任務：– 行銷部門請您協助，依下列單價區間的設計，將目前庫存存貨

歸類，並列示出各區間範圍內的交易筆數及庫存數小計。 (Inventory.fil)

– 0.00 to 9.99 10.00 to 49.99 50.00 to 99.99100.00 to 599.99

– 瞭解哪一個單價區間的庫存數量最多？

Analysing data characteristics

Non-numeric fields

PwC

16

Analysing data characteristics ( 非數值 )

• Classify

• Age

• Summarise

17

Analysing data characteristics

• Using these commands we could:

• Group data into subsets and return relevant totals for these subsets:

--> Group data in a payroll file into departments and return the total payroll value for each department;

• Perform ageing of records with date fields:

--> Age a receivables file by intervals of 30 days to determine bad and doubtful debts.

18

Classify

• 可將鍵值為文字型態之資料分類彙總，且可按種類別彙總產生長條圖。

• 選擇欲列入分類計算的資料欄位。 ( 須為數值型態 )

19

Classify

20

Age

• Performs ageing of records based upon a selected date field;

• 可自行設定 Cut - Off 的日期，且可自訂區間

21

Age

22

Summarize

• 資料須先排序或索引

• 若為數值型態，則須使用 String 函數轉換，才能執行

23

Summarize

Summarise

• The order that the Summarise On fields are selected is very important

24

Summarize

25

Workshop C3

• 經過了動之以情、誘之以利、脅之以暴，終於，您自 MIS 取得了整年度應收帳款的交易檔 (AR.fil) 。

以下是應收帳款資料有效的交易型態： Invoice – IN

Payment – PM

Credit Notes – CN

Transfer – TR

• 任務：

– 請對各交易型態的交易金額小計後與會計部門的帳務資料核對；同時，也請瞭解每一交易型態最近發生日期以及其交易客戶為何。