Please help with steps seventeen through 21.

Hokie Fitness Equipment & Accessories, Inc. is a retailer that sells fitness equipment and accessories. The manager of Hokie Fitness Equipment & Accessories, Inc. uses a spreadsheet to keep track of inventory, sales transactions, and customer data. The manager has asked for your help in completing the spreadsheet.

Steps:

3. Inventory data that was imported into the Inventory worksheet must be cleansed. In cell B6 of the Inventory worksheet, use two functions to remove any nonprintable characters and extra spaces from the data in cell A6. Copy the formula in cell B6 down to cell B70. Resize the column as needed to fit the contents.

4. In cell C6 of the Inventory worksheet, use the LEFT and FIND functions to extract the Product_ID from cell B6. The Product_ID should include the letters and numbers on the left side of the text in cell B6. (For example, the Product_ID for the first inventory item should be GB-UAM405.) Copy the formula in cell C6 down to cell C70 without modifying it. The formula must correctly extract the Product_ID for all the inventory items. Resize the column as needed to fit the contents.

5. In cell D6 of the Inventory worksheet, use the MID and FIND functions to extract the Warehouse_Bin number from cell B6. (For the first inventory item, the Warehouse_Bin number is 21.) Copy the formula in cell D6 down to cell D70 without modifying it. The formula must correctly extract the Warehouse_Bin number for all the inventory items. Resize the column as needed to fit the contents. Note: The Warehouse_Bin number can be 1 or 2 digits.

6. In cell E6 of the Inventory worksheet, use the PROPER, RIGHT, LEN and FIND functions to extract the category from cell B6. (For the first inventory item, the category is Gym Bags.) Copy the formula in cell E6 down to cell E70 without modifying it. The formula must correctly extract the category for all the inventory items. Resize the column as needed to fit the contents.

7. The SalesTrans sheet displays sales transactions for the month of September 2021. Create a Transaction_ID number for each sales transaction on the SalesTrans sheet. Place a formula in cell A5 of the SalesTrans sheet that uses the TEXT, LEFT, and RIGHT functions as well as something to join text strings. The Transaction_ID should begin with Date in DDMMYY format. The Date should be followed by the first 5 characters of the Customer_ID. The first 5 characters of the Customer_ID should be followed by the right 7 characters of the Product_ID. (For example, the Transaction_ID in cell A5 should be: 010921MB628-NBS420.) Copy the formula in cell A5 to cell range A6:A210.

8. Enter a formula in cell E5 of the SalesTrans sheet that uses a VLOOKUP function to find the Product_ID in the Inventory sheet and return the Warehouse_Bin number for that product. Copy the formula in cell E5 to cell range E6:E210.

9. Hokie Fitness Equipment & Accessories offers three different types of credit cards to its customers. It offers a Platinum card, a Red card, and an Everyday card. Customers who use one of the companys credit cards to pay for purchases receive a discount on product prices and shipping. Customers who have a Customer_ID of XXXXXXX do not have a company credit card.

Use an IF function and a VLOOKUP function in cell H5 of the SalesTrans sheet to display No_card if the Customer_ID is XXXXXXX; otherwise, the formula should find the Customer_ID in the Customer Data sheet and return the type of credit card that the customer has (either Platinum, Red or Everyday). Copy the formula in cell H5 to cell range H6:H210.

10. Enter a formula in cell I5 of the SalesTrans sheet that uses a VLOOKUP function to find the Product_ID in the Inventory sheet and return the Category for that product. Copy the formula in cell I5 to cell range I6:I210.

11. Enter a formula in cell J5 of the SalesTrans sheet that uses a VLOOKUP function to find the Product_ID in the Inventory sheet and return the Cost_per_Unit for that product. Copy the formula in cell J5 to cell range J6:J210.

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.

13. Shipping and handling rates vary depending on whether the customer has a company credit card and, if the customer has a company credit card, what type of card that is. Go to the MiscInfo sheet and use Create from Selection to name the cells with the shipping and handling rates (cells B3:B6) based on the labels in cells A3:A6.

14. Enter a formula in cell M5 of the SalesTrans sheet that uses a nested IF function and an OR function to calculate Shipping_Handling_Cost. Use the names for the shipping and handling rates that were created in the previous step. Shipping_Handling_Cost is calculated as follows:

Condition	Shipping_Handling_Cost
Sales_Type is In-store	0
Card_Type is Platinum	0
Sales_Type is Online, Card_Type is Everyday, Red or No_Card	Applicable Shipping and Handling Rate * Total_Sales

Copy the formula in cell M5 down to cell range M6:M210.

15. Add Data Validation to cells C5:C210 of the SalesTrans sheet. Limit data entry to the list of Product_IDs that appear on the Inventory sheet. Use the Stop error alert. After you test the Data Validation, make sure you restore the original Product-IDs; otherwise, your Total_Sales and Total_Due values will be incorrect.

16. Add Data Validation to cells B5:B210 of the SalesTrans sheet. Limit data entry to dates from 9/1/2021 to 9/30/2021. Display an input message.

The title of the input message should be: Valid Date

The input message should be: A date in September 2021 must be entered

Use the Stop error alert. After you test the Data Validation, make sure you restore the original dates.

17. The manager of Hokie Fitness Equipment & Accessories would like to randomly assign a customer service representative to each customer on the Customer Data sheet. Each representative should be assigned to the number of customers shown in cell B11 of the MiscInfo sheet.

Range E5:E56 of the Customer Data sheet contains random numbers. Use these random numbers to perform calculations that assign each customer to one of the customer service representative IDs that appears in cells A15:A18 of the MiscInfo sheet. Use the ROUNDUP and RANK.EQ functions in cells F5:F56. Use the INDEX function in cells G5:G56. The customer service representative IDs should be displayed in cells G5:G56. (An example of the formulas can be found on slide 65 of the ACIS 2504 Excel 7 PowerPoint Slides.)

