Five Hacks to Perform A Forensic Audit in Excel

 I am jotting 5 excel quick hacks to perform Forensic Audit in Excel envoronment.

1) Identifying Duplicate Transactions using Highlight Values:

Consider the following instance where we have a supplier name or code, supplier invoice number and the amount. There is a chance that one might have a duplicate amount number or duplicate invoice number for different suppliers – but what if a pair of records have exactly the same data for all the three parameters?  This is a definite case of duplicate invoices existing:

In the above example we have produced the column D by concatenating the data in the first three columns and then applying the conditional formatting for duplicate values.  (The exact formula is =A2&B2&C2 in cell D2 and then dragged down, the conditional formatting on cell D2:D13 can be applied by accessing Highlight Values Menu from Home Tab.)

As you can see from this screenshot, various duplicates are highlighted in the second and the third column as well as the fourth column.  We can ignore the duplicates in the second and third column as invoice number and amount could be the same for different suppliers – but the duplicate value in the fourth column means that all the three parameters are the same.  This points to a definite duplicate entry that should be checked.

2) Analyzing Round Numbered Transactions:

If a forensic accountant is getting too many round numbers, it is time to check the accuracy of the document or ask for further support.  Too many round numbers may indicate something fraudulent is occurring.

Excel has a built in function called MOD () to analyze if a number is a round number or not.  The dynamics of the formula are simple – we add the following formula in the example sheet to find if the number has to be checked for something potentially fraudulent:

=IF(MOD(A2,100)=0,”Check”,”-“)

The formula will return a non-zero number and the result can be presented as-is or nested into an IF() statement to give the desired result:

3) Above Average Payments to Vendors or Checking the Ratio between a Maximum and Minimum:

For a product, prices usually fall within historical ranges.  If anything goes beyond the average, the entry can be question and should be verified.  Consider data in the following table that has price listed for two different products.  These products are bought over a period of six months and their prices vary between a minimum of 11.01 and a maximum of 14, whereas the ratio of the max/min is 1.272.  Compared to this, if we look at the second product, we find this ratio to be 2.9 times or almost 3 times.  This points to a discrepancy.

4) Gap Detection:

This is a test that uses a simple fact that any missing invoice (number) in the sales database can indicate a potential problem.  This test can be done for such a document and help drive for further inquiry of the preparer.

In excel we can do this test by first sorting the invoice number and then taking the difference of consecutive invoices.  The following screenshot how the excel sheet is setup for this case:

In the above example, we have setup a formula that calculate the difference between the two consecutive invoice numbers, if it is one, then its okay and we move forward.  If not, it means that an invoice number was skipped – we can then write a conditional formula with IF() that can give us a custom message like this:

=IF(A3-A2<>1,A3-A2-1&” – Invoice(s) Missing”,”-“)

The above formula checks if the difference is one, if not, we have a custom message that “n-invoices are missing”.  Otherwise, we will have a blank cell.

5) Checking Ratio of the Highest to the Second Highest Number:

This is again a procedure that relies on the use of finding maximum, but not just maximum but also the second largest number in the data.

Consider the following data for a part purchased from a supplier:

We can use build in function MAX() and LARGE() to find the maximum and the second largest value in the data.  As we take the ratio of the data, we find it to be 10 times the maximum number. This means that maximum for the data is much larger than the usual average value.

Conclusion:

We cannot eliminate mistakes or “fudging” in the financial data however we can positively try to minimize it.  We have seen five techniques that can be applied using a standard tool like MS Excel for tracing such issues.  Please download the attached file to see it working practically.

 

Ways in which a Foreign Investor can invest in India

FDI - Foreign Direct Investment has been a major contributor to the Indian economy. There are an increasing number of foreign investors who are very interested in investing in the country as they are aware of the potential of Indian companies and human resources.

 

India is today’s time has been attracting heaving foreign investments due to its promising market size, natural resources, and skilled low-wage labor. The various foreign investors want to make the best use of the Indian resources for their profit and in turn major development is happening in India due to foreign direct investment.

 

However, the path of foreign investment in India is not very clear to many investors, hence in this blog I will be covering the essential points in regard to this.

 

Before making foreign investments in India it is vital to understand the foreign policy guidelines that have been laid down by the government of India from time to time because amendments are made to them. 

 

There are two routes through which foreign direct investments can be made in India and that is the

a)     government route and

