functions and operators in microsoft excel you are excited about the usefulness of the microsoft excel tool you just learned about functions and operators you have entered the data in the workbook as actual expenses and permitted expenses for the month

/in /by

PART A

Functions and Operators in Microsoft Excel

You are excited about the usefulness of the Microsoft Excel tool. You just learned about functions and operators. You have entered the data in the workbook as Actual Expenses and Permitted Expenses for the month. You want to track the months in which you have overshot the budget.

Using Online Library or the Internet, research the following:

  • Logical functions and the situations in which they can be used
  • Operators and their uses
  • Relative and absolute referencing

Based on your research, discuss the following in relation to functions and operators:

  • Explain what are logical functions, and based on the library or Internet research, discuss some situations when it would be ideal to use these types of functions to support data analysis and decision making. As a hint, a type of logical function is an “IF” function.
  • There are three types of operators in Microsoft Excel—arithmetic, text, and comparison operators. Where can each of these operators be used? Can any of these operators be used interchangeably? Why or why not? Give examples to support your opinion.
  • Explain the IF, PRODUCT, SUM, AVERAGE, HYPERLINK, and COUNT functions provided by Microsoft Excel and share examples of how you or you place of employment might use these functions.
  • In your opinion, are there any benefits to using relative referencing, absolute referencing, and appropriate operators? Support your answers with reasons and examples.

PART B

Weekly Employee Payroll

Scenario

You work for a local construction firm “South Engineering Group” and your supervisor wants to test your knowledge and skills with Microsoft Excel and has instructed you to develop a spreadsheet to calculate weekly payroll. To do this, complete the following steps:

  1. Open a new Microsoft Excel workbook. Save this workbook at an easily accessible location. (Easily accessible locations could include your desktop, your documents folder, or an external flash drive.) Also, while completing the steps for this assignment, it would be a good idea to periodically save your work so that you do not lose any progress in case of computer malfunction. In your new and opened workbook, notice three available worksheets, labeled as Sheet1, Sheet2, and Sheet3, in the bottom-left area of the window.
  2. Rename Sheet1 to Weekly Payroll – 2012.
  3. In cell A1, type the name of the construction firm as South Engineering Group.
  4. Select cells A1 through L1 and center-align the firm name South Engineering Group.
  5. In the second row, enter labels for each column as given in the following table:
    Column Labels Reference
    EFN Employee First Name
    ELN Employee Last Name
    HR Hourly Rate
    OHR Overtime Hourly Rate
    THW Total Hours Worked
    OHW Overtime Hours Worked
    GP Gross Pay
    SST Social Security Tax
    FWT Federal Withholding Tax
    SWT State Withholding Tax
    NP Net Pay
    POP Percentage of Payroll
  6. Under the labels EFN and ELN, enter the names of fifteen hypothetical employees.
  7. At this point, use the illustration for guidelines, but be aware that the illustration is not to scale; meaning that adjustments in row and column widths may be needed as further information is added in later steps.
  8. Under the HR label, enter a regular hourly rate to pay. Use any values of choice but stay in the range between fifteen and seventy-five.
  9. Under the OHR label, use a formula to calculate the hourly overtime rate of Time and Half.
  10. Under the THW label, enter the total number of hours worked. Use any number of hours but stay in the range between twenty-five and eighty, and have at least five entries over forty hours.
  11. Under the OHW label, calculate the overtime hours worked. Using the IF function, extract the overtime hours from the total hours worked values.
  12. Next, enter formulas to perform the following calculations:
    1. Calculate each employee’s gross pay (GP) for the week. To calculate GP, use the formula:
      ([Total Hours Worked – Overtime Hours Worked] * Hourly Rate) + (Overtime Hourly Rate * Overtime Hours Worked)
      The formula should use cell references.
    2. Next, calculate each employee’s SST, FWT, and SWT using the rates given in the following table:
      Cell Tax Rate
      SST: Social Security Tax 6.2%
      FWT: Federal Withholding Tax 18%
      SWT: State Withholding Tax 5%
    3. Next, calculate each employee’s net pay (NP). To calculate NP, use the formula:
      Gross Pay – (Social Security Tax + Federal Withholding Tax + State Withholding Tax)
      The formula should use cell references.
  13. Next, use the SUM function to calculate the totals for THW, OHW, GP, SST, FWT, SWT, and NP. Use the fill handle feature of Microsoft Excel here.
  14. Next, type the label Totals in cell A18, and then merge and center-align this label across the cells A18through D18.
  15. Format the cells GP, SST, FWT, SWT, and NP and set their number category to Currency.
  16. Next, under the POP label, include an absolute reference formula to calculate the percentage of payroll for each employee. To calculate POP, use the formula:
    NP / SUM (NP)
    Pay close attention to the cell references. Additionally, format these values as Percentage with two decimal places.
  17. Format the entire worksheet as follows:
    • Set the font to Arial, size to 12, and border style to All Borders. Fill the cells with a color of your choice. Set the font size 16 for the worksheet title.
    • Set the font style of all the labels and the Totals row to Bold.
  18. Next, insert a comment in the cell L18 and describe what you have learned from this assignment.
  19. Sort the values in the HR column in the descending order.
  20. Next, create a 3D exploded pie chart to illustrate the values for POP associated with EFN, and move this chart to a separate worksheet named Pie Chart. For the pie chart, add and format the data labels showing the percentages and leader lines. In addition, include the chart title All Earners.
  21. Create a copy of the Weekly Payroll – 2012 worksheet and rename the copied sheet to Weekly Payroll Filter > 1000.00.
  22. In the Weekly Payroll Filter > 1000.00 worksheet, apply a filter to show only the records in which the NP values are greater than $1,000.00. Make adjustments to the data entered to ensure that at least five records are displayed by this filter. Also, ensure that the filter does not include the totals in row 18.
  23. Next, create a 3D exploded pie chart to illustrate the values for POP associated with EFN, and move this chart to a separate worksheet named Pie Chart > 1000. For the pie chart, add and format the data labels showing the percentages and leader lines. In addition, include the chart title Top Earners Over $1000.00.
  24. As the final step, apply any additional formatting that seems fit to enhance the professional presentation. Delete any unused worksheets.