PROJECT OVERVIEW Comment by Gleason, Carolyn P: In video overview, explain that in the real world, you will probably not be given a step by step guide to do things. Your boss will tell you to do something and if you don’t know how, you will research and then ask questions. So that is what you are doing here. Research and then ask me for help if you are stuck.
· This project will consist of three parts:
· Part I: Ethical Scenarios
· Part II: Auditing AR
· Part III: Auditing Inventory
· All parts of the project will be submitted on Canvas by the due date listed in the syllabus. Part I will be a Microsoft Word document and parts II and III will be Microsoft Excel files.
· Parts II and III of the project must be completed in Microsoft Excel. All students have access to Office 365 with their student email (the instructions are posted in the syllabus). Students can also use school computers.
· The instructions specific to Excel throughout the project may seem vague at times. This has been done on purpose to stretch your skills within the software. You will likely have to play with the data and Google how to perform certain tasks, which is part of everyday life outside of school! That being said please do not spin your wheels for hours…I can help. Please send me an email if you are stuck!
· In addition to this, there are multiple ways to get to the right answer. I have provided guidelines to lead you to how I got my answer, but if you know of a better way, do it that way. However, please make sure you are answering the questions appropriately.
· This is an individual project. There will be no speaking or consulting with one another for help – Google and Carolyn Gleason are your resources.
· Remember professional skepticism. Even if you have confidence your formula is right, check for reasonableness before submitting your answer.
MICROSOFT EXCEL QUICK TRICKS
1. Alt tab Switch between computer screens
2. Alt = Automatically sums cells
3. Cntrl, a
ow Jump to the top or bottom of a string of cells
4. Shift, Cntrl, a
ow Total a column without dragging the mouse
5. Cntrl, Page up or down Tab between sheets within a workbook
6. F4 Repeats function just completed (formatting or formulas)
7. Alt, H… Quick keys on the home tab (borders, highlighting)
8. =Sumif(s) Formula for isolating data
9. Cntrl, C, dbl click corner of cell Copy formula to end of series
10. =Count(a)(blank)(if) Formula for counting cells or data
11. Sort Function useful for sorting data from small to large
12. Shift, space bar Highlights an entire row
13. Cntrl, space bar Highlights an entire column
14. Shift, Cntrl, + Add columns and rows (use after 12 or 13)
15. Cntrl, - Delete columns and rows (same as above)
16. Cntrl, F Brings up the “Find” box to search for a specific value
17. Alt, E, S… Paste (values, formulas, unformatted, formatted)
18. Cntrl, [ Follows a formula/cell to source
19. F5 Brings you back after following a formula/cell
20. F2 Edit directly in cells
21. Cntrl, ` Shows all formulas in the workbook (check hardcoded #s)
22. =Subtotal(109,…) Excludes hidden cells, subtotals, and any rows filtered out
PART I
Ethical Scenarios
7% of Final Grade
4620 – 20 points
6020 – 30 points
You will be using KPMG’s Ethical Compass model to analyze various ethical dilemmas. After working through the example on your own and posting in the weekly discussion, you will apply the same process to two cases of your choosing. ACCT 6020 will do three cases.
For each case study, you will follow the template below for your answer.
1. DOCUMENTATION FOR THE PROCESS BEFORE YOU DECIDE:
· Consider all the facts
· Assess alternative actions
· Review the ethical issues
· Evaluate potential outcomes
2. YOUR DECISION
3. DOCUMENTATION CHALLENGING YOUR DECISION:
· Consistency
· Awareness and accountability
· Rules and principles
· Equity
4. FINAL DECISION
Answers will be graded against the Ethical Reasoning Ru
ic below. Each of the cases will be worth 10 points for a total of 20 points for 4620 and 30 points for 6020.
Criteria
Ratings
Professional and Ethical Awareness
Identifies all relevant professional factors and ethical dimensions.
Identifies professional aspects of the situation, or ethical dimensions but leaves out relevant factors.
Identifies that a professional or ethical issue exists.
2 points
1 point
0.5 points
Awareness of Stakeholders
Identifies & considers all potential stakeholders relevant to the ethical decision.
Identifies & considers many or most potential stakeholders to the ethical decision but leaves out some significant stakeholders.
Identifies & considers a few potential stakeholders relevant to the ethical decision.
2 points
1 point
0.5 points
Ethical Decision Making
A
ives at an insightful comprehensive decision that coheres with problem, interested parties & situation.
Decision coheres w/problem, interested parties and/or general situation
A
ives at a decision, but lacks coherence with problem, interested parties, and/or general situation.
2 points
1 point
0.5 points
Clarity
Highly organized and easy for the reader to understand including logical order and smooth transitions.
Minor details out of order but relatively easy for the reader to understand.
Significant details out of order make it difficult for the reader to understand.
2 points
1 point
0.5 points
Writing and Gramma
Writing, punctuation, spelling, and grammar are very clear with few e
ors. Proper format.
Minor e
ors in writing, punctuation, spelling, and grammar partially distract from the overall message; Some format problems
Significant e
ors in writing, punctuation, spelling, and grammar distract from the overall message. Format problems
2 points
1 point
0.5 points
PART II
Ames Company Accounts Receivable
8% of Final Grade
4620 – 25 points
6020 – 30 points
Your client Ames Company provided you with a list of cash owed to it in the form of unpaid invoices. That information is in Excel Spreadsheet file Ames_AR.
1) [2 points] Create a pivot table (on a new tab) showing the total amount per customer. Sort the data so that the sum of amounts goes from highest to lowest. Highlight the row yellow with the first customer that has a total balance greater than $15,000.
2) [2 points] If your materiality for sampling AR were $4,500, how many customers would automatically be selected for testing if the audit team tests all customers greater than this amount? On the same tab that has your pivot table, use the COUNTIF function in Excel to perform this calculation. You can calculate it in one of the columns to the right of the work you have already performed. You must use the countif function to receive full credit.
3) [2 points] Why would we want to summarize by customer? How does this help us in auditing the AR balance? Type your answer in cell A1 of the tab that has the pivot table.
4) [2 points] The most common (and often required) form of substantive testing for AR is sending external confirmations to the customers. After making the sampling selections from the data, explain the process necessary to complete the confirmation testing of the account balance. Insert a text box in the same tab you performed parts 1 and 2 and make a list of steps for your answer.
XXXXXXXXXXONLY – [2 points] There is another way to summarize accounts receivable by customer using the Subtotal function. First, copy the data to a new tab. Second, use the Subtotal function found under the Data tab. The 1, 2, 3 at the top left of the screen allow you to expand and collapse your columns. 2 will show you just the subtotals for each customer. Finally, sort the Amount column from largest to smallest and compare to #1 above.
6) [17 points] The allowance for doubtful accounts is an estimate. In order to evaluate this estimate, you are going to test management’s assumptions by creating your own estimate. In order to do this, you are going to create your own aging schedule using the raw AR data and then use the company’s percentage assumptions. Ames’ payment terms are 30 days (or one month) and generally, customers pay on time.
Ames’ policy for calculating the allowance for doubtful accounts is as follows:
· 1% of cu
ent AR meaning not due yet
· 5% of 1-30 days past due
· 10% of 31-60 days past due
· 100% of >60 days past due except for their two largest customers (in total AR), who they only reserve 50% of the >60 days category
They cu
ently have $52,098 recorded as of 12/31/19. Your job is to determine if this is reasonable by following the steps below.
a. On the original data tab, freeze the row that has the header titles (under the View tab).
. Complete the Days Outstanding column by calculating the difference between 12/31/2019 and the date of the invoice. Your answer must be a formula.
c. Complete the aging columns provided by using IF formulas. Note that you are not yet factoring in the percentages for each category, which will happen in step e below. In order to determine the 1-30 and 31 to 60 columns, you will have to use multiple IF formulas (>31 but <60). Remember that cu
ent means the invoice is not due yet as of 12/31 (therefore, any invoice dated 12/1 or later) and 1-30 days overdue means the invoice is dated between 11/1 and 11/30. You can spot check your work by looking at the invoice dates and making sure that the invoice is appearing in the right column.
d. The data is still not useful as we need to summarize by customer in order to test management’s assumptions. On a new tab, create a pivot table by customer that shows each aging bucket as well as the total amount per customer. Freeze the top row.
e. Below the pivot table, calculate your estimate using the assumptions provided above. Is the recorded amount reasonable if materiality is $4,500? If not, what might be the reason for the difference? Is there a specific customer or category they missed? Answer in a text box below your calculations.
XXXXXXXXXXONLY – [3 points] Copy your pivot table to a new tab to perform further analysis. For each column, change the data from dollars to the percentage of each column (hint: “Show Values As”). When examining the >60 days past due column, which customers stick out as having a greater past due balance than