Independent Challenge 1 As the accounting manager of Ace Floors, a carpet and flooring company, you are reviewing the accounts payable infomation for your advertising accounts and prioritizing the overdue invoices for your collections service. You will analyze the invoices and use logical functions to emphasize priority accounts. a. Start Excel, open EX 5-4.xlsx from the location where you store your Data Files, then save it as EX 5-Ace h. Name the range B7:B13 invoice date, and give the name a scope of the accounts payable worksheet. c. Name the cell B4 current date, and give the name a scope of the accounts payable worksheet. d. Enter a formula using the named range invoice date in cell E7 that calculates the invoice due date by adding 30 to the invoice date. e. Copy the formula in cell E7 to the range E8:E13. f. In cell F7, enter a formula using the named range invoice_date and the named cell current_date that calculates the invoice age by subtracting the invoice date from the current date. g. Copy the formula in cell F7 to the range F8:F13. h. In cell G7, enter an IF function that calculates the number of days an invoice is overdue, assuming that an invoice must be paid in 30 days. (Hit The Logical test should check to see if the age of the invoice is greater than 30, the Value if true should calculate the current date minus the invoice due date, and the value if false should be 0.) Copy the IF function into the range G8 G13. i. In cell H7, enter an AND function to prioritize the overdue invoices that are more than $1,000 for collection services. (Hit The Logicall condition should check to see if the number of days overdue is more than 0, and the Logical condition should check if the amount is more than 1,000.) Copy the AND function into the range HR:H13 1. Use the Name Manager to name the range H7 H13 Priority and give the name a scope of the accounts payable worksheet. (Hint In the Name Manager dialog box, dick New to enter the range name.) k. Enter your name in the worksheet footer, save the workbook, preview the worksheet, then submit the workbook to your instructor 1. Close the workbook, then exit Excel. A B F G H C D E Ace Floors Advertising Accounts Payable Current 4 Date 5 2017/9/1 Invoice Due Date Age of Invoice Number of Days Overdue Priority 6 7 Invoice Number 2004 2157 2341 3922 2257 9864 6488 Invoice Date Account Representative 2017/7/2 John Lowe 2017/8/16 Chris Dowd 2017/6/2 Lawrence Lang 2017/8/7 Kate Lyons 2017/7/3 Patricia Donolly 2017/8/5 Harry Glen 2017/8/16 Nancy Alero Amount $2,187.65 $1,030.55 $2,977.51 $4,218.22 $987.24 $1,062.55 $799.31 13 Independent Challenge 1 As the accounting manager of Ace Floors, a carpet and flooring company, you are reviewing the accounts payable infomation for your advertising accounts and prioritizing the overdue invoices for your collections service. You will analyze the invoices and use logical functions to emphasize priority accounts. a. Start Excel, open EX 5-4.xlsx from the location where you store your Data Files, then save it as EX 5-Ace h. Name the range B7:B13 invoice date, and give the name a scope of the accounts payable worksheet. c. Name the cell B4 current date, and give the name a scope of the accounts payable worksheet. d. Enter a formula using the named range invoice date in cell E7 that calculates the invoice due date by adding 30 to the invoice date. e. Copy the formula in cell E7 to the range E8:E13. f. In cell F7, enter a formula using the named range invoice_date and the named cell current_date that calculates the invoice age by subtracting the invoice date from the current date. g. Copy the formula in cell F7 to the range F8:F13. h. In cell G7, enter an IF function that calculates the number of days an invoice is overdue, assuming that an invoice must be paid in 30 days. (Hit The Logical test should check to see if the age of the invoice is greater than 30, the Value if true should calculate the current date minus the invoice due date, and the value if false should be 0.) Copy the IF function into the range G8 G13. i. In cell H7, enter an AND function to prioritize the overdue invoices that are more than $1,000 for collection services. (Hit The Logicall condition should check to see if the number of days overdue is more than 0, and the Logical condition should check if the amount is more than 1,000.) Copy the AND function into the range HR:H13 1. Use the Name Manager to name the range H7 H13 Priority and give the name a scope of the accounts payable worksheet. (Hint In the Name Manager dialog box, dick New to enter the range name.) k. Enter your name in the worksheet footer, save the workbook, preview the worksheet, then submit the workbook to your instructor 1. Close the workbook, then exit Excel. A B F G H C D E Ace Floors Advertising Accounts Payable Current 4 Date 5 2017/9/1 Invoice Due Date Age of Invoice Number of Days Overdue Priority 6 7 Invoice Number 2004 2157 2341 3922 2257 9864 6488 Invoice Date Account Representative 2017/7/2 John Lowe 2017/8/16 Chris Dowd 2017/6/2 Lawrence Lang 2017/8/7 Kate Lyons 2017/7/3 Patricia Donolly 2017/8/5 Harry Glen 2017/8/16 Nancy Alero Amount $2,187.65 $1,030.55 $2,977.51 $4,218.22 $987.24 $1,062.55 $799.31 13