B D E F G H Close Call Trading 2 Internal Audit Inspection 1111 Davidson 3/4/17 Sales Invoices 31/3/17 Invoice No Product Unit Price Customer 4 Employee 5 Employee Name 6 Date Performed 7 8 Audit Type 9 Prepared on 10 11 Analysis 12 Sales Invoices Date Shipped 13 14 31/3/17 15 31/3/17 16 31/3/17 17 31/3/17 18 31/3/17 19 31/3/17 20 31/3/17 21 31/3/17 22 31/3/17 23 Total 24 101101 101101 101101 101 102 101102 101103 101104 101104 101104 A10 C30 D10 BOS D10 E50 C30 D10 E50 Quantity Shipped 5 3 4 1 3 $30.30 ABB Alumina $20.10 ABB Alumina $50.50 ABB Alumina $110.00 Lion Bakery $50.50 Lion Bakery $80.80 Golden South $20.10 Cotton Down $50.50 Cotton Down $80.80 Cotton Down Invoice Test Amount $151.50 Correct $100.30 Too high $200.00 Too low $110.00 Correct $100.50 Too low $484.80 Correct $40.20 Correct $220.00 Too high $565.60 Correct $1,972.90 6 2 4 7 35 Quantity Shipped Sale Value 1 5 1 5 11 13 35 Yes $151.50 $110.00 $140.50 $520.50 $1,050.40 $1,972.90 Yes Product Count# 25 26 A10 27 BOS 28 C30 2 29 D10 3 30 E50 2 31 Total 32 Match 33 34 Table 35 Employee# Employee Name Role 36 1000 Amanda Sales Clerk 37 1111 Davidson Internal Auditor 38 2000 James A/R Clerk 39 2222 Majella Internal Auditor 40 3000 Samuel Purchasing Clerk 41 3333 Zoey A/P Clerk Figure 2: Spreadsheet used to conduct an audit of sales invoices a. Required: Write a VLOOKUP command for cell B5 that shows the name associated with the employee number stored in cell B4. Employee details are stored in cells A36 to C41. (1.5 marks) b. Show a formula for cell E26 that can be used calculate the values in cells E26:E30. The formula in E26 should be constructed so that it can be copied to E27:E30 without modification. (1.5 marks) Construct a formula that can be used to calculate the values in cells F26:F30. The formula in F26 should be constructed so that it can be copied to F27:F30 without modification. (2 marks) d. Show a formula that can be placed in cell H14. The formula in H14 should be constructed so that it can be copied to H15:H22 without modification. This formula should be used to test whether the amount invoiced for each item corresponds to the formula quantity shipped x unit price, and which returns the values too low, correct, or too high to indicate whether the invoice amount value is too low, correct, or too high respectively. (4 marks) c. To respond to this question, fill in the template below: Q# Cell(s) Formula B5 a b E26 Marks /1.5 marks /1.5 marks 1/2 marks /4 marks d F26 H14 (1.5 +1.5 +2 +4 = 9 marks)
