Problem 7 AGING ACCOUNTS RECEIVABLE (AGING) Learning Objectives Prepare an accounts receivable aging schedule. Apply the allowance method of accounting for uncollectible accounts. Apply the percentage of sales of accounting for uncollectible accounts. Alter the file to include new customers. Create a chart showing estimated uncollectible balances by age category Problem Data On 11/30/2018 Amazing Animal Feed, a sole proprietor, had the following amounts due from its customers: Not Yet Due 1-30 Days 31-60 Days 61-90 Days Over 90 Customer Total Past Due Past Due Past Due Days Past Due Anteater Feed $3,900 $3,900 Bear Necessities 1,650 1,400 250 Camel Crunchies 9,200 2,000 2,200 4,000 1,000 Elephant Elements 4,875 4,875 Foxy Food Formula 6,100 3,000 2,500 600 Giraffe Nibbles 3,200 1,600 800 400 200 200 Hippopotamus Hummus 5,800 5,800 Kangaroo Concoctions 4,000 2,000 500 500 1,000 Lion Laments 2,500 1,500 1,000 Monkey Manna 1,575 1,200 300 75 Totals $42,800 $18,675 $11,150 $4,000 $5,775 $3,200 Based on its past experience, the company has established the following percentages for estimating uncollectible accounts: Not Yet Due 1% 1-30 Days Past Due 3% 31-60 Days Past Due 5% 61-90 Days Past Due 15%

Over 90 Days Past Due 25% REQUIREMENTS: 1. You have been asked to estimate the total amount of the uncollectible accounts expense as of 11/30/18 by completing the file called AGING. 2. Open the file called AGING. Enter all formulas where indicated on the worksheet. (Hint: Formula 1 is =F22. Save your completed File as AGING2. Put the appropriate header on your spreadsheet. Print the worksheet. Also print the formulas using Control ~. Check figure: Total Uncollectible (cell J33 ) = $2,387.50. 3. Open the latest file (AGING2). Create a new tab and call the tab JEs In the new tab, 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 $800. b. The Allowance for Doubtful Accounts before adjustment has a debit balance of $400. c. Assume that Decembers credit sales were $84,000. Uncollectible accounts expense is estimated at 2% of sales. (This percentage of sales method ignores the results of the aging. Instead the credit sales are multiplied by a historical percentage. The results is the amount of the adjusting entry. Any existing balance in the allowance account is ignored for purposes of determining the adjusting journal entry. Save the file as AGING3.

4. Manipulate the data: Open up the latest file (AGING3) Create a new tab and call it Manipulate. Copy the data from the most recent aging to this new tab. Then adjust the data for the following. Suppose the company discovered two additional customers with balances due at 11/30/2018. These customers were as follows: a. Nighthawk Nibbles owed $3,200 that was not yet due. b. Ostrich Morsils - Owed $1,800. Of that amount $1,000 was not yet due and $800 was 1-30 days past due. Save the File as AGING4. Make sure that all the formulas still work. (Hint: You may need to adjust them for the newly added data.) Make sure the worksheet will fit neatly on one page. 5. Chart Using the Aging4 file, create a new tab and call it Chart. 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 F30:F34 as the X-axis and then holding down the CTRL key, select J30:J34 as the range of values to be plotted. (Note: these cell references may be different if you added more than two lines for requirement 4. Adjust accordingly.) Give the chart an appropriate 3-line title. Add data labels to enhance the chart. Put your chart on the newly created separate worksheet called Chart.. Save the file as AGING5. 6. Formulas: Create a new tab and call it formulas. Copy over your completed Aging (after you added the two new accounts AGING4). Show the formulas by using Control and Tilde. You may need to widen columns to show me your complete formulas. Re-save your work. Upload your File: After reviewing your most recent file for completeness, and making sure all parts have been answered, Save the file as AGING FINAL + Your last name. Upload the final file using Canvas to me for grading. Summary of Whats Due: (I will be looking at your ONE submitted file. However, make sure that your work is set up to print out correctly. These are the Printouts that I will be reviewing in your final file.) Printout of completed template for the November Aging. Printout of the journal entries Printout of the new aging with the two new accounts. Printout of Chart

Printout of all formulas requested