IST 310 Excel 5 Instructions
pdf
keyboard_arrow_up
School
University of Phoenix *
*We aren’t endorsed by this school
Course
4300
Subject
Industrial Engineering
Date
May 10, 2024
Type
Pages
4
Uploaded by KidCrocodileMaster1108
Excel 5 IST 310: Introduction to Information and Management Productivity Systems Page 1 IST 310 Excel Assignment 5 20 Points 1. Download and save the Excel 5 Template.xlsx file as LastNameFirstNameExcel5.xlsx (e.g. BestMarkExcel5.xlsx). 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 Figure Sense: What Excel functions are needed to accomplish the required task? How should you use those functions to complete the task for the first cell? 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 Figure Sense: What Excel functions are needed to accomplish the required task? How should you use those functions to complete the task for the first cell? Rules Worksheet
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 bidder’s D&B PAYDEX score is greater than 65.
•
The bidder’s D&B Composite Credit Appraisal value is less than or equal to 2.
•
The bidder’s Net Worth is greater than $500,000.
Rule #3: Consider a bidder if any of the following criteria are met: •
The bidder’s D&B Stress Risk Class is equal to 1.
•
The bidder’s
Previous Experience Grade is Satisfactory. 4. In column G, calculate whether or not (TRUE/FALSE) the various bidders meet the criteria for Rule #1. Do not use an IF statement. Figure Sense: What Excel expression is needed to accomplish the required task?
Excel 5 IST 310: Introduction to Information and Management Productivity Systems Page 2 Check that your answers in column G are correct by visually checking to see if the D&B PAYDEX score is at least 90 for each row. 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.
Figure Sense: What Excel function is needed to accomplish the required task? How should you use that function to complete the task for the first cell? One way to check that your answers are correct is to copy the columns of the Rules worksheet into the Figure Sense worksheet. In column K, enter your expression for D&B PAYDEX score is greater than 65. In column L, enter your expression for D&B Composite Credit Appraisal value is less than or equal to 2. In column M, enter your expression for Net Worth is greater than $500,000. You can then check your answer: to obtain TRUE for Rule #2 for a certain bidder, the values in columns K through M for that bidder should all be TRUE. 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.
Figure Sense: What Excel function is needed to accomplish the required task? How should you use that function to complete the task for the first cell? One way to check that your answers are correct is to use the copy of the columns of the Rules worksheet in the Figure Sense worksheet. In column O, enter your expression for D&B Stress Risk Class is equal to 1. In column P, enter your expression for Previous Experience Grade is Satisfactory. You can then check your answer: to obtain TRUE for Rule #3 for a certain bidder, at least one of the values in columns O and P for that bidder should be TRUE. 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.
Figure Sense: What Excel function is needed to accomplish the required task? How should you use that function to complete the task for the first cell? 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
Excel 5 IST 310: Introduction to Information and Management Productivity Systems Page 3 Figure Sense: How should you use the IF function to complete the task for the first cell? Note that the syntax of the IF function is: =IF(logical_test,[value_if_true],[value_if_false]). It is also possible to have an IF function nested inside another IF function such as: =IF(first_test,value1,IF(second_test,value2,value3)). This nested IF statement can be pictured in a tree diagram like the one below: value1
first_test
second_test
value2
value3
This I
F statement will result in “value1” if the first_test statement is TRUE. Next, this IF statement will result in “value2” if the first_test statement is FALSE and the second_test statement is TRUE. Finally, this IF statement will result in “value3” if the first_test statement is FALSE and the second_test statement is FALSE. You can check your answers by looking at the Past Due Balance for each vendor and verifying that the penalty for each row is correct. 9. 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 Figure Sense: What Excel functions are needed to accomplish the required task? How should you use those functions to complete the task for the first cell? It may be helpful to draw a tree diagram. 10. In cell A10, type your first and last name in all lowercase letters. Change the font size of cell A10 to 8.
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
- Access to all documents
- Unlimited textbook solutions
- 24/7 expert homework help
Excel 5 IST 310: Introduction to Information and Management Productivity Systems Page 4 Usernames Worksheet
11. Kids R Us Child Care Centers is creating an online parent portal that parents will access with a username and password. Use a formula to create a username for each parent based on the following rules: •
Lowercase first letter of first name. •
Digit five of ID. •
Uppercase first four letters of last name. •
Last digit of phone number. •
Number of characters in first name. Figure Sense: What Excel functions are needed to accomplish the required task? How should you use those functions to complete the task for the first cell? 12. Make the Work Days tab the active worksheet, save and close your file, and submit your Excel file on Canvas.