12. Enter a formula in cell K5 of the SalesTrans sheet that uses an IF function and VLOOKUP functions to find the Product_ID in the Inventory sheet and return either the Card_Price or the Non_Card_Price for the product. If the Customer_ID is XXXXXXX, then the customer must pay the Non_Card_Price: otherwise, the customer pays the Card_Price. Copy the formula in cell K5 to cell range K6:K210.

AutoSave ON RO. 2 ACIS 2504 Excel Spreadsheet Project LastFirst.xlsx - Saved Home Insert Draw Page Layout Formulas Data Review View Tell me Share Comments Clear G Connections Properties 21 Y + LO Currencies Era Data from Picture Flash Fill FX Data Validation Text to Remove Duplicates - Consolidate Columns HE 2 What if Analysis Group al Ungroup Subtotal Get Data Analysis Tools Data Analysis 2. Solver Reapply Y Advanced Refresh All Stocks Geography * Sort Filter Edit Links E6 fx =PROPER(RIGHT(B6, LEN(FIND(B6,"*",-1)))) ((') C D G H K Hokie Fitness Equipment & Accessories, Inc. 2 3 Date 10/1/21 5 Imported Data EGE-UAM405721M BAGS GB 7 GB-UAM34373GM Mis A GB-UAS378725'GM bags 9 GB-UAS212790"GyM DAGS 10 GB-UAL43731"Gymbres 11 GB-UAL91770'GM bags 12 GB-NRM48R779'SYM BAGS 13 GB NEM271740"GYM BAGS 14 GB-NBL468762'Gymbos 15 GE-NBL242750 MM 16 GB N3542029"GYM BAGS 17 GB-NBS453732'Gymbags 18 \" FT-FC33627404 NextracKER 19 FT-FC3435746"ATNES Tracker 20 FT-FV5462?7 NesstexKER 21 FT-FVS4457257 NextracKER 22 FT-FIW346749 Ness traxKER 23 FT-FIW385761' fiestaKER 24 FT-GV361716" Tess trackER 25 FT GV315491fNeztracker 26 FT-SGF164725 Fitness trackER 27 FT-SGF37754 litnesstraKER 22 ED SKI272 6 carUDS 29 EB-SKI420757'esBUDS 30 EB-JIP139785ear BUDS 31 EB JJP137?35* RSUDS 32 EB-P196787'erBUDS 33 EE-IF2402747"carBUDS 34 EB JF2157732carBUDS 35 CD-1970759 UDS 36 EB-JIS257737"ear SUDS 37 ? EB JJS275?1.carBUOS 38 EB-SKS163778'er BUDS 39 EE-SKS132794ear SUDS 40 EB SKS1S0286cardUDS 41 WB-V2R124M3'WATER BOTtles - 42 WR-Y2R493713 WATER BOTles Product Information GB-UAN 405721"Gym Mis GE-UAM343?35 YM BAGS GC-UAS378725'OMNOS GB-U45212790MBAGS GE-WAL43731"GyMAGS GE-UALS 1770 Mb/s GE-NBM 488779 GYM BAGS GENB271240*Mbres GC-NBL468752'GyMAGs GB-NBL242750G/ MAGA GE-N3542079 GYM Mas GE-NB5453732GM bags FT-FC3352740 "RITNESS trackER FT-FC343524G ATNess trackER FT-FV546277 fitness RACKER FT-FV5445725nThe trackFR FTFIW246249Ness trackER FT-FIW38561' fitness trackER FT-GV361716 "Te Tracker FT GV315491* Nextracker FT-SGF154225"NestracKER FT-SGF37764f1Ness trackER EB SK12726'erbos EB-SK1420257'erBUDS ER-UP139785ABUDS ED UP137235*careupS CD-UP196787'eraUDC that ER-JF2402747"BUDS EDJF2157792"carbos CD-9470759 BUDS ER-15257737*arRUDS EB-15275211 BUDS EB- SKS163778'DUDS ER-SK5132794areuds EB SK515025G careUDS WG-Y2R124743'WATER DOTUS WE-V2R492713 "WATER POT Product_ID GE-UAM 405 GE LAM343 GE-UAS378 - GE-LAU12 GB VAL43 GE-UALS1 GE-NB48R GB NBM27 GE-N3468 GENBL242 GB N35423 GE-N35453 FT-FC3352 FT FC3435 FT-FV8462 FT-FV5445 FT-FIW240 FT-FIW385 FT-GV861 FT GV3154 FT-SGF164 FT-SGF37 EB SK1272 EB-SK1420 FR-UP139 EB P137 EB-UP156 ER-JF2402 EB JF2157 CB-15470 CD FR-UD57 EBJ05275 EB- SK5163 FR-SK5132 EB SK5150 WE-Y2R120 WE-V2 R493 Warehouse_Bin Category 21 1 HVALUE FVALUE 25 WALVE 90 HVALUE 31 HVALUE 70 INVALVE 79 WALUE 40 PVALUE 62 WALVE 50 WALUE S 9 PVALUE 32 WALVE 40 VALUE 45 PVALUE 7 7 WALVE 25 VALUE 49 PVALUE 61 WALVE! 16 VALUE 91 HVALUE 25 INALUE ! 64 WALLE! 6 HVALUE 57 WALVE! 85 WALUE NI 35 PVALUE 87 WALUE 47 F WALUE 32 HVALUE 69 WALUE 37 WALUE 11 HVALUE WALUE 94 WALUE 56 PVALUE 43 WALVE! 13 7 HVALUE Product_Description Under Armour Undeniable 3.0 Medium Under Armour Undeniable 3.0 Medium Under Armour Undeniable 3.0 Small Under Armour Undeniable 3.0 Small Under Armour Undeniable 3.0 Large Under Armour Undeniable 3.0 Large Nike Brasilia & Medium Nike Brasilia & Medium Nike Brasilia & Large Nike Brasilia & Large Nike Brasilia 3 Small & Nike Brasilia & Small Fitbit Charge 3 Activity Tracker Fitbit Charge 3 Activity Tracker Fitbit Verse Smartwatch Fitbit Vers Smartwatch Fitbit Ionie Watch Fitbit lonic Watch Garmin Vivofit Activity Tracker Garmin Vivofit 3 Activity Tracker Samsung Gear Fit2 Smart Fitness Band Samsung Gear Fit2 Smart Fitness Bund Skullcandy ink'd Skullcandy Ink'd Jlab Juda Pro Bluetooth Jlab.Buds Pro Bluetooth JlsbJBuds Pro Bluetooth Jlab Fit 2.0 Bluetooth Sport Jlab Fit 2.0 Bluetooth Sport Jlab buds Pro Signature Jabbude Pro Signatura Jlab buds Pro Signatura Skullcandy SP Chups Skullcandy SP Chaps Skullcandy SP Chops YETI 20 ug Rambler Tumbler YETI 20 os. Rambler Tumblr Color Black Blue Black Blue Back Blue Black Blue Black Blue Black Blue Black Gol: Black Gold Black Blue Black White Black Pink Black Blue Black Blue Greer Black Blue Black Blue Grear Blue Bill Black Pink Blue Black Cost_per_Unit Card_Price Non_Card_Price $27.89 $44.99 $46.99 $27.89 $44.99 $46.99 $24.79 $39.95 $41.99 $24.79 $39.99 $41.99 $32.85 $32.95 $54.95 $32 85 S52.99 $54.95 $24.00 $40.00 $42.00 $24.00 $40.00 $42.00 $30.00 $50.00 $52.50 $30.00 $50.00 $52.50 $21.00 $35.00 $36.75 $2100 $35.00 $36.75 $97.47 $149.95 $157.45 $97.47 $149.95 $157,45 $12997 $199.95 $209.95 $129.97 $199.95 $209.95 $194.95 $299.95 $314.95 $194 95 $299.95 $314.95 $51.99 $79.99 $ SR3.99 $51.99 $79.99 $83.99 $116.99 $179.99 $188.99 $116.99 $179.99 $18.99 $9.59 $15.99 $16.79 $9.59 $15.95 $16.79 $11.99 $19.99 $20.99 $11.99 $19.99 $20.99 $11.99 $19.95 $20.99 $17.99 $29.99 $81.49 $ $ $17.99 $29.99 $31.49 $5.99 $9.99 $10.49 $5.99 $9.99 $ $10.49 $5.99 $9.99 $10.49 $1199 $19.95 $20.99 $11.99 $19.99 $ $20.99 $11.99 $19.99 $20.99 $18.59 $29.95 $31.49 $1859 $29.99 $31.49 Honor Code Documentation Inventory SalesTrans Customer Data Miscinfo Monthly Payment Calculator Sales Pivot Employee Schedule Database Summary Advanced + Ready 1 + 100% AutoSave ON APO... 2 ACIS 2504 Excel Spreadsheet Project LastFirst.xlsx - Saved Home Insert Draw Page Layout Formulas Data Review View Tell me Share Comments Calibrl (Body) v 11 v A A ab Wrap Text General X G v FO) IN Analyze Data Paste BIU A = = = Merge & Center Insert Delete Y Format Y Conditional Format Cell Formatting es Table Styles Sort & v Filter Find & Select B220 fx C D G H I L M N P 1 ess Equipment & Accessories, Inc. 2 sfor September 2021 3 Card Type $ 50.00 4 h1 5 05420 E 2124 7 X1272 8 HOM271 97137 10 25453 11 L7343 12 K1272 13 DT220 14 H0T220 15 F4280 16 5470 17 GV361 18 C3352 19 3269 20 5420 21 FX197 22 343 23 L7303 24 27493 25 VS452 26 AM405 27 290 22 V3154 29 2402 30 DT425 32 V3154 32 GR160 33 69420 34 GR116 353R389 36 2 R175 37 K1272 38 F3143 39 FX234 40 LA91 41 GF264 42 R447 Date Product ID Customer_ID Warehouse Bin Quantity Sold Sales Type 9/1/21 GB-NB5420 M362831 9 2 Online 9/1/21 WB-Y2R124 MC46295 43 1 Online 9/1/21 EB SKI272 MD10976 5 5 2 Ir store 9/1/21 GO-NBM271 MD27728 40 1 Online 9/1/21 FR-UP137 MRA1502 35 2 Online 9/1/21 Ge-N35453 XOCXXXXX 32 Ir store 9/2/21 CM-SOL7343 MC22709 3 1 Online 9/2/21 FR-SK1272 MC32107 5 2 2 Ir-store 9/2/21 CM NDT220 MD24385 30 Ir store In 9/2/21 CM-NDT220 MD56307 30 1 Ir-store 9/2/21 WB-HF4220 XOCXXOCX 90 2 Online 9/2/21 EB JU5470 XOCXXXXX 59 2 Ir store 9/3/21 FT-GV361 MB25494 16 1 Online 9/3/21 FT-FC3262 MC56593 40 1 Online 9/3/21 WEY3R269 MD24385 2 2 Ir store 9/3/21 EB-15479 ME64082 59 1 Ir-store 9/3/21 HG-FX197 XXXXX 10 1 Online 9/3/21 Ge-UAM343 XXXXXXX 3G 2 Ir store 9/4/21 CM-SOL7343 MB35019 3 1 Online 9/4/21 WB-W2R493 WE MR36025 13 3 In-store 9/4/21 FT FVS462 MC39 104 7 Online 9/4/21 GB-UAM405 MC53802 21 1 Online 9/4/21 CM CHB00 M2 1567 - 35 1 Online 9/4/21 FT GV3154 ML26012 91 In store 9/4/21 CBF2402 MES8417 47 1 Online 9/4/21 CMNDT425 MF68107 2 1 Online 9/4/21 FT GV3154 MR92834 91 Online 9/4/21 WL-FGR160 XOXXXXXX 22 1 Online 9/5/21 GB-NS5420 MB20736 9 2 In-Store 9/5/21 WL FGR116 MC39104 7 Online 9/5/21 WB-Y3R38S MD27726 59 3 Ir-store 9/5/21 WB-Y2R176 MD33494 B2 1 Online 9/5/21 CD SK1272 MD49119 5 2 Online 9/5/21 WB-3143 ML64082 78 1 Online 9/5/21 WL-AFX234 ME98417 91 1 Online 9/5/21 GB VALS1 MR70678 70 2 In store 9/5/21 FT-SGF164 MR82854 25 1 Online 9/5/21 WL-FGR447 XXXX XOCXXXX 36 1 In-store Category Gym D Water Rottles Earbuds Gym Des Earbuds Gym Bags Cardio Machines Earbuds Cardio Machines Cardio Machines Water Bottles Earbuds Fitness Tracker FitneTracker Water Bottles Earbuds Home Gym Gym Bags Cardio Machines Water Bottles Fitness Tracker Gym Des Cardio Machines Fitness Tracker Earbuds Cardio Machines Fitness Tracker Weight Lifting Gym Raus Wielght Lifting Water Bottles Water Rottles Earbuds Water Bottles Weight Lifting Gym Bags Fitness Tracke Weight Lifting Cost_per_Unit Price per Unit $21.00 $18.59 $9.59 $24.00 $11.99 $21.00 $$46.95 $9.59 $591.84 $591.84 $25.77 $5.99 $51.99 $97.47 $21.69 $5.99 $355.10 $27.89 $$46.95 $10.59 $.29.97 $27.89 $710.21 $51.99 $17.99 $945.95 $51.99 $147.96 $21.00 $236.73 $21.69 $18.59 $9.59 $24.77 $235.73 $32.95 $116.99 $165.71 Total Sales Shipping_Handling Cost Sales Taxes Total Due $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 S0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 Inventory Sales Trans Customer Data Miscinfo Monthly Payment Calculator Sales Pivot Employee Schociules Database Summary Advanced Filter + Ready LO + 100% % AutoSave ON APO... 2 ACIS 2504 Excel Spreadsheet Project LastFirst.xlsx - Saved Home Insert Draw Page Layout Formulas Data Review View Tell me Share Comments Calibri v 11 v AP A ab Wrap Text General v G v FO) IN Analyze Data Paste B 1 I U UV 7 VA Merge & Center Insert Delete Y Format Y Conditional Format Cell Formatting as Table Styles v Sort & Filter Find & Select F5 fx =ROUNDUP(RANK.EQ(E5,$E$5:$E$56,0),2] A B C F G K L M N 1 Hokie Fitness Equipment & Accessories, Inc. 10/1/21 2 Date 3 4 Customer_ID 5 MB20736 6 M025494 7 MB35019 MB36025 9 MB47621 10 MB62831 11 M873421 12 MB83576 13 M891356 14 MC1G332 15 M622709 16 MC31771 17 MC32107 18 MC39104 19 MC46295 20 MC53802 21 MC56593 22 MC60615 23 MC72946 24 MD10876 25 MD24385 26 MD27720 27 MD33494 28 MD49119 29 MDSG307 30 MD62941 31 ME 1567 32 ME26012 33 ME30318 34 MES4105 35 MEG4092 36 ME74922 37 ME80578 39 MENGK42 39 MES8412 Millor Cust_First Name Cust Last Name Card Type Charlotte Welch Platinum Jamie Thompson Everyday Smith Rod Corey Westerfies Rec Shelly Alderman Everyday Dana Cox Red Rhonda Abbott Rec 981 Poster Ruth Baldwin Everyday Dill Sutherland Everyday Mauroud Amad Everyday Martha Talbot Red Rebecca De grey Rec Bernare Moss Everyday Barrett | Realley Platinum Miranda Radcliffe Rec Rajan Kadappakam Platinum Justin Everyday Dis Lineberry Platinum Paul Kwur Platinum Alcion Ulrich Platinum Roy Callihan Red Sally Yane Everyday Nancy Wallace Ron Walter Wipple Everyday Karla Durvis Everyday Robert Arderson Red Juan Tobar Red Josiah Taylor Rodrigo Davila Everyday Raul Sanchez Everyday Jordan Monroe Reba Caldwell Everyday Melacy Talbot Platinum Roher Seth Everyday James Fitzgerald Everyday Roger Bentley Everyday Candice Everyday Rand Roundup/Rank Cust Serv Rep ID Credit Limit Current Balance Lxt Payment Date Payment Due Date 0.647648963 16! $1,000.00 $987.18 9/17:21 3.8370S 2104 6 $1,000.00 $183.20 9/8/21 0.585627268 19 $2,000.00 $954.57 9/30/21 0.550038212 21 $1,750.00 $1,689.21 9/13/21 0.05029555 51 $1,000.00 $675.81 9/29/21 2.735085529 11 $2,500.00 $1,121.60 9/25/21 0.280G39392 33 $1,000.00 $999.60 9/7/22 3.201626376 41 $1,750.00 $1,190.27 9/4/21 3.195237075 43 $250.00 $251.44 9/27/21 0.202150055 40 $2,500.00 $547.98 9/19/21 3.725786719 12 $2,000.00 $1.075,20 9/28/21 0.273287973 37 $1,750.00 $1,305.57 9/19/21 0.932723347 1 $750.00 $747.89 9/14/21 0.158630184 45 $750.00 $738.22 9/9/21 2.209262456 39 $1,500.00 $1,488.35 9/21/21 0.324334546 31 $250.00 $83.25 9/20/21 3.7147C6331 13 $2,000.00 $1,379.49 9/13/21 0.270495778 38 $1,750.00 $456.52 9/27/21 0.339520242 29 $1,250.00 $1,169.46 9/20/21 0.593195277 18 $1,500.00 $650.95 9/29/21 0.40778464 26 $1,000.00 $226.48 9/26/21 0.85991540 5 $500.00 $231.56 9/9/21 3.445314596 24 $1,500.00 $ $1,105.61 9/20/21 0.291849468 34 $250.00 $ $231.87 9/27/21 0.345130093 28 $2,250.00 $2,231.55 0.916982788 2 2 $750.00 $686.99 9/15/21 3.705943R35 14 $750.00 $642.68 $ 9/19/21 0.358013644 27 $2,000.00 $1,662.76 9/30,21 0.2758S7351 36 $1,600.00 $1,558.65 9/4/21 2.535402512 22 $2,250.00 $2,100.15 9/28/21 0.116341127 47 $500.00 $254.35 9/10:21 5.173187601 44 $2,000.00 $1,647.89 9/7/21 0.105516445 49 $1,500.00 $1,448.78 9/22/21 0.463298124 23 $2,250.00 $2,249.67 9/30,21 0.325094557 30 $2,250,00 $1,888.SO 9/12/21 0.19889064 42 $550.00 $533.57 9/30/21 0.753348747 9 $1,500.00 $624.22 9/5/21 0.783879438 8 $750.00 $613.95 9/27/21 Credit Remaining Credit Limit Massage $12.92 5816.80 $1,035.43 $60.79 $324.18 $1,378.40 $1.40 5559.73 $1.44 $1,952.02 5924.80 $444.43 $2.13 $11.78 $11.65 $160.75 S620.51 $1,283.48 $80.54 5849.05 $773.52 $265.44 $394,39 $18.13 $15.45 $63.04 $107.32 $337.24 $41.35 $149.85 $ $215.GS $352.01 $51.22 $1.33 $361.10 $15.43 $875.78 $136.05 9/26/21 40 MEG9550 41 MF15432 42 M29753 Inventory SalesTrans Customer Data MiscInfo Monthly Payment Calculator Sales Pivot Employee Schedule Database Summary Advanced Filter + Ready + 100% % AutoSave ON APO... RO ACIS 2504 Excel Spreadsheet Project LastFirst.xlsx - Saved Home Insert Draw Page Layout Formulas Data Review View Tell me Share Comments Calibrl (Body) v 11 X G v A A Wrap Text General AYO v v FO) IN Analyze Data Pastc A = = = Insert Format - Merge & Center Delete Y Y Conditional Format Cell Formatting es Table Styles Sort & v Filter Find & Select D11 fx c F G . I 2 K TL L M N O Q R S T U Credit Umit Messages Credit Remaining Message Shipping and Handling Rates Card Type Rate 3 No card 4.00% 4 Platinum 9.OON 5 5 Red 1.00% G Everyday 2.00% 7 2 9 Sales Tax Rate 6.30% 10 Number of Customers Assigned to 11 Cust_Service_Rep 13 12 13 Customer Service Representatives 14 Cust Serv Rep ID Cust Service Rep 15 CSR-134765 Lalunya Smith 16 CSR-725034 Andy Che 17 CSR 879452 Juana Torres 10 CSR-941725 Mustafa 19 Number of workdays 20 between payment dates 20 21 September Holiday 9/6/21 22 October Holiday 10/11/21 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 32 39 Inventory SalesTrans Customer Data Miscinfo Monthly Payment Calculator Sales Pivot Employee Schedule Database Summary Advanced Filter + Ready LEN TVU? AutoSave ON RO. 2 ACIS 2504 Excel Spreadsheet Project LastFirst.xlsx - Saved Home Insert Draw Page Layout Formulas Data Review View Tell me Share Comments Clear G Connections Properties 21 Y + LO Currencies Era Data from Picture Flash Fill FX Data Validation Text to Remove Duplicates - Consolidate Columns HE 2 What if Analysis Group al Ungroup Subtotal Get Data Analysis Tools Data Analysis 2. Solver Reapply Y Advanced Refresh All Stocks Geography * Sort Filter Edit Links E6 fx =PROPER(RIGHT(B6, LEN(FIND(B6,"*",-1)))) ((') C D G H K Hokie Fitness Equipment & Accessories, Inc. 2 3 Date 10/1/21 5 Imported Data EGE-UAM405721M BAGS GB 7 GB-UAM34373GM Mis A GB-UAS378725'GM bags 9 GB-UAS212790"GyM DAGS 10 GB-UAL43731"Gymbres 11 GB-UAL91770'GM bags 12 GB-NRM48R779'SYM BAGS 13 GB NEM271740"GYM BAGS 14 GB-NBL468762'Gymbos 15 GE-NBL242750 MM 16 GB N3542029"GYM BAGS 17 GB-NBS453732'Gymbags 18 \" FT-FC33627404 NextracKER 19 FT-FC3435746"ATNES Tracker 20 FT-FV5462?7 NesstexKER 21 FT-FVS4457257 NextracKER 22 FT-FIW346749 Ness traxKER 23 FT-FIW385761' fiestaKER 24 FT-GV361716" Tess trackER 25 FT GV315491fNeztracker 26 FT-SGF164725 Fitness trackER 27 FT-SGF37754 litnesstraKER 22 ED SKI272 6 carUDS 29 EB-SKI420757'esBUDS 30 EB-JIP139785ear BUDS 31 EB JJP137?35* RSUDS 32 EB-P196787'erBUDS 33 EE-IF2402747"carBUDS 34 EB JF2157732carBUDS 35 CD-1970759 UDS 36 EB-JIS257737"ear SUDS 37 ? EB JJS275?1.carBUOS 38 EB-SKS163778'er BUDS 39 EE-SKS132794ear SUDS 40 EB SKS1S0286cardUDS 41 WB-V2R124M3'WATER BOTtles - 42 WR-Y2R493713 WATER BOTles Product Information GB-UAN 405721"Gym Mis GE-UAM343?35 YM BAGS GC-UAS378725'OMNOS GB-U45212790MBAGS GE-WAL43731"GyMAGS GE-UALS 1770 Mb/s GE-NBM 488779 GYM BAGS GENB271240*Mbres GC-NBL468752'GyMAGs GB-NBL242750G/ MAGA GE-N3542079 GYM Mas GE-NB5453732GM bags FT-FC3352740 "RITNESS trackER FT-FC343524G ATNess trackER FT-FV546277 fitness RACKER FT-FV5445725nThe trackFR FTFIW246249Ness trackER FT-FIW38561' fitness trackER FT-GV361716 "Te Tracker FT GV315491* Nextracker FT-SGF154225"NestracKER FT-SGF37764f1Ness trackER EB SK12726'erbos EB-SK1420257'erBUDS ER-UP139785ABUDS ED UP137235*careupS CD-UP196787'eraUDC that ER-JF2402747"BUDS EDJF2157792"carbos CD-9470759 BUDS ER-15257737*arRUDS EB-15275211 BUDS EB- SKS163778'DUDS ER-SK5132794areuds EB SK515025G careUDS WG-Y2R124743'WATER DOTUS WE-V2R492713 "WATER POT Product_ID GE-UAM 405 GE LAM343 GE-UAS378 - GE-LAU12 GB VAL43 GE-UALS1 GE-NB48R GB NBM27 GE-N3468 GENBL242 GB N35423 GE-N35453 FT-FC3352 FT FC3435 FT-FV8462 FT-FV5445 FT-FIW240 FT-FIW385 FT-GV861 FT GV3154 FT-SGF164 FT-SGF37 EB SK1272 EB-SK1420 FR-UP139 EB P137 EB-UP156 ER-JF2402 EB JF2157 CB-15470 CD FR-UD57 EBJ05275 EB- SK5163 FR-SK5132 EB SK5150 WE-Y2R120 WE-V2 R493 Warehouse_Bin Category 21 1 HVALUE FVALUE 25 WALVE 90 HVALUE 31 HVALUE 70 INVALVE 79 WALUE 40 PVALUE 62 WALVE 50 WALUE S 9 PVALUE 32 WALVE 40 VALUE 45 PVALUE 7 7 WALVE 25 VALUE 49 PVALUE 61 WALVE! 16 VALUE 91 HVALUE 25 INALUE ! 64 WALLE! 6 HVALUE 57 WALVE! 85 WALUE NI 35 PVALUE 87 WALUE 47 F WALUE 32 HVALUE 69 WALUE 37 WALUE 11 HVALUE WALUE 94 WALUE 56 PVALUE 43 WALVE! 13 7 HVALUE Product_Description Under Armour Undeniable 3.0 Medium Under Armour Undeniable 3.0 Medium Under Armour Undeniable 3.0 Small Under Armour Undeniable 3.0 Small Under Armour Undeniable 3.0 Large Under Armour Undeniable 3.0 Large Nike Brasilia & Medium Nike Brasilia & Medium Nike Brasilia & Large Nike Brasilia & Large Nike Brasilia 3 Small & Nike Brasilia & Small Fitbit Charge 3 Activity Tracker Fitbit Charge 3 Activity Tracker Fitbit Verse Smartwatch Fitbit Vers Smartwatch Fitbit Ionie Watch Fitbit lonic Watch Garmin Vivofit Activity Tracker Garmin Vivofit 3 Activity Tracker Samsung Gear Fit2 Smart Fitness Band Samsung Gear Fit2 Smart Fitness Bund Skullcandy ink'd Skullcandy Ink'd Jlab Juda Pro Bluetooth Jlab.Buds Pro Bluetooth JlsbJBuds Pro Bluetooth Jlab Fit 2.0 Bluetooth Sport Jlab Fit 2.0 Bluetooth Sport Jlab buds Pro Signature Jabbude Pro Signatura Jlab buds Pro Signatura Skullcandy SP Chups Skullcandy SP Chaps Skullcandy SP Chops YETI 20 ug Rambler Tumbler YETI 20 os. Rambler Tumblr Color Black Blue Black Blue Back Blue Black Blue Black Blue Black Blue Black Gol: Black Gold Black Blue Black White Black Pink Black Blue Black Blue Greer Black Blue Black Blue Grear Blue Bill Black Pink Blue Black Cost_per_Unit Card_Price Non_Card_Price $27.89 $44.99 $46.99 $27.89 $44.99 $46.99 $24.79 $39.95 $41.99 $24.79 $39.99 $41.99 $32.85 $32.95 $54.95 $32 85 S52.99 $54.95 $24.00 $40.00 $42.00 $24.00 $40.00 $42.00 $30.00 $50.00 $52.50 $30.00 $50.00 $52.50 $21.00 $35.00 $36.75 $2100 $35.00 $36.75 $97.47 $149.95 $157.45 $97.47 $149.95 $157,45 $12997 $199.95 $209.95 $129.97 $199.95 $209.95 $194.95 $299.95 $314.95 $194 95 $299.95 $314.95 $51.99 $79.99 $ SR3.99 $51.99 $79.99 $83.99 $116.99 $179.99 $188.99 $116.99 $179.99 $18.99 $9.59 $15.99 $16.79 $9.59 $15.95 $16.79 $11.99 $19.99 $20.99 $11.99 $19.99 $20.99 $11.99 $19.95 $20.99 $17.99 $29.99 $81.49 $ $ $17.99 $29.99 $31.49 $5.99 $9.99 $10.49 $5.99 $9.99 $ $10.49 $5.99 $9.99 $10.49 $1199 $19.95 $20.99 $11.99 $19.99 $ $20.99 $11.99 $19.99 $20.99 $18.59 $29.95 $31.49 $1859 $29.99 $31.49 Honor Code Documentation Inventory SalesTrans Customer Data Miscinfo Monthly Payment Calculator Sales Pivot Employee Schedule Database Summary Advanced + Ready 1 + 100% AutoSave ON APO... 2 ACIS 2504 Excel Spreadsheet Project LastFirst.xlsx - Saved Home Insert Draw Page Layout Formulas Data Review View Tell me Share Comments Calibrl (Body) v 11 v A A ab Wrap Text General X G v FO) IN Analyze Data Paste BIU A = = = Merge & Center Insert Delete Y Format Y Conditional Format Cell Formatting es Table Styles Sort & v Filter Find & Select B220 fx C D G H I L M N P 1 ess Equipment & Accessories, Inc. 2 sfor September 2021 3 Card Type $ 50.00 4 h1 5 05420 E 2124 7 X1272 8 HOM271 97137 10 25453 11 L7343 12 K1272 13 DT220 14 H0T220 15 F4280 16 5470 17 GV361 18 C3352 19 3269 20 5420 21 FX197 22 343 23 L7303 24 27493 25 VS452 26 AM405 27 290 22 V3154 29 2402 30 DT425 32 V3154 32 GR160 33 69420 34 GR116 353R389 36 2 R175 37 K1272 38 F3143 39 FX234 40 LA91 41 GF264 42 R447 Date Product ID Customer_ID Warehouse Bin Quantity Sold Sales Type 9/1/21 GB-NB5420 M362831 9 2 Online 9/1/21 WB-Y2R124 MC46295 43 1 Online 9/1/21 EB SKI272 MD10976 5 5 2 Ir store 9/1/21 GO-NBM271 MD27728 40 1 Online 9/1/21 FR-UP137 MRA1502 35 2 Online 9/1/21 Ge-N35453 XOCXXXXX 32 Ir store 9/2/21 CM-SOL7343 MC22709 3 1 Online 9/2/21 FR-SK1272 MC32107 5 2 2 Ir-store 9/2/21 CM NDT220 MD24385 30 Ir store In 9/2/21 CM-NDT220 MD56307 30 1 Ir-store 9/2/21 WB-HF4220 XOCXXOCX 90 2 Online 9/2/21 EB JU5470 XOCXXXXX 59 2 Ir store 9/3/21 FT-GV361 MB25494 16 1 Online 9/3/21 FT-FC3262 MC56593 40 1 Online 9/3/21 WEY3R269 MD24385 2 2 Ir store 9/3/21 EB-15479 ME64082 59 1 Ir-store 9/3/21 HG-FX197 XXXXX 10 1 Online 9/3/21 Ge-UAM343 XXXXXXX 3G 2 Ir store 9/4/21 CM-SOL7343 MB35019 3 1 Online 9/4/21 WB-W2R493 WE MR36025 13 3 In-store 9/4/21 FT FVS462 MC39 104 7 Online 9/4/21 GB-UAM405 MC53802 21 1 Online 9/4/21 CM CHB00 M2 1567 - 35 1 Online 9/4/21 FT GV3154 ML26012 91 In store 9/4/21 CBF2402 MES8417 47 1 Online 9/4/21 CMNDT425 MF68107 2 1 Online 9/4/21 FT GV3154 MR92834 91 Online 9/4/21 WL-FGR160 XOXXXXXX 22 1 Online 9/5/21 GB-NS5420 MB20736 9 2 In-Store 9/5/21 WL FGR116 MC39104 7 Online 9/5/21 WB-Y3R38S MD27726 59 3 Ir-store 9/5/21 WB-Y2R176 MD33494 B2 1 Online 9/5/21 CD SK1272 MD49119 5 2 Online 9/5/21 WB-3143 ML64082 78 1 Online 9/5/21 WL-AFX234 ME98417 91 1 Online 9/5/21 GB VALS1 MR70678 70 2 In store 9/5/21 FT-SGF164 MR82854 25 1 Online 9/5/21 WL-FGR447 XXXX XOCXXXX 36 1 In-store Category Gym D Water Rottles Earbuds Gym Des Earbuds Gym Bags Cardio Machines Earbuds Cardio Machines Cardio Machines Water Bottles Earbuds Fitness Tracker FitneTracker Water Bottles Earbuds Home Gym Gym Bags Cardio Machines Water Bottles Fitness Tracker Gym Des Cardio Machines Fitness Tracker Earbuds Cardio Machines Fitness Tracker Weight Lifting Gym Raus Wielght Lifting Water Bottles Water Rottles Earbuds Water Bottles Weight Lifting Gym Bags Fitness Tracke Weight Lifting Cost_per_Unit Price per Unit $21.00 $18.59 $9.59 $24.00 $11.99 $21.00 $$46.95 $9.59 $591.84 $591.84 $25.77 $5.99 $51.99 $97.47 $21.69 $5.99 $355.10 $27.89 $$46.95 $10.59 $.29.97 $27.89 $710.21 $51.99 $17.99 $945.95 $51.99 $147.96 $21.00 $236.73 $21.69 $18.59 $9.59 $24.77 $235.73 $32.95 $116.99 $165.71 Total Sales Shipping_Handling Cost Sales Taxes Total Due $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 S0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 Inventory Sales Trans Customer Data Miscinfo Monthly Payment Calculator Sales Pivot Employee Schociules Database Summary Advanced Filter + Ready LO + 100% % AutoSave ON APO... 2 ACIS 2504 Excel Spreadsheet Project LastFirst.xlsx - Saved Home Insert Draw Page Layout Formulas Data Review View Tell me Share Comments Calibri v 11 v AP A ab Wrap Text General v G v FO) IN Analyze Data Paste B 1 I U UV 7 VA Merge & Center Insert Delete Y Format Y Conditional Format Cell Formatting as Table Styles v Sort & Filter Find & Select F5 fx =ROUNDUP(RANK.EQ(E5,$E$5:$E$56,0),2] A B C F G K L M N 1 Hokie Fitness Equipment & Accessories, Inc. 10/1/21 2 Date 3 4 Customer_ID 5 MB20736 6 M025494 7 MB35019 MB36025 9 MB47621 10 MB62831 11 M873421 12 MB83576 13 M891356 14 MC1G332 15 M622709 16 MC31771 17 MC32107 18 MC39104 19 MC46295 20 MC53802 21 MC56593 22 MC60615 23 MC72946 24 MD10876 25 MD24385 26 MD27720 27 MD33494 28 MD49119 29 MDSG307 30 MD62941 31 ME 1567 32 ME26012 33 ME30318 34 MES4105 35 MEG4092 36 ME74922 37 ME80578 39 MENGK42 39 MES8412 Millor Cust_First Name Cust Last Name Card Type Charlotte Welch Platinum Jamie Thompson Everyday Smith Rod Corey Westerfies Rec Shelly Alderman Everyday Dana Cox Red Rhonda Abbott Rec 981 Poster Ruth Baldwin Everyday Dill Sutherland Everyday Mauroud Amad Everyday Martha Talbot Red Rebecca De grey Rec Bernare Moss Everyday Barrett | Realley Platinum Miranda Radcliffe Rec Rajan Kadappakam Platinum Justin Everyday Dis Lineberry Platinum Paul Kwur Platinum Alcion Ulrich Platinum Roy Callihan Red Sally Yane Everyday Nancy Wallace Ron Walter Wipple Everyday Karla Durvis Everyday Robert Arderson Red Juan Tobar Red Josiah Taylor Rodrigo Davila Everyday Raul Sanchez Everyday Jordan Monroe Reba Caldwell Everyday Melacy Talbot Platinum Roher Seth Everyday James Fitzgerald Everyday Roger Bentley Everyday Candice Everyday Rand Roundup/Rank Cust Serv Rep ID Credit Limit Current Balance Lxt Payment Date Payment Due Date 0.647648963 16! $1,000.00 $987.18 9/17:21 3.8370S 2104 6 $1,000.00 $183.20 9/8/21 0.585627268 19 $2,000.00 $954.57 9/30/21 0.550038212 21 $1,750.00 $1,689.21 9/13/21 0.05029555 51 $1,000.00 $675.81 9/29/21 2.735085529 11 $2,500.00 $1,121.60 9/25/21 0.280G39392 33 $1,000.00 $999.60 9/7/22 3.201626376 41 $1,750.00 $1,190.27 9/4/21 3.195237075 43 $250.00 $251.44 9/27/21 0.202150055 40 $2,500.00 $547.98 9/19/21 3.725786719 12 $2,000.00 $1.075,20 9/28/21 0.273287973 37 $1,750.00 $1,305.57 9/19/21 0.932723347 1 $750.00 $747.89 9/14/21 0.158630184 45 $750.00 $738.22 9/9/21 2.209262456 39 $1,500.00 $1,488.35 9/21/21 0.324334546 31 $250.00 $83.25 9/20/21 3.7147C6331 13 $2,000.00 $1,379.49 9/13/21 0.270495778 38 $1,750.00 $456.52 9/27/21 0.339520242 29 $1,250.00 $1,169.46 9/20/21 0.593195277 18 $1,500.00 $650.95 9/29/21 0.40778464 26 $1,000.00 $226.48 9/26/21 0.85991540 5 $500.00 $231.56 9/9/21 3.445314596 24 $1,500.00 $ $1,105.61 9/20/21 0.291849468 34 $250.00 $ $231.87 9/27/21 0.345130093 28 $2,250.00 $2,231.55 0.916982788 2 2 $750.00 $686.99 9/15/21 3.705943R35 14 $750.00 $642.68 $ 9/19/21 0.358013644 27 $2,000.00 $1,662.76 9/30,21 0.2758S7351 36 $1,600.00 $1,558.65 9/4/21 2.535402512 22 $2,250.00 $2,100.15 9/28/21 0.116341127 47 $500.00 $254.35 9/10:21 5.173187601 44 $2,000.00 $1,647.89 9/7/21 0.105516445 49 $1,500.00 $1,448.78 9/22/21 0.463298124 23 $2,250.00 $2,249.67 9/30,21 0.325094557 30 $2,250,00 $1,888.SO 9/12/21 0.19889064 42 $550.00 $533.57 9/30/21 0.753348747 9 $1,500.00 $624.22 9/5/21 0.783879438 8 $750.00 $613.95 9/27/21 Credit Remaining Credit Limit Massage $12.92 5816.80 $1,035.43 $60.79 $324.18 $1,378.40 $1.40 5559.73 $1.44 $1,952.02 5924.80 $444.43 $2.13 $11.78 $11.65 $160.75 S620.51 $1,283.48 $80.54 5849.05 $773.52 $265.44 $394,39 $18.13 $15.45 $63.04 $107.32 $337.24 $41.35 $149.85 $ $215.GS $352.01 $51.22 $1.33 $361.10 $15.43 $875.78 $136.05 9/26/21 40 MEG9550 41 MF15432 42 M29753 Inventory SalesTrans Customer Data MiscInfo Monthly Payment Calculator Sales Pivot Employee Schedule Database Summary Advanced Filter + Ready + 100% % AutoSave ON APO... RO ACIS 2504 Excel Spreadsheet Project LastFirst.xlsx - Saved Home Insert Draw Page Layout Formulas Data Review View Tell me Share Comments Calibrl (Body) v 11 X G v A A Wrap Text General AYO v v FO) IN Analyze Data Pastc A = = = Insert Format - Merge & Center Delete Y Y Conditional Format Cell Formatting es Table Styles Sort & v Filter Find & Select D11 fx c F G . I 2 K TL L M N O Q R S T U Credit Umit Messages Credit Remaining Message Shipping and Handling Rates Card Type Rate 3 No card 4.00% 4 Platinum 9.OON 5 5 Red 1.00% G Everyday 2.00% 7 2 9 Sales Tax Rate 6.30% 10 Number of Customers Assigned to 11 Cust_Service_Rep 13 12 13 Customer Service Representatives 14 Cust Serv Rep ID Cust Service Rep 15 CSR-134765 Lalunya Smith 16 CSR-725034 Andy Che 17 CSR 879452 Juana Torres 10 CSR-941725 Mustafa 19 Number of workdays 20 between payment dates 20 21 September Holiday 9/6/21 22 October Holiday 10/11/21 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 32 39 Inventory SalesTrans Customer Data Miscinfo Monthly Payment Calculator Sales Pivot Employee Schedule Database Summary Advanced Filter + Ready LEN TVU