WHAT ARE THE FORMULAS USED FOR EACH

Work Days Worksheet

2. In cells C2:C12, use a function to calculate the number of work days between the start dates in cells A2:A12 and the end dates in cells B2:B12 not considering the holiday dates in cells G2:G12. Your formula should leave the number of work days blank in column C if the end date is blank in column B. Hint: Use an IF statement. Check Figure: Cell C4 = 169

3. In cells D2:D12, use a function to calculate the number of work days between the start dates in cells A2:A12 and the end dates in cells B2:B12 considering the holiday dates in cells G2:G12. Your formula should leave the number of work days blank in column D if the end date is blank in column B. Check Figure: Cell D12 = 90

Rules Worksheet

4.

Rule #1: Consider a bidder if its D&B PAYDEX score is at least 90.

Rule #2: Consider a bidder if all of the following criteria are met:

• The bidders D&B PAYDEX score is greater than 65.

• The bidders D&B Composite Credit Appraisal value is less than or

  equal to 2.

• The bidders Net Worth is greater than $500,000.

  Rule #3: Consider a bidder if any of the following criteria are met:

• The bidders D&B Stress Risk Class is equal to 1.

• The bidders Previous Experience Grade is Satisfactory.

  In column G, calculate whether or not (TRUE/FALSE) the various bidders meet the criteria for Rule #1. Do not use an IF statement.

Excel 6

IST 310: Introduction to Information and Management Productivity Systems Page 1

5. In column H, calculate whether or not (TRUE/FALSE) the various bidders meet the criteria for Rule #2. Do not use an IF statement.

6. In column I, calculate whether or not (TRUE/FALSE) the various bidders meet the criteria for Rule #3. Do not use an IF statement.

7. In cells G8:I8, use a function to determine whether or not (TRUE/FALSE) any of the bidders meet each of the various rules. Do not use an IF statement.

IF Statements Worksheet

8.

In column G, use an IF statement to calculate the penalty for each vendor based on the following criteria:

• For accounts with a Total Past Due Balance of $10,000 or more, apply a penalty of $600.

• For accounts with a Total Past Due Balance of less than $10,000 but more than $3,000, apply a penalty of $250.

• For accounts with a Total Past Due Balance of less than or equal to $3,000, do not apply a penalty (i.e. $0).

  Check Figure: Cell G4 = $250

  In column H, use an IF statement to calculate the penalty for each vendor based on the following criteria:

• For vendors in the Labor Category, apply a penalty of $100 or 6% of the Total Past Due Balance, whichever is higher.

• For vendors in the Utilities Category, apply a penalty of $150.

• For vendors in any other Category, apply a penalty of 2.5% of the

  Total Past Due Balance. Check Figure: Cell H6 = $192

  In cell A10, type your first and last name in all lowercase letters. Change the font size of cell A10 to 8.

