Answered step by step
Verified Expert Solution
Link Copied!

Question

1 Approved Answer

Section D: Basic Data Analysis Perform all work in Excel 3. Suppose that an employee starts working after completing her MBA at age 25 at

Section D: Basic Data Analysis

Perform all work in Excel

3. Suppose that an employee starts working after completing her MBA at age 25 at a starting salary of $50,000. She expects an average salary increase of 4% each year. Her retirement plan requires that she contribute 8% of her salary, and her employer adds to her retirement plan an amount equal to 35% of her contribution. She anticipates an annual return of 8% on her retirement portfolio. Compute the total balance in her account at the end of her 50th year by performing the following steps. In all the following steps, make relative or absolute cell references as appropriate; do not type numbers.

i) Type all the available information in an organized manner in the designated Excel worksheet.

ii) Create the following columns Age, Salary, Employee's Contribution, Employer's contribution, Total contribution, Returns, and Balance.

iii) Type the current age in the first cell of Age column.

In the first row of the table (that is, where age = 25);

iv) In Salary column, make a cell reference to current salary (from the data you have typed in part (i)).

v) Compute Employee's Contribution, which is 8% of current salary.

vi) Compute Employer's contribution, which is 35% of the Employee's Contribution.

vii) Compute Total contribution, which is the sum of Employee's Contribution and Employer's contribution.

viii) Leave Returns cell empty for the first year.

ix) Compute Balance, which is the same as Total contribution in the first year.

In the 2nd year (that is age = 26);

x) Increase age by 1 with a formula.

xi) Compute salary, which increases by 4% per year, and follow the above process to compute Employee's Contribution, Employer's contribution, and Total contribution.

xii) Compute Returns, which is previous years Balance times the Annual return on investment.

xiii) Compute Balance, which is the sum of previous years Balance, current years Total contribution, and current years Returns.

For any subsequent rows,

xiv) you should be able to drag down if you have made all cell references correctly.

Step by Step Solution

There are 3 Steps involved in it

Step: 1

blur-text-image

Get Instant Access to Expert-Tailored Solutions

See step-by-step solutions with expert insights and AI powered tools for academic success

Step: 2

blur-text-image_2

Step: 3

blur-text-image_3

Ace Your Homework with AI

Get the answers you need in no time with our AI-driven, step-by-step assistance

Get Started

Recommended Textbook for

Auditing

Authors: Timothy J. Ph.D. Robertson, Jack C.; Louwers

9th Edition

0072906952, 9780072906950

More Books

Students also viewed these Accounting questions