2173 Salk Avenue, Suite 250 Carlsbad, CA

support@assignmentprep.info

Overview and Instructions Assignment: Data Analytics: Helping to Solve Financial

April 27, 2024

Overview and Instructions
Assignment: Data Analytics: Helping to Solve Financial Fraud
The following Course Outcome is assessed in this assignment:
Appraise investigative techniques including interviewing and inquiry, in solving financial crimes.
Introduction:
In this assignment, you apply what you learned concerning investigative techniques, including interviewing and inquiry to resolve a financial fraud. Read the case study and then respond to the checklist items in a substantive manner.
Chapter 4: Case 4: Using Data Analytics: Analyzing and Summarizing Data with Excel Pivot
Case Adapted from the Casebook 4-1 Anderson Internal Medicine (Data Analytics): Using Data Analytics — Analyzing And Summarizing Data with Excel Pivot
History Behind The Fraud Case
The president of an unlicensed securities firm was suspected of securities fraud (i.e., selling bogus stock warrants to unsuspecting investors). The contents of 12 monthly bank statements, canceled checks, and deposit tickets of the business were scheduled (over 50 pages of transactions). How could the transactions be easily summarized? What does the schedule of transactions reveal? Were there any significant transactions? Excel Pivot came to the rescue.
In a half hour and several clicks on Excel Pivot, the Pivot tables of deposits and disbursements showed that the president used the business checking account as if it were his personal piggy bank. He primarily used debit cards to pay for two to three meals a day, every day travel and entertainment, an automobile for a girlfriend, and payments for household utilities, cell phones, and “strip clubs.” Based on the timing of receipts from customers, he celebrated at “strip clubs.” Soon after he was indicted and arrested, he read the fraud examination report and accompanying schedules. Shortly thereafter, he agreed to plead guilty to racketeering. The judge sentenced him to 20 years for racketeering.
Background
Tonya Larsen was the sole person in charge of the practice’s finances, including accounting during the period from September 1, 2015, to March 31, 2016.
After completing the examination steps in Anderson Internal Medicine cases (Cases 2 and 3), you decided to schedule the bank transactions shown on the bank statements for the six months preceding March 2016 (the month you examined in Cases 2 and 3).
Before proceeding, go to the companion website and download the Excel spreadsheet titled, Anderson Bank Transactions (Sep Oct Nov Dec Jan Feb). It is also available in a lengthy hardcopy format in Chapter 7, section 7‐4, “Using Data Analytics: Analyzing and Summarizing Data with Excel Pivot.” The downloadable version contains drop-down categories, which helps simplify the process of adding categories, whereas the hardcopy version does not. This spreadsheet contains the result of your efforts for September 1, 2015, through February 29, 2016. Based on the initial review of the Excel spreadsheet, you decide that you need to add categories to the withdrawal transactions so you can better summarize the data.
You decide to use the following categories commonly used by medical practices:
Answering Service
Automobile
Bank Charges
Basement
Books/Subscriptions
Business
Cash
Cash Deposit
Coding
Contributions
Food
Hardware
Health Insurance
Insurance
Janitorial Services
Legal & Accounting
Linen
Marketing/Promotions|
Medical Supplies/Pharmaceuticals
Medical Waste
Miscellaneous
Mortgage
Office Supplies
Parking
Printing
Repairs & Maintenance
Shipping
Taxes
Telephone
Travel
Utilities
In scanning the spreadsheet, you notice unusual names that do not fall under the common categories used by medical practices. Accordingly, you will use the following categories for those unusual transactions.
People
Unusual Transactions
To facilitate categorization, a drop‐down menu has been provided in the detail of transactions column. To use the menu, select the cell you want to use and click the down arrow that will appear to the right. Then select the category you feel is appropriate for the transaction.
Note: There are intentional spelling errors in the spreadsheet that you will identify and then sanitize (scrub) before completing the Pivot.
Summary Requirements:
Your firm has been contracted to perform the fraud examination. The attorney for Southern Appalachian Insurance Company added an additional assignment: analyze the bank transactions from September 1, 2015, through February 29, 2016.
Access the Study section 4-2, How to Use Excel Pivot to Analyze and Summarize Data, and download the Excel Data sheet provided before conducting the specific requirements of this case.
The results of your assignment will be used as a summary schedule in your report, a schedule attached to the report, and possibly a courtroom audiovisual should you be called to testify as an expert witness. Your assignment (in general) is, as follows:
Assign categories (using the drop‐down choices provided) to the disbursement transactions to assist in summarizing transactions.
Insert a PivotTable in a new worksheet and obtain the total quantities (count) of transactions and total dollars (sum) for each category. Make at least three new PivotTables (Exercises 1, 2, and 3):
Deposits (Amount Deposit and Deposit Category)
Check and Debit Card Disbursements (Check Category, Payee per Check/Debit, Amount Check/Debit) (un‐scrubbed)
Check and Debit Card Disbursements (Check Category, Payee per Check/Debit, Amount Check/Debit) (scrubbed)
Sort the total dollars per category in descending order.
Enhance the scrubbed PivotTables for report and courtroom presentation purposes: Add an appropriate title and use proper “$” signs, decimal format, etc.
Checklist: Make sure to address all of the following in your response:
Provide the 7 irregularities noted in the first pivot table you developed.
Provide the pivot table from the first exercise.
Assess the irregularities in the data.
Determine what inquiries you would make of Anderson Internal Medicine to learn more about these irregularities.
Recommend what questions you would ask in an interview to gain more insight into these irregularities.
Provide the anomaly you determined in the deposits pivot table.
Determine the source of the anomaly.
Determine if this check is a normal deposit for Anderson Internal Medicine.
Provide the pivot table from the first exercise.
Provide the Final Excel Pivot Tables that would be included in your final fraud report to serve as an exhibit during your expert witness testimony.
This is an individual assignment. In particular, all students are to perform the various fraud examination steps and submit their Excel PivotTables for grades. NOTE: No Excel template is provided. You are to create your own Excel PivotTables by following the step‐by‐step instructions.