18. The Customer Data sheet displays the credit limits and current credit card balances for customers with company credit cards. Use the WORKDAY function in cell K5 of the Customer Data sheet to calculate the Payment_Due_Date. The Payment_Due_Date is a specific number of days from the Last_Payment_Date. This specific number appears in cell B20 of the MiscInfo sheet. There are two holidays that must be considered. These holidays are shown in cells B21 and B22 of the MiscInfo sheet.

Copy the formula from cell K5 to cells K6:K56 without making changes to it

19. Use a VLOOKUP function in cell M5 of the Customer Data sheet that will look up Credit_Remaining in a table/array and display a message. You will also need to use an IFERROR function to display a message if Credit_Remaining cannot be found in the lookup table.

The first step is to create a lookup table. The column headings for the lookup table must be in cells D2:E2 of the MiscInfo sheet. Do NOT format the lookup table as an Excel table. Name the table MessageLookup and do not include the column headings in the name. Following are the messages that should be displayed based on the amount of credit remaining: (The following table must be re-arranged. It is not in the correct format to be used with a VLOOKUP function.)

Message	Credit Remaining
No issues	>=100
Monitor credit limit	>=50 and
Approaching credit limit	>=10 and
Credit limit reached	>=0 and

Use the name of the lookup table in your VLOOKUP formula and make sure the messages are spelled and capitalized correctly. You will not receive credit if the lookup table is not named correctly and the messages are misspelled and/or improperly capitalized.

Once you have created the VLOOKUP formula, nest it inside an IFERROR function. The IFERROR function should display Check current balance (without the quotes) if Credit_Remaining cannot be found in the lookup table.

Copy the formula in cell M5 down the column to cell M56.

20. Add custom Data Validation to cell range A5:A56 on the Customer Data sheet. The 5 characters on the right side of the Customer_ID must be greater than 10000 and less than 99999. The length of the Customer_ID must be 7. Use the Stop error alert. After you test the Data Validation, make sure you restore the original Customer_ID.

21. The company manager would like to highlight Customer_IDs for customers who have a Platinum card and a Credit_Limit_Message of Credit limit reached. Apply conditional formatting to cells A5:A56 of the Customer Data sheet. Use a formula to determine which cells to format. If the Card_Type is Platinum and the Credit_Limit_Message is Credit limit reached, apply a light blue fill color (second row and fifth column of the color palette) to the cells.

