• Home
  • About Us
  • Services
    • Training
    • Consulting
    • HNI Family Back Office
  • Success Stories
  • Events
  • Testimonials
  • Blog
  • Contact Us
Data Analytics in Excel – Training & Consulting Data Analytics in Excel – Training & Consulting
  • Home
  • About Us
  • Services
    • Training
      • Business Analytics
      • Assurance Analytics
      • Forensic Accounting
    • Consulting
  • Success Stories
  • Events
  • Testimonials
  • Blog
  • Contact Us


OUR CLIENTS RESPONSE
TO OUR EFFORTS.

HERE ARE A FEW OF OUR CLIENT'S SUCCESS STORIES...
Empty section. Edit page to add content here.
Integrated Risk Rating & Reporting Model

The Internal audit team of a large private sector bank, having over 300 branches across India, conducted audit of its retail liabilities branches as per the audit plan. Although the members of the audit team worked hard to complete assignments within given time frame, the Sr. Vice President Internal audit firmly believed that the quality of audits could be significantly improved and approached us for the same. Of the numerous issues faced by them, key issues were identified viz.:

  • Inconsistent approach of the audit team in audit of the same processes at different RL branches.

  • Non-implementation of a mathematical risk rating model.

  • Rating RL branches largely based on audit managers’ perception (that varied from manager to manager based on experience and other subjective factors).

  • Humongous amount of time spent on compiling audit reports after the assignment.
    Using their existing audit check-lists and questionnaires, we developed a risk-rating model that prescribed standard data driven analytical processes for audit of every material process within RL. This risk rating model also accepted the results of the analytical processes and other observations of the auditors as inputs. And on a click of button, it prepared a complete audit report with Executive summary, detailed report and annexures in PDF Format, ready to print!!

Thus with this integrated Risk rating and reporting model, the clients were not only able to implement consistent data driven audit processes across all locations and rate branches objectively but were also successful in saving humongous time in compiling reports after the assignment. Needless to say, the entire integrated risk rating and reporting model was developed on MS Excel / VBA platform!!

Implementing statistical sampling

A large audit firm, auditing several of the largest privately owned companies in India sought our advice on how it can maintain its audit engagement risk within a predetermined and acceptable level. Our trainings on Assurance Analytics with MS Excel helped them to implement statistical sampling and use Binomial distribution to determine the sample size, set tolerable rate of misstatement, expected population exception rate and confidence level. Further various sampling techniques like Random Sampling, Systematic Sampling, Probability proportional to size Sampling, enabled the auditors to draw statistical samples and to determine how representative were the samples to the population, and finally extrapolate the results of evaluation of sample to the population to manage audit risk involved in the attestation.

Financial Projections Model

One of our clients have several hotels all over the world that are financed mostly by bank loans. For taking new loans (for building / acquiring new hotels) from bank and to comply with the requirement conditions of the existing loans, our client had to submit its project financial statements and cash flow statement for the current year and further 10 years to the bank.

Preparing a forecast of financial statements and cash flow statement requires consideration of several factors, estimates etc which have a tendency to change year on year. Eg: growth rate, tax, expansion of hotels, change in staff.

By considering these variables, the clients team used to prepare the projected financial and cash flow statements for current and future 10 years by adjusting year on year figures according to the changing variables. This was a herculean task in itself as it used to take several days to complete and was prone to human error. Then again, checking and reviewing these projected data was another daunting task for the management.
Development and deployment of excel model:

Our team developed two integrated excel models.

  • For calculating and preparing 10 years projected financials for n number of hotels.

  • To combine the projections from independent hotels and make a consolidated financials.

The model was prepared after understanding clients exact needs and researching the earlier projections made by the client during last several years.

This model had just one input sheet, where the client needs to feed in the variables data i.e. say,  the financial data from the last audited accounts, the expected growth rate of the company, expected extension/expansion in hotel rooms etc. i.e. information which are available with the client.

Based on the inputs given by the user, the model then automatically calculated all the fields and prepared the projected financial statements and cash flow statements for next 10 years.These projections calculated by excel model were made in the precise format that were required by the bank. This excel model could be used to prepare projections for n number of hotels and to merge the figures of these individual hotels and prepare a consolidated projected report.

Our client submits these resulting excel sheets (projections) to the bank for loan purposes and also for internal studies.

Impact:
Before the introduction of the model, the client used spend 8-10 days in perfecting and reviewing the projections.
The work is now handled by the Excel model in matter of minutes!!

Implementation of Benford’s law

Empowered with the knowledge of concepts and implementation of Benford’s law (a module that forms part of training program on Assurance and Forensics),an auditor implemented Benford’s law analysis on interest credited to all savings bank account holders’ account. He was surprised to note the unreasonably high rate of occurrence of the digit “0” in the second and third position in the numbers representing amounts credited as interest in hundreds of thousands of saving bank accounts. He was at wit’s end on why the rate of occurrence of “0” did not match with the rate of occurrence as suggested by Benford’ law. Upon rigorous follow-up with the IT team in the bank, he was able to trace the root cause as an error in the algorithm that calculated the interest. That was a big one!! Imagine identifying error in the algorithm that calculated incorrect interest and credited to the account of hundreds of thousands of account holders!! Thank you Mr. Benford!!!

…AND CLICK HERE TO SCRIPT YOURS

Contact Info

  • Dafi
  • 1056, Hubtown Solaris, N.S.Phadke Road, Andheri - East, Mumbai
  • 09967770040 / 022 623 62 555
  • info@www.dafi.in
Privacy Policy I Anti-Spam policy

Fresh from blog

  • Past Event – Training on Business Analytics & Data Visualization
  • Past Event – Training on Business Analytics & Data Visualization
  • Past Event – Training on Forensic Accounting Insights
  • Past Event – Training on Data Analysis with MS Excel for Beginner to Pro. Level.
  • Past Event – Training on Detecting & Investigating Financial Fraud
  • Past Event – Training on Forensic Accounting and Fraud Detection

Recent Work

    Categories

    • update

    Latest Posts

    Past Event – Training on Business Analytics & Data Visualization

    November 18, 2023

    Training on Business Analytics & Data Visualization...

    Past Event – Training on Business Analytics & Data Visualization

    Past Event – Training on Business Analytics & Data Visualization

    October 20, 2023

    Training on Business Analytics & Data Visualization...

    Past Event – Training on Forensic Accounting Insights

    June 17, 2021

    Training on Forensic Accounting Insights – CBI, Mumbai...

    Video Widget

    Dribble Widget

    DAFI- All rights reserved

    Built by Brij Design Studio