Struggling With a Similar Paper? Get Reliable Help Now.

Delivered on time. Plagiarism-free. Good Grades.

What is this?

It’s a homework service designed by a team of 23 writers based in Carlsbad, CA with one specific goal – to help students just like you complete their assignments on time and get good grades!

Why do you do it?

Because getting a degree is hard these days! With many students being forced to juggle between demanding careers, family life and a rigorous academic schedule. Having a helping hand from time to time goes a long way in making sure you get to the finish line with your sanity intact!

How does it work?

You have an assignment you need help with. Instead of struggling on this alone, you give us your assignment instructions, we select a team of 2 writers to work on your paper, after it’s done we send it to you via email.

What kind of writer will work on my paper?

Our support team will assign your paper to a team of 2 writers with a background in your degree – For example, if you have a nursing paper we will select a team with a nursing background. The main writer will handle the research and writing part while the second writer will proof the paper for grammar, formatting & referencing mistakes if any.

Our team is comprised of native English speakers working exclusively from the United States. 

Will the paper be original?

Yes! It will be just as if you wrote the paper yourself! Completely original, written from your scratch following your specific instructions.

Is it free?

No, it’s a paid service. You pay for someone to work on your assignment for you.

Is it legit? Can I trust you?

Completely legit, backed by an iron-clad money back guarantee. We’ve been doing this since 2007 – helping students like you get through college.

Will you deliver it on time?

Absolutely! We understand you have a really tight deadline and you need this delivered a few hours before your deadline so you can look at it before turning it in.

Can you get me a good grade? It’s my final project and I need a good grade.

Yes! We only pick projects where we are sure we’ll deliver good grades.

What do you need to get started on my paper?

* The full assignment instructions as they appear on your school account.

* If a Grading Rubric is present, make sure to attach it.

* Include any special announcements or emails you might have gotten from your Professor pertaining to this assignment.

* Any templates or additional files required to complete the assignment.

How do I place an order?

You can do so through our custom order page here or you can talk to our live chat team and they’ll guide you on how to do this.

How will I receive my paper?

We will send it to your email. Please make sure to provide us with your best email – we’ll be using this to communicate to you throughout the whole process.

Getting Your Paper Today is as Simple as ABC

No more missed deadlines! No more late points deductions!

}

You give us your assignments instructions via email or through our order page.

Our support team selects a qualified writing team of 2 writers for you.

l

In under 5 minutes after you place your order, research & writing begins.

Complete paper is delivered to your email before your deadline is up.

Want A Good Grade?

Get a professional writer who has worked on a similar assignment to do this paper for you