Business Analysis Assignment Solution - Finance Module, Semester 1

This document presents a comprehensive solution to a business analysis assignment focused on personal finance and retirement planning. The solution includes detailed answers to questions concerning retirement contributions, utilizing the PMT function in Excel to calculate future values and monthly payments. The assignment explores different scenarios by varying annual return rates to demonstrate their impact on retirement savings. Furthermore, it analyzes the impact of different contribution rates and retirement timelines. The solution highlights key concepts such as company match and the importance of understanding Excel functions for financial planning. The assignment also includes an annotated bibliography with relevant academic sources, providing a foundation for the analysis. The student demonstrates an understanding of financial concepts, applying them practically to a retirement plan scenario.

Running head: BUSINESS ANALYSIS
Business Analysis
Name of the Student:
Name of the University:
Author’s note:

1BUSINESS ANALYSIS
Table of Contents
Answers:..........................................................................................................................................2
Answer to question 2.e:...............................................................................................................2
Answer to question 3.a:...............................................................................................................2
Answer to question 3.c:...............................................................................................................3
Answer to question 3.d:...............................................................................................................3
Answer to question 3.e:...............................................................................................................3
Answer to question 3.f:................................................................................................................4
Annotated Bibliography:-................................................................................................................6

2BUSINESS ANALYSIS
Answers:
Monthly contribution $574.08 $497.54 $5,970.43
Years until retirement 30
Average annual return 4.00% 5.00% 6% 7% 8% 3% 2% 1.00%
How much will I have at retirement? $218,620.48 $11,951.68 $13,444.10 $11,951.68 $16,654.70 $18,357.86 $10,544.09 $9,227.75 $6,888.96
Years in retirement 30 13.40
How much will that pay me per month? $551.12
Yearly Monthly
Gross Income $45,926.40 $3,827.20
401(k)
My contribution ($) $3,674.11 $306.18
Company match $2,296.32 $191.36
Roth IRA (max 5,500 per year) $918.53 $76.54
Total Contributions to Retirement $6,888.96 $574.08
What % of Income are we contributing to retirement? 13% 15.00%
My Contribution (%) 8%
Company Match 5%
Retirement Account
Retirement Contributions
Answer to question 2.e:
The percentage (%) of income that is being contributed to retirement in B20 is found to
be 15%. With the help of Gross income and total contribution to the retirement, we calculated it.
Answer to question 3.a:
Using PMT function, the amount of money in the account would be $11951.68 according
to the monthly contribution of $574.08, years until retirement 30and average annual return 4%.
The total amount of money would be $218062.48. We assumed that there is no money currently
in the account and payments would be applied at the end of every month.

3BUSINESS ANALYSIS
Answer to question 3.c:
If the average annual return is changed from 1%,2%,3% 4% ,5%, 6%, 7% and 8%
respectively, then amount of money are $6888.96, $9227.75, $10544.09, $11951.68, $13444.10,
$16654.70 and $18357.86. The annual return is found to be $11951.68. This would move me
toward over-saving rather than over-estimating and being caught short retirement.
Answer to question 3.d:
As per PMT function, we calculated the monthly payment value at retirement in cell B5.
It is found to be = $ 218620.48.
Now, we used NPER function to get the years of retirement plan. B20 is already given in
the B20 cell as 13%. We calculated the total contribution at retirement as $497.54 instead of
$574.08. Now, applying the new monthly amount of contribution with respect to the same
amount of yearly interest rate and average annual return, we found the years in retirement 13.4
years.
According to the B20, I am going to work in bank for 13.4 years. Therefore, it could be
said that I want to retire early. If I wanted to have money left at my death to leave behind as an
inheritance, the amount is = $ 218620.48.
The PMT formula changed to NPER formula.
Answer to question 3.e:
Considering the assumptions that has been in the computation of the retirement is the use
of the PMT functions. Additionally, monthly rate has been assumed in deriving the contributions

4BUSINESS ANALYSIS
that has to be made in the retirement plan. On the other hand, the contribution treatment of 15%
has been considered to derive gross income generated from the retirement plan.
Answer to question 3.f:
On analysing the project an important consideration that can be introduced in this context
is that I have learned that a monthly contribution of $574.08 for a span of 30 years at the interest
rate would result in yearly contribution of $6,888.96. Additionally, I have learned that on my
contributions of 8% I can gain a yearly return of 3,674.11 and on the other hand from my
company match I can attain a yearly contribution of 2.296.32. As understood from the
contribution margin I have understood that based on the monthly basis my contribution value
stands $306.18for each month while the company match based on the monthly contribution
stands 191.36.
I have learned that the total amount of the monthly retirement contribution stands 547.08
based on the company match of 5% while my contribution based on my retirement has stood 8%.
As evident from the analysis I have understood that there are certain excel functions that make
the computations easy for me to compute my retirement plan based on the amount of
contribution that would be made by me. Additionally, I have also learned regarding the use of the
PMT functions that have contributed to my learning knowledge and have contributed
significantly to my practical skills.
The retirement plan has provided me significant understanding regarding the future. As
evident from computation retirement plans is considered as the investment plans that lets me to
allocate my portion of savings to get accumulated over the period of time and given that I am
provided with the steady source of income after my retirement. I have learned from the

5BUSINESS ANALYSIS
retirement computation that if though a person has a better amount of savings, a retirement plans
is considered as a vital source of income for an individual. I have realised that savings get
exhausted in no span of time and I have also understood that they are at times used in
emergencies to meet the need of urgency. Hence, computing and investing through careful
means in the retirement plan serves me with secure amount of cash flow for meeting my basic
regular needs following the retirement.
I have understood that on making a constant investment in return the amount grows
manifold times because of the compounding impact that makes lot of difference to my final
savings. A correct computation of the retirement plan would help in planning for retirement in
the phased manner. On practically breaking down the returns based on the yearly and monthly
basis pension plan have been considered as the better mode of investment plans which helps in
ensuring that I have secured life following retirement. Additionally, the use of excel tools have
been help to me in understanding the appropriate calculation tools that are needed in the
computation of the retirement plan. Conclusively, having a right pension plan enables me to plan
for my retirement in a phased manner and simultaneously acts as the saviour in the future period.

6BUSINESS ANALYSIS
Annotated Bibliography:-
Barth, M.E., Beaver, W.H. and Landsman, W.R., 2001. The relevance of the value relevance
literature for financial accounting standard setting: another view. Journal of accounting and
economics, 31(1), pp.77-104.
Chewning Jr, E.G. and McKie, A., 2002. Accounting for asset retirement obligations. The CPA
Journal, 72(5), p.56.
Franzoni, F. and Marin, J.M., 2006. Pension plan funding and stock market efficiency. the
Journal of Finance, 61(2), pp.921-956.
Kotlikoff, L.J., 1995. Generational accounting. NBER Reporter, p.8.
Scott, W.R., 1997. Financial accounting theory (Vol. 2, No. 0, p. 0). Upper Saddle River, NJ:
Prentice hall.

7BUSINESS ANALYSIS