So I have this project and I'm trying all the formulas but can't get right. Can someone help me with that please? Thank you!! I really appreciate it!
6 worksheets, each pertaining to accounts receivable outstanding at each year-end.
Worksheets “Dec 31 2013”, “Dec 31 2014”, “Dec 31 2015”, “Dec31 2016”, and “Dec 31 2017” contain the historical data, and worksheet “Dec 31 2018” contain the outstanding receivables for which you need to estimate the allowance. There are five columns of data in each worksheet:
- Column A presents customer ID
- Column B presents the total amount invoiced during the year
- 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 inthe “Dec 31 2018”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.
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).
Considering I can't add any column too, the example below but doesn't work. Below is an example of the data, but the size of my analysis is much bigger.
I have to fill the yellow columns. What am I doing wrong with the formula to calculate Percentage of outstanding receivables written off by receivable age group?
'=SUMIFS('Dec 31 2013'!C3:C311,'Dec 31 2013'!D3:D311,">="&0,'Dec 31 2013'!D3:'Dec 31 2013'!D311,"<="&30,'Dec 31 2013'!E3:'Dec 31 2013'!E311,"")/SUM('Dec 31 2013'!B3:'Dec 31 2013'!B311)
6 worksheets, each pertaining to accounts receivable outstanding at each year-end.
Worksheets “Dec 31 2013”, “Dec 31 2014”, “Dec 31 2015”, “Dec31 2016”, and “Dec 31 2017” contain the historical data, and worksheet “Dec 31 2018” contain the outstanding receivables for which you need to estimate the allowance. There are five columns of data in each worksheet:
- Column A presents customer ID
- Column B presents the total amount invoiced during the year
- 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 inthe “Dec 31 2018”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.
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).
Considering I can't add any column too, the example below but doesn't work. Below is an example of the data, but the size of my analysis is much bigger.
I have to fill the yellow columns. What am I doing wrong with the formula to calculate Percentage of outstanding receivables written off by receivable age group?
'=SUMIFS('Dec 31 2013'!C3:C311,'Dec 31 2013'!D3:D311,">="&0,'Dec 31 2013'!D3:'Dec 31 2013'!D311,"<="&30,'Dec 31 2013'!E3:'Dec 31 2013'!E311,"")/SUM('Dec 31 2013'!B3:'Dec 31 2013'!B311)
