Steps to perform an Expenses Trend Analysis in IDEA

Hi,

 

You can take an Expenses Day Book or General Ledger and cast it into an insightful trend table and chart for studying expense patterns and outliers.

 

The steps in IDEA would be as below -

 

(a) Import the Expenses Day Book into IDEA

 

(b) Based on the Expense Voucher Transaction Date append a virtual numeric field titled 'Expense Month' with the criteria @month(TRANDATE)

 

(c) Now apply a Pivot Table under Analysis. In the Pivot view drag and drop the Expense Description field from the Pivot Table dialog box to the Pivot Row. Drag and drop the 'Expense Month' field from the Pivot Table dialog box to the Pivot Column and finally drag drop the 'Voucher Amount' into the Pivot Data Centre.

 

The pivot result will give you a cross dimensional month wise trend table of various expenses. Using Pivot Statistics you can either look at -

 

- Sum of Expenses

 

- Max of Expenses

 

- Min of Expenses

 

(d) The Pivot Result can be exported to either MS-EXCEL or as a separate IDEA database child file too using Send To in the Pivot Result toolbar.

 

(e) Finally the Pivot Result exported as an IDEA database can be captured as a CHART with Expense Description (for expenses under scrutiny) under X-Axis and Month under Y-Axis for better visualization of expense outliers.

 

The above can be applied for any form of trend analysis like -

 

Buy Rate Trending

 

Sell Rate Trending

 

Interest Rate Trending

 

Accounts Receivable Trending

 

etc

 

Kind Regards

 

Jairam