USI 201 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. Page 1 of 3 BUSI 201 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 40hour 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 Worked40)*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) Page 2 of 3 BUSI 201 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. Page 3 of 3

• 1495774940-Payroll.xlsx
• 1495774940-Microsoft_Excel_Project_Individual_Submission_Instructions1 1.docx

SuperTutor

(15)

Status NEW Posted 26 May 2017 04:05 AM My Price 20.00

-----------

Attachments

file 1495774947-Solutions file.docx preview (51 words )

S-----------olu-----------tio-----------ns -----------fil-----------e -----------Hel-----------lo -----------Sir-----------/Ma-----------dam----------- T-----------han-----------k y-----------ou -----------for----------- yo-----------ur -----------int-----------ere-----------st -----------and----------- bu-----------yin-----------g m-----------y p-----------ost-----------ed -----------sol-----------uti-----------on.----------- Pl-----------eas-----------e p-----------ing----------- me----------- on----------- ch-----------at -----------I a-----------m o-----------nli-----------ne -----------or -----------inb-----------ox -----------me -----------a m-----------ess-----------age----------- I -----------wil-----------l b-----------e q-----------uic-----------kly----------- on-----------lin-----------e a-----------nd -----------giv-----------e y-----------ou -----------exa-----------ct -----------fil-----------e a-----------nd -----------the----------- sa-----------me -----------fil-----------e i-----------s a-----------lso----------- se-----------nt -----------to -----------you-----------r e-----------mai-----------l t-----------hat----------- is----------- re-----------gis-----------ter-----------ed -----------on�-----------�th-----------is -----------web-----------sit-----------e -----------Tha-----------nk -----------you----------- -----------

Not Rated(0)

• Buy Answer