b)     automatic route.

 

Through the government route, there are certain sectors in which the government of India has put restrictions for foreign investments that only after the approval from the government, investments in that sector can be done by the investors.

Through the automatic route, there are some sectors in which the government has allowed that no government approval is required for investments.

 

There are foreign portfolio investments that can be made by foreign investors in India. The Portfolio Investment Scheme (PIS) allows the eligible foreign organizations for the investments in the share and convertible debentures of the various Indian organizations, units of domestic mutual funds, or Indian stock exchanges.

[The list of eligible foreign institutions includes foreign institutional investors (FIIs), non-resident Indians (NRIs), persons of Indian origin (PIOs) and qualified foreign investors (QFIs).]

 

FII 

The Foreign Institutional Investors (FII) is the organizations that are based abroad but they are willing to invest in the securities in India. Therefore, for such investors, the RBI gives permission to get them registered with SEBI first and the SEBI registers FII can make investments in the Indian market through the PIS route.

 

NRIs

The Non-resident Indians can also make Investments in India through the buying and selling of shares, convertible debentures via a registered stockbroker on a registered stock exchange. It is essential to follow the guidelines of the stock exchange market and be registered only with a registered broker. NRI’s are also given an allowance by the Indian government for the buying and selling of mutual funds units in India. The NRIs and FIIs can make investments in government securities, treasury bills, listed nonconvertible debentures, bonds, commercial papers released by Indian companies and units of domestic mutual funds in accordance with the restrictions that are imposed by RBI.

 

QFI 

QFI is the individuals and organizations that are a member of the Financial Action Task Force (FATF) and they are given the allowance of investing in the mutual funds, equity shares, and government/corporate bonds.

 

Foreign venture capital investors 

Foreign venture capital investors can make investments in a domestic venture capital fund or venture capital undertaking. It is essential for the foreign venture capital investors to undertake separate registration from SEBI and their investments must be of 66.67% of the investible funds in unlisted equity shares/equity-linked instruments of an Indian venture capital undertaking.

BIG Gold Fraud by Chinese Company

Over the past decade, China has emerged as the world's biggest counterfeiter of various, mostly industrial metals used to secure bank loans, infamously called "ghost collateral", and often several banks would have claims to the same (fake) asset. In a recent development, which would spark a brief wave of outrage among physical gold holders, China's Wuhan-based Kingold Jewelry Inc has been accused of depositing fake gold bars as collateral to obtain loan worth 20 billion yuan ($2.8 billion) from 14 Chinese financial institutions, mostly trust companies (also known as shadow banks), over the past five years, as per a report in Zero Hedge.

Considered to be one of the biggest gold counterfeiting scandals in recent history, the scam not only involves China, but it emerges from Wuhan city, the capital of Hubei Province, that has become synonymous for all that is scandalous about the country.

Listed on Nasdaq stock exchange, Kingold Jewelry is one of the world's largest privately owned gold processor with a current market cap of just $8 million. The company is led by Chairman Jia Zhihong, an intimidating ex-military man who is the controlling stakeholder in the company.

As per the report, the company used 83 tonnes of gold bars as collateral and insurance policies to cover any losses but many of them have turned out to be gilded copper. This has left creditors holding the collateral for the remaining 16 billion yuan of loans outstanding against the fake gold bars.

The loans were covered by 30 billion yuan of property insurance policies issued by Chinese insurer PICC Property and Casualty Co. Ltd. (PICC P&C) and other smaller insurers, it said.

The scam came to light in February this year when Kingold defaulted on loans to Dongguan Trust Co. Ltd. (a Chinese shadow bank). Dongguan Trust said it found that the gold bars that were pledged as collateral turned out to be gilded copper alloy. The news spooked Kingold's creditors.

Following Dongguan Trust, China Minsheng Trust, one of Kingold's biggest creditors, obtained a court order to test collateral before Kingold's debts came due. The test result, which came on May 22, said the bars sealed in Minsheng Trust's coffers were also copper alloy.

Authorities have already begun investigations into securities fraud. Meanwhile, Kingold chief Jia Zhihong, known as "an intimidating ex-military man", has denied that the company lodged fake bars with Chinese lenders. Jia has served in the military in Wuhan and Guangzhou.

Established in 2002 by Jia, Kingold was previously a gold factory in Hubei affiliated with the People's Bank of China that was split off from the central bank during restructuring. The company's shares are listed on the Nasdaq stock exchange.

