Doug Gold is an accountant for Ward Consulting, a firm that provides research services for various corporate and government agencies. Each month, Doug provides the controller with an analysis of the outstanding accounts. Doug uses Excel to track these accounts. He asks you to enter formulas to allow him to analyze the data. Complete the following:
1. Open the Receivables workbook, then save the workbook as Receivables Overdue.
2. In the Documentation worksheet, enter your name and the date.
3. In the Invoices worksheet, in cell B1, enter 7/1/2016 as the current date. Note the defined name CurrentDate has been assigned to cell B1.
(Troubleshoot) 4. The sales rep commission rate varies for each sales rep. In column D, Doug used a VLOOKUP function to look up the commission rate for each sales rep, and then multiplied the commission rate by the invoice amount to calculate the commission. Although the first two rows in column D of the Excel table named Aging display the correct commission, all the other cells display #N/A. Find the problem with the formulas in the Commission column and fix it.
5. In column G, calculate the days past due. If the number of days since the invoice was sent (CurrentDate – Invoice Date) is greater than 30, calculate the days past due (Current Date – Invoice Date – 30); otherwise, enter 0.
6. Create the following formulas to assign the value in the Invoice Amount column to one of five columns— Current, 1-30 days, 31-60 days, 61-90 days, and Over 90 days:
a. In the Current column, create a formula to display the invoice amount (column F) in the Current column if the number of days past due is 0.
b. In the 1-30 days column, create a formula to display the invoice amount if the number of days past due is greater than or equal to 1 and less than or equal to 30.
c. In the 31-60 days column, create a formula to display the invoice amount if the number of days past due is greater than or equal to 31 and less than or equal to 60.
d. In the 61-90 days column, create a formula to display the invoice amount if the number of days past due is greater than or equal to 61 and less than or equal to 90.
e. In the Over 90 days column, create a formula to display the invoice amount if the number of days past due is greater than or equal to 91 days.
f. Format columns H through L in the Accounting format with two decimal places.
7. The invoice amount (column F) for each invoice can only appear once in columns H through L. In column N, do the following to create a formula to verify this rule.
a. In cell N3, enter the label Error Check.
b. In the range N4:N105, enter a formula using the IF and COUNT functions. The logical test of the IF function counts the number of the cells that have an entry in columns H through L for each invoice. If the count is greater than one, the formula displays Error; otherwise, it leaves the cell blank. (Hint: click to select one cell, not label)
8. Copy the Invoices worksheet to a new sheet and name is Overdue Accts. (Press Ctrl + L-button and drag and drop to copy a sheet.) In the Overdue Accts worksheet, do the following:
a. Filter the records so only invoices whose balance is past due are displayed.
b. Sort the filtered data by invoice date (oldest first).
c. Include a Total row in this table and display sums for columns I through L. (Quick Analysis)
d. Hide columns C, D, F, H, N (Home – Format – Hide and Unhide)
e. Remove the filter buttons and gridlines from the table. (Hint: Use options on VIEW tab and the TABLE TOOLS DESIGN tab.)
(Troubleshoot) 9. In the Invoice Reports worksheet, Doug used the COUNTIF function to count the number of invoices for each sales rep. The formulas he created display only zeros. Fix the formulas in the range B3:B7 so that they display the number of invoices processed by each sales rep.
10. In the Invoice Reports worksheet, complete the Sales Rep Analysis report. In the Commission and Total Amount columns (columns C and D), use the SUMIF function to summarize commissions (column D in the Aging table in the Invoice worksheet) and the invoice amount (column F in the Aging table) for each sales rep. In row 7 of the report, calculate the totals. Format these columns appropriately.
11. In the Invoice Reports worksheet, complete the Accounts Receivable Aging report in the range F1:H8 by creating formulas that count the number of invoices for each group in the invoices worksheet and sum the total amounts for those invoices.
12. In the Invoice Reports worksheet, in the range A12:B17, use the COUNTIF, SUMIF, and AVERAGEIF functions to complete the report. (Hint: The formulas will reference the Invoice Amount (column F) in the Invoices worksheet. Review Figure 8-39 to see various ways to enter criteria in the COUNTIF, SUMIF, and AVERAGEIF functions.)
a. In cell B15, use the COUNTIF function to count the number of invoices greater than the amount in cell B13.
b. In cell B16, use the SUMIF function to add the total value of invoices greater than the amount in cell B13.
c. In cell B17, use the AVERAGEIF function to calculate the average value of these invoices.
13. In cell B13, enter 1000 as the invoice amount above which invoices are included in the report.
14. Save the workbook, and then close it.
