Aging method of determining bad debt expense

brma0101

New Member
Joined
Oct 7, 2007
Messages
4
My company uses the aging method of determining bad debt expense. I need to calculate the appropriate balance in the allowance for bad debts account. Based on historical analysis: amounts less than 16 days outstanding have a 98% chance of being collected; between 1 and 30 days probability is 90%; 31-45 days=85%; 46-60=75%; 61-75=40%; and over 75 days outstanding, there is no chance of collection.

Please help me build a spreadsheet that will have the following features:
1.Calculates the appropriate balance in the allowance account
2.Allows the user to enter the dollar amount of accounts outstanding in each category. These amounts are available from another computer report, the aged trial balance.
3.Is easy to follow and understand
4.Does not allow unauthorized changes to likelihood of collection percentages
 

Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.
I can't set-up your spreadsheet but I think that you will need a lookup table like this - columns A and B constitute the table, column D is the query and column E is the result

Sheet4

<table border="1" cellspacing="0" cellpadding="0" style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; padding-left:2pt; padding-right:2pt; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:64px;" /><col style="width:71px;" /><col style="width:13px;" /><col style="width:116px;" /><col style="width:75px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >A</td><td >B</td><td >C</td><td >D</td><td >E</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td >Days</td><td >Prob</td><td > </td><td >Days Outstanding</td><td >Probability</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td style="text-align:right; ">1</td><td style="text-align:right; ">0.98</td><td > </td><td style="text-align:right; ">5</td><td style="text-align:right; ">0.98</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td style="text-align:right; ">17</td><td style="text-align:right; ">0.9</td><td > </td><td style="text-align:right; ">18</td><td style="text-align:right; ">0.9</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td style="text-align:right; ">31</td><td style="text-align:right; ">0.85</td><td > </td><td style="text-align:right; ">40</td><td style="text-align:right; ">0.85</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >5</td><td style="text-align:right; ">46</td><td style="text-align:right; ">0.75</td><td > </td><td style="text-align:right; ">60</td><td style="text-align:right; ">0.75</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >6</td><td style="text-align:right; ">61</td><td style="text-align:right; ">0.4</td><td > </td><td style="text-align:right; ">74</td><td style="text-align:right; ">0.4</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >7</td><td style="text-align:right; ">75</td><td >No chance</td><td > </td><td style="text-align:right; ">100</td><td >No chance</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >8</td><td > </td><td > </td><td > </td><td style="text-align:right; ">3</td><td style="text-align:right; ">0.98</td></tr></table>
<table style="font-family:Arial; font-size:10pt; border-style: groove ;border-color:#00ff00;background-color:#fffcf9; color:#000000; "><tr><td >Spreadsheet Formulas</td></tr><tr><td ><table border = "1" cellspacing="0" cellpadding="2" style="font-family:Arial; font-size:9pt;"><tr style="background-color:#cacaca; font-size:10pt;"><td >Cell</td><td >Formula</td></tr><tr><td >E2</td><td >=VLOOKUP(D2,$A2:B$2:$B$7,2,TRUE)</td></tr><tr><td >E3</td><td >=VLOOKUP(D3,$A$2:B3:$B$7,2,TRUE)</td></tr><tr><td >E4</td><td >=VLOOKUP(D4,$A$2:B4:$B$7,2,TRUE)</td></tr><tr><td >E5</td><td >=VLOOKUP(D5,$A$2:B5:$B$7,2,TRUE)</td></tr><tr><td >E6</td><td >=VLOOKUP(D6,$A$2:B6:$B$7,2,TRUE)</td></tr><tr><td >E7</td><td >=VLOOKUP(D7,$A$2:B7:$B$7,2,TRUE)</td></tr><tr><td >E8</td><td >=VLOOKUP(D8,$A$2:B8:$B$7,2,TRUE)</td></tr></table></td></tr></table>
Excel tables to the web - Excel Jeanie Html 4
 
Upvote 0
Great start, it will take a week or two to create this spreadsheet with other constaints causing bottlenecks. Thanks a million or at least 100%
 
Upvote 0
I'm wondering, how can the dollar amounts be added to this spreadsheet?
1.Calculates the appropriate balance in the allowance account.
2.Allows the user to enter the dollar amount of accounts outstanding in each category. These amounts are available from another computer report, the aged trial balance.
 
Upvote 0

Forum statistics

Threads
1,213,558
Messages
6,114,296
Members
448,564
Latest member
ED38

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top