According to Trading Economics, China ranks sixth in terms of total gold reserves with total reserves of 1,948.30 tonnes as on March 31, 2020. The US leads the country list with total gold reserves of 8,134 tonnes followed by Germany and Italy with 3,364 tonnes and 2,452 tonnes, respectively. India has also entered into the list of top ten countries with gold reserves of 642 tonnes, which is ninth highest in the world.

Reproduced work of Chitranjan Kumar for Business Today on 01.07.2020

'The money's gone': Wirecard collapses owing $4 billion

FRANKFURT (Reuters) - Wirecard collapsed on Thursday owing creditors almost $4 billion after disclosing a gaping hole in its books that its auditor EY said was the result of a sophisticated global fraud.

The payments company filed for insolvency at a Munich court saying that, with 1.3 billion euros ($1.5 billion) of loans due within a week its survival as a going concern was "not assured".

Wirecard's implosion came just seven days after EY, its auditor for more than a decade, refused to sign off on the 2019 accounts, forcing out Chief Executive Markus Braun and leading it to admit that $2.1 billion of its cash probably didn't exist.

"There are clear indications that this was an elaborate and sophisticated fraud involving multiple parties around the world," EY said in a statement.

EY said while it was completing the 2019 audit, it was provided with false confirmations with regard to escrow accounts and reported them to the relevant authorities.

Wirecard declined to comment following EY's statement.

The financial technology company is the first member of Germany's prestigious DAX stock index to go bust, barely two years after winning a spot among the country's top 30 listed companies with a market valuation of $28 billion.

"The Wirecard case damages corporate Germany. It should be a wake-up call for reforms," said Volker Potthoff, chairman of corporate governance think-tank ArMID.

Creditors have scant hope of getting back the 3.5 billion euros they are owed, sources familiar with the matter said. Of that total, Wirecard has borrowed 1.75 billion from 15 banks and issued 500 million in bonds.

"The money's gone," said one banker. "We may recoup a few euros in a couple of years but will write off the loan now."

'TOTAL DISASTER'

The collapse of Wirecard, once one of the hottest fintech companies in Europe, dwarfs other German corporate failures. It has shaken the country's financial establishment with Felix Hufeld, head of regulator BaFin, calling it a "total disaster".

German Finance Minister Olaf Scholz described the collapse as a "scandal", acknowledging it was time to review regulation.

"We must rethink our supervisory structures," said Scholz, adding he had asked his ministry to come up with ideas in the next few days.

"If legal, legislative, regulatory measures are needed, we will embrace them and implement them," he said. "A scandal like Wirecard is a wake-up call that we need more monitoring and oversight than we have today," he said.

Wirecard shares, which were suspended ahead of an earlier announcement that it would seek creditor protection, crashed 80% when trading resumed. They have lost 98% since auditor EY questioned its accounts last Thursday.

EY, one of the world's "Big Four" accountancy and consulting firms, faces a wave of litigation in a debacle that has drawn comparisons with Arthur Andersen's disastrous oversight of U.S. energy company Enron.

German law firm Schirp & Partner said that with Wirecard now effectively sidelined, it would file class actions against EY on behalf of shareholders and bondholders.

"It is frightening how long Wirecard AG was able to operate without being objected to by the auditors," partner Wolfgang Schirp said.

Wirecard's new management had been in crisis talks with creditors but pulled out on Thursday morning "due to impending insolvency and over-indebtedness".

The insolvency filing did not include its Wirecard Bank subsidiary, which holds an estimated 1.4 billion euros in deposits and is already under emergency management by BaFin.

'COMPLETE VINDICATION'

A second source close to talks with creditors said although the company had a healthy core, it had faked two-thirds of its sales. This meant there was no way it could repay all its debt, notwithstanding all the legal challenges it will face.

The ascent of Wirecard, which was founded in 1999 and is based in a Munich suburb, was dogged by allegations from whistleblowers, reporters and speculators that its revenue and profits had been pumped up through fake transactions.

Braun fended off the critics for years before finally calling in outside auditor KPMG late last year to run an independent investigation.

KPMG, which published its findings in April, was unable to verify 1 billion euros in cash balances, questioned Wirecard's acquisition accounting and said it could not trace hundreds of millions in cash advances to merchants.

