Here is the excel template

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 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 Days Past Due Total Past Due Past Due Past Due $3,900 250 1,400 $3,900 1,650 9,200 4,875 6,100 3,200 2,000 2,200 4,000 Customer Anteater Feed Bear Necessities Camel Crunchies Elephant Elements Foxy Food Formula Giraffe Nibbles Hippopotamus Hummus Kangaroo Concoctions Lion Laments Monkey Manna Totals 1,000 4,875 3,000 1,600 2,500 800 600 400 200 200 5,800 5,800 2,000 500 500 1,000 1,000 4,000 2,500 1,575 $42,800 1,200 $11,150 300 $4,000 1,500 75 $5,775 $18,675 $3,200 Based on its past experience, the company has established the following percentages for estimating uncollectible accounts: Not Yet Due 1-30 Days Past Due 31-60 Days Past Due 61-90 Days Past Due 1% 3% 5% 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 133 ) = $2,387.50. 3. Open the latest file (AGING2). Create a new tab and call the tab "Es" 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 December's 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:34 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 AGINGS. 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. Aging Aging Accounts Receivable Date: 11/30/18 Amazing Animals Food Supply Aging Analysis 11/30/2018 $ Customer Anteater Feed Bear Necessities Camel Crunchies Elephant Elements Foxy Food Formula Giraffe Nibbles Hippopotamus Hummus Kangaroo Concoctions Lion Laments Monkey Manna Totals Not Yet Due 1-30 Days 31-60 Days 61-90 Days Over 90 Total Past Due Past Due Past Due Days Past Due 3,900 $ 3,900 1,650 1,400 250 9,200 2,000 2,200 4,000 1,000 4,875 4,875 6,100 3,000 2,500 600 3,200 1,600 800 400 200 200 5,800 5,800 4,000 2,000 500 500 1,000 2,500 1,500 1,000 1,575 1,200 300 75 42,800 $ 18,675 $ 11,150 $ 4,000 $ 5,775 $ 3,200 $ Not Yet Due 1-30 Days Past Due 31-60 Days Past Due 61-90 Days Past Due Over 90 Days Past Due Estimate of Probable Uncollectible Accounts Expense Total Percent Total Amount Uncollectible Uncollectible Formula 1 1% Formula 7 Formula 2 3% Formula 8 Formula 3 5% Formula 9 Formula 4 15% Formula 10 Formula 5 25% Formula 11 Formula 6 Formula 12