Objective: Use Excel to calculate the various payroll withholdings and net pay. Then create the related journal entries. This should look professional in appearance and be set up 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$)$:

a$.$ 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.

i$.$ Federal Income Taxes: Assume all employees withhold $25\%$

ii$.$ State Income Taxes: Assume ND employees withhold $5\%$ and MN employees withhold at $10\%.$

iii. FICA: Calculate BOTH employee and employer amounts for BOTH portions of FICA.

$1.$ For $2023,$ assume that the $6\mathrm{.}2\%$ Social Security taxes are paid only on the first $$168,600$ in wages $($call this FICA$-$Social Security$).$

$2.$ For $2023,$ assume that the $1\mathrm{.}45\%$ Medicare taxes are paid on all wages $($call this FICA$-$Medicare$).$

iv$.$ Health Insurance: Monthly premium is $$500$ for those who elect coverage $($this is withheld from the employee paycheck$).$

v$.$ SUTA: $0\mathrm{.}80\%$ on the first $$7,000$ of employee earnings $($paid by employer$).$

vi$.$ FUTA: $5\mathrm{.}40\%$ on the first $$7,000$ of employee earnings $($paid by employer$).$

b$.$ 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$).$ Some of the employees are new in $2024.$

Employee Year$-$to$-$Date Wages as of $11/30/24$ Wages for December $2024$ State of Residency Elected Health Care Insurance?

A $$66,000$ $$6,000$ MN Y

B $108,0009,000$ ND N

C $158,00014,250$ ND Y

D $6,0003,000$ MN N

E $275,00025,000$ ND Y

F $2,0001,600$ ND N

$2.$ Other than the info noted above, ALL OTHER amounts in the Excel document should be based on formulas and cell references.

a$.$ I would expect some $$IF$$ functions, absolute cell references, etc. Use your knowledge from TLF $116$ or research on Google!

b$.$ There should not be ANY hard$-$keyed information in any of your formulas.

c$.$ 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 and business professionals!

$3.$ Calculate the amount of each type of payroll tax, other withholdings and net pay for each employee for the December payroll.

a$.$ Based on the total amounts of the six employees, create the journal entry $($or entries$)$ to record all transactions associated with the December $31$ payroll.

i$.$ 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.

b$.$ Show subsequent journal entries for the following payment dates:

i$.$ Pay Date to employees: January $5$

ii$.$ All taxes: January $10$

iii. Health Insurance: January $15$

c$.$ 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 professional in appearance.

$5.$ Upload your completed Excel document to Blackboard by the deadline listed on Blackboard.

a$.$ Please save the document $$Payroll$\_[$your last name$]\_[$your first name$].$xlsx$$

If theres any specific formatting to be done on the excel sheet please specify how.

Thank you.