IST 310 Excel 5 Instructions

.pdf

School

University of Phoenix *

*We aren’t endorsed by this school

Course

4300

Subject

Industrial Engineering

Date

May 10, 2024

Type

pdf

Pages

4

Uploaded by KidCrocodileMaster1108 on coursehero.com

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
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