4) In the range F13:F45, insert a formula to calculate the 401(k) contributions for each employee....
Fantastic news! We've Found the answer you've been seeking!
Question:
Transcribed Image Text:
4) In the range F13:F45, insert a formula to calculate the 401(k) contributions for each employee. The formula should determine that if the number of years employed is greater than or equal to 2, then the contribution is equal to the contribution percentage in cell F4 multiplied by the employee's salary; otherwise, the contribution is zero. 5) In the range G13:G45, insert a formula to calculate the health insurance cost for each employee by testing whether the name of the employee's plan is equal to the name of the plan in cell B4. If it is, then the cost of the health plan is equal to the value of cell C4, otherwise; the cost is equal to the value of cell C5. 6) In the range B7:B9, enter the formulas to calculate the total salaries, 401(k) contributions, and health insurance costs. A 1 Payroll Information 2 Today's Date 3 4 Health Plans 5 6 7 Total Salaries 8 9 Total Health Insurance 10 11 Total 401(k) Contributions 12 Employee 13 Abbot 14 Anderson 15 Baker 16 Clausen 17 Czechiski 18 Dawes 19 Dearborn 20 Derks 21 Eagleton 22 Evans 23 Fawkes 24 Freeborn Ready Documentation Accessibility: Good to go B 5/1/2022 Premier Standard Date Hired 2/28/2018 12/31/2009 12/31/2020 6/1/2015 4/30/2016 1/31/2018 4/30/2005 3/31/2022 3/31/2022 5/1/2019 11/30/2016 7/31/2010 Payroll C $6,500 $5,500 (+) Years 4 12 1 7 6 0 D Salary Health Plan $50,000 Premier $65,000 Premier $22,500 Standard $41,000 Standard $43,000 Standard 4 $21,500 Standard 17 $81,000 Premier $39,000 Standard $39,000 Standard $41,500 Premier $49,500 Premier ܘ ܚ ܗ ܘ E 401(k) Costs 12 $61 500 Premier F 3% G 401(k) Health Insurance H 4) In the range F13:F45, insert a formula to calculate the 401(k) contributions for each employee. The formula should determine that if the number of years employed is greater than or equal to 2, then the contribution is equal to the contribution percentage in cell F4 multiplied by the employee's salary; otherwise, the contribution is zero. 5) In the range G13:G45, insert a formula to calculate the health insurance cost for each employee by testing whether the name of the employee's plan is equal to the name of the plan in cell B4. If it is, then the cost of the health plan is equal to the value of cell C4, otherwise; the cost is equal to the value of cell C5. 6) In the range B7:B9, enter the formulas to calculate the total salaries, 401(k) contributions, and health insurance costs. A 1 Payroll Information 2 Today's Date 3 4 Health Plans 5 6 7 Total Salaries 8 9 Total Health Insurance 10 11 Total 401(k) Contributions 12 Employee 13 Abbot 14 Anderson 15 Baker 16 Clausen 17 Czechiski 18 Dawes 19 Dearborn 20 Derks 21 Eagleton 22 Evans 23 Fawkes 24 Freeborn Ready Documentation Accessibility: Good to go B 5/1/2022 Premier Standard Date Hired 2/28/2018 12/31/2009 12/31/2020 6/1/2015 4/30/2016 1/31/2018 4/30/2005 3/31/2022 3/31/2022 5/1/2019 11/30/2016 7/31/2010 Payroll C $6,500 $5,500 (+) Years 4 12 1 7 6 0 D Salary Health Plan $50,000 Premier $65,000 Premier $22,500 Standard $41,000 Standard $43,000 Standard 4 $21,500 Standard 17 $81,000 Premier $39,000 Standard $39,000 Standard $41,500 Premier $49,500 Premier ܘ ܚ ܗ ܘ E 401(k) Costs 12 $61 500 Premier F 3% G 401(k) Health Insurance H
Expert Answer:
Answer rating: 100% (QA)
the formulas to calculate the 401k contributions and health insurance costs for each employee and also calculate the total salaries 401k contributions and health insurance costs Calculating 401k contr... View the full answer
Related Book For
Posted Date:
Students also viewed these finance questions
-
Theresa Pratt works in the business office at Mills College, a technical college in Council Bluffs, Iowa. She has created a workbook to store and analyze registration and employee data. The workbook...
-
Step Instructions Point Value 1 Open exploring_e05_grader_h2_RealEstate.xlsx and save it as exploring_e05_grader_h2_RealEstate_LastFirst. 2 In cell G2 in the Sales Subtotals sheet, insert a formula...
-
You work in the Admissions Office for a small regional university in Massachusetts Your assistant entered a list of college applicants for the Fall 2021 semester. You determine if a student qualifies...
-
The Qantas Group includes a sustainability report in its annual report. What key performance indicators are included in this report? Explain the different stakeholders that would be interested in...
-
Sketch a rough graph of the outdoor temperature as a function of time during a typical spring day.
-
A 1.5-m3 insulated rigid tank contains 2.7 kg of carbon dioxide at 100 kPa. Now paddle-wheel work is done on the system until the pressure in the tank rises to 150 kPa. Determine the entropy change...
-
Gross Profit Percentage Calculate the company's 2019 gross profit percentage and compare the L04 result to the industry average. Use the following financial data for Brenner Instruments Sales...
-
AMI International is a large office products company. Headquar-ters management imposed pressure on operating division managers to meet profit fore-casts. The division managers met these profit goals...
-
The annual rates of return of Stock Zee for the last four years are 0.10, 0.15, 0.05, and 0.20, respectively. Compute the standard deviation of the annual rate of return for Stock Zee. *
-
Ruth Dennis, CEO of Prescott Industries, is concerned about the recent volatility in the company's operating income. She believes that since the number of units sold has been fairly stable over the...
-
A company is divided into 5 0 0 , 0 0 0 shares. An investor purchases 5 0 , 0 0 0 shares. What percent of the company does the investor own?
-
What factors can be used to control audit risk?
-
What two methods can be used to measure variation in a population?
-
Explain how the auditor might determine tolerable error.
-
If other factors are held constant, what happens to the sample size of substantive tests of details (becomes larger or smaller) when a. Reliance on the internal control structure increases? b....
-
Over what period can a tax loss be used as an offset against taxable income?
-
When should minutes of meetings and other follow-up documentation be distributed, and to whom?
-
Experiment: Tossing four coins Event: Getting three heads Identify the sample space of the probability experiment and determine the number of outcomes in the event. Draw a tree diagram when...
-
101, 115, 143, 106, 100, 142, 157, 163, 155, 141, 145, 153, 152, 147, 143, 115, 164, 160, 147, 150 (90%) Find the confidence interval of the median, indicated in parentheses, for the set of data.
-
The hourly compensation costs (in U.S. dollars) for production workers in selected countries are represented below. Class Frequency 2.487.48.....7 7.4912.49......3 12.5017.50....1 17.5122.51....7...
-
Are movie admissions related to ethnicity? A 2007 study indicated the following numbers of admissions (in thousands) for two different years. At the 0.05 level of significance can it be concluded...
-
Let \(h: \boldsymbol{x} \mapsto \mathbb{R}\) be a convex function and let \(\boldsymbol{X}\) be a random variable. Use the subgradient definition of convexity to prove Jensen's inequality: \[...
-
The purpose of this exercise is to prove the following Vapnik-Chernovenkis bound: for any finite class \(\mathscr{G}\) (containing only a finite number \(|\mathscr{G}|\) of possible functions) and a...
-
Using Jensen's inequality, show that the Kullback-Leibler divergence between probability densities \(f\) and \(g\) is always positive; that is, \[ \mathbb{E} \ln...
Study smarter with the SolutionInn App