BUSI 201 Microsoft Excel Project Solution

Download Now

MICROSOFT EXCEL PROJECT: INDIVIDUAL SUBMISSION INSTRUCTIONS

Rationale:

The purpose of this assignment is to build your familiarity with Excel spreadsheets and the various components therein.

Complete the following tasks on your own and submit your work to Blackboard.

Scenario:

As manager of the Liberty Bank & Trust, you are responsible for managing the weekly payroll. Your assistant developed a partial worksheet, and now you must enter the formulas to calculate the following:

• Regular Pay

• Overtime Pay

• Gross Pay

• Taxable Pay

• Withholding Tax

• FICA

• Net Pay

Additionally, you will total pay columns and calculate some basic statistics. As you construct formulas, ensure that you use both absolute and relative cell references correctly in formulas while avoiding circular references.

Directions:

1. Open the Payroll.xlsx workbook and save it to your computer.

2. Study the worksheet structure, and then read the business rules in the Notes section.

3. Use IF functions to calculate the regular pay and overtime pay based on a regular 40-hour workweek. Pay overtime only for overtime hours. Calculate the gross pay based on the regular and overtime pay. Mark's regular pay is $518. With eight overtime hours, Mark's overtime pay is $155.40.

Hint: This question will require 2 “if statements:”

Regular Pay: The first “if statement” will determine the amount of regular pay that the employee will receive. In its simplest form, this can be calculated by multiplying the number of Hours Worked by the Hourly Wage. However, you need a more advanced formula that will only apply the hourly wage to the first 40 hours. This is where the IF function will come in.

Your function will test whether the Hours Worked is greater than 40 (logical test). If it is, you will return the result of a formula multiplying just the 40 hours by the Hourly Wage (true value). If the Hours Worked is not greater than 40, you will multiply the Hours Worked by the Hourly Wage.

Remember to use absolute cell references where applicable. Any cells you refer to outside of row 5 where you find the information specific to the first employee will need an absolute cell reference. F4 is the keyboard shortcut to absolute reference a cell.

A correct formula will yield the result: $518

Overtime Pay: This section’s IF function will calculate the overtime pay. If the employee has worked more than 40 hours, you will multiply only the hours over 40 by the hourly rate and then multiply by the Overtime Rate (Hours Worked-40)*Hourly Rate*Overtime Rate. If the employee did not earn overtime, you should return a zero.

Gross Pay: Do not forget to calculate the Gross Pay, which is the Total amount of pay the employee earned.

4. Create a formula to calculate the taxable pay. With 2 dependents, Mark's taxable pay is $573.40. Use the Deduction Amount found in B24.

5. Use the appropriate function to identify and calculate the federal withholding tax. With a taxable pay of $573.40, Mark's tax rate is 15%, and the withholding tax is $86.01.

Hint: This question will use a VLOOKUP. The formula will determine the employee Taxable Pay. It will then refer to the table in D21:E25 to determine the correct Tax Rate.

• The VLOOKUP should refer to the Taxable Pay found in Column H (lookup value)

• Use the table range as your Table Array. Remember to use an absolute reference to the table since you will copy the formula and do not want the table reference to change.

• The column index number argument indicates which column of the table you want your result to come from. In this case, you want to pull your result from the second column of the table using a col_index_num of 2

• You will multiply your result (15%) by the Taxable Pay. A correct answer will give the actual amount of tax that will be paid, or $86.01 for the first employee.

6. Calculate FICA based on gross pay and the FICA rate, and then calculate the net pay. The net pay can be calculated by subtracting the Federal Withholding Tax & FICA from the Gross Pay. Finally, calculate the Yearly Gross Pay based on the Gross Pay and number of weeks per year found in B25.

7. Copy all formulas down their respective columns.

8. Calculate the total regular pay, overtime pay, gross pay, taxable pay, withholding tax, FICA, net pay, and yearly gross pay. These calculations will go in Row 17.

9. Apply Accounting Number Format to the range C5:Cl6. Apply Accounting Number Format to the first row of monetary data and to the total row. Apply Comma Style to the monetary values for the other employees. Underline the last employee's monetary values, and then use the Format Cells dialog box to apply Double Accounting Underline for the totals. The Double Accounting Underline can be found by opening the Format Cells dialog box. On the Font tab, you will find the Underline dropdown menu.

10. Insert appropriate functions to calculate the average, highest, and lowest values in the Summary Statistics area of the worksheet.

11. Save and submit your work to Blackboard.

Submit your Microsoft Excel Project: Individual Submission by 11:59 p.m. (ET) on Monday of Module/Week 2.