Use Excel to calculate the various payroll withholdings, net pay and create the related journal entries.  This should look professional in appearance, and be setup as a template (to be used for each payroll by this company).  Therefore, the current pay period information (hours worked, and any changes to hourly rates, salaries, or other inputs) should be able to be updated and the spreadsheet should automatically calculate the current payroll and update the journal entries.

Requirements:

1. Data Inputs (info you may hard-key into Excel):
   1. Create a data input area within your spreadsheet with the following withholding information (suggestion: top left corner of spreadsheet).  This info should be used in formulas when calculating the payroll information.
      1. Federal Income Taxes: Assume all employees withhold 25%
      2. State Income Taxes: Assume ND employees withhold 5% and MN employees withhold at 10%.
      3. FICA: Calculate BOTH employee and employer amounts for BOTH portions of FICA.
         1. For 2019, assume that the 6.2% Social Security taxes are paid only on the first $128,400 in wages.
         2. For 2019, assume that the 1.45% Medicare Tax are paid only on all wages.
      4. Health Insurance: Monthly premium is $400 for those who elect coverage.
   2. Employee Information: Assume the following payroll information for the month of December and year-to-date totals as of November 30 (needed for calculating some of the taxes properly).

Employee	Year-to-Date Wages as of 11/30/19	Wages for December 2019	State of Residency	Elected Health Care Insurance?
A	$55,000	$5,000	MN	Y
B	108,000	9,000	ND	N
C	137,500	12,500	ND	Y
D	38,000	6,500	MN	N
E	220,000	20,000	ND	Y

2. Other than the info noted above, ALL OTHER amounts in the Excel document should be based on formulas and cell references.
   1. I would expect some “IF” functions, absolute cell references, etc.  Use your knowledge from MIS 116 or research on Google!
   2. There should not be ANY hard-keyed information in any of your formulas.
   3. Setting up Excel spreadsheets like this (having an area with all the input data, which all formulas refer to, and the same formulas for all employees) enables changes to be made efficiently and more accurately.  For instance, if the Federal Income Tax rate were to change from 25% to 20%, you should be able to type 20% into that 1 cell and it should update all Federal Income Tax amounts in the whole document.   Or if you were to add more employees, you should be able to simply copy/paste the formulas for them.  Creating efficient spreadsheets is a critical skill for accountants!
3. Calculate the amount of each type of payroll taxes, other withholdings and net pay for each employee for the December payroll.
   1. Based on the total amounts of the five employees, create the journal entry (or entries) to record all transactions associated with the December 31 payroll.
      1. For each type of calculation (FICA, federal income taxes, etc.) you should apply the same formula for each of the same employees; there should NOT be different formulas for different employees based on compensation amounts.
   2. Show subsequent journal entries for the following payment dates:
      1. Pay Date to employees: January 1
      2. All taxes: January 10
      3. Health Insurance: January 15
   3. Use formulas to link amounts in the journal entries to your payroll calculations.
4. Format the Excel document as if it was the formal payroll document for a company.  It should be set up to calculate and present the information in an efficient manner, be easy to understand, and overall professionalism in appearance.