ACT558 Fraud and Forensic Accounting Data Analysis Week one CT1

/ Uncategorized / By

Description

Option #1: Fraud Examination Casebook with Documents Textbook: Case 4 – Exercises 1 and 3
In your Fraud Examination Casebook with Documents textbook, read Case 4: Using Data Analytics: Analyzing and Summarizing Data with Excel Pivot (pp. 61-77).
Perform Exercise 1: Excel Pivot (Checks and Debit Card Disbursements – Individual Assignment) on pages 77-79 of the Casebook.
Also perform Exercise 3: Excel Pivot (Scrubbing) on page 81 of the Casebook.
Submit your completed Excel Pivot file for inclusion in the fraud examiner’s/forensic accountant’s report.TONYA LARSEN EDITS TO DEPOSITS JANUARY TO MARCH 2016
TRANSACTION DESCRIPTION FROM MEDATRIX
ULTRASCAN
V = “√” vouched edit total to deposit
ticket
ORIG.
EDIT
V
MONTHLY
TOTALS
DIFF
January
$
–
$
February
–
TONYA LARSEN EDITS TO DEPOSITS JANUARY TO MARCH 2016
V = “√” vouched edit total to deposit
ticket
TRANSACTION DESCRIPTION FROM MEDATRIX
ULTRASCAN
ORIG.
EDIT
V
MONTHLY
TOTALS
DIFF
March
$
–
$
–
$
–
$
–
$
–
TONYA LARSEN EDITS TO DEPOSITS JANUARY TO MARCH 2016
TRANSACTION DESCRIPTION FROM MEDATRIX
ULTRASCAN
V = “√” vouched edit total to deposit
ticket
ORIG.
EDIT
V
DIFF
MONTHLY
TOTALS
Financial Statement Fraud, Exercise #1 – Template
From Schedules C (AS REPORTED)
2013
%
2014
%
2015
Revenue
Fuel revenue
Beginning inventory
+ Purchases
= Ending inventory
Cost of fuel
Gross profit
Groceries/food revenue
Beginning inventory
+ Purchases
= Ending inventory
Cost of groceries
Gross profit
Beverages revenue
Beginning inventory
+ Purchases
= Ending inventory
Cost of beverages
Gross profit
Tobacco revenue
Beginning inventory
+ Purchases
= Ending inventory
Cost of tobacco
Gross profit
Print media revenue
Beginning inventory
+ Purchases
= Ending inventory
Cost of print media
Gross profit
Bank ATM rents
Beginning inventory
+ Purchases
= Ending inventory
Cost of bank ATM rents
Gross profit
Total revenue
Beginning inventory
+ Purchases
= Ending inventory
Total cost of goods sold
Total gross profit
Expenses
Administration fee
Advertisement
Cash over/short
Credit card fees
Rent expense
Equipment station repair
Expense Items
Insurance
NSF checks expense
Payroll tax expense
%
REVISED based on
Estimated Ending Inventory
2015
%
Financial Statement Fraud, Exercise #1 – Template
From Schedules C (AS REPORTED)
2013
%
2014
%
2015
REVISED based on
Estimated Ending Inventory
2015
%
%
Revenue
Permits/legal
Salaries
Taxes
Utilities
Total All Expenses
Net Profit (REPORTED)
Net Profit (REVISED)
–
Difference (Reported, Less Estimated)
(a) Ending Inventory is Estimated using Cost of Goods Sold / Gross Receipts Method
2013
2014
2013-14 Avg.
% PROOF
2015
(Estimated)
2015
(Reported)
% PROOF
2015
(Estimated)
$
$
Cost of groceries
Gross Revenue – Groceries/Food
2013-2014 Percent COGS/Gross receipts
Cost of beverages
Gross Revenue – Beverages
2013-2014 Percent COGS/Gross receipts
Cost of tobacco
Gross Revenue – Tobacco
2013-2014 Percent COGS/Gross receipts
DAYS SALES OF INVENTORY CALCULATION
2013
TOTAL INVENTORY
Inventory
/ Cost of Sales
x 365 Days Sales of Inventory
$
2014
–
$
–
–
GROCERIES INVENTORY
Inventory
/ Cost of Sales
x 365 Days Sales of Inventory
BEVERAGES INVENTORY
Inventory
/ Cost of Sales
x 365 Days Sales of Inventory
TOBACCO INVENTORY
Inventory
/ Cost of Sales
x 365 Days Sales of Inventory
OTHER INCOME TAX RETURN INFORMATION – NOT IN SCHEDULES C
2013
Wages, salaries
Interest
Estimated Tax Payments
2014
2015
(Reported)
–
Financial Statement Fraud, Exercise #1 – Template
From Schedules C (AS REPORTED)
2013
%
2014
%
2015
Revenue
%
REVISED based on
Estimated Ending Inventory
2015
%
Financial Statement Fraud, Exercise #2 – Template
January 2014, 2015, 2016 Financial Statements
Larsen’s Convenience
Source: Sharptop Bank
1.a. – 1.b.
As Given (Reported) to Sharptop Bank
1/7/2014
1/7/2015
1/7/2016
Residence – 213 Underwood Street, Canton, Georgia
Cash – Savings
Cash – Sharptop Bank (Larsen’s Convenience)
2011 Jeep Grand Cherokee
2014 Ford F-150
Household goods
Leasehold improvements
Inventory (Larsen’s Convenience)
Total assets
$
–
$
–
$
–
Credit card – Sharptop Bank
Automobile Loan – Sharptop Bank
Truck Loan – Sharptop Bank
Accounts payable – Shamrock Groceries
Accounts payable – Discount Tobacco Supply
Accounts payable – Marietta Beverage Co.
Accounts payable – Georgia-Tennesse Fuel
Accounts payable – Jenkins Media Services
State Sales and Use Taxes Payable
SBA Note Payable
Mortgage – Integrity Mortgage
Total liabilities
Total equity
Total liabilities and equity (equals total assets)
–
$
$
$
–
$
$
$
–
$
$
$
REVISED
Business Assets & Liabilities Only
1/7/2014
1/7/2015
1/7/2016
(Per Source Docs.)
1/7/2016
Cash – Sharptop Bank (Larsen’s Convenience)
Leasehold improvements
Inventory (Larsen’s Convenience)
Total assets
$
–
$
–
$
–
Accounts payable – Shamrock Groceries
Accounts payable – Discount Tobacco Supply
Accounts payable – Marietta Beverage Co.
Accounts payable – Georgia-Tennesse Fuel
Accounts payable – Jenkins Media Services
State sales and use taxes payable
Note payable
Wrongful death liability
Total liabilities
Total equity
Total liabilities and equity (equals total assets)
$
$
$
–
$
$
$
–
$
$
$
–
1/7/2014
Residence – 213 Underwood Street, Canton, Georgia
Cash – Sharptop Bank (Tonya)
2011 Jeep Grand Cherokee
2014 Ford F-150
Household goods
Total assets
$
Personal Assets & Liabilities Only
1/7/2015
1/7/2016
–
$
–
$
–
$
–
Credit card – Sharptop Bank
Automobile Loan – Sharptop Bank
Truck Loan – Sharptop Bank
Mortgage – Integrity Mortgage
Total liabilities
Total equity
Total liabilities and equity (equals total assets)
$
$
$
–
$
$
$
–
$
$
$
–
1.c. – 1.d.
Horizontal and vertical analysis:
Business (1/7/16 v. prior years):
Personal:
1.e. – Accounts Payable Under-Statement
Shamrock Groceries per Financial Statement
Shamrock Groceries per Sales Summary
Discount Tobacco per Financial Statement
Discount Tobacco per Sales Summary
Marietta Beverage per Financial Statement
Marietta Beverage per Sales Summary
Georgia-Tennessee per Financial Statement
Georgia-Tennessee per Sales Summary
Jenkins Media Sales per Financial Statement
Jenkins Media Sales per Sales Summary
Date
1.f. – Ending Inventory Over-Statement
Ending Inv. – Estimated
Ending Inv. – Schedule C
Ending Inv. – Sharptop Bank
Ending Inv. – Proof of Loss
Date
Amount
Difference
///////
///////
///////
///////
///////
Amount
Total
Accounts
Payable
Under-Statement
$
–
Difference
///////
Ending Inventory
Over-Statement
$
–
2
If the tax return or insurance claim is misstated, did Greg Larsen commit some form of financial statement fraud? If so, what kind of
fraud? Explain.
3
What accurary certifications (e.g., penalties of perjury, false statements) on tax return and bank financial statement address materially
inaccurate information?
4
Was Larsen Convenience solvent on January 7, 2016? Explain.
5
How does Greg Larsen’s solvency impact Tonya Larsen?
2-5
Check Fraud, Debit Card Fraud, Larceny Case, Exercise #1 Template
Bank Reconciliation – Anderson Internal Medicine (4-1-16) (REVISED)
1. Balance per bank
Add: Deposits in transit
Balance per books
Add: Credits not recorded
Less: Outstanding checks
Subtract: Debits not recorded
Less: unknown difference (if any)
Reconciled balance
$
–
Reconciled balance
$
–
Comments (if any):
Compare the front and back of checks and the deposit slips to the cash receipts and cash
disbursement journals and identify:
2.a. Check numbers of the checks where payees do not match on check and cash disbursements journal
2.b. Check numbers of the checks with irregular disbursements – not listed as vendors in CDJ
2.c. Check numbers of the checks with irregular maker signatures – different from known signature
2.d. Check numbers of the checks with irregular endorsements
Review the bank statements for disbursements not involving checks and trace to the cash
disbursements journal
3.a. What are these transactions (e.g., cash withdrawals, ewithdrawals, debit card withdrawals)?
3.b.
Where and when (date and day of the week) were these non-check transactions actually made (may
not be the same as the date when recorded)?
3.c.
What is the distance between the location of these non-check transactions and Internal Medical
Associates?
3.d. Where does Tonya Larsen live?
3.e. What part did Tonya Larsen play in making these non-check disbursements?
3.f. Did Tonya Larsen benefit from these non-check transactions? If yes, how did she benefit?
Review the deposit slips
4.a How often and when were the deposits made?
4.b. Is anything missing from the deposits?
4.c. How do the deposits relate to the bank reconciliation?
Check Fraud, Debit Card Fraud, Larceny Case, Exercise #2 Template
1. List the items (e.g., check numbers, transaction dates, and individual amounts) and totals of each
category of fraud.
Check Number
Total Checks
Amount
$
–
$
–
$
–
Trans. Date
Total Debit Card Transactions
Cash – unknown difference
Total
2. What are the alleged frauds (e.g., accounts receivable skimming, disbursement fraud,
embezzlement, larceny, payroll fraud, skimming, other)? Discuss each one.
3. How were each of the frauds concealed?
4. What circumstances allowed the frauds to happen (e.g., internal control failures, lack of
background checks, missing policies and procedures) – discuss and be specific?
5. What would you recommend (e.g., controls, monitoring, etc.) to deter future frauds from
happening – discuss and be specific?
6. Is there anyone you would like to interview? If so, what do you want to learn from them?
7. Are there any additional steps you would like to take with respect to the fraud examination?
Row Labels

