Please answer whatever parts you can answer or even any sub questions. Thanks!

ACCT7013: F20 DATA ANALYSIS ASSIGNMENT

Your firm has been hired to audit KATS Insurance company. Youve been selected to be a member of the audit team assigned to the engagement and are required to come up with a testing plan for the sales account. To aid you with your plan, youve obtained a copy of the full dataset (population) of KATS Insurance sales for the year under review, January 1 December 31, 2017 (you can access this under the assignment module on FOL content). Results from your interview with the VP, National Sales indicated that the company has experienced an overall 2% decline in sales year over year. He indicated that this aligns with the national industry trend so he has expressed no concern overall in this regard. He has also indicated that there are no specific concerns with staffing. Average turnover has occurred and there doesnt appear to be much change in the competitive environment. Additional information from 2016 that may aid with your analysis is included as follows: Table 2016.1

List of Sales Totals by Region (2016) Region Sales Totals Midwest $7,992,123.799 New England 4,905,811.122 North Central 7,140,136.563 Northeast 9,534,172.902 Southeast 7,664,970.834 West 7,111,098.801

Top 5 Sales Performance Adam* Mario Peter Mike Kellie

Bottom 5 Sales Performance - Crissy Frank Sara Nancy* Sheldon*

*Indicates individuals are no longer employed with the company.

Details of the departure were obtained during an initial interview as follows: Adam left to start a new online insurance brokerage. Any sales individuals who sell less than $2,500,000 in policies during a year are placed on a 3-month probationary period to increase their sales using specific targets during that time and are mentored by a Regional Sales Director. Both Nancy and Sheldon did not meet their probationary targets. L. De Luca Fanshawe College 2020 REQUIRED: In groups of 4, complete both part 1 and 2 of the assignment below.

PART 1 ANSWER THE FOLLOWING:

a) What risks have you identified PRIOR to analyzing your data based on the case information? (Identify up to 4 risks mark each up to 2 marks)

b) What are the main assertions to be tested for sales/revenue accounts and why? (2 marks)

c) Briefly describe the need to prepare data for use by auditors. What needs to be considered before you can begin your data analytical procedures and why is this an important step? (3 marks)

d) Review the dataset and identify whether any data cleansing is required (formatting, spelling errors, etc) that will need to be addressed prior to starting the analysis. Summarize any cleansing that was required in your Word document submission. (2Marks)

e) Perform analysis using at least four different techniques (using Excel, or try out Tableau or Power BI) that will help you better understand the population and identify issues, or trends that would help focus your testing. The following options are available in Excel and should only be used once each: - Pivot Tables - Charts - Formula Functions - Conditional Formatting For each technique, clearly state your objective of the procedure (what are you trying to accomplish/conclude) Save your work in a dedicated tab for each technique used (12 Marks: 3 marks per technique 1 for objective, 1 for originality, and 1 for proficiency)

PART 2:

1. What notable items have you identified in your analysis? State the conclusion and provide rationale to support it based on the technique used in your analysis. (4 conclusions required, 4 marks 1 per item).

2. What additional steps will you perform in the audit based on the items identified in part 2, 1 (notable items). Be specific and write the procedure (using appropriate audit terminology referring to evidence gathering techniques). 2 marks each for a total of 8marks 1 test procedure per notable item.

3. What additional information would aid with your analysis to better focus your testing efforts and potentially refine your initial conclusions? Use a combination of internal and external data that might be available. Be creative and make assumptions. State clearly what the data is and how you would use it, as well as any assumptions made. (Include at least two additional sources of data 2 marks each, 1 for the data source and 1 mark to supportit).