2. Insert Functions and Formulas at DATA worksheet At Data Worksheet, you need to insert lookup functions by using the tours table that look up the tour code, and then return the trip description, departure date, and base cost of the trip for Columns D, E and F. Then you need to insert a formula to calculate the cost with taxes and fees and the monthly payment. a. Click cell D10 on the Data sheet, and then insert a VLOOKUP function that looks up the tour code (Col. C), using table tours as the Table_array, and returns the tour description. b. Same as step a, Click cell E10, and using VLOOKUP function to get the departure date. c. Same as step a, Click cell F10, and using VLOOKUP function to get the base cost of the trip. d. Click cell G10, and then insert a formula that adds taxes and fees to the base cost of the trip (in cell F10) by using the percentage value in the Input area, cell H4. Use an absolute cell reference when you need. e. Click cell H10, and then insert the PMT function to calculate the payments for students who want to pay for their trips in three (3) installments. Use the interest rate and months in the input area. Use appropriate relative, mixed, and/or absolute cell references in the formula. f. Hide the Tour Code column.

Excel File Edit View Insert Format 1 a 2 + 3 Sun Oct 10 9:43 PM Q-DEFINE NAME> 1 8P ... Tools Data Window Help Lab Two of ITM 300 Saved to my Mac Data Review View Tell me AutoSave IF Home Insert Draw Page Layout Formulas Share Comments OFF DAN Eraser Add Pen LABBO Select Draw with Trackpad D10 X for A B C E H L M N 0 P R S 1 2 D European Excursions Agent Sales and Commissions 4 Input Area Taxes and Fees Loan APR Months Per Yes Tatal Payment: 2018 99 12 3 8 Total Cost Base Cost of with Takes & Trip Fees Student Tour Code 20 Trip Description Departure Date Payment Plan 30 10 99 60 70 10 411 90 9 Agent Name 10 Avery 11 Collins 12 Avery 13 McDonough 14 Ross 15 Shannon 16 Collins 17 Ross 18 McDonough 19 Ross 20 Shannon 21 Avery 22 Kinsley 23 Collins 24 Avery 25 McDonough 26 McDonnough 27 Collins 28 Kinsley 29 Avery 3D Ross 31 Avery 32 Ross 33 Ross Chart Student ID 10001 10002 10003 10004 10005 10006 10002 1018 10009 10010 10011 10012 10013 10014 10015 10016 10017 10019 10019 10020 10021 10022 10023 10074 70 20 43 70 30 70 30 10 10 90 40 60 10 101 Lookup Tables Data + Edit + 109% OUT 10 otv S A cs Aa Excel File Edit ... 2 Sun Oct 10 9:44 PM Q-DEFINE NAME > 1 AutoSave IF View Insert Format 1 a 2 + 3 Page Layout Formulas Tools Data Window Help Lab Two of ITM 300 Saved to my Mac Data Review View Tell me Home Insert Draw Share Comments OFF Draw Eraser Add Pen Draw with Trackpad LABBO Select X fx D10 A C D E G H L M N 0 D R S 20 80 20 40 10025 10026 10027 10028 10029 10030 10031 10032 10033 10034 10035 10036 10037 10038 90 70 80 60 70 30 30 201 30 34 Collins 25 Kinsley 36 McDonnough 37 Kinsley 38 McDonough 39 Kinsley 40 Avery 41 McDonough 42 Collins 43 Ross 44 Collins 45 Ress 46 Ross 47 Shannon 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 Chart Lookup Tables Data + Edit + 109% OUT 10 tv ON A Aa Excel File Edit View Insert Format 1 a 2 + 3 Sun Oct 10 9:43 PM Q-DEFINE NAME> 1 8P ... Tools Data Window Help Lab Two of ITM 300 Saved to my Mac Data Review View Tell me AutoSave IF Home Insert Draw Page Layout Formulas Share Comments OFF DAN Eraser Add Pen LABBO Select Draw with Trackpad D10 X for A B C E H L M N 0 P R S 1 2 D European Excursions Agent Sales and Commissions 4 Input Area Taxes and Fees Loan APR Months Per Yes Tatal Payment: 2018 99 12 3 8 Total Cost Base Cost of with Takes & Trip Fees Student Tour Code 20 Trip Description Departure Date Payment Plan 30 10 99 60 70 10 411 90 9 Agent Name 10 Avery 11 Collins 12 Avery 13 McDonough 14 Ross 15 Shannon 16 Collins 17 Ross 18 McDonough 19 Ross 20 Shannon 21 Avery 22 Kinsley 23 Collins 24 Avery 25 McDonough 26 McDonnough 27 Collins 28 Kinsley 29 Avery 3D Ross 31 Avery 32 Ross 33 Ross Chart Student ID 10001 10002 10003 10004 10005 10006 10002 1018 10009 10010 10011 10012 10013 10014 10015 10016 10017 10019 10019 10020 10021 10022 10023 10074 70 20 43 70 30 70 30 10 10 90 40 60 10 101 Lookup Tables Data + Edit + 109% OUT 10 otv S A cs Aa Excel File Edit ... 2 Sun Oct 10 9:44 PM Q-DEFINE NAME > 1 AutoSave IF View Insert Format 1 a 2 + 3 Page Layout Formulas Tools Data Window Help Lab Two of ITM 300 Saved to my Mac Data Review View Tell me Home Insert Draw Share Comments OFF Draw Eraser Add Pen Draw with Trackpad LABBO Select X fx D10 A C D E G H L M N 0 D R S 20 80 20 40 10025 10026 10027 10028 10029 10030 10031 10032 10033 10034 10035 10036 10037 10038 90 70 80 60 70 30 30 201 30 34 Collins 25 Kinsley 36 McDonnough 37 Kinsley 38 McDonough 39 Kinsley 40 Avery 41 McDonough 42 Collins 43 Ross 44 Collins 45 Ress 46 Ross 47 Shannon 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 Chart Lookup Tables Data + Edit + 109% OUT 10 tv ON A Aa