Purchase answer to see full
attachment

Description Option #1: Fraud Examination Casebook with Documents Textbook: Case 4 – Exercises 1 and 3 In your Fraud Examination Casebook with Documents textbook, read Case 4: Using Data Analytics: Analyzing and Summarizing Data with Excel Pivot (pp. 61-77). Perform Exercise 1: Excel Pivot (Checks and Debit Card Disbursements – Individual Assignment) on pages 77-79 of the Casebook. Also perform Exercise 3: Excel Pivot (Scrubbing) on page 81 of the Casebook. Submit your completed Excel Pivot file for inclusion in the fraud examiner’s/forensic accountant’s report.TONYA LARSEN EDITS TO DEPOSITS JANUARY TO MARCH 2016 TRANSACTION DESCRIPTION FROM MEDATRIX ULTRASCAN V = “√” vouched edit total to deposit ticket ORIG. EDIT V MONTHLY TOTALS DIFF January $ – $ February – TONYA LARSEN EDITS TO DEPOSITS JANUARY TO MARCH 2016 V = “√” vouched edit total to deposit ticket TRANSACTION DESCRIPTION FROM MEDATRIX ULTRASCAN ORIG. EDIT V MONTHLY TOTALS DIFF March $ – $ – $ – $ – $ – TONYA LARSEN EDITS TO DEPOSITS JANUARY TO MARCH 2016 TRANSACTION DESCRIPTION FROM MEDATRIX ULTRASCAN V = “√” vouched edit total to deposit ticket ORIG. EDIT V DIFF MONTHLY TOTALS Financial Statement Fraud, Exercise #1 – Template From Schedules C (AS REPORTED) 2013 % 2014 % 2015 Revenue Fuel revenue Beginning inventory + Purchases = Ending inventory Cost of fuel Gross profit Groceries/food revenue Beginning inventory + Purchases = Ending inventory Cost of groceries Gross profit Beverages revenue Beginning inventory + Purchases = Ending inventory Cost of beverages Gross profit Tobacco revenue Beginning inventory + Purchases = Ending inventory Cost of tobacco Gross profit Print media revenue Beginning inventory + Purchases = Ending inventory Cost of print media Gross profit Bank ATM rents Beginning inventory + Purchases = Ending inventory Cost of bank ATM rents Gross profit Total revenue Beginning inventory + Purchases = Ending inventory Total cost of goods sold Total gross profit Expenses Administration fee Advertisement Cash over/short Credit card fees Rent expense Equipment station repair Expense Items Insurance NSF checks expense Payroll tax expense % REVISED based on Estimated Ending Inventory 2015 % Financial Statement Fraud, Exercise #1 – Template From Schedules C (AS REPORTED) 2013 % 2014 % 2015 REVISED based on Estimated Ending Inventory 2015 % % Revenue Permits/legal Salaries Taxes Utilities Total All Expenses Net Profit (REPORTED) Net Profit (REVISED) – Difference (Reported, Less Estimated) (a) Ending Inventory is Estimated using Cost of Goods Sold / Gross Receipts Method 2013 2014 2013-14 Avg. % PROOF 2015 (Estimated) 2015 (Reported) % PROOF 2015 (Estimated) $ $ Cost of groceries Gross Revenue – Groceries/Food 2013-2014 Percent COGS/Gross receipts Cost of beverages Gross Revenue – Beverages 2013-2014 Percent COGS/Gross receipts Cost of tobacco Gross Revenue – Tobacco 2013-2014 Percent COGS/Gross receipts DAYS SALES OF INVENTORY CALCULATION 2013 TOTAL INVENTORY Inventory / Cost of Sales x 365 Days Sales of Inventory $ 2014 – $ – – GROCERIES INVENTORY Inventory / Cost of Sales x 365 Days Sales of Inventory BEVERAGES INVENTORY Inventory / Cost of Sales x 365 Days Sales of Inventory TOBACCO INVENTORY Inventory / Cost of Sales x 365 Days Sales of Inventory OTHER INCOME TAX RETURN INFORMATION – NOT IN SCHEDULES C 2013 Wages, salaries Interest Estimated Tax Payments 2014 2015 (Reported) – Financial Statement Fraud, Exercise #1 – Template From Schedules C (AS REPORTED) 2013 % 2014 % 2015 Revenue % REVISED based on Estimated Ending Inventory 2015 % Financial Statement Fraud, Exercise #2 – Template January 2014, 2015, 2016 Financial Statements Larsen’s Convenience Source: Sharptop Bank 1.a. – 1.b. As Given (Reported) to Sharptop Bank 1/7/2014 1/7/2015 1/7/2016 Residence – 213 Underwood Street, Canton, Georgia Cash – Savings Cash – Sharptop Bank (Larsen’s Convenience) 2011 Jeep Grand Cherokee 2014 Ford F-150 Household goods Leasehold improvements Inventory (Larsen’s Convenience) Total assets $ – $ – $ – Credit card – Sharptop Bank Automobile Loan – Sharptop Bank Truck Loan – Sharptop Bank Accounts payable – Shamrock Groceries Accounts payable – Discount Tobacco Supply Accounts payable – Marietta Beverage Co. Accounts payable – Georgia-Tennesse Fuel Accounts payable – Jenkins Media Services State Sales and Use Taxes Payable SBA Note Payable Mortgage – Integrity Mortgage Total liabilities Total equity Total liabilities and equity (equals total assets) – $ $ $ – $ $ $ – $ $ $ REVISED Business Assets & Liabilities Only 1/7/2014 1/7/2015 1/7/2016 (Per Source Docs.) 1/7/2016 Cash – Sharptop Bank (Larsen’s Convenience) Leasehold improvements Inventory (Larsen’s Convenience) Total assets $ – $ – $ – Accounts payable – Shamrock Groceries Accounts payable – Discount Tobacco Supply Accounts payable – Marietta Beverage Co. Accounts payable – Georgia-Tennesse Fuel Accounts payable – Jenkins Media Services State sales and use taxes payable Note payable Wrongful death liability Total liabilities Total equity Total liabilities and equity (equals total assets) $ $ $ – $ $ $ – $ $ $ – 1/7/2014 Residence – 213 Underwood Street, Canton, Georgia Cash – Sharptop Bank (Tonya) 2011 Jeep Grand Cherokee 2014 Ford F-150 Household goods Total assets $ Personal Assets & Liabilities Only 1/7/2015 1/7/2016 – $ – $ – $ – Credit card – Sharptop Bank Automobile Loan – Sharptop Bank Truck Loan – Sharptop Bank Mortgage – Integrity Mortgage Total liabilities Total equity Total liabilities and equity (equals total assets) $ $ $ – $ $ $ – $ $ $ – 1.c. – 1.d. Horizontal and vertical analysis: Business (1/7/16 v. prior years): Personal: 1.e. – Accounts Payable Under-Statement Shamrock Groceries per Financial Statement Shamrock Groceries per Sales Summary Discount Tobacco per Financial Statement Discount Tobacco per Sales Summary Marietta Beverage per Financial Statement Marietta Beverage per Sales Summary Georgia-Tennessee per Financial Statement Georgia-Tennessee per Sales Summary Jenkins Media Sales per Financial Statement Jenkins Media Sales per Sales Summary Date 1.f. – Ending Inventory Over-Statement Ending Inv. – Estimated Ending Inv. – Schedule C Ending Inv. – Sharptop Bank Ending Inv. – Proof of Loss Date Amount Difference /////// /////// /////// /////// /////// Amount Total Accounts Payable Under-Statement $ – Difference /////// Ending Inventory Over-Statement $ – 2 If the tax return or insurance claim is misstated, did Greg Larsen commit some form of financial statement fraud? If so, what kind of fraud? Explain. 3 What accurary certifications (e.g., penalties of perjury, false statements) on tax return and bank financial statement address materially inaccurate information? 4 Was Larsen Convenience solvent on January 7, 2016? Explain. 5 How does Greg Larsen’s solvency impact Tonya Larsen? 2-5 Check Fraud, Debit Card Fraud, Larceny Case, Exercise #1 Template Bank Reconciliation – Anderson Internal Medicine (4-1-16) (REVISED) 1. Balance per bank Add: Deposits in transit Balance per books Add: Credits not recorded Less: Outstanding checks Subtract: Debits not recorded Less: unknown difference (if any) Reconciled balance $ – Reconciled balance $ – Comments (if any): Compare the front and back of checks and the deposit slips to the cash receipts and cash disbursement journals and identify: 2.a. Check numbers of the checks where payees do not match on check and cash disbursements journal 2.b. Check numbers of the checks with irregular disbursements – not listed as vendors in CDJ 2.c. Check numbers of the checks with irregular maker signatures – different from known signature 2.d. Check numbers of the checks with irregular endorsements Review the bank statements for disbursements not involving checks and trace to the cash disbursements journal 3.a. What are these transactions (e.g., cash withdrawals, ewithdrawals, debit card withdrawals)? 3.b. Where and when (date and day of the week) were these non-check transactions actually made (may not be the same as the date when recorded)? 3.c. What is the distance between the location of these non-check transactions and Internal Medical Associates? 3.d. Where does Tonya Larsen live? 3.e. What part did Tonya Larsen play in making these non-check disbursements? 3.f. Did Tonya Larsen benefit from these non-check transactions? If yes, how did she benefit? Review the deposit slips 4.a How often and when were the deposits made? 4.b. Is anything missing from the deposits? 4.c. How do the deposits relate to the bank reconciliation? Check Fraud, Debit Card Fraud, Larceny Case, Exercise #2 Template 1. List the items (e.g., check numbers, transaction dates, and individual amounts) and totals of each category of fraud. Check Number Total Checks Amount $ – $ – $ – Trans. Date Total Debit Card Transactions Cash – unknown difference Total 2. What are the alleged frauds (e.g., accounts receivable skimming, disbursement fraud, embezzlement, larceny, payroll fraud, skimming, other)? Discuss each one. 3. How were each of the frauds concealed? 4. What circumstances allowed the frauds to happen (e.g., internal control failures, lack of background checks, missing policies and procedures) – discuss and be specific? 5. What would you recommend (e.g., controls, monitoring, etc.) to deter future frauds from happening – discuss and be specific? 6. Is there anyone you would like to interview? If so, what do you want to learn from them? 7. Are there any additional steps you would like to take with respect to the fraud examination? Row Labels Purchase answer to see full attachment

Why Choose Us

  • 100% non-plagiarized Papers
  • 24/7 /365 Service Available
  • Affordable Prices
  • Any Paper, Urgency, and Subject
  • Will complete your papers in 6 hours
  • On-time Delivery
  • Money-back and Privacy guarantees
  • Unlimited Amendments upon request
  • Satisfaction guarantee

How it Works

  • Click on the “Place Your Order” tab at the top menu or “Order Now” icon at the bottom and a new page will appear with an order form to be filled.
  • Fill in your paper’s requirements in the "PAPER DETAILS" section.
  • Fill in your paper’s academic level, deadline, and the required number of pages from the drop-down menus.
  • Click “CREATE ACCOUNT & SIGN IN” to enter your registration details and get an account with us for record-keeping and then, click on “PROCEED TO CHECKOUT” at the bottom of the page.
  • From there, the payment sections will show, follow the guided payment process and your order will be available for our writing team to work on it.