Assignment
Tim, the owner of Ace Tile Company, would like you to manage his accounts receivable file, making it automated and organized so that he can easily locate late payers. Open the file Ace_Tile_Company_Accounts_Receivable_Database_and_Aging_Report.xls to see the outline of the accounts receivable file. It contains a sample of accounts receivable list. You need to type in your student ID and Today's Date as prompted when you access the file. The days outstanding for each account should be automatically calculated. You also need to produce an aging report that automatically classifies the records according to four categories of lateness: current (30 days or less), between 31 days and 60 days, between 61 days and 90 days, and over 90 days.
Tim would also like some reporting mechanism to identify late payers so that he can phone them and expedite payment. The accounts receivable file should be sorted first by greatest number of days outstanding to smallest and then by highest invoice balance to lowest. A listing of customers more than 60 days late with outstanding balances over $1200 would also be useful.
Tim's company will upgrade all computers to Windows 10 soon. Therefore, the Excel application (included in MS-Office XP) that has been used since 2002 must be replaced as it is incompatible with the new operating system. Tim has heard of open source software and cloud computing in a small business forum. You are asked to investigate possible replacement options and make a recommendation. The recommended spreadsheet application should be able to process Accounts Receivable largely the same way as it has been done.
Specific requirements:
1. Enter your student ID at cell address C10.
2. Enter today's date at cell address J10 (do not use today function, but just enter date in the form of mm/dd/yyyy like 02/25/2016).
3. Calculate the number of days each invoice has been outstanding in the cell range F15:F35. This is the difference between the transaction date and today's date at J10. Use absolute address if necessary.
4. Classify the invoices using formulas into the four categories using IF statements in the cell range G15:J35. That is, you need to compare the outstanding day of each account with each category (less than 30 days, between 31 and 60 days, between 61 and 90 days, or more than 90 days). Put the transaction amount in the right category, and put 0 in the other categories. For example, if the outstanding day at F15 is 35 days, then put the transaction amount ($63.41) at H15, and put 0 at G15, I15, and H15. Use the AND operator or embedded if statements to express "between .. and.." condition.
5. Enter formulas to calculate sums at cell addresses E38 (total transaction amount), G38 (total current amount), H38 (total 31-60 days amount), I38 (total 61-90 days amount), and J38 (total over 90 days amount)
6. Sort the database in descending order using the number of days outstanding as the primary sort key and the amount as the secondary sort key.
7. At E39, use Count function to compute the number of transactions. At G39 to J39, use CountIF to compute the number of transactions for each aging group.
8. In "late payers" worksheet, use filter to display a list of customers more than 60 days late with outstanding balances over $1200.
9. Return to "Accounts Receivable" worksheet, and in the designated areas, input the recommendation about replacing the current spreadsheet software. At G44, enter recommended spreadsheet application or office suite. At G55, enter cost information. At G46:G50, enter reasons why you recommended this option. At G52:G55, enter sources used for research and recommendation.
10. Format the spreadsheet in an attractive and readable manner.
Attachment:- Ace Tile Company Accounts Receivable Database and Aging Report.xls