"Miles Flown"

"Customers"

PLEASE show your work for 3a-3d.

You have recently landed a business analyst position for Northeast Airlines. In your first meeting, the manager learned that you are taking an Operations Management class in your MBA program. As the manager is not very proficient in using office software tools, he is doing all frequent-flier mileage in two separate Excel workbooks- One is the customer contact information and the second is the miles flown. Being familiar with the possibilities of spreadsheet applications, you suggest setting up one workbook to handle both functions. To complete this, you must access two worksheets in the workbook labeled "Customers" and "Miles Flown". 1 2 3 4 S 6 2 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 Frequent Flier Number Miles Flown 978445 6057 925803 5933 883456 2355 874563 6628 867814 9205 837399 1567 818401 4811 813258 8456 803686 8693 788839 8100 752937 1784 747197 6028 742971 9266 740617 3812 725722 1971 634054 5012 603151 5653 542880 7692 530376 7906 525630 5098 515086 3857 472458 7609 434996 1598 424960 3899 401419 9222 398846 7898 380392 4975 306513 1280 304136 7985 294736 7527 276924 1911 259804 7526 224029 6211 219608 7349 211641 1845 206584 1766 189143 8275 144323 6797 135797 6570 79235 3144 74826 9081 43476 1335 15782 2942 41 42 43 44 c 1 Last_Name First Name Phone_Number 2 Austin Joseph 509-333-4576 3 Baker Beth 509-555-8947 4 Baldwin MaryAnn 208-882-4455 5 Bare Megan 666-256-4897 6 Beasley Steve 509-332-2354 7 Becker Hubert 509-336-6578 8 Boyd Gloria 509-332-1234 9 Cho Diane 208-882-5478 10 Clark Heather 509-332-9087 11 Clausen Gina 509-336-7777 12 Cox Linda 509-332-7456 13 Dumont Mark 509-332-4526 14 Easton Dennis 208-882-3476 15 Frazier Nancy 509-332-1243 16 Fuhrmann Leslie 509-336-9807 17 Fujimoto Carla 509-332-4532 18 Gibson Femanda 208-882-6745 19 Gillespie Patricia 509-332-4578 20 Hall Marion 509-357-3434 21 Henderson Angela 509-332-4343 22 Herman Frank 509-332-1267 23 Hunt Jack 509-332-6708 24 Hunter Anna 208-882-3424 25 Hutchinson Eric 509-336-5465 26 Jones Keith 444-789-6523 27 Lawrence Earl 509-123-4561 28 Lee Soojin 509-332-6589 29 LI Chun 509-357-4433 30 Lin Yu-Lin 208-882-3232 31 Looney Carl 509-222-5645 32 Marshal Debbie 208-882-1525 33 Nauman Michael 509-357-5673 34 Norton Cynthia 509-357-5676 35 Proctor Ginny 208-875-3333 36 Sanchez Ramon 208-882-9898 37 Sanderson Sonja 509-336-5567 38 Stewart Kathy 509-357-8889 39 Thompson Caitlin 509-332-8790 40 Thomton Janet 509-332-8679 41 Walker Ken 509-332-6709 42 Wallace Alex 208-882-5576 43 Weber Cliff 208-875-7844 44 West Helen 509-357-3487 45 Yang Jung 509-357-4509 46 Zhan Man 509-332-4567 47 Frequent_Flier_Number Miles Flown 398846 219608 206584 984564 144323 603151 925803 224029 276924 43476 472458 74826 725722 294736 752937 542880 79235 803686 135797 434996 837399 424960 189143 867814 100000 813258 740617 211641 530376 401419 788839 742971 634054 525630 818401 380392 306513 15782 883456 259804 304136 874563 747197 515086 978445 + # Item 3a Use the VLOOKUP function to enter the miles flown on the Customers tab. (VLOOKUP) 3b Highlight all frequent fliers who have less than 4,000 total miles using conditional formatting on the Customers worksheet. (CONDITIONAL FORMATTING) 3c Sort the frequent fliers by total miles in descending order on the "Customers worksheet. (SORT/FILTER) 3d Make recommendations or provide insight on your findings. You have recently landed a business analyst position for Northeast Airlines. In your first meeting, the manager learned that you are taking an Operations Management class in your MBA program. As the manager is not very proficient in using office software tools, he is doing all frequent-flier mileage in two separate Excel workbooks- One is the customer contact information and the second is the miles flown. Being familiar with the possibilities of spreadsheet applications, you suggest setting up one workbook to handle both functions. To complete this, you must access two worksheets in the workbook labeled "Customers" and "Miles Flown". 1 2 3 4 S 6 2 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 Frequent Flier Number Miles Flown 978445 6057 925803 5933 883456 2355 874563 6628 867814 9205 837399 1567 818401 4811 813258 8456 803686 8693 788839 8100 752937 1784 747197 6028 742971 9266 740617 3812 725722 1971 634054 5012 603151 5653 542880 7692 530376 7906 525630 5098 515086 3857 472458 7609 434996 1598 424960 3899 401419 9222 398846 7898 380392 4975 306513 1280 304136 7985 294736 7527 276924 1911 259804 7526 224029 6211 219608 7349 211641 1845 206584 1766 189143 8275 144323 6797 135797 6570 79235 3144 74826 9081 43476 1335 15782 2942 41 42 43 44 c 1 Last_Name First Name Phone_Number 2 Austin Joseph 509-333-4576 3 Baker Beth 509-555-8947 4 Baldwin MaryAnn 208-882-4455 5 Bare Megan 666-256-4897 6 Beasley Steve 509-332-2354 7 Becker Hubert 509-336-6578 8 Boyd Gloria 509-332-1234 9 Cho Diane 208-882-5478 10 Clark Heather 509-332-9087 11 Clausen Gina 509-336-7777 12 Cox Linda 509-332-7456 13 Dumont Mark 509-332-4526 14 Easton Dennis 208-882-3476 15 Frazier Nancy 509-332-1243 16 Fuhrmann Leslie 509-336-9807 17 Fujimoto Carla 509-332-4532 18 Gibson Femanda 208-882-6745 19 Gillespie Patricia 509-332-4578 20 Hall Marion 509-357-3434 21 Henderson Angela 509-332-4343 22 Herman Frank 509-332-1267 23 Hunt Jack 509-332-6708 24 Hunter Anna 208-882-3424 25 Hutchinson Eric 509-336-5465 26 Jones Keith 444-789-6523 27 Lawrence Earl 509-123-4561 28 Lee Soojin 509-332-6589 29 LI Chun 509-357-4433 30 Lin Yu-Lin 208-882-3232 31 Looney Carl 509-222-5645 32 Marshal Debbie 208-882-1525 33 Nauman Michael 509-357-5673 34 Norton Cynthia 509-357-5676 35 Proctor Ginny 208-875-3333 36 Sanchez Ramon 208-882-9898 37 Sanderson Sonja 509-336-5567 38 Stewart Kathy 509-357-8889 39 Thompson Caitlin 509-332-8790 40 Thomton Janet 509-332-8679 41 Walker Ken 509-332-6709 42 Wallace Alex 208-882-5576 43 Weber Cliff 208-875-7844 44 West Helen 509-357-3487 45 Yang Jung 509-357-4509 46 Zhan Man 509-332-4567 47 Frequent_Flier_Number Miles Flown 398846 219608 206584 984564 144323 603151 925803 224029 276924 43476 472458 74826 725722 294736 752937 542880 79235 803686 135797 434996 837399 424960 189143 867814 100000 813258 740617 211641 530376 401419 788839 742971 634054 525630 818401 380392 306513 15782 883456 259804 304136 874563 747197 515086 978445 + # Item 3a Use the VLOOKUP function to enter the miles flown on the Customers tab. (VLOOKUP) 3b Highlight all frequent fliers who have less than 4,000 total miles using conditional formatting on the Customers worksheet. (CONDITIONAL FORMATTING) 3c Sort the frequent fliers by total miles in descending order on the "Customers worksheet. (SORT/FILTER) 3d Make recommendations or provide insight on your findings