Individual Data Analytics Project: Estimating Allowance for Doubtful Accounts

General:

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 doubtful accounts on the financial reports. 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 and use that information to provide an informed data$-$based report backing your conclusions.

You requested that the firm$$s IT personnel provide you with historic information dating back $5$ years. The information they supplied you contains the total sales on account for the year and the

receivables outstanding at year$-$end of $2013-2017$ for each customer as well as the dates they

were paid off the following year. Any outstanding receivables which were not paid over the course of the year following the invoice were written off and sold for cents on the dollar to a debt collector $($disregard the amount received for the sale of these receivables in your calculation$).$

Instructions to download the file:

$1.$ Download the file $$ReceivablesDataSimulation$.$xls$$ from Blackboard $($Individual Data Analytics Project folder$).$

$2.$ Open the file using Excel. The file is structured to produce a simulated database. To generate the database, click on the $$Generate Data$$ button. You will need to $$enable macros$$ on your program in order for the code to work.

$3.$ Save the file under the name $$AR$\_$LastName$\_$FirstName$\_$Mohideen.xls$$ you will need to

submit this file on Blackboard under this name, with all your data work, along with a short report.

Explanations and assumptions:

Once the data is generated you will see $6$ worksheets, each pertaining to accounts receivable outstanding at each year$-$end. Worksheets $$Dec $312013,$Dec $312014,$Dec $312015,$Dec

$312016,$ and $$Dec $312017$ contain the historical data, and worksheet $$Dec $312018$ contains

the outstanding receivables for which you need to estimate the allowance $(6$ identical data sheets

are created for grading purposes $$ see explanation below$).$ There are five columns of data in each

worksheet:

$$ Column A presents customer ID

$$ Column B presents the total amount invoiced during the year

ACC$3000$: Intermediate Accounting I

Spring $2024$: Professor Ira Weissman

$2$

$$ Column C presents the amounts outstanding at year end

$$ Column D presents the date the outstanding invoice was issued

$$ Column E presents the date the invoice was paid the following year $($this column is N$/$A in

the $$Dec $312018$worksheet$.)$

$$ The firm had a different number of customers each year as well as total receivable sales.

Assume that if the receivable was not paid over the course of the following year, the entire

amount is written off. In that case, the corresponding cell in column D remains blank.

Assume $4$ categories of aging receivables: $(1)0-30$ days outstanding, $(2)30-60$ days outstanding,

$(3)60-90$ days outstanding, and $(4)$ over $90$ days outstanding.

Analysis:

a$.$ For each year, calculate the percentage of accounts written off out of total sales on account.

b$.$ For each year, calculate the percent written off in each outstanding receivable age $($think about which dates are relevant when aging receivables$).$

So$,$ basicaly I need help with the excel formula to canculate the percent written off in each outstanding receivable age. I am trying everything and can't do it$.$

I need help with the excel formula in the question b$)$For each year, calculate the percent written off in each outstanding receivable age. What formula can I apply for Excel