# Project #89843 - Excel workbook

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.

 Subject Business Due By (Pacific Time) 10/30/2015 12:00 am
TutorRating
pallavi

Chat Now!

out of 1971 reviews
amosmm

Chat Now!

out of 766 reviews
PhyzKyd

Chat Now!

out of 1164 reviews
rajdeep77

Chat Now!

out of 721 reviews
sctys

Chat Now!

out of 1600 reviews

Chat Now!

out of 770 reviews
topnotcher

Chat Now!

out of 766 reviews
XXXIAO

Chat Now!

out of 680 reviews