AutoSave Om suus Home Insert RodriguezAnaExcel Draw Page Layout Formulas Data Review View Tell me X [ Calibri (Body 14 A A Wrap Text Paste General B A Merge & Center $ % vfe Conditional Format Formatting Table 0 E Start Date G End Date H of Work Days (Holidays Not considered) of Work Days (Holldays Considered) Holiday 2 Date 3 7/13/18 6/22/18 9/14/18 8/16/18 4/20/18 5 1/4/18 1/15/18 1/23/18 2/12/18 2/22/18 3/19/18 5/7/18 5/16/18 7/2/18 8/13/18 8/21/18 . New Year's Day Martin Luther King Ir. Day Presidents' Day Easter Memorial Day Independence Day Labor Day Columbus Day Veterans Day Thanksgiving Day Christmas Day 7/20/18 11/30/18 8/24/18 1/1/18 1/15/18 2/19/18 4/1/18 5/28/18 7/4/18 9/3/18 10/8/18 11/11/18 11/22/18 12/25/18 9 10 11 12/28/18 12 13 14 15 16 17 18 19 Tell me what you want to do 20 AutoSave Besu. Home Insert Draw Page Layout RodriguezAaExcel Formulas Data Review View X Tell me Calibri (Body 14 - A 25 Wrap Text B IU General QA Merge & Center A1 HH x fx Bidder $% Conditional Format Ced Formatting as Table Styles Insert De 3 C H 1 Bidder N D&B Composite Credit Appraisal (1 Best) D&B PAYDEX (100 Best) Rule 1 Rule #2 Rule 3 2 Ribba Technology 3 TOR Corporation 4 Computer 5 Exceed WR 6 Roberts Inc. Previous Experience Grade Satisfactory Satisfactory Satisfactory None Unsatisfactory Net Worth (Dollars) $ 5,000,000 $ 286,000 $ 4,560,000 $ 1,089,000 $ 23,459,000 D&B Stress Risk Class (1 Best) 1 1 3 1 2 1 3 2 89 79 87 45 68 1 2 Do any of the bidders meet the rule? 9 10 12 13 AutoSave OF etu: Home Insert Draw Page Layout RodriguezAna Excel Formulas Data Review View Tell me w Calibri (Bodyl 14 - A A Paste 29 Wrap Text B IU General A A1 Merge & Center Xfx Vendor Name $ % ) Conditional Format Formatting as Table Styles Inse Vendor Name Category H 1 1 Penalty 1 Penalty 12 2 RTF Electric 3 Ross County Water & Sewer 4 YNC Trucking 5 Italian Leather Group Ltd. 6 Union Plastics 7 Freight to Go 8 Temps R'Us Notworth Telephone Utilities Utilities Transportation Raw Materials Raw Materials Transportation Labor Telephone D 30-Days 60-Days 90-Days Total Past Past Due Past Due Past Due Due Balance $ 12.100$ $ $ 12.100 $ 500S $ $ 500 $ $ $ 3,100 S 3,100 $ $ 850 $ 5,674 6,524 $ 7.250 $ 436 S $ 7,686 $ $ S 8,730 $ 8,730 $ 2,700 S $ $ 2,700 $ 250 650 $ 400 S 1,300 10 11 12 14 15 16 Home Insert Draw Page Layout RodriguezAnaEx Formulas Data Review View Tell me X IG Calibri (Body) 14 ' v Paste 2 Wrap Text B I U General v y A Merge & Center A1 Xfx Parent ID $ % E Parent Username G H Cally B 1 Parent ID Parent Last Name Parent First Name Parent Phone 2 100000 Ewing Levi (550) 726-7424 3 100001 Gillespie Vera (263) 579-3567 4 100002 Mccall Astra (897) 263-3526 5 100003 Woodward Constance (845) 953-5717 6 100004 Duke Keaton (289) 465-5356 7 100005 Stephenson Alana (693) 335-3919 8 100006 Guzman (836) 183-4738 9 100007 Zimmerman Flavia (590) 994-9083 10 100008 Cooper Jameson (373) 497-3310 11 100009 Fleming Jemima (367) 563-1301 12 100010 Waller Yolanda (547) 638-5601 13 100011 Lloyd Nigel (664) 205-4196 14 100012 Mack Gil (547) 452-5690 15 100013 Blankenship Michelle (162) 427-2133 16 100014 Slater Akeem (940) 387-3950 17 100015 Huber Aaron (846) 956-4117 18 100016 Levine Quytin (596) 852-1734 19 100017 Lindsey Patrick (675) 252-8525 20 100018 Mccullough May (294) 149-3783 21 100019 Sampson Brent (676) 693-7135 22 100020 Welch Sierra (932) 627-7402 23 100021 Parrish lan (232) 151-6837 24 100022 Mccormick Keefe (111) 213-9128 25 100023 Monroe Melinda (420) 774-4450 26 100024 Gomez Lisandra (354) 379-7398 27 100025 Turner Kylan (697) 846-7407 28 100026 Lloyd Maris (656) 976-4033 29 100027 Norris Chancellor (435) 134-7933 30 100028 Davidson Haviva (977) 136-1493 31 100029 Shields Henry (149) 178-0256 32 100030 Quinn Knox (424) 596-0322 33 100031 Strickland Tatiana (271) 432-7679 34 100032 Carey Shaine (261) 848-1743 100023 Camnen Vaccio ICE 61.776 Work Days Rules IF Statements Usernames + Rendly AutoSave OFF Home Insert Draw Page Layout | RodriguezAnaExcel#6 Formulas Data Review View X Tell me Calibri (Body! 14 y Paste BIU ' ' A General 29 Wrap Text Merge & Center 168 x fx $ % ) B G H Lucas 35 100033 Sampson 36 100034 Burch 37 100035 Hebert 38 100036 Mccormick 39 100037 Sherman 40 100038 Vang 41 100039 Guerra 42 100040 Hampton 43 100041 Burt 44 100042 Hicks 45 100043 Gilbert 46 100044 47 100045 Greene 48 100046 Mcdowell 49 100047 Jacobs 50 100048 Fuentes 51 100049 Stark 52 100050 Riddle 53 100051 Rose 54 100052 Myers 55 100053 Carlson 56 100054 Mejia 57 100055 Leach 58 100056 Dawson 59 100057 Duncan 60 100058 Donaldson 61 100059 Dillon 62 100060 Knight 63 100061 Chan 64 100062 Cameron 65 100063 Welch 66 100064 Davidson 67 100065 Clemons 68 100066 Hays Kessie (285) 861-7766 Moana (857) 585-5823 Georgia (651) 920-2824 Quentin (540) 681-8708 Alfreda (195) 510-6921 Quintessa (531) 592-6407 Gisela (326) 619-0468 Eden (274) 769-1937 Daryl (992) 729-1292 Kaitlin (415) 759-1946 Cade (806) 813-7664 Camden (408) 719-9887 Stephanie (503) 395-9008 Avram (797) 778-5712 Brent (110) 801-3833 Jana (285) 202-5829 Ariana (387) 674-2225 Karleigh (527) 546-8125 Caleb (808) 592-8467 Katelyn (810) 615-7101 Ivy (133) 266-3971 Alden (623) 887-1192 Cole (410) 590-7806 Cheryl (671) 930-6181 Wyatt (197) 564-4050 Sonya (983) 890-1863 Christen (969) 311-8272 Beck (892) 960-5089 Amity (265) 970-4108 Halla (127) 646-0829 Preston (657) 112-3710 Benjamin (215) 610-1579 Leslie (332) SOS-0765 Ezra (646) 926-8308 Chelhor Iron enz.cC16 Rules IF Statements Usernames + Work Days Ready AutoSave novo Home Insert Draw Rodriguez AndExcel Page Layout Formulas Data Review View X Tell me Calibri (Booyi A A 3 Wrap Text BIU General A Merge & Center 170 $% Call Formning as The Styles Insert 1 M Landry Valde: Whitehead Duke Ortiz Webb Collins Lancaster Mcgowan 70 100068 71 100069 72 100070 73 100071 74 100072 75 100073 76 100074 77100075 78 100076 79 100077 30 100078 81 100079 32 100080 83 100081 84 100082 *5 100083 86 100084 87 100085 3 100086 89 100087 99 100 91 1000R9 92 100090 93 100091 94 100092 35 100093 9100094 97 100095 100096 100097 100 100098 101 100099 102 103 Cannon Chaves Castaneda Gray Moon Murray Carson Mclean Rice Holman Deleon Hicks Bates Mckee Blackwell Tanner Micke Ford C Ignatius Melinda Violet Jacqueline Selma Uin Iris Colton Marshall Claudia Tashya Gretchen Kevin Remedios Yen Brody Marah Oscar Nell Lila Malachi Justin Quincy Eleanor Mariam Marshall Callie Nomlanga Amber AR Quentin Evangeline 0 (761) 977-6789 (791) 899-0824 (609) 652-5065 (579) 465-5638 (167) 572-0274 (339) 104-0873 (796) 347-4352 (232) 486-3397 (856) 831.6966 1762) 191-0097 (855) 886-1224 (568) 509-4380 (865) 497-3836 0981) 370-7176 (230) 667.7112 (782) 405-7897 (461) 411-2256 (SOS) 716-0299 (796) 564-4658 (654) 804-9610 (887) 397-7647 (685) 568-8723 1837) 861-2525 18381 951-3208 1779) 812-6939 (340) 993-9303 1200) 858-7993 1945) 925-9663 (508) 158-6778 (761) 746-1116 (478) 345-5517 1739) 364-1513 Aguirre Maddox Medina Work Days Rules IF Statements Usernames Ready AutoSave Om suus Home Insert RodriguezAnaExcel Draw Page Layout Formulas Data Review View Tell me X [ Calibri (Body 14 A A Wrap Text Paste General B A Merge & Center $ % vfe Conditional Format Formatting Table 0 E Start Date G End Date H of Work Days (Holidays Not considered) of Work Days (Holldays Considered) Holiday 2 Date 3 7/13/18 6/22/18 9/14/18 8/16/18 4/20/18 5 1/4/18 1/15/18 1/23/18 2/12/18 2/22/18 3/19/18 5/7/18 5/16/18 7/2/18 8/13/18 8/21/18 . New Year's Day Martin Luther King Ir. Day Presidents' Day Easter Memorial Day Independence Day Labor Day Columbus Day Veterans Day Thanksgiving Day Christmas Day 7/20/18 11/30/18 8/24/18 1/1/18 1/15/18 2/19/18 4/1/18 5/28/18 7/4/18 9/3/18 10/8/18 11/11/18 11/22/18 12/25/18 9 10 11 12/28/18 12 13 14 15 16 17 18 19 Tell me what you want to do 20 AutoSave Besu. Home Insert Draw Page Layout RodriguezAaExcel Formulas Data Review View X Tell me Calibri (Body 14 - A 25 Wrap Text B IU General QA Merge & Center A1 HH x fx Bidder $% Conditional Format Ced Formatting as Table Styles Insert De 3 C H 1 Bidder N D&B Composite Credit Appraisal (1 Best) D&B PAYDEX (100 Best) Rule 1 Rule #2 Rule 3 2 Ribba Technology 3 TOR Corporation 4 Computer 5 Exceed WR 6 Roberts Inc. Previous Experience Grade Satisfactory Satisfactory Satisfactory None Unsatisfactory Net Worth (Dollars) $ 5,000,000 $ 286,000 $ 4,560,000 $ 1,089,000 $ 23,459,000 D&B Stress Risk Class (1 Best) 1 1 3 1 2 1 3 2 89 79 87 45 68 1 2 Do any of the bidders meet the rule? 9 10 12 13 AutoSave OF etu: Home Insert Draw Page Layout RodriguezAna Excel Formulas Data Review View Tell me w Calibri (Bodyl 14 - A A Paste 29 Wrap Text B IU General A A1 Merge & Center Xfx Vendor Name $ % ) Conditional Format Formatting as Table Styles Inse Vendor Name Category H 1 1 Penalty 1 Penalty 12 2 RTF Electric 3 Ross County Water & Sewer 4 YNC Trucking 5 Italian Leather Group Ltd. 6 Union Plastics 7 Freight to Go 8 Temps R'Us Notworth Telephone Utilities Utilities Transportation Raw Materials Raw Materials Transportation Labor Telephone D 30-Days 60-Days 90-Days Total Past Past Due Past Due Past Due Due Balance $ 12.100$ $ $ 12.100 $ 500S $ $ 500 $ $ $ 3,100 S 3,100 $ $ 850 $ 5,674 6,524 $ 7.250 $ 436 S $ 7,686 $ $ S 8,730 $ 8,730 $ 2,700 S $ $ 2,700 $ 250 650 $ 400 S 1,300 10 11 12 14 15 16 Home Insert Draw Page Layout RodriguezAnaEx Formulas Data Review View Tell me X IG Calibri (Body) 14 ' v Paste 2 Wrap Text B I U General v y A Merge & Center A1 Xfx Parent ID $ % E Parent Username G H Cally B 1 Parent ID Parent Last Name Parent First Name Parent Phone 2 100000 Ewing Levi (550) 726-7424 3 100001 Gillespie Vera (263) 579-3567 4 100002 Mccall Astra (897) 263-3526 5 100003 Woodward Constance (845) 953-5717 6 100004 Duke Keaton (289) 465-5356 7 100005 Stephenson Alana (693) 335-3919 8 100006 Guzman (836) 183-4738 9 100007 Zimmerman Flavia (590) 994-9083 10 100008 Cooper Jameson (373) 497-3310 11 100009 Fleming Jemima (367) 563-1301 12 100010 Waller Yolanda (547) 638-5601 13 100011 Lloyd Nigel (664) 205-4196 14 100012 Mack Gil (547) 452-5690 15 100013 Blankenship Michelle (162) 427-2133 16 100014 Slater Akeem (940) 387-3950 17 100015 Huber Aaron (846) 956-4117 18 100016 Levine Quytin (596) 852-1734 19 100017 Lindsey Patrick (675) 252-8525 20 100018 Mccullough May (294) 149-3783 21 100019 Sampson Brent (676) 693-7135 22 100020 Welch Sierra (932) 627-7402 23 100021 Parrish lan (232) 151-6837 24 100022 Mccormick Keefe (111) 213-9128 25 100023 Monroe Melinda (420) 774-4450 26 100024 Gomez Lisandra (354) 379-7398 27 100025 Turner Kylan (697) 846-7407 28 100026 Lloyd Maris (656) 976-4033 29 100027 Norris Chancellor (435) 134-7933 30 100028 Davidson Haviva (977) 136-1493 31 100029 Shields Henry (149) 178-0256 32 100030 Quinn Knox (424) 596-0322 33 100031 Strickland Tatiana (271) 432-7679 34 100032 Carey Shaine (261) 848-1743 100023 Camnen Vaccio ICE 61.776 Work Days Rules IF Statements Usernames + Rendly AutoSave OFF Home Insert Draw Page Layout | RodriguezAnaExcel#6 Formulas Data Review View X Tell me Calibri (Body! 14 y Paste BIU ' ' A General 29 Wrap Text Merge & Center 168 x fx $ % ) B G H Lucas 35 100033 Sampson 36 100034 Burch 37 100035 Hebert 38 100036 Mccormick 39 100037 Sherman 40 100038 Vang 41 100039 Guerra 42 100040 Hampton 43 100041 Burt 44 100042 Hicks 45 100043 Gilbert 46 100044 47 100045 Greene 48 100046 Mcdowell 49 100047 Jacobs 50 100048 Fuentes 51 100049 Stark 52 100050 Riddle 53 100051 Rose 54 100052 Myers 55 100053 Carlson 56 100054 Mejia 57 100055 Leach 58 100056 Dawson 59 100057 Duncan 60 100058 Donaldson 61 100059 Dillon 62 100060 Knight 63 100061 Chan 64 100062 Cameron 65 100063 Welch 66 100064 Davidson 67 100065 Clemons 68 100066 Hays Kessie (285) 861-7766 Moana (857) 585-5823 Georgia (651) 920-2824 Quentin (540) 681-8708 Alfreda (195) 510-6921 Quintessa (531) 592-6407 Gisela (326) 619-0468 Eden (274) 769-1937 Daryl (992) 729-1292 Kaitlin (415) 759-1946 Cade (806) 813-7664 Camden (408) 719-9887 Stephanie (503) 395-9008 Avram (797) 778-5712 Brent (110) 801-3833 Jana (285) 202-5829 Ariana (387) 674-2225 Karleigh (527) 546-8125 Caleb (808) 592-8467 Katelyn (810) 615-7101 Ivy (133) 266-3971 Alden (623) 887-1192 Cole (410) 590-7806 Cheryl (671) 930-6181 Wyatt (197) 564-4050 Sonya (983) 890-1863 Christen (969) 311-8272 Beck (892) 960-5089 Amity (265) 970-4108 Halla (127) 646-0829 Preston (657) 112-3710 Benjamin (215) 610-1579 Leslie (332) SOS-0765 Ezra (646) 926-8308 Chelhor Iron enz.cC16 Rules IF Statements Usernames + Work Days Ready AutoSave novo Home Insert Draw Rodriguez AndExcel Page Layout Formulas Data Review View X Tell me Calibri (Booyi A A 3 Wrap Text BIU General A Merge & Center 170 $% Call Formning as The Styles Insert 1 M Landry Valde: Whitehead Duke Ortiz Webb Collins Lancaster Mcgowan 70 100068 71 100069 72 100070 73 100071 74 100072 75 100073 76 100074 77100075 78 100076 79 100077 30 100078 81 100079 32 100080 83 100081 84 100082 *5 100083 86 100084 87 100085 3 100086 89 100087 99 100 91 1000R9 92 100090 93 100091 94 100092 35 100093 9100094 97 100095 100096 100097 100 100098 101 100099 102 103 Cannon Chaves Castaneda Gray Moon Murray Carson Mclean Rice Holman Deleon Hicks Bates Mckee Blackwell Tanner Micke Ford C Ignatius Melinda Violet Jacqueline Selma Uin Iris Colton Marshall Claudia Tashya Gretchen Kevin Remedios Yen Brody Marah Oscar Nell Lila Malachi Justin Quincy Eleanor Mariam Marshall Callie Nomlanga Amber AR Quentin Evangeline 0 (761) 977-6789 (791) 899-0824 (609) 652-5065 (579) 465-5638 (167) 572-0274 (339) 104-0873 (796) 347-4352 (232) 486-3397 (856) 831.6966 1762) 191-0097 (855) 886-1224 (568) 509-4380 (865) 497-3836 0981) 370-7176 (230) 667.7112 (782) 405-7897 (461) 411-2256 (SOS) 716-0299 (796) 564-4658 (654) 804-9610 (887) 397-7647 (685) 568-8723 1837) 861-2525 18381 951-3208 1779) 812-6939 (340) 993-9303 1200) 858-7993 1945) 925-9663 (508) 158-6778 (761) 746-1116 (478) 345-5517 1739) 364-1513 Aguirre Maddox Medina Work Days Rules IF Statements Usernames Ready