Reference no: EM132532238

Learning Objective 1: Understand the FASB codification system and research accounting issues to find the relevant parts of the codification that provide information on each issue.

Learning Objective 2: Analyze the size and liquidity of Accounts Receivable by focusing on ratio analysis: Accounts Receivable as a percentage of sales and Accounts Receivable turnover.

Learning Objective 3: Use the Accounts Receivable aging analysis to estimate the amount of uncollectible accounts and assess the reasonableness of Allowance for Doubtful Account balance.

Instructions:

Part A: FASB Codification

You are working for Mindak LLP, an accounting consulting firm, as a research and accounting specialist. Emerald Inc. (a manufacturing company) asked you to research and explain the U.S. GAAP rules for various transactions. The client wants you to reference and explain the rules in the FASB codification for this topic. Emerald Inc. will then use your business professional memo to look up the rules in the codification.

Please make sure you include in your memo discussion of all three items (a) to (c) below. Reference the relevant topic, subtopic, section and paragraph(s). You should reference the codification by using "FASB ASC XXX-XX-XX-XX". Your grade will primarily focus on whether you are identifying the proper topics, subtopics, sections and paragraphs in the codification. You can summarize each reference in 1-2 sentences. You should not be copying any of the codification into your memo as the client can go look directly at the codification reference.
a) Emerald Inc. has a large amount of equipment. They want to know when the company needs to test the equipment for recoverability (where the carrying amount of the equipment is not recoverable and exceeds its fair value.) Determine the reference(s) from the codification system that explain the GAAP guidance on this topic.
b) Emerald Inc. is unsure on what they need to disclose in regards to the company's earnings per share. Determine what the company needs to disclose in relation to their earnings per share and which reference(s) from the codification system that explain the GAAP guidance on this topic.
c) The company has Goodwill on their balance sheet. They are testing Goodwill for impairment and want to know how to determine the fair value of the reporting unit. Determine the reference(s) from the codification system that explain the GAAP guidance on this topic.

Part B: Use the "ACC 541 Emerald Inc.AR Spring 2020" file to answer the following questions.
Note - pay close attention to the data. In performing the data analysis, you need to make sure that you capture anything unusual in the data, that you understand the data you are given, and that you communicate your analysis effectively. Pay close attention to dates on this part of the project.

Questions 1 to 5 focus on the size of Accounts Receivable and its liquidity as of 12/31/2020.
1) You need to determine theoutstanding amount of each 2020 sales transaction as of 12/31/2020. You will need to merge the Sales Invoice and Payments files to determine which sales transactions made in 2020 have been paid by 12/31/2020. You can do this with multiple functions and there is no one set way to merge the data. One way you could merge the data sets would be with an iferror and vlookup formula to merge the two data sets.
a. As a DePaul student, you have access to LinkedIn Learning which gives you a vast amount of tutorials on Excel. If you do not know how to use functions in Excel - this is a great way to learn the program and become more knowledgeable in Excel.

2) You need to use a pivot table to summarize the Accounts Receivable (AR) data (amounts unpaid as of 12/31/2020) by location. You should get a grand total of the Sales and Accounts Receivable data for Emerald Inc. and format the pivot table to have a business professional layout (i.e. two decimal places, using commas for numbers, nice header of the pivot table including company name, consistency in any table shading). There are 10 locations included in Emerald Inc.'s business.

