PLEASE ANSWER IN SCREENSHOTS Four J\'s Painting is a home painting contractor th

ID: 3916464 • Letter: P

Question

PLEASE ANSWER IN SCREENSHOTS

Four J's Painting is a home painting contractor that completes interior painting work for new construction projects. Four J's Painting has four employees: Jim, Jill, Jake, and Jennifer. You have been hired to determine how the company can be more efficient and provide better service. You have tracked information on the last 20 painting projects in table 1. Employees receive bonuses for efficient and high-quality work. Occasionally, some jobs require additional work to correct flaws (touch-up work). Information about the projects that required touch-up work is stored in table 2. Table 3 summarizes information about the employees. Table 4 categorizes the projects according to difficulty. Complete tables 1 through 4 to determine how to more efficiently and effectively run the company. Complete each task so that the formulas you use can be copied and pasted down to complete the columns of the tables. Do not name your lookup tables; rather, you should keep them as references (e.g. B16:C30).

Task #

Points

Task Description

Use the HLOOKUP function to complete the "Hourly Wage" column of table 1. Use the "Employee" column of table 1 as the lookup_value and the "Employee Wage Information" above table 1 as your reference table.

Use the AND function to complete the "Time Bonus?" column of table 1. An employee earns a time bonus if the project’s "Hours Worked" are fewer than the "Estimated Hours" and if the work "Quality" is greater than 1.

Use the OR function to complete the "Outcome Bonus?" column of table 1. An employee earns an outcome bonus if the difficulty of a job is greater than 3 or if the quality of their work is equal to 3.

Use the IF function to complete the "Time Bonus $" column of table 1. If an employee earns a time bonus (i.e., the corresponding cell in the "Time Bonus?" column is TRUE), then "Time Bonus $" is the "Job Pay" for that project times the bonus percentage in cell M11. Otherwise "Time Bonus $" is 0.

Use the IF function to complete the "Outcome Bonus $" column of table 1. If an employee earns an outcome bonus (i.e., the corresponding cell in the "Outcome Bonus?" column is TRUE), then "Outcome Bonus $" is the "Job Pay" for that project times the outcome bonus percentage in cell M12; otherwise, "Outcome Bonus $" is 0.

Use the IF function to complete the "Comments" column of table 1. Display "Good Job" if both the "Hours Worked" are less than or equal to the "Estimated Hours" for a project and the assessed "Quality" of that project is greater than 1. Display "Too Much Time" if the "Hours Worked" on a project exceed the "Estimated Hours" for that project; otherwise, display "Poor Quality."

Use the VLOOKUP function to complete the "Employee" column of table 2. Use "Job ID" from table 2 as your lookup_value(s) and table 1 as the reference table.

Use the VLOOKUP function to complete the "Difficulty" column of table 2. Again, use "Job ID" from table 2 as the lookup_value(s) and table 1 as the reference table.

Use the COUNTIF function to complete the "# of Jobs" column in table 3. Reference the appropriate field in table 1 as your range and the "Employee" names in table 3 as your criteria.

Use the SUMIF function to complete the "Total Hours" column in table 3. Reference the appropriate field in table 1 as your range and the "Employee" names in table 3 as your criteria.

Use the SUMIF function to complete the "Total Pay" column in table 3. Reference the "Employee" field in table 1 as your range, the "Employee" names in table 3 as your criteria, and the "Total Pay" field in table 1 as your sum_range.

Use the COUNTIF function to complete the "# of Touch-ups" column in table 4. Reference the appropriate field in table 2 as your range and the "Difficulty" rating in table 4 as your criteria.

Use the SUMIF function to complete the "Cost Touch-ups" column in table 4. Reference the appropriate field in table 2 as your range and the "Difficulty" rating in table 4 as your criteria.

Use the AVERAGEIF function to complete the "Average Hours/Job" column in table 4. Reference the appropriate field in table 1 as your range and the "Difficulty" rating in table 4 as your criteria.

Total:

Explanation / Answer

Below is the answer:

1. Use the HLOOKUP function to complete the "Hourly Wage" column of table 1. Use the "Employee" column of table 1 as the lookup_value and the "Employee Wage Information" above table 1 as your reference table.
Answer: =HLOOKUP(D16, $E$11:$H$12, 2, 0)

2. Use the AND function to complete the "Time Bonus?" column of table 1. An employee earns a time bonus if the project’s "Hours Worked" are fewer than the "Estimated Hours" and if the work "Quality" is greater than 1.
Answer: AND(E16<C16)

3. Use the OR function to complete the "Outcome Bonus?" column of table 1. An employee earns an outcome bonus if the difficulty of a job is greater than 3 or if the quality of their work is equal to 3.
Answer: OR(G16>=3)

4. Use the IF function to complete the "Time Bonus $" column of table 1. If an employee earns a time bonus (i.e., the corresponding cell in the "Time Bonus?" column is TRUE), then "Time Bonus $" is the "Job Pay" for that project times the bonus percentage in cell M11. Otherwise "Time Bonus $" is 0.
Answer: =IF(I16=True, (K16*$M$11,0)

5. Use the IF function to complete the "Outcome Bonus $" column of table 1. If an employee earns an outcome bonus (i.e., the corresponding cell in the "Outcome Bonus?" column is TRUE), then "Outcome Bonus $" is the "Job Pay" for that project times the outcome bonus percentage in cell M12; otherwise, "Outcome Bonus $" is 0.
Answer: =IF(I16=True, (K16*$M$11,0)

6. Use the IF function to complete the "Comments" column of table 1. Display "Good Job" if both the "Hours Worked" are less than or equal to the "Estimated Hours" for a project and the assessed "Quality" of that project is greater than 1. Display "Too Much Time" if the "Hours Worked" on a project exceed the "Estimated Hours" for that project; otherwise, display "Poor Quality."
Answer: =IF(E17>C17, "PoorQuality","Too MuchTime"

7. Use the VLOOKUP function to complete the "Employee" column of table 2. Use "Job ID" from table 2 as your lookup_value(s) and table 1 as the reference table.
Answer: =vlookup(B40,$B$16:$1$35,3,0)

8. Use the VLOOKUP function to complete the "Difficulty" column of table 2. Again, use "Job ID" from table 2 as the lookup_value(s) and table 1 as the reference table.
Answer: =Vlookup(B40,$B$16:$H$35,6,0)

9. Use the Countif function to complete the #of Jobs, column in table 3. Reference the "employee" names in table three as your criteria.
Answer: =Countif(B16:D35,G39)

10. Use the SUMIF function to complete the "Total Hours" column in table 3. Reference the appropriate field in table 1 as your range and the "Employee" names in table 3 as your criteria.
Answer: =sumif($D$16:D35, G39, $E$16:$E$35)

11. Use the SUMIF function to complete the "Total Pay" column in table 3. Reference the "Employee" field in table 1 as your range, the "Employee" names in table 3 as your criteria, and the "Total Pay" field in table 1 as your sum_range.
Answer: =SUMIF($D$16:$D$35,G39,$K$16:$K$K35)

12. Use the COUNTIF function to complete the "# of Touch-ups" column in table 4. Reference the appropriate field in table 2 as your range and the "Difficulty" rating in table 4 as your criteria.
Answer:

Navigate

PLEASE ANSWER IN PYTHON Write a function named midline that draws a line based o

PLEASE ANSWER IN STEP BY STEP AND FOR THE FORMULAS PLEASE POST THEM AS IMAGES 1.

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.

PLEASE ANSWER IN SCREENSHOTS Four J\'s Painting is a home painting contractor th

Question

Explanation / Answer

Related Questions

Navigate