B D E F. G H J K A 1 Hokie Fitness Equipment & Accessories, Inc. 10/1/2021 2 3 Date 4 5 Imported Data 6 2GB-UAM405?21*GYM BAGS 3 7 2 GB-UAM343?36*gYM BAGS 8 IGB-UAS378?25*GYM BAGS 9 AGB-UAS212790*GYM BAGI 10 EGB-UAL43?31*GYM BAGS 2 11 2 GB-UAL91?70*GYM BAGS 12 GB-NBM488?79*GYM BAGI 13 E GB-NBM271240*GYM BAGS 14 IGB-NBL468?62*GyM DAGI 15 GB-NBL242250*GyM DAGI 16 GB-NBS420?9*GyM DAGI 17 GB-NBS453?32*GyM DAGs 18 2 FT-FC3362?40*fitness trackER 19 EFT-FC3435246*fitNess trackER ? 20 2 FT-FVS462?7*fiNess tRacKER 21 OFT-FVS445?25*fitNess trackER 22 FT-FIW34649*fitness trackERI 23 2 2 FT-FIW385?61 *fitness tRacKER 24 EFT-GV361?16*fitNess trackER 2 25 2 2 FT-GV3154791*fiNess trackER 26 EFT-SGF164?25*fiNess trackER 2 27 FT-SGF37?64*fitNess trackERI 28 DEB-SK1272?6*earBUDS Product Information GB-UAM405?21*GYM BAGS GB-UAM343?36*gYM BAGS GB-UAS378?25*GYM BAGS GB-UAS212290*GyM DAGs GB-UAL43?31*GYM BAGS GB-UAL91?70*GYM BAGS GB-NBM488?79*GYM BAGS GB-NBM271240*GYM BAGS GB-NBL468?62*GYM BAGS GB-NBL242250*GYM BAGS GB-NBS420?9*GYM BAGS GB-NBS453?32*GYM BAGS FT-FC3362?40*fiNess trackER FT-FC3435246*fitNess trackER FT-FVS462?7*fitness trackER FT-FVS445?25*fiNess trackER FT-FIW34649*filNess trackER FT-FIW385261*fiNess tRacKER FT-GV361?16*fitNess trackER FT-GV3154791*fitness trackER FT-SGF164?25*fiNess trackER FT-SGF37?64*fiNess trackER EB-SKI272?6*earBUDS Product_ID Warehouse_Bin Category Product Description Color GB-UAM405 21 21*GYM BAGS Under Armour Undeniable 3.0 Medium Black GB-UAM34:36 3?36*gYM WAGs Under Armour Undeniable 3.0 Medium Blue GB-UAS378 25 225*GYM BAGS Under Armour Undeniable 3.0 Small Black GB-UAS212 90 2290*GyM DAGs Under Armour Undeniable 3.0 Small Blue GB-UAL43 31 1*GYM BAGS Under Armour Undeniable 3.0 Large Black GB-UAL91 70 ?70*GYM BAGS Under Armour Undeniable 3.0 Large Blue GB-NBM488 79 ?79*GYM BAGS Nike Brasilia 8 Medium Black GB-NBM27140 1240*GYM BAGs Nike Brasilia 8 Medium Blue GB-NBL468 62 ?62*GYM BAGS Nike Brasilia 8 Large Black GB-NBL242 50 250*GYM BAGS Nike Brasilia 8 Large Blue GB-NBS420 9* ?9*GYM BAGS Nike Brasilia 8 Small Black GB-NBS453 32 ?32*GYM BAGS Nike Brasilia 8 Small Blue FT-FC3362 40 TNess trackER Fitbit Charge 3 Activity Tracker Black FT-FC3435 46 ess trackER Fitbit Charge 3 Activity Tracker Gold FT-FVS462 7* Ness trackER Fitbit Versa Smartwatch Black FT-FVS445 25 Ness trackER Fitbit Versa Smartwatch Gold FT-FIW346 49 Ness trackER Fitbit lonic Watch Black FT-FIW385 61 iTNess tRacKER Fitbit lonic Watch Blue FT-GV361 16 ss trackER Garmin Vivofit 3 Activity Tracker Black FT-GV3154 91 iTNess trackER Garmin Vivofit 3 Activity Tracker White FT-SGF164 25 ess trackER Samsung Gear Fit2 Smart Fitness Band Black FT-SGF37 64 ess trackER Samsung Gear Fit2 Smart Fitness Band Pink EB-SKI272 6* 2?6*earBUDS Skullcandy Ink'd Black Cost_per_Unit Card_Price Non_Card_Price $27.89 $44.99 $46.99 $27.89 $44.99 $46.99 $24.79 $39.99 $41.99 $24.79 $39.99 $41.99 $32.85 $52.99 $54.95 $32.85 $52.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 $21.00 $35.00 $36.75 $97.47 $149.95 $157,45 $97.47 $149.95 $157.45 $129.97 $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 $83.99 $51.99 $79.99 $83.99 $116.99 $179.99 $188.99 $116.99 $179.99 $188.99 $9.59 $15.99 $16.79 r DLDON EN ALIAA COLLADA DLDC L11 LLLL AL 10 Documentation Inventory SalesTrans Customer Data Miscinfo Monthly Payment Calculator Sales Pivot II. Employee Schedule Database Summar M Card Type Category Cost_per_Unit $0.00 1 Online 1 Hokie Fitness Equipment & Accessories, Inc. 2 Sales Transactions for September 2021 3 4 Transaction_ID Date Product_ID Customer_ID Warehouse Bin Quantity_Sold Sales Type 5 9/1/2021 GB-NBS420 MB62831 2 Online 6 9/1/2021 WB-Y2R124 MC46295 1 Online 7 9/1/2021 EB-SK1272 MD10876 2 In-store 8 9/1/2021 GB-NBM271 MD27726 1 Online 9 9 9/1/2021 EB-JJP137 MR61502 2 Online 10 9/1/2021 GB-NBS453 XXXXXXX 1 In-store 11 9/2/2021 CM-SOL7343 MC22709 1 Online 12 9/2/2021 EB-SK1272 MC32107 2 In-store 13 9/2/2021 CM-NDT220 MD24386 1 In-store 14 9/2/2021 CM-NDT220 MD56307 1 In-store 15 9/2/2021 WB-HF4280 XXXXXXX 2 Online 16 9/2/2021 EB-JJS470 XXXXXXX 2 In-store 17 9/3/2021 FT-GV361 MB25494 1 Online 18 9/3/2021 FT-FC3362 MC56593 19 9/3/2021 WB-Y3R269 MD24386 - 2 In-store 20 9/3/2021 EB-JS470 ME64082 1 In-store 21 9/3/2021 HG-BFX197 XXXXXXX 1 Online 22 9/3/2021 GB-UAM343 XXXXXXX - 2 In-store 23 9/4/2021 CM-SOL7343 MB35019 1 Online 24 9/4/2021 WB-Y2R493 MB36025 3 In-store 25 9/4/2021 FT-FV5462 MC39104 1 Online 26 9/4/2021 GB-UAM405 MC53802 1 Online 27 9/4/2021 CM-SCH390 ME21567 1 Online 28 9/4/2021 FT-GV3154 ME26013 1 In-store 29 9/4/2021 EB-JF2402 ME98417 1 Online 30 9/4/2021 CM-NDT425 MF68107 1 Online 31 9/4/2021 FT-GV3154 MR92834 1 Online 32 9/4/2021 WL-FGR160 XXXXXXX 1 Online 33 9/5/2021 GB-NBS420 MB20736 2 In-store 34 9/5/2021 WL-FGR116 MC39104 1 Online 35 9/5/2021 WB-Y3R389 MD27726 3 In-store 36 9/5/2021 WB-Y2R176 MD33494 1 Online 37 9/5/2021 EB-SK1272 MD49119 2 Online Price_per_Unit 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 $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 Sales Pivot Databas $0.00 $0.00 $0.00 Documentation Inventory Sales Trans Customer Data Misclnfo Monthly Payment Calculator Employee Schedule A B C D E F G H | J K L M 1 Hokie Fitness Equipment & Accessories, Inc. 10/1/2021 Card Type Payment_Due Date Credit_Limit_Message Westerfield Alderman Credit_Remaining $12.82 $816.80 $1,035.43 $60.79 $324 10 $324.19 51 370 $140 $1.40 $559.73 -$1.44 sem Everyday phonda 2 Date 3 4 + Customer_ID 5 MB20736 6 MB25494 7 MB35019 8 MB36025 9 9 MB47621 3 10 MB62831 11 MB73421 12 MB83576 13 MB91356 14 MC16332 15 MC22709 16 MC31771 17 M107 17 MC32107 10 M40 18 MC39104 19 MC46295 20 MC53802 21 MCEEEE 21 MC56593 22 MC60615 20 MCG0515 23 MC72946 20 MC 2016 24 MD10876 $260 Sutherland Everyday Everyday $2.00 51 750 $1 952 $92 1E Rebecca Bernard Delaney $444.43 649 $2.11 $11.78 $11 $11.65 $ : Everyday Beasley Dadcliffe Miranda to 5166.75 Kadappakam Cust_First_Name Cust_last_Name Charlotte Welch Platinum Jamie Thompson Everyday Julius Smith Red Core Corey Red Shell Shelly Fvers Dana nad Red COX Abbott Red Ded Loan Joan Foster Red Puth Ruth Baldwin Everyday Jill Mahmoud Ahmad Ahmad Martha Talbot Red Red Moss More Barat Barrett Platinum Red De Kadann Rajan Platinum Justin Luetin Mille Miller Everyday Delia Lineberry Platinum Won Paul Kwon Aiden Ulrich Platinum Demi Roy Callihan Red sal Sally Yang Everyday Nancy Wallace Red Walter Wipple Everyday WP Karla Davis Everyday ban sans Robert Anderson Red Juan Tobar Red Josiah Taylor Red am Rodrigo Davila wania Everyday Raul Sanchez sarcin Everyday Jordan Monroe Red . Reba Caldwell Everyday Melody Talbot Platinum Rohan Seth Everyday James Fitzeerald Evervday Documentation Rand Roundup/Rank Cust_Serv_Rep_ID Credit_Limit Current_Balance 0.647648863 $1,000.00 $987.18 0.837092104 $1,000.00 $183.20 0.586627268 $2,000.00 $964.57 0.550058218 $1,750.00 $1,689.21 06029555 1.000 0.06029555 $67581 $675.81 $1,000.00 0.736085629 COOK $2,500.00 $1,121.60 0.280639388 $1.00 $1,000.00 $998.60 SOORE 201606076 0.201626076 $1,750.00 $1,190.27 $1 19027 0106937075 0.196237075 $250.00 $251.44 202150055 0.202150055 $2,500.00 $547.98 725786719 0.725786719 $2,000.00 $ $1,075.20 0.273287973 $1 305 57 0.932723347 037773347 250 $750.00 22700 $747.89 158630184 TEN 0.158630184 $750.00 $738.22 0.209262456 SEO $1,500.00 $1,488.35 0.324334586 250 $250.00 $83.25 0.714706331 $2,000.00 $1379 40 270405278 0.270495778 $1,750.00 $456 50 $466.52 0.338580242 $1,250.00 $1,169.46 0.593195277 $1,500.00 $650.95 0.40778464 5.20 $226.48 $1,000.00 0.859815401 $500.00 $231.56 0.445314696 $1,500.00 $1,105.61 0.291849468 $250.00 $231.87 0.345130093 $2,250.00 $2,231.55 0.916982788 $750.00 $686.99 were 0.706943835 re $750.00 $642.68 0.358013644 $2,000.00 - $1,662.76 X.ro 0.275897351 $1,600.00 $1,558.65 ** . 0.536402512 $2,250.00 $2,100.15 . 0.116341127 $500.00 $284.35 W 2010 0.173187601 $2,000.00 $1,647.99 Wacamat . 0.106516445 $1,500.00 $1,448.78 0.463298124 $2,250.00 $2,248.67 0.325094657 $2,250.00 $1,888.90 0.19889064 $550.00 $533.57 Sales Trans Customer Data Misclnfo Last_Payment_Date 9/17/2021 9/8/2021 9/30/2021 9/13/2021 9/29/2021 TE 9/25/2021 9/7/2021 9/4/2021 9/27/2021 9/19/2021 128190 9/28/2021 9/19/2021 110 120 12094 9/14/2021 Q/9/2021 9/9/2021 9/21/2021 0/08/2004 9/28/2021 9/18/2004 9/13/2021 9/27/2021 17120 9/20/2021 9/2012021 9/29/2021 Q/26/2001 9/26/2021 9/8/2021 9/20/2021 9/27/2021 74021 9/26/2021 9/15/2021 9/19/2021 --- 9/30/2021 9/4/2021 12- 9/28/2021 wa 9/10/2021 121 9/7/2021 1021 9/22/2021 9/30/2021 9/12/2021 9/30/2021 Platinum $1.500 25 MD24386 26 MD27726 27 MD33494 28 MD49119 29 MD56307 30 MD67941 31 ME21567 ALLA 32 ME26013 ME 33 ME30018 Mbwene 34 ME54105 e 35 ME64082 36 ME74922 *** 37 ME80578 38 ME86842 39 ME98417 40 ME99550 $620.51 $1 283 $80.54 Cena $849.05 R40 05 $773.52 $ $268.44 $394.39 $ $18.13 $18.45 $63.01 WWW. $107.32 . $337.24 WWW. $41.35 $149.85 . $215.65 $352.01 $51.22 $1.33 $361.10 $16.43 Sales Pivot Inventory Monthly Payment Calculator Employee Sched D E Credit Limit Messages Credit Remaining Message A B 1 Shipping and Handling Rates 2 Card Type Rate 3 No_card 4 Platinum 5 Red 6 Everyday 7 4.00% 0.00% 1.00% 2.00% 8 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 LaTonya Smith 16 CSR-725034 Andy Chen 17 CSR-879452 Juana Torres 18 CSR-941725 Mustafa Ali 19 Number of workdays 20 between payment 20 21 September Holiday 9/6/2021 22 October Holiday 10/11/2021 Documentation Inventory SalesTrans Customer Data Misclnfo Monthly Pa ABC D E F G H 1 J K K L M N C 1 1 2 Interest Paid Annual Payment 3 4 4 5 6 7 8 9 Amortization Schedule Year Beginning Balance Ending Balance Paid on Principal 1 2 3 4 5 6 7 4 Monthly* Payment Calculator Date 9-May-2022 Annual Rate 4.250% Item Cargo Van Term (in years) 5 Price $56,000.00 Payments per Year 12 Down Pymt. $10,000.00 Monthly Payment Loan Amount Total Interest Total Cost Monthly_Payment - Varying Interest Rate and Term Term (in years) 5 6 6 3.000% 3.250% 3.500% 3.750% 4 onno 42500 4.250% 500 4.500% 4.750% 5.000% 5.250% 5.500% Varying Interest Rate Schedule Rate Monthly Payment Total Interest Total Cost 8 9 10 10 11 12 13 14 15 16 17 18 19 20 21 22 23 $0.00 Subtotal Down Pymt. Total Cost $0.00 #NAME? ? #NAME? *Assume the monthly payment is made at the end of the period. Errors checks Paid on Principal Interest Paid Total Cost Annual Payment 24 25 26 27 28 29 30 31 32 33 34 35 36 37 29 3.000% 3.250% 3.500% Iron 3.750% 4.000% 4.250% FOOD 4.500% 4.750% 5.000% 5.250% 5.500% Documentation Inventory Sales Trans Customer Data Misclnfo Monthly Payment Calculator A B n D E F G H J 1 Hokie Fitness Equipment & Accessories, Inc. Mon Tue Wed Thu Fri Sat 0 1 1 1 1 1 1 1 0 0 1 1 1 0 0 1 1 1 1 1 1 0 0 1 nm in ooooom UwU 1 1 1 0 0 1 1 1 1 1 Schedule Days Off Staff Sun A Sun, Mon 2 0 Mon,Tue 2 1 Tue, Wed 2 1 D Wed, Thu 2 1 Thu, Fri 2 1 Fri, Sat 2 1 G Sat, Sun 2 0 Total staff working 14 10 Staff needed 2 Work week 40 hours Avg hourly pay per employee $16.50 Weekly payroll $9,240.00 0 0 1 10 1 1 1 1 0 11 10 10 10 10 10 10 191 12 1 1 1 2 2 13 14 15 16 17 Solver Model 18 19 20 Documentation Inventory Sales Trans Customer Data Miscinfo Monthly Payment Calculator Add header Add header Add header Hokie Fitness Equipment & Accessories, Inc. for Platinum or Red cardholders with a Credit_Limit_Message of Credit limit reached Customer ID ust_First_Namust Last Nam Card Ipe Rand Roundup/Ranlust Sery_Rep_Credit Limit urrent_Balancest Payment Da ayment Due Dacredit_Remainin. Credit Limit_Message or Red cardholders who purchased cardio machines in the store fransaction Date Product_ID Customer_id'arehouse_Bi Quantity_Sol Sales_Type Card_Type Category Cost_per_Unit Price_per_Uni Total_Sales hipping_Handling_Cdales_Taxcotal_Due Inventory SalesTrans Customer Data Misclnfo Monthly Payment Calculator Sales Pivot Database Employee Schedule II. Summary A B C D E F G G H 1 Earbuds Fitness Tracker Gym Bags Home Gym Water Bottles Weight Lifting $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 1 Hokie Fitness Accessories, Inc. 2 Summary for September 2021 3 4 Total Sales 5 Number of Transactions 6 7 Total Sales by Sales_Type and Category 8 Sales Type Cardio Machines 9 Online 10 In-store 11 Total $0.00 12 13 Total Sales by Sales_Type and Card_Type 14 Sales_Type Platinum 15 Online 16 In-store 17 Total $0.00 18 19 Number of Sales Transactions 20 Online 21 In-store 22 No_card 23 24 Credit Limit Message Red Everyday No_card $0.00 $0.00 $0.00 Credit limit reached Approaching Monitor credit credit limit limit Check current balance No issues Total 0 Cardholder Current 25 Balance Information 26 Number of cardholders 27 Average Current Balance 28 Total Current Balance 29 $0.00 30 Inventory SalesTrans Customer Data Misclnfo Monthly Payment Calculator B D E F. G H J K A 1 Hokie Fitness Equipment & Accessories, Inc. 10/1/2021 2 3 Date 4 5 Imported Data 6 2GB-UAM405?21*GYM BAGS 3 7 2 GB-UAM343?36*gYM BAGS 8 IGB-UAS378?25*GYM BAGS 9 AGB-UAS212790*GYM BAGI 10 EGB-UAL43?31*GYM BAGS 2 11 2 GB-UAL91?70*GYM BAGS 12 GB-NBM488?79*GYM BAGI 13 E GB-NBM271240*GYM BAGS 14 IGB-NBL468?62*GyM DAGI 15 GB-NBL242250*GyM DAGI 16 GB-NBS420?9*GyM DAGI 17 GB-NBS453?32*GyM DAGs 18 2 FT-FC3362?40*fitness trackER 19 EFT-FC3435246*fitNess trackER ? 20 2 FT-FVS462?7*fiNess tRacKER 21 OFT-FVS445?25*fitNess trackER 22 FT-FIW34649*fitness trackERI 23 2 2 FT-FIW385?61 *fitness tRacKER 24 EFT-GV361?16*fitNess trackER 2 25 2 2 FT-GV3154791*fiNess trackER 26 EFT-SGF164?25*fiNess trackER 2 27 FT-SGF37?64*fitNess trackERI 28 DEB-SK1272?6*earBUDS Product Information GB-UAM405?21*GYM BAGS GB-UAM343?36*gYM BAGS GB-UAS378?25*GYM BAGS GB-UAS212290*GyM DAGs GB-UAL43?31*GYM BAGS GB-UAL91?70*GYM BAGS GB-NBM488?79*GYM BAGS GB-NBM271240*GYM BAGS GB-NBL468?62*GYM BAGS GB-NBL242250*GYM BAGS GB-NBS420?9*GYM BAGS GB-NBS453?32*GYM BAGS FT-FC3362?40*fiNess trackER FT-FC3435246*fitNess trackER FT-FVS462?7*fitness trackER FT-FVS445?25*fiNess trackER FT-FIW34649*filNess trackER FT-FIW385261*fiNess tRacKER FT-GV361?16*fitNess trackER FT-GV3154791*fitness trackER FT-SGF164?25*fiNess trackER FT-SGF37?64*fiNess trackER EB-SKI272?6*earBUDS Product_ID Warehouse_Bin Category Product Description Color GB-UAM405 21 21*GYM BAGS Under Armour Undeniable 3.0 Medium Black GB-UAM34:36 3?36*gYM WAGs Under Armour Undeniable 3.0 Medium Blue GB-UAS378 25 225*GYM BAGS Under Armour Undeniable 3.0 Small Black GB-UAS212 90 2290*GyM DAGs Under Armour Undeniable 3.0 Small Blue GB-UAL43 31 1*GYM BAGS Under Armour Undeniable 3.0 Large Black GB-UAL91 70 ?70*GYM BAGS Under Armour Undeniable 3.0 Large Blue GB-NBM488 79 ?79*GYM BAGS Nike Brasilia 8 Medium Black GB-NBM27140 1240*GYM BAGs Nike Brasilia 8 Medium Blue GB-NBL468 62 ?62*GYM BAGS Nike Brasilia 8 Large Black GB-NBL242 50 250*GYM BAGS Nike Brasilia 8 Large Blue GB-NBS420 9* ?9*GYM BAGS Nike Brasilia 8 Small Black GB-NBS453 32 ?32*GYM BAGS Nike Brasilia 8 Small Blue FT-FC3362 40 TNess trackER Fitbit Charge 3 Activity Tracker Black FT-FC3435 46 ess trackER Fitbit Charge 3 Activity Tracker Gold FT-FVS462 7* Ness trackER Fitbit Versa Smartwatch Black FT-FVS445 25 Ness trackER Fitbit Versa Smartwatch Gold FT-FIW346 49 Ness trackER Fitbit lonic Watch Black FT-FIW385 61 iTNess tRacKER Fitbit lonic Watch Blue FT-GV361 16 ss trackER Garmin Vivofit 3 Activity Tracker Black FT-GV3154 91 iTNess trackER Garmin Vivofit 3 Activity Tracker White FT-SGF164 25 ess trackER Samsung Gear Fit2 Smart Fitness Band Black FT-SGF37 64 ess trackER Samsung Gear Fit2 Smart Fitness Band Pink EB-SKI272 6* 2?6*earBUDS Skullcandy Ink'd Black Cost_per_Unit Card_Price Non_Card_Price $27.89 $44.99 $46.99 $27.89 $44.99 $46.99 $24.79 $39.99 $41.99 $24.79 $39.99 $41.99 $32.85 $52.99 $54.95 $32.85 $52.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 $21.00 $35.00 $36.75 $97.47 $149.95 $157,45 $97.47 $149.95 $157.45 $129.97 $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 $83.99 $51.99 $79.99 $83.99 $116.99 $179.99 $188.99 $116.99 $179.99 $188.99 $9.59 $15.99 $16.79 r DLDON EN ALIAA COLLADA DLDC L11 LLLL AL 10 Documentation Inventory SalesTrans Customer Data Miscinfo Monthly Payment Calculator Sales Pivot II. Employee Schedule Database Summar M Card Type Category Cost_per_Unit $0.00 1 Online 1 Hokie Fitness Equipment & Accessories, Inc. 2 Sales Transactions for September 2021 3 4 Transaction_ID Date Product_ID Customer_ID Warehouse Bin Quantity_Sold Sales Type 5 9/1/2021 GB-NBS420 MB62831 2 Online 6 9/1/2021 WB-Y2R124 MC46295 1 Online 7 9/1/2021 EB-SK1272 MD10876 2 In-store 8 9/1/2021 GB-NBM271 MD27726 1 Online 9 9 9/1/2021 EB-JJP137 MR61502 2 Online 10 9/1/2021 GB-NBS453 XXXXXXX 1 In-store 11 9/2/2021 CM-SOL7343 MC22709 1 Online 12 9/2/2021 EB-SK1272 MC32107 2 In-store 13 9/2/2021 CM-NDT220 MD24386 1 In-store 14 9/2/2021 CM-NDT220 MD56307 1 In-store 15 9/2/2021 WB-HF4280 XXXXXXX 2 Online 16 9/2/2021 EB-JJS470 XXXXXXX 2 In-store 17 9/3/2021 FT-GV361 MB25494 1 Online 18 9/3/2021 FT-FC3362 MC56593 19 9/3/2021 WB-Y3R269 MD24386 - 2 In-store 20 9/3/2021 EB-JS470 ME64082 1 In-store 21 9/3/2021 HG-BFX197 XXXXXXX 1 Online 22 9/3/2021 GB-UAM343 XXXXXXX - 2 In-store 23 9/4/2021 CM-SOL7343 MB35019 1 Online 24 9/4/2021 WB-Y2R493 MB36025 3 In-store 25 9/4/2021 FT-FV5462 MC39104 1 Online 26 9/4/2021 GB-UAM405 MC53802 1 Online 27 9/4/2021 CM-SCH390 ME21567 1 Online 28 9/4/2021 FT-GV3154 ME26013 1 In-store 29 9/4/2021 EB-JF2402 ME98417 1 Online 30 9/4/2021 CM-NDT425 MF68107 1 Online 31 9/4/2021 FT-GV3154 MR92834 1 Online 32 9/4/2021 WL-FGR160 XXXXXXX 1 Online 33 9/5/2021 GB-NBS420 MB20736 2 In-store 34 9/5/2021 WL-FGR116 MC39104 1 Online 35 9/5/2021 WB-Y3R389 MD27726 3 In-store 36 9/5/2021 WB-Y2R176 MD33494 1 Online 37 9/5/2021 EB-SK1272 MD49119 2 Online Price_per_Unit 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 $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 Sales Pivot Databas $0.00 $0.00 $0.00 Documentation Inventory Sales Trans Customer Data Misclnfo Monthly Payment Calculator Employee Schedule A B C D E F G H | J K L M 1 Hokie Fitness Equipment & Accessories, Inc. 10/1/2021 Card Type Payment_Due Date Credit_Limit_Message Westerfield Alderman Credit_Remaining $12.82 $816.80 $1,035.43 $60.79 $324 10 $324.19 51 370 $140 $1.40 $559.73 -$1.44 sem Everyday phonda 2 Date 3 4 + Customer_ID 5 MB20736 6 MB25494 7 MB35019 8 MB36025 9 9 MB47621 3 10 MB62831 11 MB73421 12 MB83576 13 MB91356 14 MC16332 15 MC22709 16 MC31771 17 M107 17 MC32107 10 M40 18 MC39104 19 MC46295 20 MC53802 21 MCEEEE 21 MC56593 22 MC60615 20 MCG0515 23 MC72946 20 MC 2016 24 MD10876 $260 Sutherland Everyday Everyday $2.00 51 750 $1 952 $92 1E Rebecca Bernard Delaney $444.43 649 $2.11 $11.78 $11 $11.65 $ : Everyday Beasley Dadcliffe Miranda to 5166.75 Kadappakam Cust_First_Name Cust_last_Name Charlotte Welch Platinum Jamie Thompson Everyday Julius Smith Red Core Corey Red Shell Shelly Fvers Dana nad Red COX Abbott Red Ded Loan Joan Foster Red Puth Ruth Baldwin Everyday Jill Mahmoud Ahmad Ahmad Martha Talbot Red Red Moss More Barat Barrett Platinum Red De Kadann Rajan Platinum Justin Luetin Mille Miller Everyday Delia Lineberry Platinum Won Paul Kwon Aiden Ulrich Platinum Demi Roy Callihan Red sal Sally Yang Everyday Nancy Wallace Red Walter Wipple Everyday WP Karla Davis Everyday ban sans Robert Anderson Red Juan Tobar Red Josiah Taylor Red am Rodrigo Davila wania Everyday Raul Sanchez sarcin Everyday Jordan Monroe Red . Reba Caldwell Everyday Melody Talbot Platinum Rohan Seth Everyday James Fitzeerald Evervday Documentation Rand Roundup/Rank Cust_Serv_Rep_ID Credit_Limit Current_Balance 0.647648863 $1,000.00 $987.18 0.837092104 $1,000.00 $183.20 0.586627268 $2,000.00 $964.57 0.550058218 $1,750.00 $1,689.21 06029555 1.000 0.06029555 $67581 $675.81 $1,000.00 0.736085629 COOK $2,500.00 $1,121.60 0.280639388 $1.00 $1,000.00 $998.60 SOORE 201606076 0.201626076 $1,750.00 $1,190.27 $1 19027 0106937075 0.196237075 $250.00 $251.44 202150055 0.202150055 $2,500.00 $547.98 725786719 0.725786719 $2,000.00 $ $1,075.20 0.273287973 $1 305 57 0.932723347 037773347 250 $750.00 22700 $747.89 158630184 TEN 0.158630184 $750.00 $738.22 0.209262456 SEO $1,500.00 $1,488.35 0.324334586 250 $250.00 $83.25 0.714706331 $2,000.00 $1379 40 270405278 0.270495778 $1,750.00 $456 50 $466.52 0.338580242 $1,250.00 $1,169.46 0.593195277 $1,500.00 $650.95 0.40778464 5.20 $226.48 $1,000.00 0.859815401 $500.00 $231.56 0.445314696 $1,500.00 $1,105.61 0.291849468 $250.00 $231.87 0.345130093 $2,250.00 $2,231.55 0.916982788 $750.00 $686.99 were 0.706943835 re $750.00 $642.68 0.358013644 $2,000.00 - $1,662.76 X.ro 0.275897351 $1,600.00 $1,558.65 ** . 0.536402512 $2,250.00 $2,100.15 . 0.116341127 $500.00 $284.35 W 2010 0.173187601 $2,000.00 $1,647.99 Wacamat . 0.106516445 $1,500.00 $1,448.78 0.463298124 $2,250.00 $2,248.67 0.325094657 $2,250.00 $1,888.90 0.19889064 $550.00 $533.57 Sales Trans Customer Data Misclnfo Last_Payment_Date 9/17/2021 9/8/2021 9/30/2021 9/13/2021 9/29/2021 TE 9/25/2021 9/7/2021 9/4/2021 9/27/2021 9/19/2021 128190 9/28/2021 9/19/2021 110 120 12094 9/14/2021 Q/9/2021 9/9/2021 9/21/2021 0/08/2004 9/28/2021 9/18/2004 9/13/2021 9/27/2021 17120 9/20/2021 9/2012021 9/29/2021 Q/26/2001 9/26/2021 9/8/2021 9/20/2021 9/27/2021 74021 9/26/2021 9/15/2021 9/19/2021 --- 9/30/2021 9/4/2021 12- 9/28/2021 wa 9/10/2021 121 9/7/2021 1021 9/22/2021 9/30/2021 9/12/2021 9/30/2021 Platinum $1.500 25 MD24386 26 MD27726 27 MD33494 28 MD49119 29 MD56307 30 MD67941 31 ME21567 ALLA 32 ME26013 ME 33 ME30018 Mbwene 34 ME54105 e 35 ME64082 36 ME74922 *** 37 ME80578 38 ME86842 39 ME98417 40 ME99550 $620.51 $1 283 $80.54 Cena $849.05 R40 05 $773.52 $ $268.44 $394.39 $ $18.13 $18.45 $63.01 WWW. $107.32 . $337.24 WWW. $41.35 $149.85 . $215.65 $352.01 $51.22 $1.33 $361.10 $16.43 Sales Pivot Inventory Monthly Payment Calculator Employee Sched D E Credit Limit Messages Credit Remaining Message A B 1 Shipping and Handling Rates 2 Card Type Rate 3 No_card 4 Platinum 5 Red 6 Everyday 7 4.00% 0.00% 1.00% 2.00% 8 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 LaTonya Smith 16 CSR-725034 Andy Chen 17 CSR-879452 Juana Torres 18 CSR-941725 Mustafa Ali 19 Number of workdays 20 between payment 20 21 September Holiday 9/6/2021 22 October Holiday 10/11/2021 Documentation Inventory SalesTrans Customer Data Misclnfo Monthly Pa ABC D E F G H 1 J K K L M N C 1 1 2 Interest Paid Annual Payment 3 4 4 5 6 7 8 9 Amortization Schedule Year Beginning Balance Ending Balance Paid on Principal 1 2 3 4 5 6 7 4 Monthly* Payment Calculator Date 9-May-2022 Annual Rate 4.250% Item Cargo Van Term (in years) 5 Price $56,000.00 Payments per Year 12 Down Pymt. $10,000.00 Monthly Payment Loan Amount Total Interest Total Cost Monthly_Payment - Varying Interest Rate and Term Term (in years) 5 6 6 3.000% 3.250% 3.500% 3.750% 4 onno 42500 4.250% 500 4.500% 4.750% 5.000% 5.250% 5.500% Varying Interest Rate Schedule Rate Monthly Payment Total Interest Total Cost 8 9 10 10 11 12 13 14 15 16 17 18 19 20 21 22 23 $0.00 Subtotal Down Pymt. Total Cost $0.00 #NAME? ? #NAME? *Assume the monthly payment is made at the end of the period. Errors checks Paid on Principal Interest Paid Total Cost Annual Payment 24 25 26 27 28 29 30 31 32 33 34 35 36 37 29 3.000% 3.250% 3.500% Iron 3.750% 4.000% 4.250% FOOD 4.500% 4.750% 5.000% 5.250% 5.500% Documentation Inventory Sales Trans Customer Data Misclnfo Monthly Payment Calculator A B n D E F G H J 1 Hokie Fitness Equipment & Accessories, Inc. Mon Tue Wed Thu Fri Sat 0 1 1 1 1 1 1 1 0 0 1 1 1 0 0 1 1 1 1 1 1 0 0 1 nm in ooooom UwU 1 1 1 0 0 1 1 1 1 1 Schedule Days Off Staff Sun A Sun, Mon 2 0 Mon,Tue 2 1 Tue, Wed 2 1 D Wed, Thu 2 1 Thu, Fri 2 1 Fri, Sat 2 1 G Sat, Sun 2 0 Total staff working 14 10 Staff needed 2 Work week 40 hours Avg hourly pay per employee $16.50 Weekly payroll $9,240.00 0 0 1 10 1 1 1 1 0 11 10 10 10 10 10 10 191 12 1 1 1 2 2 13 14 15 16 17 Solver Model 18 19 20 Documentation Inventory Sales Trans Customer Data Miscinfo Monthly Payment Calculator Add header Add header Add header Hokie Fitness Equipment & Accessories, Inc. for Platinum or Red cardholders with a Credit_Limit_Message of Credit limit reached Customer ID ust_First_Namust Last Nam Card Ipe Rand Roundup/Ranlust Sery_Rep_Credit Limit urrent_Balancest Payment Da ayment Due Dacredit_Remainin. Credit Limit_Message or Red cardholders who purchased cardio machines in the store fransaction Date Product_ID Customer_id'arehouse_Bi Quantity_Sol Sales_Type Card_Type Category Cost_per_Unit Price_per_Uni Total_Sales hipping_Handling_Cdales_Taxcotal_Due Inventory SalesTrans Customer Data Misclnfo Monthly Payment Calculator Sales Pivot Database Employee Schedule II. Summary A B C D E F G G H 1 Earbuds Fitness Tracker Gym Bags Home Gym Water Bottles Weight Lifting $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 1 Hokie Fitness Accessories, Inc. 2 Summary for September 2021 3 4 Total Sales 5 Number of Transactions 6 7 Total Sales by Sales_Type and Category 8 Sales Type Cardio Machines 9 Online 10 In-store 11 Total $0.00 12 13 Total Sales by Sales_Type and Card_Type 14 Sales_Type Platinum 15 Online 16 In-store 17 Total $0.00 18 19 Number of Sales Transactions 20 Online 21 In-store 22 No_card 23 24 Credit Limit Message Red Everyday No_card $0.00 $0.00 $0.00 Credit limit reached Approaching Monitor credit credit limit limit Check current balance No issues Total 0 Cardholder Current 25 Balance Information 26 Number of cardholders 27 Average Current Balance 28 Total Current Balance 29 $0.00 30 Inventory SalesTrans Customer Data Misclnfo Monthly Payment Calculator