Skip to main content

Data

 

Step-1: import data

Go to data tab in excel and get data from Table/Range

Step-2: Look for and remove duplicate entries

Select the user ID column and highlight the duplicate entries using conditional formatting in the Home tab

Filter out the highlighted entries in User ID column by using the sort by colour option under the filter

Now you can see the duplicate entries which are highlighted

Copy these duplicate entries to separate sheet which can be used as an appendix to the report

Now go to table design tab and ,click on remove duplicate entries at the top left. This will remove the duplicate entries

Step-3: Insert the Pivot table

Create a pivot table by clicking on “summarise with pivot table” under table design tab

A pivot table will appear on a new sheet in the excel file

Step-4: Document Check analysis

On the pivot table you can see all the data fields and four areas 1. Filters, 2. Column 3. Rows 4. Values

Add the result field in the rows area and user id field in the values area

You can see the total number of clear and consider (rejected) applications. You can copy this data and transform this into desired presentation

Step-5: Document Check analysis

To analyse the visual authenticity result,  Add the visual authenticity result field in the columns area, Sub-result field in rows area and user id field in the values area

You can see the visual authenticity result entries in clear and rejected and blank categories along with the sub result categories of, caution, clear, rejected and suspended entries.

You can copy this data and transform this into desired presentation.

Repeat above step for all the other checks for example image integrity result, face detection result, image quality result, created at     supported document result, conclusive document quality result, colour picture result, data validation result, data consistency result, data comparison result.  etc

Step-6: Month wise analysis

On the pivot table you can see all the data fields and four areas 1. Filters, 2. Column 3. Rows 4. Values

Add the result field in the filters area, Creation date field in column area and user id field in the values area

Now you can filter the result to only select consider (rejected) fields

You can see the month wise entries of rejected applications. You can copy this data and transform this into desired presentation

Step-7: Gender wise and document type analysis

To analyse the gender wise and document type data, go to properties column in the excel sheet, select the filter on properties column and write female in text filers. Now you can see all the entries of female applicants. Now go to result column and filter the data of rejected entries by selecting the consider. This will show the rejected entries of female applicants. You can count and record the total and rejected entries on a separate sheet with your desired presentation

You can apply the same filters for male, and document type such as passport, national identity card etc.

Comments

Post a Comment

Popular posts from this blog

Chapter 5: Finding Balance in the Unbalanced

  As Mark settled into his role as an auditor, he quickly realized that finding a balance between the demanding nature of his job and his personal life was a constant tightrope walk. The thrill of uncovering financial mysteries was exhilarating, but the long hours and looming deadlines often left him feeling stretched thin. It was a challenge he was determined to conquer. The turning point came during one particularly gruelling week. Mark had been burning the midnight oil, reviewing financial statements and preparing for a crucial client meeting. His phone buzzed with a text message from his daughter's school—a reminder of her school play scheduled for the following afternoon. Mark's heart sank. He had promised to be there, but the looming deadlines seemed insurmountable. As he sat in his dimly lit office, Mark pondered his options. He could continue pushing himself to meet the deadline, sacrificing his promise to his daughter, or he could find a way to manage his time more...
Post a Comment
Read more

Echoes of the Balance Sheet

<!-- Ezoic Code --> <script src="//www.ezojs.com/basicads.js?d=auditor-story.blogspot.com" type="text/javascript"></script> <!-- Ezoic Code --> In the bustling heart of the corporate world, where numbers reigned supreme and financial intricacies wove a tapestry of profit and loss, there existed a realm less explored—a world of auditors, those unsung heroes who delved deep into the labyrinthine spreadsheets to unveil truths hidden within the columns and rows. "Life of an Auditor" is a journey into this lesser-known sphere, where the stories of individuals come to life against the backdrop of annual reports and balance sheets. In the pages that follow, you will encounter Mark, an auditor with a knack for humor, an appreciation for precision, and a determination to seek the extraordinary within the mundane. From the halls of the Big 4 audit firms to the intricacies of client interactions, from the tension-laden meeting rooms to the...
Post a Comment
Read more

Chapter 4: Beacon of Honesty and Diligence

Mark had always been meticulous and detail-oriented, qualities that served him well in his role as an auditor at a prominent Big 4 firm. He approached each audit with a dedication to uncovering the truth behind the numbers, knowing that his work had a direct impact on the financial health of the organizations he served. One day, Mark was assigned to lead an audit for a client in the manufacturing industry. The client was a well-established company with a history of consistent financial performance. As Mark delved into the financial statements and conducted his analyses, everything seemed to be in order. However, there was a nagging feeling in the back of his mind—a sense that something was amiss. As Mark continued to dig deeper, he began to notice subtle inconsistencies. The revenue figures for the past year appeared to be higher than what industry benchmarks would suggest, given the economic conditions. The expenses also seemed unusually low, raising questions about the company...
Post a Comment
Read more