PART AFunctions and Operators in Microsoft ExcelYou are exci
PART AFunctions and Operators in Microsoft ExcelYou 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 usedOperators and their usesRelative and absolute referencingBased 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 BWeekly Employee PayrollScenarioYou 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: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.Rename Sheet1 to Weekly Payroll – 2012.In cell A1, type the name of the construction firm as South Engineering Group.Select cells A1 through L1 and center-align the firm name South Engineering Group.In the second row, enter labels for each column as given in the following table:Column LabelsReferenceEFNEmployee First NameELNEmployee Last NameHRHourly RateOHROvertime Hourly RateTHWTotal Hours WorkedOHWOvertime Hours WorkedGPGross PaySSTSocial Security TaxFWTFederal Withholding TaxSWTState Withholding TaxNPNet PayPOPPercentage of PayrollUnder the labels EFN and ELN, enter the names of fifteen hypothetical employees.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.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.Under the OHR label, use a formula to calculate the hourly overtime rate of Time and Half.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.Under the OHW label, calculate the overtime hours worked. Using the IF function, extract the overtime hours from the total hours worked values.Next, enter formulas to perform the following calculations: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.Next, calculate each employee’s SST, FWT, and SWT using the rates given in the following table:CellTax RateSST: Social Security Tax6.2%FWT: Federal Withholding Tax18%SWT: State Withholding Tax5%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.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.Next, type the label Totals in cell A18, and then merge and center-align this label across the cells A18through D18.Format the cells GP, SST, FWT, SWT, and NP and set their number category to Currency.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.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.Next, insert a comment in the cell L18 and describe what you have learned from this assignment.Sort the values in the HR column in the descending order.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.Create a copy of the Weekly Payroll – 2012 worksheet and rename the copied sheet to Weekly Payroll Filter > 1000.00.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.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.As the final step, apply any additional formatting that seems fit to enhance the professional presentation. Delete any unused worksheets.
Collepals.com Plagiarism Free Papers
Are you looking for custom essay writing service or even dissertation writing services? Just request for our write my paper service, and we'll match you with the best essay writer in your subject! With an exceptional team of professional academic experts in a wide range of subjects, we can guarantee you an unrivaled quality of custom-written papers.
Get ZERO PLAGIARISM, HUMAN WRITTEN ESSAYS
Why Hire Collepals.com writers to do your paper?
Quality- We are experienced and have access to ample research materials.
We write plagiarism Free Content
Confidential- We never share or sell your personal information to third parties.
Support-Chat with us today! We are always waiting to answer all your questions.
