1) In the invoices worksset in cell B1 enter 7/1/2016 as the current date. Note the defined name CurrentDate has been assigned to cell B1
2) The sales rep commision 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 multipled the commission rate by the invoice amount to calculate the commission. Althought the first two rows in column D of the excel table names Aging display the correct commission all the oteher cells display #NA. Find the problem with the formulas in the commisson column and fix it.
3) In clumn G, calculate the days past due. If the number of days since the invoce was sent (currentDate – Invoice Date) is greated than 30, calculate the days past due (Current Date – Invoice Date- 30) otherwise enter 0
4) Create the following formulas to assign the value in the Invoice Amount column to one of the columsn – Current, 1-30 days, 31-60 days, 61-90 days, and over 90.
a. in the Current column, create a formula to display the invoice amount column (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 numbers of days past due is greated than or equal to 1 and less than or equal to 30.
c. in the 31-60 days column creat a formula to display the invoice amount if the number of days past due is greated 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 forumla to display the invoice amount if the number of days past due is greated than or equal to 91 days.
f. formate columsn H throught L in the accounting formate with 2 decimal places.
5) the invoice amount (column F) for each invoice can only appear once in columns H throught L. In colum N do the folowing to create a formula to verify this rul.
a. in cell N# enter the label Error Check.
b. in the range N4:N105, enter a formula using the If and COUT functions. the logical test of the IF function counts the number of cells that have an entry in coluns H L for each invoice. If the count is greater than one, the formula displays ERRO, otherwise it leaves the cell blank.
6) cop the invoices worksheet to a new shee and name is Overdue Accts. In the Overdue Accts worksheet, do the following.
a. filter the records so only invoices whose balnce 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 throught L.
d. Hide columsn C D F H and N
e. remove the filter buttons and gridlines from the table (hint: use options on VIEW tab and the Table tools design tab)
7) In the invoice reports worksheet, Dough used the COUNTIF function to count the number of invoices for each sales rep. The formulas he created display only zeros. Fix the formula in the range B3:B7 so that they display the number of invoices processed by each sales rep.
8) in the invoice reports worksheet, complete the sales rep analysis report. In the commission and total amoun colums (column C and D) use the SUMIF function to summarize commission (clumn D in the aging table in the Invoice worksheet) and the invoice amount (column F in the againg table) for each sales rep. In fow 7 of the report, calculate the totals. format these colums appropriately.
9) in the invoice reports worksheet, complete the Accounts Receivalbe Aging report in the range F1:H8 by createing formulas that count the number of invoices for each group in the Invoice worksheet and sum the total amounts for those invoices.
10) Inthe inivoie reports workeeht, in the range A12:B17, use the COUNTIF, SUMIF, and AVERAGEIF functions to complte the report (hint: the formulas will reference the Invoice Amount (column F) in the Invoies worksheet)
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 ad 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.
11) in cell B13 enter 1000 as the invoies amount above which invoices are included in the report.
Ward Consulting
Author
Date
Purpose To age Accounts Receivable and prepare summary reports
Data Definition Table
Field Description Data Type Notes
Invoice Nbr Invoice number Number
Company Name of company Text
Sales Rep Name of sales representative Text
Sales Rep Commission Commission for sales rep Number Lookup up commission rate using sales rep name
Invoice Date Date of invoice Date Enter dates using mm/dd/yyyy
Invoice Amount Amount of invoice Number Accounting format with two decimal places
Days Past Due Number of days invoice past due Number Invoice past due if (current date – invoice date) – 30 is greater than 30 days. Enter 0 if invoice not overdue
Current Invoice amount is current Number Accounting format with two decimal places
1-30 days Invoice amount is overdue 1-30 days Number Accounting format with two decimal places
31-60 days Invoice amount is overdue 31-60 days Number Accounting format with two decimal places
61-90 days Invoice amount is overdue 61-90 days Number Accounting format with two decimal places
Over 90 days Invoice amount is overdue over 90 days Number Accounting format with two decimal places
TAKE ADVANTAGE OF OUR PROMOTIONAL DISCOUNT DISPLAYED ON THE WEBSITE AND GET A DISCOUNT FOR YOUR PAPER NOW!