90.2K

Verified Solution

Link Copied!

Just explain to me how can i answer the following questions, be specific like with the formulars and cells, step by step for each question. I have attached also some images from the data itself for reference

contains the outstanding receivables for which you need to estimate the allowance. The firm had a different number of customers each year as well as total credit sales. There are five columns in each worksheet: - Column A: the customer ID - Column B: the total sale on account and invoiced during the year - Column C: the account receivable balance outstanding at year-end - Column D: the date when the invoice was issued - Column E: the date when the invoice balance was paid off in the following year (this column is not in the "Dec 312018 " worksheet). 1 Assume that if the receivable was not paid over the course of the following year, the entire amount would be written off. In that case, the corresponding cell in column E remains blank. Assume four categories of aging receivables: (1) 0-30 days outstanding, (2) 31-60 days outstanding, (3) 61-90 days outstanding, and (4) above 90 days outstanding. The worksheet "Solution" contains a table which needs to be filled with the answers. The "Solution" sheet is the only sheet that will be graded - therefore, all answers should be placed on this sheet. Do not put anything on other worksheets. Excel files will be auto graded. For the system to work, you cannot change the names of the data sheets or any of the data columns (a-e in the files) - that includes adding sums, averages or anything else to these columns. You can add as many work sheets as you like to the file but do not change / add any data to columns A-E in the existing data sheets. Requirements: 1. Submit Excel file "AR LastName FirstName StudewtW" to Blackboard - the "Solution" worksheet must have: a. For each year, calculate the percentage of account receivables written off out of total credit sales. b. For each year, calculate the percentage of account receivables written off in each category of outstanding receivable age (think about which dates are relevant when assigning the categories of aging receivables). c. Collect the data for years 2013-2017 (total outstanding receivables, percentage of receivables written off relative to credit sales, and percentage of receivables written off in each aging category of outstanding receivables), and fill in the "Solution" worksheet. 2. You are required to prepare and submit a short report of your findings on Blackboard. The report should not be more than one page long and should be submitted under the name "AR_LastName_FirstName_StudentID.pdf'. The report should provide the following: a. A brief description of the task you were given, the data used and your method of analysis. b. A table presenting the analysis results in the following format*: \begin{tabular}{c|c|c|c|c|c|c} \hline \hline Year & & & & \begin{tabular}{c} Percent of sales \\ on account \\ written off \end{tabular} & \multicolumn{5}{|c}{\begin{tabular}{c} Percent of outstanding receivable written off \\ by receivable age group \end{tabular}} \\ \hline & & & 030 & 3160 & 6190 & Over 90 \\ \hline 2013 & & & & & & \\ \hline 2014 & & & & & & \end{tabular} contains the outstanding receivables for which you need to estimate the allowance. The firm had a different number of customers each year as well as total credit sales. There are five columns in each worksheet: - Column A: the customer ID - Column B: the total sale on account and invoiced during the year - Column C: the account receivable balance outstanding at year-end - Column D: the date when the invoice was issued - Column E: the date when the invoice balance was paid off in the following year (this column is not in the "Dec 312018 " worksheet). 1 Assume that if the receivable was not paid over the course of the following year, the entire amount would be written off. In that case, the corresponding cell in column E remains blank. Assume four categories of aging receivables: (1) 0-30 days outstanding, (2) 31-60 days outstanding, (3) 61-90 days outstanding, and (4) above 90 days outstanding. The worksheet "Solution" contains a table which needs to be filled with the answers. The "Solution" sheet is the only sheet that will be graded - therefore, all answers should be placed on this sheet. Do not put anything on other worksheets. Excel files will be auto graded. For the system to work, you cannot change the names of the data sheets or any of the data columns (a-e in the files) - that includes adding sums, averages or anything else to these columns. You can add as many work sheets as you like to the file but do not change / add any data to columns A-E in the existing data sheets. Requirements: 1. Submit Excel file "AR LastName FirstName StudewtW" to Blackboard - the "Solution" worksheet must have: a. For each year, calculate the percentage of account receivables written off out of total credit sales. b. For each year, calculate the percentage of account receivables written off in each category of outstanding receivable age (think about which dates are relevant when assigning the categories of aging receivables). c. Collect the data for years 2013-2017 (total outstanding receivables, percentage of receivables written off relative to credit sales, and percentage of receivables written off in each aging category of outstanding receivables), and fill in the "Solution" worksheet. 2. You are required to prepare and submit a short report of your findings on Blackboard. The report should not be more than one page long and should be submitted under the name "AR_LastName_FirstName_StudentID.pdf'. The report should provide the following: a. A brief description of the task you were given, the data used and your method of analysis. b. A table presenting the analysis results in the following format*: \begin{tabular}{c|c|c|c|c|c|c} \hline \hline Year & & & & \begin{tabular}{c} Percent of sales \\ on account \\ written off \end{tabular} & \multicolumn{5}{|c}{\begin{tabular}{c} Percent of outstanding receivable written off \\ by receivable age group \end{tabular}} \\ \hline & & & 030 & 3160 & 6190 & Over 90 \\ \hline 2013 & & & & & & \\ \hline 2014 & & & & & & \end{tabular}

Answer & Explanation Solved by verified expert

See Answer