P6 AGING ACCOUNTS RECEIVABLE (AGING) LEARNING OBJECTIVES Prepare an accounts receivable aging schedule. Apply the allowance method of accounting for uncollectible accounts. Interpret changes in collection patterns. Alter the file to include one additional customer. Create a chart showing estimated uncollectible balances by age category. PROBLEM DATA On October 31, 2012, Easy Breezy Beauty Supplies had the following amounts due from its customers: Not Yet Due $ 2,600 31-60 Days Past Due 1-30 Days Past Due $ 700 Total $ 3,600 Over 90 61-90 Days Days Past Past Due Due $ 1,300 Customer Attitudes for Hair Beauty Box Hair Affair 7,500 10,000 5,600 3,000 1,900 3,000 $2,000 $2,000 Murnal or Commy, GHG 4 Tetod My Hanu Ebook Webdeo Hair Affair 10,000 3,000 3,000 $2,000 $2,000 Headliners 3,800 3,800 Salon Ooh La La 800 800 Hair Shear 3,500 1,000 640 1,400 460 Paradise Totals $ 30,200 $ 15,000 $ 6,600 $2,640 $3,400 $2,560 Based on its past experience, the company has established the following percentages for estimating uncollectible accounts: Age Interval Percentage Uncollectible Not yet due 1% 1-30 days past due 3 31-60 days past due 5 61-90 days past due 10 Over 90 days past due 25 REQUIREMENT 1. You have been asked to estimate the total amount of uncollectible accounts expense as of October 31 by completing the file called AGING. Quick Tour Print Search Annotations Accessibility Bookma 2. Open the file AGING from the website for this book at cengagebrain.com. Entor all formulas where indicated on the worksheet (hint: FORMULA1 Is =C17). Enter your name in cell A1 Save your completed file as AGING2. Print the worksheet. Also print your formulas. Check figure: Total uncollectible (cell F28). $1,460. 3. In the spaces provided below, record the journal entry for the provision for uncollectible accounts under each of the following independent assumptions: a. The Allowance for Doubtful Accounts before adjustment has a credit balance of $500, b. The Allowance for Doubtful Accounts before adjustment has a debit balance of $250 C. Assume that October's credit sales were $70,000. Uncollectible accounts expense is estimated at 2% of sales. WHAT-IF ANALYSIS 4. Erase the aging information for October and enter the following information for November 30, 2012 1-20 31260 wor 90 61.90 Print Search Annotations Accessory Bookmark 4. Quick Tour Erase the aging information for October and enter the following information for November 30, 2012 Days Not Yet Due $3,400 1-30 31-60 61-90 Over 90 Days Days Days Past Due Past Due Past Due Past Due $ 1,600 $ 700 3,000 3,000 $ 2,000 $ 2,000 5,000 Customer Total Attitudes $5,700 for Hair Hair Affair 10,000 Hello 5,000 Gorgeous Ooh La La 800 Hair Shear 6,040 Paradise Tai 17,800 Salon Totals $ 45,340 800 3,000 1.000 640 1,400 17,800 $ 29,200 $4,600 $ 4.700 $ 2,640 $ 4,200 Save the results as AGING4. Print the worksheet. Has the estimated total uncollectible accounts increased or decreased in November? Explain. CHART ANALYSIS 5. a. With AGING4 still on the screen, click the Chart sheet tab. Descrbe what is being plotted out on this chart b. Open the AGING2 file and click the Chart sheet tab. Compare the pattern of this chart to the one for AGING4. Note any trends below. so $ When the assignment is complete, close the files without saving them again. 9 TICKLERS (optional) Worksheet. Suppose that there had been one additional customer with a balance due at November 30. This customer was Double Take Hair, and it owed $2,600 that was not yet due Alter the AGING4 worksheet to allow entry of this information. Use the Print Preview command to make sure that the worksheet will print neatly on one page, and then print the worksheet. Save the completed file as AGINGT. Chart. Using the AGING2 file, develop a 3-D column chart to show the total estimated uncollectible amounts (in dollars) for each age category. No Chart Data Table is needed; use B23 to B27 as the X-axis (or make up your own labels) and then, holding down the CTRL key. select F23 to F27 as the range of values to be plotted. Enter your name somewhere on the chart. Save the file again as AGING2. Print the chart NEXT PAGE 1-30 2 AGANG 3 A counce 4 5 6 Date: October 31, 2012 7 Analysis of Accounts Receivable by Age B Not 31-60 61-00 9 Over 90 Yet Days Days Customer Days 10 Days Total Due Past Due Past Due Past Due 11 Attitudes For Hair Past Due $4,600 $2,600 $700 $1,300 12 Beauty Box 7.500 5.600 1,900 13 Hair Affair 10.000 3,000 3,000 2,000 2,000 14 Headliners Salon 3.800 3,800 15 Ooh La La Hair 800 800 16. Shear Paradise 3,500 1.000 640 460 17. Totals $30,200 $15,000 $6.600 32,640 $3,400 52,560 18 19 Estimate of Probable 20 Uncollectible Accounts Expense 21 Total Percent Total 22 Amount Uncol Uncoll 23 Not yet due FORMULA1 1% FORMULA 24 1-30 days past due FORMULAZ 3% FORMAULS 25 31-60 days past due FORMULAJ 5% FORMUALS 26 61-90 days past due FORMULA4 10% FORMULA 10 27 Over 90 days past due FORMULAS 25% FORMULA 11 28 Totals FORMULA FORMULA 12 29 ver 1400 ok P6 AGING ACCOUNTS RECEIVABLE (AGING) LEARNING OBJECTIVES Prepare an accounts receivable aging schedule. Apply the allowance method of accounting for uncollectible accounts. Interpret changes in collection patterns. Alter the file to include one additional customer. Create a chart showing estimated uncollectible balances by age category. PROBLEM DATA On October 31, 2012, Easy Breezy Beauty Supplies had the following amounts due from its customers: Not Yet Due $ 2,600 31-60 Days Past Due 1-30 Days Past Due $ 700 Total $ 3,600 Over 90 61-90 Days Days Past Past Due Due $ 1,300 Customer Attitudes for Hair Beauty Box Hair Affair 7,500 10,000 5,600 3,000 1,900 3,000 $2,000 $2,000 Murnal or Commy, GHG 4 Tetod My Hanu Ebook Webdeo Hair Affair 10,000 3,000 3,000 $2,000 $2,000 Headliners 3,800 3,800 Salon Ooh La La 800 800 Hair Shear 3,500 1,000 640 1,400 460 Paradise Totals $ 30,200 $ 15,000 $ 6,600 $2,640 $3,400 $2,560 Based on its past experience, the company has established the following percentages for estimating uncollectible accounts: Age Interval Percentage Uncollectible Not yet due 1% 1-30 days past due 3 31-60 days past due 5 61-90 days past due 10 Over 90 days past due 25 REQUIREMENT 1. You have been asked to estimate the total amount of uncollectible accounts expense as of October 31 by completing the file called AGING. Quick Tour Print Search Annotations Accessibility Bookma 2. Open the file AGING from the website for this book at cengagebrain.com. Entor all formulas where indicated on the worksheet (hint: FORMULA1 Is =C17). Enter your name in cell A1 Save your completed file as AGING2. Print the worksheet. Also print your formulas. Check figure: Total uncollectible (cell F28). $1,460. 3. In the spaces provided below, record the journal entry for the provision for uncollectible accounts under each of the following independent assumptions: a. The Allowance for Doubtful Accounts before adjustment has a credit balance of $500, b. The Allowance for Doubtful Accounts before adjustment has a debit balance of $250 C. Assume that October's credit sales were $70,000. Uncollectible accounts expense is estimated at 2% of sales. WHAT-IF ANALYSIS 4. Erase the aging information for October and enter the following information for November 30, 2012 1-20 31260 wor 90 61.90 Print Search Annotations Accessory Bookmark 4. Quick Tour Erase the aging information for October and enter the following information for November 30, 2012 Days Not Yet Due $3,400 1-30 31-60 61-90 Over 90 Days Days Days Past Due Past Due Past Due Past Due $ 1,600 $ 700 3,000 3,000 $ 2,000 $ 2,000 5,000 Customer Total Attitudes $5,700 for Hair Hair Affair 10,000 Hello 5,000 Gorgeous Ooh La La 800 Hair Shear 6,040 Paradise Tai 17,800 Salon Totals $ 45,340 800 3,000 1.000 640 1,400 17,800 $ 29,200 $4,600 $ 4.700 $ 2,640 $ 4,200 Save the results as AGING4. Print the worksheet. Has the estimated total uncollectible accounts increased or decreased in November? Explain. CHART ANALYSIS 5. a. With AGING4 still on the screen, click the Chart sheet tab. Descrbe what is being plotted out on this chart b. Open the AGING2 file and click the Chart sheet tab. Compare the pattern of this chart to the one for AGING4. Note any trends below. so $ When the assignment is complete, close the files without saving them again. 9 TICKLERS (optional) Worksheet. Suppose that there had been one additional customer with a balance due at November 30. This customer was Double Take Hair, and it owed $2,600 that was not yet due Alter the AGING4 worksheet to allow entry of this information. Use the Print Preview command to make sure that the worksheet will print neatly on one page, and then print the worksheet. Save the completed file as AGINGT. Chart. Using the AGING2 file, develop a 3-D column chart to show the total estimated uncollectible amounts (in dollars) for each age category. No Chart Data Table is needed; use B23 to B27 as the X-axis (or make up your own labels) and then, holding down the CTRL key. select F23 to F27 as the range of values to be plotted. Enter your name somewhere on the chart. Save the file again as AGING2. Print the chart NEXT PAGE 1-30 2 AGANG 3 A counce 4 5 6 Date: October 31, 2012 7 Analysis of Accounts Receivable by Age B Not 31-60 61-00 9 Over 90 Yet Days Days Customer Days 10 Days Total Due Past Due Past Due Past Due 11 Attitudes For Hair Past Due $4,600 $2,600 $700 $1,300 12 Beauty Box 7.500 5.600 1,900 13 Hair Affair 10.000 3,000 3,000 2,000 2,000 14 Headliners Salon 3.800 3,800 15 Ooh La La Hair 800 800 16. Shear Paradise 3,500 1.000 640 460 17. Totals $30,200 $15,000 $6.600 32,640 $3,400 52,560 18 19 Estimate of Probable 20 Uncollectible Accounts Expense 21 Total Percent Total 22 Amount Uncol Uncoll 23 Not yet due FORMULA1 1% FORMULA 24 1-30 days past due FORMULAZ 3% FORMAULS 25 31-60 days past due FORMULAJ 5% FORMUALS 26 61-90 days past due FORMULA4 10% FORMULA 10 27 Over 90 days past due FORMULAS 25% FORMULA 11 28 Totals FORMULA FORMULA 12 29 ver 1400 ok