You have been tasked with providing the CFO of your firm an analysis of the receivables outstanding at the end of 2018 and advising her as to what should be the ending balance of the allowance for uncollectible accounts on the balance sheet. After learning in accounting about the different methods of estimating bad debt expense, you decided to analyze the aging and percentage of sales methods using historical data of the firm. You requested that the firm’s IT personnel provide you with historic information dating back five years (2013-2017). You are going to use that information to provide an informed data-based report backing your conclusions.
Once the data are generated you will see six worksheets, each pertaining to accounts receivables outstanding at each year-end. The worksheets “Dec 31 2013”, “Dec 31 2014”, “Dec 31 2015”, “Dec 31 2016”, and “Dec 31 2017” contain the historical data, and the worksheet “Dec 31 2018” 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 (thiscolumn is not in the “Dec 31 2018” 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:
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. It should include:
- A brief description of the task you were given, the data used and your method of analysis.
- A table presenting the analysis results in the following format*:(same format as solution page.
* Please be sure to include a heading and explanation of the table you present.
c. Your recommendations and the reasoning behind them – should you use percent of credit sales or aging of receivables? (Some questions you may want to consider are: is the aging method the right choice for the company given the historical data? Should all the years be included in the analysis? Should you use mean or median to calculate the percentages? Are there any extreme observations that should not be included in the analysis? Do the number of customers matter in a given year? Does the balance of total receivables vary significantly and what you conclude given these differences?)
d. Based on your recommendation, calculate the amount of bad debt expense you should report for 2018. Assume beginning balance of Allowance for Uncollectible Accounts is zero in 2018 (Provide the analysis of the credit sales or outstanding receivables in each aging category for 2018 that led you to this number in the table above).
notes to keep in mind:
-Calculation of writeoff by age group is done by taking all the writeoffs in a specific age group and dividing it by the total receivables in that age group.
-You can use a formula or calculate and type in the numbers. I recommend using a formula. It’s easier for you to check your work later, and it’s good practice for after you graduate. Your seniors and managers will expect you to use formulas (for when they are checking your work).
-Aging should be calculated on a specific date (in this case, the end of the year). Therefore the calculation is dec 31st – date of purchase.
-We are pretending that we are at year-end for 2018. Based on past data regarding how old a receivable is at year-end, we are trying to learn about how much ends up geting written off per age group. Based on that prior experience, we can look at our year-end receivables for 2018 and try to predict how much we think is uncollectible (and thus to be written off in the future).
–the customer numbers are independent across years (new customers, not repeating). The aging is from the date purchased to the end of the year (December 31). We are not paying attention to a due date, just how old the receivable is at year-end. For example, if the date purchased is December 30, that receivable would be 1 day old on December 31.
