Total Spend Analytic

The total spend analytic was made by taking a dump of the general ledger for the preceding financial year. For a spend of 5.6 bn INR the system dump lead to a file running to 80MB. This looked like a dead end as Microsoft Excel or for that matter any other spreadsheet software is limited by the number of rows that it can handle. We therefore had to find an innovative way to extract only the relevant data and get it in a presentable format. To get over this we first had to identify what was it that we needed? All we needed from the general ledger is the department wise division wise category wise spend. The date of spend would also have been a valuable data entry to track the movement of spend. However, the resources in place were limited and therefore we developed a C program which would sum up each category of spend over the entire year. This way though we would not be able to track the movement of the spend over a time frame. We still had a consolidated spend of the entire year for each category in a much more manageable size. A point worth noting here is that we had used the C program not because it offered us some advantages over any other programming language but because I as a Mechanical Engineer had learnt only the basics of C about 8 years back. I was conversant with only this language and therefore we used the same. I’am sure that there maybe better ways of doing the same and also having the spend movement tracking. (Please check footnote to find why spend tracking is essential)

The 80 MB file broken into a text file of 1.7 MB was manageable. This sheet went in as the data sheet classified in the heads shown below:

Ø Division
Ø Department
Ø Description of Spend
Ø Level of Influence
Ø Actual Spend

While the Division, Department, Description of Spend & Actual Spend was had from the dump itself the level of influence we can have on each of the spends was input manually by sorting the spend on the basis of the description. In order to throw up the actual spends for the preceding year, under different combinations of Department, Division, Description and Level of Influence we took the aid of macros and pivot tables. To clarify the working of the spreadsheet I would quote an example.

Selecting the marketing division only would allow me to evaluate the total spend of the different marketing departments under different categories like POP Print, POP Gift, Events etc… Selecting a particular department under marketing would give me the spend of the same department under various categories like POP Print & Gift etc. Selecting the category of spend only would give the spend of the entire organization and also under different departments. Thus enabling us to have a macro picture of the spends.
This tool would allow anybody to identify spends across any of the heads under any combination desired. While this tool enabled us to identify and classify the spends we needed a further drill down. To enable further drill down we developed two more tools which I have mentioned above and will be describing below.