3) Create an additional column with your table from item (1) to show each location's ending AR balance as a percentage of total company sales. Compare each location and identify which location has the highest Accounts Receivable as a percentage of sales, and which has the lowest. Make sure your added column's format aligns with the rest of the table.
a. Determine if there is any other way to analyze the data by location. Be creative and perform another analysis to see if any other locations have higher risk in relation to AR than other locations. Discuss your findings.
4) Calculate the 2020 Total Sales Amounts, 12/31/2020 AR ending balance, and AR turnover for each customer.
a. Pull the Customer Name into your analysis to show the results with Customer Names (Hint: you could use the vlookup function).
b. Create a Pivot Table that shows the Total Sales and Total ending AR as of 12/31/2020.
c. Add the Prior Year AR balance by customer to your analysis, which you will use to compute an average AR amount by customer.
d. Calculate AR Turnover by Customer.
i. AR turnover should be calculated as Net Credit Sales / Average Accounts Receivable.
ii. Emerald Inc. makes all sales on credit and does not have any cash sales transactions during the period.
e. Provide additional analysis and discussion on which customers are the highest risk and why (be creative and think of any additional ways to analyze the AR data to show which customer accounts are riskier). You should also discuss what other data would be useful to obtain to help in your AR Turnover analysis.

5) Based on the above location and customer analysis, what conclusion can you draw about the health of the company's accounts receivable practices?

Questions 6 to 14use an aging analysis to assess the risk of the company's Accounts Receivable and the reasonableness of Emerald Inc.'s Allowance of DoubtfulAccounts as of 12/31/2020. Emerald Inc. sets up an aging schedule with five different aging categories 0-30 days, 31-60 days, 61-90 days, 91 to 120 days, and over 120 days. Any invoices falling into the aging categories representing periods greater than 30 days are cause for an increasing sense of alarm, especially if they drop into the oldest time category. The period end is 12/31/2020 and should be the basis for your aging analysis.

Answer the following questions for the aging analysis. When you are asked to create tables, make sure that they can stand alone....meaning that a user should be able to look at the table or chart and know the company, the time period, the topic of the analysis, etc.
6) Using your sales data from question 4 where you obtained the sales by customer, pulled in the customer name, and determined which sales transactions were unpaid as of 12/31/2020, calculate the aging of each remaining unpaid balance for each sales transaction.
a. In the Excel File, create columns for each aging category (0-30, 31-60, 61-90, 91-120, over 120 days outstanding).
b. Calculate the time (days) that have lapsed from the sales date to the end of year (12/31/2020).
c. Place the unpaid sales transaction amounts as of 12/31/2020 into one of the five aging categories using a series ofIf/Then Statements.
d. Check your data. Make sure that your If/Then statements are properly putting the data into the right aging category. Make sure that all of your data has been put into an aging category if it is unpaid as of 12/31/2020.

7) Create a summary and show the aging of Accounts Receivable by each customer and by each aging category. Use a pivot table to create this analysis. There are 23 customers. Your pivot table should include a row for each customer and a column for each aging category. In addition, show the total AR balance as of 12/31/2020 for each customer in your table.

8) Determine the total Accounts Receivable balance as of 12/31/2020 for each aging category.
a. Assume that the AR balances from prior year were either paid or written off during the year. You are not given the payment data for payments of prior year AR transactions.

9) Calculate the allowance amount per aging category from your above analysis.
a. Based on past experience, Emerald Inc. applies the following percentages to the categories to estimate uncollectible accounts: under 31 days, 4.5%; 31-60 days, 8.3%; 61-90 days, 16.2%; 91-120 days, 22.8%; and over 120 days, 47.5%.
b. Show the total estimated allowance for doubtful accounts balance for 2020.

10) Create a pie chart that shows the % of the total allowance per aging category.
a. Make sure you create professionalism with the pie chart. Include numerical data for each pie slice and format good data labels and titles that provides enough details. A user should be able to look at the pie chart and understand the information clearly.

11) The company previously calculated and recorded theirAllowance for Doubtful Accounts,which has a current balance of $1,240,250. Based on your analysis, should an adjusting entry be recorded to adjust this ending balance? If so, what journal entry should be recorded to make the adjustment?

12) Analyze your results for question 6-11 and determine which (if any) customers are at a high risk of default and whether you feel the company has any issues with their collection of AR process? Provide detailed reasons for your conclusions.

13) Document anything that you find unusual within the data.

14) Discuss if there is any other data that you would like to obtain from the client to perform additional analysis on the company's risk in relation to Accounts Receivable.

Attachment:- Data Analytics Assignment.zip