"Today is a complete vindication for those that exposed the fraud," said Fraser Perring, who bet on a fall in Wirecard's shares and co-authored a 2016 report that alleged fraud.

The Munich prosecutor's office, which is investigating Braun on suspicion of misrepresenting Wirecard's accounts and of market manipulation, said: "We will now look at all possible criminal offences."

Braun was arrested on Monday and released on bail of 5 million euros a day later. Former chief operating officer Jan Marsalek is also under suspicion and believed to be in the Philippines, according to justice officials there.

($1 = 0.8903 euros)

By Arno Schuetze and John O'Donnell

(Additional reporting by Joern Poltz, Hans Seidenstuecker, Edward Taylor, Madeline Chambers; Writing by Douglas Busvine; Editing by Maria Sheahan and David Clarke)

Dhoni faces tough deliveries in Amrapali Mess

This web site uses cookies to ensure you get the best experience. Privacy Policy

OK

Even as MS Dhoni has maintained that he was merely a brand ambassador of the Amrapali Group, filings made by the Delhi-based real estate company with the Registrar of Companies, when viewed alongside Tuesday's Supreme Court judgment, point to a more complex relationship between the former India captain and the beleaguered company now indicted for fraud. Dhoni's wife Sakshi Singh Dhoni was a director and a 25 percent shareholder in a group company called Amrapali Mahi Developers Pvt Ltd (AMDPL), where Amrapali Group CMD Anil Kumar Sharma held the remaining 75 percent stake (till September 2014, as per records available). AMDPL, according to the findings of a forensic audit on the Amrapali Group submitted to the Supreme Court, was revealed to be among the 47 Amrapali group companies that allegedly received homebuyers fund diverted by the real estate group to its subsidiaries. The audit report alleged that homebuyers funds amounting to Rs 5,619 crore were diverted by Amrapali Group to these companies. The report that listed AMDPL as one of the recipients of the homebuyers funds, stated that it received share capital in cash and all the expenses were paid in cash. While the company was incorporated in December 2011, RoC filings of AMDPL do not reveal any operations, revenue and profit for the three years FY 12, FY 13 and FY 14, for which data is available. The audit report said We are informed verbally that this company was incorporated for development of a project in Ranchi. An MoU was also entered between the parties though we were not provided a copy of that. Faced with homebuyers ire, Dhoni disassociated himself with the Amrapali group in 2016. Three years down the line, the cricketer approached the Supreme Court in March this year seeking its intervention to get Rs 40 crore from the Amrapali group for using his services for branding and marketing activities for six years until 2016. Taking note of the audit findings, the Supreme Court had indicted the firm Tuesday: In view of the findings recorded by the Forensic Auditors and fraud unearthed, indicating prima facie violation of the FEMA and other fraudulent activities, money laundering, we direct Enforcement Directorate and concerned authorities to investigate and fix liability on persons responsible for such violation and submit the progress report in the Court and let the police also submit the report of the investigation made by them so far.  The auditors noted that another Amrapali Group company, Amrapali Sapphire Developers Private Limited, paid a sum of Rs 6.52 crore out of the total amount of Rs 42.22 crore paid from the Amrapali Group of Companies to Rhiti Sports Management Private Limited during the years 2009 – 2015. While Rhiti Sports is a company that manages Dhoni's sponsorships and endorsements, the audit report said that Amrapali Group and Rhiti Sports Management made agreements on plain paper (which were) executed only between Amrapali and Rhiti Sports Management Private Limited. Pointing out that there was no resolution attached in favor of Arun Pandey, Signatory of Rhiti Sports Management Private Limited, the audit report said, This clearly shows that these Agreements have just been made for payment of amounts to Rhiti Sports Management Private Limited Company..(and) are sham agreements…We feel that Home Buyers money has been diverted illegally and wrongly to Rhiti Sports Management Private Limited and should be recovered from them as the said Agreement in our opinion do not stand the test of Law.  While Rhiti Group did not respond on a specific query on Sakshi Dhoni's shareholding in AMDPL and the company being a recipient of diverted homebuyers funds, it said: We would only like to clarify that the observations mentioned in the forensic report (only) are bereft of proper information or relevant documents. The question of siphoning funds does not arise because Rhiti provided all professional services as per the agreements and the then pre-agreed endorsement fee received from Amrapali was paid to relevant endorsing stars and celebrities.. In fact, we have legitimate business claims of approximately Rs 40 crore against the Amrapali group and towards the recovery of the same. Stating that Rhiti was cooperative during the forensic audit and furnished all sought information/documents, it said that Rhiti was neither asked to provide any further documents or information nor the issues raised in the report like authority of Mr Anil Sharma (CMD, Amrapali) or authorized signatory of Rhiti were ever raised to us to enable us to satisfy the said auditors. Rhiti, in response to queries sent by The Indian Express, also said that it is currently seeking legal advice and would accordingly take further steps if so advised.

