Need some help analyzing the data and what this data presents

Content Calendar Communication Assessment Learner Management Locker Group Project ACC 3900 Fall 2019 Group Project 00 Fall 2019 Group Project ACC 3900 Group Project - Fall 2019 Purpose: Provide students an opportunity to undertake accounting and audit-related analyses over sample data sets using a commonly software package (Excel), and present their findings to the class. Students will gain expertise in identifying appropriate Excel functions to be used for analytics. Specifically, students will analyze the datasets provided to their group for possibly anomalous/fraudulent data, as would be investigated during an audit. Students will also review the attributes of their datasets and create analyses/summarizations that help explain what this data presents. Software/Group Superstore PCard Bibitor 9/30 Vendor Invoices Bibitor 6/30 Vendor Invoices BCGG 6/30 Sales Invoices BOGG 6/30 Purchase Invoices BOGG 3/30 Sales invoices BOGG 3/30 Purchase Invoices PSU 2014 JE's Example: Possible questions to investigate with Superstore data: 1 2 3 4 5 6 17 8 9 10 a Are there any indications of sales-related issues? Are there any indications of cut-off issues? Are there any indicators of related party transactions? Which customers should receive confirmations? Are there any issues or concerns with discounts and cash receipts? What is the Accounts Receivable 12/31 balance for each year? What is the quality of Superstore's accounts receivable? Are there any indicators that inventory might be overvalued? What are shipping costs per region or product? Are there any other issues you think are important? Possible questions to investigate: Superstore Orders Data: Discrepancies between Order and Ship Dates? Unusual posting times that could indicate fraudulent transactions? Do any customers appear to receive unusually high discounts? (indicates related party transactions?) Join Orders and Returns data to identify high return quantities by product Join Orders and People data to identify if any salespersons are selling to customers outside of their region Generate an analysis of sales by category and sub category, based on units sold Procurement Data: Discrepancies between Transaction and Posting Dates? Unusual Transaction and/or Posting Dates? (holidays, weekends?) Identify purchases by Cardholder; relative amounts, by Description, by Vendor iii iv v Vi b [ 2 12 E v cet Add-ins h? Pictures Shapes Icons Pivot Table Recommended Table Pivot Tables My Add-ins Recommended Charts : A1 X fx Row ID Order Date Ship Date Customer 1 Rowicorder ID Posting Time Ship Mode 4069 CA-2011-102673 11/1/15 11:59:57 PM 11/5/15 Standard Class KH-16630 9643 CA 2011-104563 1/7/15 10:48:35 PM 3/12/15 Standard Class CM-12715 6370 CA-2013-103919 10/4/17 10:40:24 PM 10/8/17 Standard Class TP-21565 9019 CA-2013-161361 3/27/17 4:59:48 PM 3/29/17 Second Class MV-17485 2537 CA-2012-168746 1/27/16 4:59:48 PM 1/29/16 Second Class SV-20365 8205 CA-2012-114811 11/8/16 4:59:40 PM 11/8/16 Same Day KD-16495 5436 CA 2012-160171 10/19/16 4:59:40 PM 10/24/16 Standard Class RM-19675 1056 CA 2012-110667 4/4/16 4:59:40 PM 4/8/16 Standard Class NF-18595 390 CA-2014-101798 12/12/18 4-5940 PM 12/16/18 Standard Class MV-18190 1865 CA-2014-163531 6/27/18 4.59:31 PM 7/1/18 Standard Class SC-20725 9537 CA 2013-108630 1 1/19/17 4:59:31 PM 11/19/17 Same Day BE-11410 2912 CA-2011-113929 6/16/15 4:59:31 PM 6/21/15 Standard Class CK-12205 4:59:31 PM 9/18/17 Standard Class AJ-10795 28 US-2012-150630 94/16 4:59:23 PM 9/21/16 Standard Class T8-21520 3044 A 2014-149559 9/12/18 4:59 23 PM 9/13/18 Same Day F-16285 9710 CA 2011-161746 10/22/17 4:59 23 PM 10/28/17 Standard Class 5-11950 599) US 2014-163300 9/16/18 4:59 23 PM 9/22/18 Standard Class ES-14020 228 CA. 2013-123050 4/5/17 4:59:23 PM 4/9/17 Second Class BC-11125 5730 CA. 2014-117324 12/9/18 4:59:23 PM 12/14/18 Standard Class JP-15520 5914 US-2012-126977 9/17/16 4:59:23 PM 9/23/16 Standard Class PF-19120 6879 US-2012-123918 10/15/16 4.59:23 PM 10/15/16 Same Day CG-12520 2509 CA 2011-127446 11/25/15 4:59:23 PM 11/30/15 Standard Cass MC-17590 3427 A 2012-153381 9/24/16 4-59 14 PM /28/16 Standard Class DE-13255 257) CA 2013-155845 /11/17 4:59:14 PM /16/17 Second Cass CM-12235 976 US 2014-103247 10/6/18 4:59:14 PM 10/9/18 Second Class PO-19195 3773 CA2011-115357 8/6/15 4:59:14 PM 8/11/15 Second Class RF-19735 6938 CA 2011-126963 6/15/15 4:59:14 PM 6/15/15 Same Day PS-18750 5738 A 2011-143614 1/21/15 4:59:14 PM 1/26/15 Standard Class MV-17485 O A 2012-145352 V1616 4:59:14 PM 1/22/16 Standard Class M-12185 3815 CA 2012-144274 45905 PM 11/25/16 Second Class PW-19240 A 2011- 1 6 12/20/15 459-OS PM 12/22/15 First Class CT-11995 14 A 2014-151911 1/18 4:59:05 PM 56/18 Second Cass DL-13495 8723 CA 2011-120824 6/11/17 4-59.05 PM /17/17 Second Class AW-10010 249 CA 2011-131926 6/1/15 4-58:57 PM 6/6/15 Second Class DW-13480 36 6049 CA 2012-105571 1 177/16 458 45 PM 11/11/16 Standard Cass 12340 3315 A 2014-1 12/ 2 8 458 45 PM 12/29/18 Standard Gas HG-15025 712 2015-164 1117 458 117 Fast Cass F15295 A 2011-12 12 458 10/22/17 Second Class 110 A 2011-1276 FC 14345 nois 458 4 0 PM Is First Class CA 2013-11704 D-159 WI 531 10/17 Standard Class IP 15520 Orders Customer Name Ken Heidel Craig Molinari Tracy Poddar Mark Van Huff Seth Vernon Keith Dawkins Robert Marley Nicole Fjeld Mike Vittorini Steven Cartwright Bobby Elias Chloris Kastensmidt Anthony Johnson Tracy Blumstein Karen Ferguson Carlos Soltero Erica Smith Becky Castell Jeremy Pistek Peter Fuller Claire Gute Matt Callister Deanna Eno Chris McAfee Phillina Ober Roland Fjeld Pamela Stabb Mark Van Huff Christopher Martinez Mere Wener Carol Triggs Dionis Lloyd Arthur Wiediger Danna Wilson Hunter Glantz Jason Fortune Frank Carlisle Jonathan Doherty eremy Pistek Content Calendar Communication Assessment Learner Management Locker Group Project ACC 3900 Fall 2019 Group Project 00 Fall 2019 Group Project ACC 3900 Group Project - Fall 2019 Purpose: Provide students an opportunity to undertake accounting and audit-related analyses over sample data sets using a commonly software package (Excel), and present their findings to the class. Students will gain expertise in identifying appropriate Excel functions to be used for analytics. Specifically, students will analyze the datasets provided to their group for possibly anomalous/fraudulent data, as would be investigated during an audit. Students will also review the attributes of their datasets and create analyses/summarizations that help explain what this data presents. Software/Group Superstore PCard Bibitor 9/30 Vendor Invoices Bibitor 6/30 Vendor Invoices BCGG 6/30 Sales Invoices BOGG 6/30 Purchase Invoices BOGG 3/30 Sales invoices BOGG 3/30 Purchase Invoices PSU 2014 JE's Example: Possible questions to investigate with Superstore data: 1 2 3 4 5 6 17 8 9 10 a Are there any indications of sales-related issues? Are there any indications of cut-off issues? Are there any indicators of related party transactions? Which customers should receive confirmations? Are there any issues or concerns with discounts and cash receipts? What is the Accounts Receivable 12/31 balance for each year? What is the quality of Superstore's accounts receivable? Are there any indicators that inventory might be overvalued? What are shipping costs per region or product? Are there any other issues you think are important? Possible questions to investigate: Superstore Orders Data: Discrepancies between Order and Ship Dates? Unusual posting times that could indicate fraudulent transactions? Do any customers appear to receive unusually high discounts? (indicates related party transactions?) Join Orders and Returns data to identify high return quantities by product Join Orders and People data to identify if any salespersons are selling to customers outside of their region Generate an analysis of sales by category and sub category, based on units sold Procurement Data: Discrepancies between Transaction and Posting Dates? Unusual Transaction and/or Posting Dates? (holidays, weekends?) Identify purchases by Cardholder; relative amounts, by Description, by Vendor iii iv v Vi b [ 2 12 E v cet Add-ins h? Pictures Shapes Icons Pivot Table Recommended Table Pivot Tables My Add-ins Recommended Charts : A1 X fx Row ID Order Date Ship Date Customer 1 Rowicorder ID Posting Time Ship Mode 4069 CA-2011-102673 11/1/15 11:59:57 PM 11/5/15 Standard Class KH-16630 9643 CA 2011-104563 1/7/15 10:48:35 PM 3/12/15 Standard Class CM-12715 6370 CA-2013-103919 10/4/17 10:40:24 PM 10/8/17 Standard Class TP-21565 9019 CA-2013-161361 3/27/17 4:59:48 PM 3/29/17 Second Class MV-17485 2537 CA-2012-168746 1/27/16 4:59:48 PM 1/29/16 Second Class SV-20365 8205 CA-2012-114811 11/8/16 4:59:40 PM 11/8/16 Same Day KD-16495 5436 CA 2012-160171 10/19/16 4:59:40 PM 10/24/16 Standard Class RM-19675 1056 CA 2012-110667 4/4/16 4:59:40 PM 4/8/16 Standard Class NF-18595 390 CA-2014-101798 12/12/18 4-5940 PM 12/16/18 Standard Class MV-18190 1865 CA-2014-163531 6/27/18 4.59:31 PM 7/1/18 Standard Class SC-20725 9537 CA 2013-108630 1 1/19/17 4:59:31 PM 11/19/17 Same Day BE-11410 2912 CA-2011-113929 6/16/15 4:59:31 PM 6/21/15 Standard Class CK-12205 4:59:31 PM 9/18/17 Standard Class AJ-10795 28 US-2012-150630 94/16 4:59:23 PM 9/21/16 Standard Class T8-21520 3044 A 2014-149559 9/12/18 4:59 23 PM 9/13/18 Same Day F-16285 9710 CA 2011-161746 10/22/17 4:59 23 PM 10/28/17 Standard Class 5-11950 599) US 2014-163300 9/16/18 4:59 23 PM 9/22/18 Standard Class ES-14020 228 CA. 2013-123050 4/5/17 4:59:23 PM 4/9/17 Second Class BC-11125 5730 CA. 2014-117324 12/9/18 4:59:23 PM 12/14/18 Standard Class JP-15520 5914 US-2012-126977 9/17/16 4:59:23 PM 9/23/16 Standard Class PF-19120 6879 US-2012-123918 10/15/16 4.59:23 PM 10/15/16 Same Day CG-12520 2509 CA 2011-127446 11/25/15 4:59:23 PM 11/30/15 Standard Cass MC-17590 3427 A 2012-153381 9/24/16 4-59 14 PM /28/16 Standard Class DE-13255 257) CA 2013-155845 /11/17 4:59:14 PM /16/17 Second Cass CM-12235 976 US 2014-103247 10/6/18 4:59:14 PM 10/9/18 Second Class PO-19195 3773 CA2011-115357 8/6/15 4:59:14 PM 8/11/15 Second Class RF-19735 6938 CA 2011-126963 6/15/15 4:59:14 PM 6/15/15 Same Day PS-18750 5738 A 2011-143614 1/21/15 4:59:14 PM 1/26/15 Standard Class MV-17485 O A 2012-145352 V1616 4:59:14 PM 1/22/16 Standard Class M-12185 3815 CA 2012-144274 45905 PM 11/25/16 Second Class PW-19240 A 2011- 1 6 12/20/15 459-OS PM 12/22/15 First Class CT-11995 14 A 2014-151911 1/18 4:59:05 PM 56/18 Second Cass DL-13495 8723 CA 2011-120824 6/11/17 4-59.05 PM /17/17 Second Class AW-10010 249 CA 2011-131926 6/1/15 4-58:57 PM 6/6/15 Second Class DW-13480 36 6049 CA 2012-105571 1 177/16 458 45 PM 11/11/16 Standard Cass 12340 3315 A 2014-1 12/ 2 8 458 45 PM 12/29/18 Standard Gas HG-15025 712 2015-164 1117 458 117 Fast Cass F15295 A 2011-12 12 458 10/22/17 Second Class 110 A 2011-1276 FC 14345 nois 458 4 0 PM Is First Class CA 2013-11704 D-159 WI 531 10/17 Standard Class IP 15520 Orders Customer Name Ken Heidel Craig Molinari Tracy Poddar Mark Van Huff Seth Vernon Keith Dawkins Robert Marley Nicole Fjeld Mike Vittorini Steven Cartwright Bobby Elias Chloris Kastensmidt Anthony Johnson Tracy Blumstein Karen Ferguson Carlos Soltero Erica Smith Becky Castell Jeremy Pistek Peter Fuller Claire Gute Matt Callister Deanna Eno Chris McAfee Phillina Ober Roland Fjeld Pamela Stabb Mark Van Huff Christopher Martinez Mere Wener Carol Triggs Dionis Lloyd Arthur Wiediger Danna Wilson Hunter Glantz Jason Fortune Frank Carlisle Jonathan Doherty eremy Pistek