Could I please get some help on completing this? I'm a bit stuck on how to even get it started....so if you can start by just doing item number 1 that would be great. Thanks.

EXTRA CREDIT 1 POINTS 15 Points BACKGROUND This exercise is designed to expand your Microsoft Excel skills using the subtotal, pivot tables and ifthen functions, as well as review your knowledge of accounting for customer receivables. REQUIRED Complete the following requirements, and upload a copy of your Microsoft Excel file to Blackboard. All of your work should be completed in one Excel file. You should create new worksheets for the various tasks, but complete all tasks within the same workbook. This is the one file you will upload to Blackboard. All numbers should be properly formatted (i.e. dollar amounts, dates, invoice numbers, etc.). Be diligent and thoughtful in completing the requirements. There is a small mistake in one of the formulas the instructions from KPMG has you use. If you check your work, you should be able to easily locate and correct it. If you need additional assistance with Microsoft Excel, KPMG University Connection has some excellent resources you are encouraged to explore. Registration on the website is free. http://www.kpmguniversityconnection.com/Audit/CurriculumSupport/TechnicalVideos/index.aspx?cid=we-CAMWEB2 1. Open the file titled \"KPMG - Excel - AR - worksheet.xlsx.\" Before completing the tasks outlined in the KPMG PDF file, add three new clients to the table with four transactions each. Invoice dates should range between June 1 and December 31, 2013, with some transactions falling in each quarter. 123 LLC sells its product for $23 per ounce. Your transactions should each be unique, both within your data file, and as compared to your classmates. Copy the data into two new sheets, one titled AR Detail - Pivot and one titled AR Detail - Aging. 2. Open the file titled \"KPMG - Excel - AR.pdf\" and complete the tasks using the revised data. Complete Task 1, determine the balances for each client as of 12/31/13, using the subtotals method in the original tab and the pivot table method in the new tab. 3. Complete Task 2, develop an A/R aging report, by following the directions in \"KPMG - Excel - AR.pdf\" in the AR Detail - Aging tab of the revised data file. Prepare an A/R aging report for 123 LLC as a whole, as well as for each customer. 4. Using the assumptions below, calculate the appropriate ending balance in the Allowance for Doubtful Accounts. Prepare the adjusting entry for bad debt expense, assuming the Allowance account had a balance of $1M prior to your adjustment. Create a new sheet within your Microsoft Excel file and show your adjusting entry and the calculation. Days Outstanding 0 - 30 Days 31 - 60 Days 61 - 90 Days 91 + Days Probability of Not Collecting 2% 7% 12% 33% 5. Review the Accounts Receivable disclosures for three large, Fortune 500 companies of your choosing. Include a screenshot of the three disclosures in a separate sheet within your Microsoft Excel file. Choose one of the disclosures to model the Accounts Receivable disclosure for 123 LLC after, and create a new sheet with your disclosure in it. As of 12/31/2013 Customer # Customer Name Invoice # 1 ABC Co. 1 ABC Co. 1 ABC Co. 1 ABC Co. 1 ABC Co. 1 ABC Co. 1 ABC Co. 1 ABC Co. 1 ABC Co. 10 BCD Co. 10 BCD Co. 10 BCD Co. 10 BCD Co. 10 BCD Co. 10 BCD Co. 10 BCD Co. 10 BCD Co. 10 BCD Co. 10 BCD Co. 10 BCD Co. 9 YZA Co. 9 YZA Co. 9 YZA Co. 9 YZA Co. 9 YZA Co. 9 YZA Co. 9 YZA Co. 9 YZA Co. 9 YZA Co. 9 YZA Co. 9 YZA Co. 9 YZA Co. 9 YZA Co. 9 YZA Co. 9 YZA Co. 9 YZA Co. 9 YZA Co. 9 YZA Co. 15 70669 70730 70762 70790 225615 225673 225727 225756 290298 290396 290413 290464 290468 290491 290507 320272 320342 320367 320409 344561 344568 344583 344606 344607 344623 344640 344658 344659 344752 344763 366623 366662 366667 366677 366718 366721 366734 Invoice Date Ozs Sold Amount Invoiced 6/1/2013 33,292 765,723 7/8/2013 33,284 765,528 7/8/2013 9,848 226,508 7/8/2013 37,042 851,957 7/8/2013 39,676 912,557 9/29/2013 8,190 188,359 9/29/2013 22,879 526,207 9/29/2013 35,329 812,566 9/29/2013 23,493 540,335 11/3/2013 7,456 171,486 11/3/2013 5,542 127,456 11/3/2013 23,536 541,326 11/3/2013 11,998 275,949 11/3/2013 37,458 861,529 11/3/2013 4,397 101,123 11/3/2013 10,139 233,202 11/18/2013 19,681 452,653 11/18/2013 20,280 466,436 11/18/2013 26,070 599,604 11/18/2013 40,676 935,557 12/1/2013 13,347 306,984 12/1/2013 37,378 859,687 12/1/2013 33,730 775,786 12/1/2013 39,975 919,417 12/1/2013 13,028 299,638 12/1/2013 6,765 155,598 12/1/2013 13,989 321,739 12/1/2013 25,919 596,132 12/1/2013 5,751 132,272 12/1/2013 5,873 135,090 12/1/2013 43,307 996,066 12/13/2013 28,036 644,830 12/13/2013 39,804 915,501 12/13/2013 38,936 895,537 12/13/2013 25,452 585,407 12/13/2013 15,173 348,975 12/13/2013 10,350 238,044 12/13/2013 12,136 279,133 A/R Aging Report Test your ExcelTM Skills using Subtotal, PivotTables, and IF-THEN formulas Assume that you are auditing the A/R balance for the company, 123 LLC as of 12/31/13. In conjunction with your audit procedures, you want to analyze 123 LLC's client balances in further detail; however, the Company is unable to provide an A/R Aging analysis or any information which details the total balance for each of its clients. Assuming the Company's standard payment terms are 30 days for its clients, develop your own subtotals by client and A/R aging report. Task 1: Determine the balances for each client as of 12/31/13 Option 1: Using subtotals Open the file \"KPMG-Excel-AR-worksheet.xlsx\". Save a copy of the file. 1. Click on the \"AR Detail\" worksheet tab. Sort the A/R Detail by customer name. 2. Click on any cell in the detail, navigate to the \"Data\" tab in the MS Office Ribbon, and select \"Subtotal\". Watch a video about how to use the Subtotal function: http://www.kpmguniversityconnection.com/Audit/ CurriculumSupport/TechnicalVideos/Subtotal.aspx 3. Fill out the pop up box as appropriate (see at right). (NOTE: In this instance Customer # could be selected instead of Customer Name and produce the same result.) 4. Select \"OK\" and subtotals will be automatically added to the data. An outline will be added to the left of the row labels with \"1\