{"id":13787,"date":"2021-11-05T07:53:32","date_gmt":"2021-11-05T07:53:32","guid":{"rendered":"https:\/\/assignmenttutors.net\/blog\/solved-exp19_excel_ch07_hoeassessment_employees-computer-science-homework-help\/"},"modified":"2021-11-05T07:53:32","modified_gmt":"2021-11-05T07:53:32","slug":"solved-exp19_excel_ch07_hoeassessment_employees-computer-science-homework-help","status":"publish","type":"post","link":"https:\/\/assignmenttutors.net\/blog\/solved-exp19_excel_ch07_hoeassessment_employees-computer-science-homework-help\/","title":{"rendered":"Solved exp19_excel_ch07_hoeassessment_employees | Computer Science homework help"},"content":{"rendered":"
\n

Exp19_Excel_Ch07_HOEAssessment_Employees<\/strong><\/p>\n

Project Description:<\/strong><\/h2>\n

You work for a clothing distributor that has locations in Iowa, Minnesota, and Wisconsin. You will use date and logical functions to complete the main employee data set, use database functions to calculate key summary statistics and create a map, and use financial functions to complete a loan amortization table.<\/p>\n

\u00a0\u00a0\u00a0\u00a0\u00a0<\/p>\n

Start \u00a0\u00a0Excel. Download and open the file named Exp19_Excel_Ch07_HOEAssessment_Employees.xlsx<\/em>. Grader has automatically added \u00a0\u00a0your last name to the beginning of the filename.<\/p>\n

\u00a0<\/p>\n

The 1-Data worksheet contains \u00a0\u00a0employee data. You will insert several functions to complete this worksheet. \u00a0\u00a0Column C contains the actual hire dates for the employees. You want to \u00a0\u00a0extract only the year in column G.
\u00a0<\/p>\n

\u00a0\u00a0In cell G9, insert the appropriate date function to extract the year from the \u00a0\u00a0date in cell C9. Copy the function from cell G9 to the range G10:G33.<\/p>\n

\u00a0<\/p>\n

Next, you want to determine how \u00a0\u00a0many years each employee has worked for the company.
\u00a0<\/p>\n

\u00a0\u00a0In cell H9, insert the YEARFRAC function to calculate the years between the \u00a0\u00a0hire date and the last day of the year contained in cell G2. Use a mixed \u00a0\u00a0reference to cell G2. Copy the function from cell H9 to the range H10:H33.<\/p>\n

\u00a0<\/p>\n

You want to determine what day \u00a0\u00a0of the week each employee was hired.
\u00a0<\/p>\n

\u00a0\u00a0In cell I9, insert the WEEKDAY function to display the day of the week the \u00a0\u00a0first employee was hired. Use 2 as the return_type. Copy the function from \u00a0\u00a0cell I9 to the range I10:I33.<\/p>\n

\u00a0<\/p>\n

The value returned in cell I9 is \u00a0\u00a0a whole number. You want to display the weekday equivalent.
\u00a0<\/p>\n

\u00a0\u00a0In cell J9, insert a VLOOKUP function to look up the value stored in cell I9, \u00a0\u00a0compare it to the array in the range H2:I6, and return the day of the week. \u00a0\u00a0Use mixed references to the table array. Copy the function from cell J9 to \u00a0\u00a0the range J10:J33.<\/p>\n

\u00a0<\/p>\n

Column D contains the city each \u00a0\u00a0employee works in. You want to display the state.
\u00a0<\/p>\n

\u00a0\u00a0In cell F9, insert the SWITCH function to switch the city stored in cell D9 \u00a0\u00a0with the respective state contained in the range C2:C4. Switch Des Moines<\/strong> for Iowa, St. Paul<\/strong> for Minnesota, and Milwaukee <\/strong>for Wisconsin. Use mixed \u00a0\u00a0references to cells C2, C3, and C4. Copy the function from cell F9 to the \u00a0\u00a0range F10:F33.<\/p>\n

\u00a0<\/p>\n

Your next task is to calculate \u00a0\u00a0the bonus for the first employee. If the employee was hired before 1\/1\/2010, \u00a0\u00a0the employee’s salary is multiplied by 3%. If the employee was hired before \u00a0\u00a01\/1\/2015, the employee’s salary is multiplied by 2%. If the employee was \u00a0\u00a0hired before 1\/1\/2020, the employee’s salary is multiplied by 1%.
\u00a0<\/p>\n

\u00a0\u00a0In cell K9, insert the IFS function to create the three logical tests to \u00a0\u00a0calculate the appropriate bonus. Use mixed references to cells within the \u00a0\u00a0range K2:L4. Copy the function from cell K9 to the range K10:K33.<\/p>\n

\u00a0<\/p>\n

Top management decided to ensure \u00a0\u00a0all representatives’ salaries are at least $62,000 (cell G2).
\u00a0<\/p>\n

\u00a0\u00a0In cell L9, nest an AND function within an IF function. If the job title is Representative \u00a0\u00a0<\/strong>and the salary \u00a0\u00a0is less than the minimum representative salary, calculate the difference \u00a0\u00a0between the minimum representative salary and the actual salary. If not, \u00a0\u00a0return zero. Use a mixed reference to cell G3. Copy the function from cell L9 \u00a0\u00a0to the range L10:L33.<\/p>\n

\u00a0<\/p>\n

The 2-Summary worksheet contains \u00a0\u00a0data to insert conditional math and statistical functions to provide summary \u00a0\u00a0data. First, you want to count the number of employees in each state.
\u00a0<\/p>\n

\u00a0\u00a0Click the 2-Summary worksheet. In cell J3, insert the COUNTIF function to \u00a0\u00a0count the number of employees in Iowa, using the state abbreviation column \u00a0\u00a0and the state abbreviation in cell I3. Use mixed references for the range and \u00a0\u00a0cell to keep the row numbers the same. Copy the function from cell J3 to the \u00a0\u00a0range J4:J5.<\/p>\n

\u00a0<\/p>\n

Next, you want to calculate the \u00a0\u00a0total payroll for each state.
\u00a0<\/p>\n

\u00a0\u00a0In cell K3, insert the SUMIF function to total the salaries for employees who \u00a0\u00a0work in Iowa, using the state abbreviation column and the state abbreviation \u00a0\u00a0in cell I3. Use mixed references for the ranges and cell to keep the row \u00a0\u00a0numbers the same. Copy the function from cell K3 to the range K4:K5.<\/p>\n

\u00a0<\/p>\n

Next, you want to calculate the \u00a0\u00a0average salary for executives and the average salary for representatives.
\u00a0<\/p>\n

\u00a0\u00a0In cell J8, insert the AVERAGEIF function to calculate the average salary for \u00a0\u00a0executives. Use mixed references to the range and for the cell containing \u00a0\u00a0Executives. Copy the function to cell J9.<\/p>\n

\u00a0<\/p>\n

You want to identify the number \u00a0\u00a0of representatives hired before 1\/1\/2015.
\u00a0<\/p>\n

\u00a0\u00a0In cell J12, insert the COUNTIFS function to count the number of \u00a0\u00a0representatives who were hired before 1\/1\/2015. Use ranges for the criteria \u00a0\u00a0ranges and the reference to cell H9 that contains Representatives. Enter the \u00a0\u00a0date condition directly in the criteria box.<\/p>\n

\u00a0<\/p>\n

In cell J13, insert the SUMIFS \u00a0\u00a0function to calculate the total salaries for representatives hired before \u00a0\u00a01\/1\/2015. Use ranges for the criteria ranges and the reference to cell H9 \u00a0\u00a0that contains Representatives. Enter the date condition directly in the \u00a0\u00a0criteria box. Use mixed references to the ranges and cell reference so that \u00a0\u00a0you can copy the function in the next step.<\/p>\n

\u00a0<\/p>\n

Copy the function from cell J13 \u00a0\u00a0to the range J14:J16. Edit the function in cell J14 to calculate averages.<\/p>\n

\u00a0<\/p>\n

Edit the function in cell J15 to \u00a0\u00a0calculate the lowest salary.<\/p>\n

\u00a0<\/p>\n

Edit the function in cell J16 to \u00a0\u00a0calculate the highest salary.<\/p>\n

\u00a0<\/p>\n

Insert a map to display total \u00a0\u00a0salaries by states.
\u00a0<\/p>\n

\u00a0\u00a0Select the ranges I2:I5 and K2:K5 and insert a filled map. Change the chart \u00a0\u00a0title to Payroll by State<\/strong>. Cut the chart and paste it in cell M1.<\/p>\n

\u00a0<\/p>\n

The 3-Finance worksheet contains \u00a0\u00a0inputs for a loan.
\u00a0<\/p>\n

\u00a0\u00a0In cell F2, insert the PV function to calculate the loan. Use cell references \u00a0\u00a0in the Input Area and Basic Output Area.<\/p>\n

\u00a0<\/p>\n

You are ready to insert formulas \u00a0\u00a0and functions in the loan amortization table.
\u00a0<\/p>\n

\u00a0\u00a0In cell C7, enter a reference to the Payment that is stored in cell D2. Use \u00a0\u00a0mixed reference so that the row number does not change. Copy the formula from \u00a0\u00a0cell C7 to the range C8:C42.<\/p>\n

\u00a0<\/p>\n

The next column is designed to \u00a0\u00a0display the interest portion of each payment.
\u00a0<\/p>\n

\u00a0\u00a0In cell D7, insert the IPMT function to calculate the interest for the first \u00a0\u00a0payment. Use mixed references for cells in the Basic Output Area and make \u00a0\u00a0sure the result is a positive value. Copy the function from cell D7 to the \u00a0\u00a0range D8:D42.<\/p>\n

\u00a0<\/p>\n

Next, you are ready to calculate \u00a0\u00a0the portion of the monthly payment that goes toward the principal.
\u00a0<\/p>\n

\u00a0\u00a0In cell E7, insert the PPMT function to calculate the principal portion of \u00a0\u00a0the first payment. Use mixed references for cells in the Basic Output Area \u00a0\u00a0and make sure the result is a positive value. Copy the function from cell E7 \u00a0\u00a0to the range E8:E42.\u00a0<\/p>\n

\u00a0<\/p>\n

Now you are ready to calculate \u00a0\u00a0the balance of the loan after each payment.
\u00a0<\/p>\n

\u00a0\u00a0In cell F7, enter a formula to calculate the ending balance after subtracting \u00a0\u00a0the Principal Repayment from the Beginning Balance. Copy the formula from \u00a0\u00a0cell F7 to the range F8:F42.
\u00a0<\/p>\n

\u00a0\u00a0In cell B8, enter a formula that refers to the ending balance for the \u00a0\u00a0previous month in cell F7. Copy the relative formula to the range B9:B42.<\/p>\n

\u00a0<\/p>\n

Now you want to calculate the \u00a0\u00a0cumulative interest after each payment.
\u00a0<\/p>\n

\u00a0\u00a0In cell H7, insert the CUMIPMT function. Use mixed references for cells in \u00a0\u00a0the Basic Output Area and the Start_period argument. Make sure the result is \u00a0\u00a0a positive value. Copy the function from cell H7 to the range H8:H42.\u00a0<\/p>\n

\u00a0<\/p>\n

Finally, you want to calculate \u00a0\u00a0the cumulative principal after each payment.
\u00a0<\/p>\n

\u00a0\u00a0In cell I7, insert the CUMPRINC function. Use mixed references for cells in \u00a0\u00a0the Basic Output Area and the Start_period argument. Make sure the result is \u00a0\u00a0a positive value. Copy the function from cell I7 to the range I8:I42.<\/p>\n

\u00a0<\/p>\n

Insert a footer on all three \u00a0\u00a0sheets with your name on the left side, the sheet name code in the middle, \u00a0\u00a0and the file name code on the right side.<\/p>\n

\u00a0<\/p>\n

Save and close Exp19_Excel_Ch07_HOEAssessment_Employoees.xlsx<\/em>. \u00a0\u00a0Exit Excel. Submit the file as directed.<\/p>\n<\/div>\n","protected":false},"excerpt":{"rendered":"

Exp19_Excel_Ch07_HOEAssessment_Employees Project Description: You work for a clothing distributor that has locations in Iowa, Minnesota, and Wisconsin. You will use date and logical functions to complete the main employee data set, use database functions to calculate key summary statistics and create a map, and use financial functions to complete a loan amortization table. \u00a0\u00a0\u00a0\u00a0\u00a0 Start […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[31],"tags":[],"class_list":["post-13787","post","type-post","status-publish","format-standard","hentry","category-homework-help"],"_links":{"self":[{"href":"https:\/\/assignmenttutors.net\/blog\/wp-json\/wp\/v2\/posts\/13787","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/assignmenttutors.net\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/assignmenttutors.net\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/assignmenttutors.net\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/assignmenttutors.net\/blog\/wp-json\/wp\/v2\/comments?post=13787"}],"version-history":[{"count":0,"href":"https:\/\/assignmenttutors.net\/blog\/wp-json\/wp\/v2\/posts\/13787\/revisions"}],"wp:attachment":[{"href":"https:\/\/assignmenttutors.net\/blog\/wp-json\/wp\/v2\/media?parent=13787"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/assignmenttutors.net\/blog\/wp-json\/wp\/v2\/categories?post=13787"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/assignmenttutors.net\/blog\/wp-json\/wp\/v2\/tags?post=13787"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}