- The Indian Express. 25.07.2019

Fake Link - Income Tax Refund Fraud

Today one of my clients received an SMS wherein they need to respond for Income Tax Refund. Instantly SMS was sent to me for further needful. As I am aware of the client's Income Tax Refund status, I advised not to respond and I will verify the same.

After due diligence, I thought to publish the report so that some innocent people may be saved from this Large Scam.

Why I had doubts:

1) I am aware of Income Tax status of the client.
2) Income Tax never sends SMS to upload bank details straight away.
3) Link of the same was not of the authentic website of Indian Income Tax Department.
4) It was received from a commercial platform (BZ-ITDEFL) and not a Government one.

Observation:
What I observed was that the Income Tax Web Site welcome page was apparently copied with minute flaws (viz. absence of live ticker, absence of employee's login etc.) which is highly possible to be overlooked.

I am attaching a screenshot of both FAKE and ORIGINAL webpage for better comparison.

Modus Operandi:
The client enters the required bank account information along with their phone number. Subsequently, with a fake FIR fraudster manages to procure duplicate SIM and then apply for forget password for net banking. 
Wollah...... They have all that was required. 
By the time the victim is aware of fraud, their fate is SEALED. 


Take Away:

1) Don't get Lured:
Fraudster always serves you Free. In this case, it was an Income Tax Refund. So don't get lured. Know your eligibility in the first place.

2) Check for Authenticate Link:
Check for the authenticate links. The link must contain the original name of the web owner in the first place, followed by a hyphen and then suffixes. 

3) Consult Professional:
In case of Refund or any legal/financial matter, connect to your financial advisor and get the update before self-motivated moves.

4) NEVER Share Confidential Information
Except in IVR voice recording system, do not share any information. Even account numbers and Phone numbers to anyone.

Trust this comes informative to many.
Thanks for reading.

Bank Fraud: Court convicts CA for Life imprisonment

A special court last day (26.04.2019) had convicted six persons including a chartered accountant, a branch head of a public sector bank to life imprisonment for allegedly committing fraud by taking a loan of Rs 1.5 crore on false documents from the bank. 

While Manoharlal Ahuja, Amit Ahuja, Mahesh Bohra, Sandesh Nage, Shantilal Chauhan and Bhagwan Das Joshi were sentenced to life imprisonment, another accused Yunus Memon was handed a three-year jail term. While the six accused were fined Rs 1.53 crore each, Memon was asked to pay a fine of over Rs 4 lakh. 

The loan was availed by father-son duo Manoharlal and Amit by Bank of India’s Mandvi branch between 2000 and 2003 for business purposes and daily running of their partnership firm, Swift Service Lining. The two men, who also have other similar cases registered against them, had also taken Letters of Credit worth Rs 1 crore from the bank, against the firm’s stocks and booked debts. 

According to the prosecution led by J K Sharma and Rakesh Bhatnagar, the Ahujas submitted documents pertaining to balance sheets and income tax as well as the titled deed of an open plot located in Versova valued at Rs 3 crore at the time of applying for the loan. The prosecution alleged that the open plot did not exist and a forged title deed was submitted as collateral. 

It added that Bohra, a chartered accountant by profession, gave a false certificate stating that the firm is in a good financial condition by forging its balance sheets. He also prepared false documents for the non-existing open plot. Bohra also received the proceeds and opened an account in the name of shell companies maintained in various public sector banks. 

In the year 2004, when the firm became a non-performing asset, the bank found out that the property could not be mortgaged and that the documents were forged. The Ahujas had also claimed that the stocks and book debts had been destroyed in a fire in their godown.

Extracts from: https://www.taxscan.in/bank-fraud-special-court-ca/35356/?fbclid=IwAR3TX5jfgo7fvecsEpGVoC6ACw66ou2l8I3BXOZkKtofCJQngkCc-MMSGkE