Calculate the federal tax rate. The federal tax rate is a function of the adjusted income and the employee's marital status. Reference the tax tables in the model assumptions and the marital status in the employee information section to construct a formula (or a set of nested formulas) to calculate the tax rate. For example, a single employee with $15,000 in income would have a 33% marginal tax rate. (Calculation is being calculated and inputed in cell C36 for "Federal Income Tax Rate" modelingBasics-WO-Kathryn_Logsdon • Saved -O Search (Alt+Q)AutoSaveKathryn Logsdon KLOffAssignn Assignment TasksX JareFileHomeInsertPage LayoutFormulasDataReviewViewHelpEDPaycheck CalculatorCalculate the federal tax rate. The federal tax rate is a function of the adjusted income andInstruction TaskSubmit Viewthe employee's marital status. Reference the tax tables in the model assumptions and themarital status in the employee information section to construct a formula (or set of nestedformulas) to calculate the tax rate. For example, a single employee with $15,000 in incomewould have a 33% marginal tax rate. [7 points]SheetGuideReport10Show Instructions Automatic ScoringfeYou work in the human resources department of your company herpmg ewemproyeescomprete tre ECEJJary paperwornE16are anABDEFGHKMNP12Employee InformationModel AssumptionsHours WorkedPay RateMarital Status# of Allowances385$30.00# regular hours in Pay Period# of paychecks per year480Federal Tax Rate TablesMarried26"Single" Tax Rate"Married" Tax Rate25%$125.00$1,500.006IncomeRateIncomeRate$0$1,450$5,8927Retirement Savings %Payroll Tax information$0 10%10%Health Insurance (every paycheck)Medicare RateSocial Security Rate81.45%$72515%15%Flexible Spending (Annual)$2,94694.2%25%25%10$7,13828%$11,89228%11Paycheck SummaryIncome Tax Information$14,88833%$18,12133%$2,625.00$313.94Allowance DeductionGross PayTotal Deductions12$146$32,36335%$32,36335%13State Tax Rate5%Adjusted IncomeTotal Taxes14$2,311.06$0.00Paycheck Calculator1516You work in the human resources department of your company helping new employeesNet Pay$2,311.0617complete the necessary paperwork to get their first paycheck. There are a number of18decisions that employees must make when they complete this paperwork including: (1)Paycheck Calculationswhich health insurance package to buy (this impacts how much money will be deducted1920Incomeeach pay period to pay for the premium), (2) how much money to put in a flexible spendingHours Worked85account annually to cover medical and childcare expenses, and (3) the percentage of theirregular paychecks to invest in the company's 401k retirement savings plan. The expenses21Pay Rate$30.0022PaycheckPricingReady * Accessibility: Investigate100%2:42 AMe Type here to search30°F3/25/2022近 AutoSavemodelingBasics-WO-Kathryn_Logsdon · Saved -P Search (Alt+Q)困OffKathryn Logsdon KLAssignn Assignment TasksX JareFileHomeInsertPage LayoutFormulasDataReviewViewHelpEDPaycheck CalculatorCalculate the federal tax rate. The federal tax rate is a function of the adjusted income andInstruction TaskSubmit Viewthe employee's marital status. Reference the tax tables in the model assumptions and themarital status in the employee information section to construct a formula (or set of nestedformulas) to calculate the tax rate. For example, a single employee with $15,000 in incomewould have a 33% marginal tax rate. [7 points]SheetGuideReport10Show Instructions Automatic ScoringC36feABDFGHKP21Hours Worked85account annually to cover medical and childcare expenses, and (3) the percentage of theirPay RateRegular Payovertime Pay$30.00$2,400.00$225.00$2,625.0022regular paychecks to invest in the company's 401k retirement savings plan. The expenses23paid for these three items are not subject to Federal or State income taxes. Each newemployee must also complete a W4 form where they claim their marital status and numberof allowances for deductions for tax purposes. Often these employees will ask you to2425Total Gross Pay26project their first paycheck so that they know how much money to plan on for their personalDeductions (Items not subject to income tax)$125.0027budget. You have decided to create a spreadsheet model that will make these projections28Insurancegiven the information the new employee provides on their paperwork.Flexible SpendingRetirementTotal Deductions$57.69$131.25293031$313.9432Adjusted Income$2,311.063334ТаxesFederal Income Tax Rate353637Federal Income TaxAdjustment for AllowancesNet Federal Income TaxState Income TaxMedicare TaxSocial Security Tax3839404142PaycheckPricingReady * Accessibility: Investigate曲100%2:43 AMe Type here to search30°F3/25/2022

Answers: Here is the formula for Federal Income Tax Rate: The formula for Cell 36:…

Answered: Calculate the federal tax rate. The…

7th Edition

ISBN:9780078022159

Author:Abraham Silberschatz Professor, Henry F. Korth, S. Sudarshan

Publisher:Abraham Silberschatz Professor, Henry F. Korth, S. Sudarshan

Chapter1: Introduction

Section: Chapter Questions

Problem 1PE