Prepare a biweekly payroll report for the Payroll Department of the XFIRM. The department employees and related information for the present pay period is as follows:

Create the worksheet shown on the next page as follows:

Use the following formulas to determine the gross pay, federal tax, state tax and net pay:

Gross Pay = Rate * Hours (no overtime at the X-Firm)

Federal Tax = 19% * (Gross Pay - Dependents * DependentRate)

State Tax = 3.4% * Gross Pay

Net Pay = Gross Pay - (Federal Tax + State Tax)

Note that the present DependentRate needs to be referenced in a specific cell of the spreadsheet. For example, on the attached, it is cell B19.

Show totals for the hours, gross pay, federal tax, state tax and net pay in Row 12

(d) Use the appropriate functions to determine the average, highest, and lowest values of each column in rows 14 through 16.

(e) Format the worksheet title so that it is centered across the worksheet area, is of size 18 pt. and of a different font than the rest of the worksheet.

(f) Bold and italicize the column headings. Draw a line across row 3 from columns B through H.

(g) Bold the names and row titles in Column A. Italicize the contents of A14-A16.

(h) Apply the currency style to the numeric values in cells E4 through H16, and make the remaining cells look consistent.

(i) Add a footer to the spreadsheet that contains GSB622 in the left corner and your name in the right corner. Change the page setup to landscape orientation and select the fit to a single page option.

(j) Add a pie chart to the worksheet that illustrates the distribution of hours among the employees. The chart is to use the employee names as labels for the pie pieces as shown on the back of this page.

EMPLOYEE Bisset, Sam Coors, Henry Edwards, Kate Karlin, Mike Nelson, Bette Rogers, David Satin, Mavis HOURLY RATE 15.50 14.75 12.25 9.95 13.75 18.25 19.15 HOURS 80.00 78.00 82.00 80.00 83.25 79.75 88.50 DEPENDENTS 2 4 3