Can you complete an excel assignment?

User Generated

gybir0918

Business Finance

Description

I need this excel assignment completed by March 13, 2018. I have attached instructions. I attached the excel sheet to be completed. Two attachments total

Unformatted Attachment Preview

BUSI 201 MICROSOFT EXCEL PROJECT: GROUP SUBMISSION INSTRUCTIONS Rationale: This assignment aims to continue your mastery of previously learned Microsoft Excel concepts, to build upon previously learned concepts with newer ones, and to fortify individual and group efforts through means of collaborative efforts in a virtual domain. Directions: 1. Review the work completed by each group member for the individual submission portion of the Microsoft Excel Project. Together, determine which project will represent the group in your final version, making any adjustments or corrections as needed. 2. Unhide the Retirement worksheet in the Payroll file. Choose 1 employee from the Payroll worksheet to create a retirement plan for. This worksheet will help you calculate how much is being saved for retirement, and how that will affect the amount that can be withdrawn through the retirement years. a. Begin by determining the yearly/monthly gross income that this employee can expect given his/her current pay and hours. b. Many companies offer to match retirement contributions when their employees participate in a 401(k) plan. In this scenario, the employer will contribute up to 5% of the employee’s gross income if the employee also contributes 5%. (E.g. employees contributing 3% of their income would receive an additional 3% from the company and employees contributing 7% would receive an additional 5% from the company, etc.) c. Use a formula to add a contribution amount of your choice as well as any company match in cells B14:B15. Use an IF function to automatically determine the company match based on your chosen percentage. Be sure to use the assumption cells B22:B23 in your formulas. Test various contribution amounts to ensure that your formula works in all cases. d. Roth IRAs can be used for low to upper middle class workers to fund retirement with post-tax dollars (i.e. net pay). Consider adding additional funds to a Roth IRA in cell B16. Total the contributions to your retirement in cell B17 and calculate the monthly contributions (D12:D17). The monthly contribution will now appear in cell B2. e. Calculate the percentage (%) of income that is being contributed to retirement in B20. Page 1 of 2 BUSI 201 3. Now that you have calculated the amount to save for retirement, you will calculate how much this amount will yield in 30 years and what the monthly income will be in retirement. a. In cell B5, use the appropriate financial function to calculate how much money will be in the account when the employee is ready to retire. Remember to use a negative value for the PMT argument. There is no money currently in the account and payments will be applied at the end of every month. b. Once you have saved for retirement, how much can you draw from the retirement account every month over the 30 years that you expect to be retired? Assume that at the end of 30 years, the account balance will be zero and the payments to you will be made at the beginning of every month. (Hint: Use the PMT function to calculate the payments to yourself. Use the ending balance in your retirement account as the present value. Add a negative sign before the present value so the result appears as a positive number.) c. Play with different values for the Average Annual Return and see how a small change in the rate can have a big effect on your return. Over 30 years, the average inflation-adjusted returns for the S&P 500 have been between 5–8%. Use a lower percentage to be more conservative in your calculations. This would move you toward over-saving rather than over-estimating and being caught short at retirement. d. Consider any changes that should be made to the contributions to yield an appropriate income in retirement, such as: How long will you be working and contributing to retirement? Thirty years is the average assumption. What if you want to work longer or retire earlier? What if you wanted to have money left at your death to leave behind as an inheritance? How would your formula(s) change to accommodate this? e. Write a short paragraph (3–5 sentences) detailing the assumptions used in your retirement calculations. f. Write a second paragraph (200–500 words) analyzing what you have learned from this project, both in a practical way and in your use of Excel. 4. Save and submit your work to Blackboard. Only 1 student will submit the final versions of your group’s work. You can attach more than 1 file to your Blackboard submission by clicking “Browse for File” a second time. Your submission will include the following: a. Excel spreadsheet file b. Word document containing your work from step 3 of the above instructions. Submit your Microsoft Excel Project: Group Submission by 11:59 p.m. (ET) on Monday of Module/Week 3. Page 2 of 2 Retirement Account Monthly contribution Years until retirement Average annual return How much will I have at retirement? $0,00 30 5,00% Years in retirement How much will that pay me per month? 30 Retirement Contributions Yearly Monthly Gross Income 401(k) My contribution ($) Company match Roth IRA (max 5,500 per year) Total Contributions to Retirement What % of Income are we contributing to retirement? My Contribution (%) Company Match 5%
Purchase answer to see full attachment
User generated content is uploaded by users for the purposes of learning and should be used following Studypool's honor code & terms of service.

Explanation & Answer

See attached. Please inform me if you need any clarification.

Liberty Bank & Trust
Lynchburg, VA Branch
Payroll Data for Week of Jan 1-7
No. of
Name
Dependents
Cortez, Mark
2
Higgins, Judy
1
Howard, Francis
3
Lindsey, Lamar
1
Manning, Joe
1
Meyer, Delores
1
Pittman, Oliver
3
Schultz, Leslie
2
Spencer, Kelly
3
Vasquez, Cassandra
2
Watson, Rosemary
1
Williams, Lynne
2
Totals
22

Hours
Worked
48
48
50
36
40
20
40
44
38
45
28
41
478

Taxable Pay
(weekly)

Assumptions
Base Work Hours
Overtime rate
FICA rate
Deduct per Depend $
Weeks per year

Hourly
Wage
12.95
16.6
26.37
13.02
18.5
11.89
22.08
26.38
13.01
19.56
14.85
20.62
215.83

40
1.5
6.20%
50.00
52

$0
$179
$730
$1,767
$8,013

Regular
Overtime
Pay
Pay
518
155.4
664
199.2
1054.8
395.55
468.72
0
740
0
237.8
0
883.2
0
1055.2
158.28
494.38
0
782.4
146.7
415.8
0
824.8
30.93
8139.1
1086.06

Gross Pay Taxable Pay
673.4
573.4
863.2
813.2
1450.35
1300.35
468.72
418.72
740
690
237.8
187.8
883.2
733.2
1213.48
1113.48
494.38
344.38
929.1
829.1
415.8
365.8
855.73
755.73
9225.16
8125.16

FICA
41.7508
53.5184
89.9217
29.06064
45.88
14.7436
54.7584
75.23576
30.65156
57.6042
25.7796
53.05526
571.95992

Net Pay
545.6392
606.3816
1035.3408
376.85136
590.62
194.8864
645.1416
859.87424
412.07144
664.2208
335.1504
613.74224
6879.92008

Summary Statistics

Tax Rate
10%
15%
25%
28%
33%

Federal
Withholding
Tax
86.01
203.3
325.0875
62.808
103.5
28.17
183.3
278.37
51.657
207.275
54.87
188.9325
1773.28

Average
Highest
Lowest

# of Hours
Net Pay
Gross Pay (yr)
39.83 $
573.33 $
9,225.16
50 $ 1,03...


Anonymous
Really helped me to better understand my coursework. Super recommended.

Studypool
4.7
Trustpilot
4.5
Sitejabber
4.4

Similar Content

Related Tags