Dashboard is a powerful tool for stakeholders to quickly assess the data.

A visual dashboard was created in MS Excel for a risk control matrix of an organisation. The risk control matrix had various segments like advances, deposits, derivates, treasury, etc whose testing had to be performed by us. The risks were categorised into three categories viz. high, medium and low levels on the basis of our assessment and the pass/fail status was also mentioned in the sheet as per the audit procedures performed and the evidence so obtained.

Rather than building the dashboard manually and editing the charts to make them visually appealing for each individual sheet (which would have been time-consuming), I used AI to automate the task to dynamically read the data and then generate clear & easy-to-read charts-both for a particular segment (like advances alone) and for the entire portfolio (i.e the entire workbook). The charts thus represented the distribution of risk (High, medium and low levels) and compliance levels (Pass/Fail) for each sheet & the entire workbook.

The backend job was done using VBA and AI was used to leverage that.

The prompts were given to Chatgpt to create the VBA code with instructions like -

a) The risk control matrix is in MS Excel and a macro is to be created to show a pie chart showcasing how many tests have passed/failed (test results being shown in Column G of the sheet).

b) Further, a line chart needs to be created to showcase the number of high/medium and low risks (shown in Column F).

c) Colour, height, font size, placement specifications. 

d) Add a button in each sheet to link it to macro so created. Name of the button to be “Generate Risk Charts”.

e) The message should pop up-“All charts successfully created”.

f) Also create a VBA code to create a button to create a button for the individual worksheets and the button name should be “Create Chart" .

The buttons so created in the sheet were made to allow the user to simply click on the same to generate the charts for that segment/all segments all at once.

Thus, the management of the organisation was able to gauge the risk and compliance status without needing to dig through the raw data.

Screenshot 1 of the interface of the buttons created:-