excel
1
WEEK # 5 - EXERCISES CHAPTER # 4
Exercise 4-1: Level 1 – Evaluating Job Applications for the BBB Company
You work in the Human Resources Department (or simply HR) for the accounting firm BBB. The firm has recently increased its client base and hired several university graduates for entry-level positions. The HR manager has established a formal process for evaluating job applicants. This process takes into account the applicant’s academic performance and work experience, as well as the impression made during the personal interview. In addition, all applicants are given a skills-based exam to determine their proficiency in spreadsheet and database applications. Because some applicants are not business majors but might be otherwise qualified for a position, the exam also covers some basic business concepts in accounting, finance, and marketing. You have been asked to evaluate the information on the candidates being considered. Each job application provides the following information: • College GPA (valid scores range from 1.5 to 4.0) • Major Code, universal standardized system that indicates the applicant’s undergraduate major; for example, Engineering=1, Business=2, Economics=3, Physical Science=4, and so on (valid codes for majors are 1 through 100)
• The total number of references submitted by the applicant • A Personal Interview Rating • If the applicant has previous work experience (TRUE or FALSE) • The Employment Exam score (valid scores are between 300 and 800) • The undergraduate school ranking (compared with all colleges across the country)
The HR manager has established criteria to determine if an applicant will be automatically disqualified or automatically hired, or if no decision is made. The criteria, which are applied in order, are described in the following list. An applicant is automatically disqualified if any of the following criteria are TRUE:
• The applicant has submitted an invalid GPA score, Employment Exam score, or Major Code. • The applicant has a GPA less than 3.0. • The applicant provided fewer than two references. • The applicant has an Employment Exam score below 600. • The applicant has a Personal Interview Rating of less than 3.
An applicant is automatically hired if all of the following criteria are TRUE: • The applicant has not been automatically disqualified. • The applicant has a GPA score over 3.8. • The applicant has a Major Code between 1 and 25 (inclusive). • The applicant graduated from one of the top 50 schools (ranking of 50 or less). • The applicant has an Employment Exam score above 720. • The applicant has a personal interview rating of 4 or higher. • The applicant has prior work experience.
If an applicant is neither automatically disqualified nor automatically hired, the applicant’s status is undecided.
2
WEEK # 5 - EXERCISES CHAPTER # 4
Complete the following: 1. Open the workbook named 4-Hiring-BBB.xlsx attached with the data of the assignment, and then save the file as 4-1-BBB-Hiring-Analysis-YourName.xlsx. 2. Write a formula in cell I4 that can be copied down the column to determine if (TRUE or FALSE) any of the following scores or codes listed for this applicant are invalid:
GPA, Major Code, Employment Exam. (Hint: Use the information provided in the problem description to determine the appropriate criteria.)
3. Write a formula in cell J4 that can be copied down the column to determine if (TRUE or FALSE) the applicant should be automatically disqualified based on the given criteria. 4. Write a formula in cell K4 that can be copied down the column to determine if this candidate is not automatically disqualified. (Hint: Use the results determined in Step 3.) 5. In cell L4, write a formula that can be copied down the column to determine if (TRUE or FALSE) the candidate should be automatically hired based on the given criteria. (Hint: For criteria between two values, test that the value is both >= the lower limit and <= the higher limit.) 6. Write a formula in cell M4 that can be copied down the column to determine if this candidate is not automatically hired. (Hint: Use the results determined in Step 5.) 7. Write a formula in cell N4 that can be copied down the column to determine if no decision is made on this applicant. Recall that no decision is made if the applicant is both not automatically disqualified (K) and not automatically hired (M). 8. Write a formula in cell I14 that can be copied across the row (through column N) to determine if all of the applicants have invalid scores. 9. Write a formula in cell I15 that can be copied across the row (through column N) to determine if any of the applicants have invalid scores. 10. To summarize the results, write a formula in cell I16 that displays the total number of applicants who have invalid scores. Copy this formula across the row (through column N). This formula should automatically update if any of the scores or criteria are later modified. 11. Apply conditional formatting to highlight the important points, as follows:
a) Highlight all of the TRUE values in the Automatically Disqualified column (J4:J12) using a dark green and bold text format.
b) Use gradient fill blue data bars to highlight the Personal Interview Rating scores of the applicants.
c) Highlight the name of any applicant with an Employment Exam Score of more than 720 using a light blue background.
3
WEEK # 5 - EXERCISES CHAPTER # 4
The following figure shows how should be the result:
12. Add your name and date at the end of the exercise 13. Save and close the 4-1-BBB-Hiring-Analysis-YourName.xlsx workbook.
4
WEEK # 5 - EXERCISES CHAPTER # 4
Exercise 4-2: Level 2 – Unpaid Invoice Penalty TheZone accounts payable group can sometimes be delinquent in paying The Zone’s vendors in a timely manner. In some cases, this is a deliberate effort to hold off payment as long as possible; in others, it is simply an oversight. The accounts payable group has developed a worksheet listing some of the vendors to which TheZone owes past due balances, organized by past due categories of 30-days past due, 60- days past due, and 90-days past due. Figure 4.35 shows this worksheet.
Recently, some vendors have started to apply different penalty and discount schemes to overdue accounts, similar to those being proposed by The Zone’s accounts receivable group. As a preemptive measure, you have been asked to help calculate some of these possible penalty scenarios that TheZone might incur based on its current outstanding balances. A list of these past due balances is provided in the workbook named 4-Unpaid.xlsx. This file also contains the data input values that you need to calculate the penalties in the top portion of the worksheet (similar to the spreadsheet in Figure 4.35). Keep in mind that you should use cell references in your formulas wherever possible. Complete the following: 1. Open the workbook named 4-Unpaid.xlsx attached with the data of the assignment, and then save
the file as 4-2-Unpaid-Invoice-Penalties-YourName.xlsx 2. Rule to calculate Fixed Penalty column Some vendors have agreed on an industry-standard penalty of $45 on all past due accounts regardless of the past due amount or number of days past due. These vendors are identified by the value TRUE in the corresponding row of column C. Write a formula in column H, which can be copied down the column, listing the penalty for the corresponding account: $45 for vendors that are participating in this standard penalty and $0 for all other vendors. Only vendors that are owed past due balances are listed on this sheet.
5
WEEK # 5 - EXERCISES CHAPTER # 4
3. Rule to calculate 90-Days Penalty column Calculate another possible penalty whereby only those accounts with 90-days past due balances are owed a fee. In column I, write a formula that can be copied down the column to calculate the penalty based on the following criteria:
• For accounts with a 90-days past due balance (Column F) of $50 (in B3) or more, apply a fee of 12% (in B4) of the 90-days past due balance. • For all other accounts, no penalty is applied.
4. Rule to calculate Graduated Penalty column Another penalty scheme being used by vendors is a graduated method based on the total past due balances (column G). In column J, write a formula that can be copied down the column to calculate the penalty based on the following criteria:
• For accounts with a past due balance (column G) of $10,000 (in G4) or more, apply a penalty of $600 (in H4). • For accounts with a past due balance (column G) of less than $10,000 (in G4) but more than $3,000 (in G3), apply a penalty of $250 (in H3). • For accounts with a total past due balance of less than or equal to $3,000, do not apply a penalty.
5. Rule to calculate Category Penalty column Penalties can sometimes be specific to vendor category. In column K, write a formula that can be copied down the column to calculate the penalty based on the following criteria: • For vendors in the Labor category, apply a fee of 6% of the total past due balance (column G). • For vendors in the Utilities category, apply a fee of 9% of the total past due balance (column G). • For vendors in all other categories, apply a fee of 12% of the total past due balance.
6
WEEK # 5 - EXERCISES CHAPTER # 4
6. Format columns H through K to match column G.
The following figure shows how should be the result:
7. Add your name and date at the end of the exercise 8. Save and close the 4-2-Unpaid-Invoice-Penalties-YourName.xlsx workbook