Question

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 year’s Balance times the Annual return on investment.

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

For any subsequent rows,

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