FIN 1011 George Brown College Cash Flow Statement for Brian and Nakita Excel Task Hey I have a project to work on. It consists of 8 Parts. I have to do 2 p

FIN 1011 George Brown College Cash Flow Statement for Brian and Nakita Excel Task Hey I have a project to work on. It consists of 8 Parts. I have to do 2 parts only.

Part 2: Personal Net-Worth Statement (Balance Sheet) and Analysis.

Part 3: Cash Flow Statement and Analysis.

Note: All statements should be completed using a Microsoft Excel Spreadsheet.

There is 2 attachments. Assignment instructions and Assignment Clues.

Please read and understand the assignment instructions file. The assignment clues has great tips and clues to do the assignment. 1
Assignment
Fundamentals of Financial Planning
FIN1011
General information
It is your task to review the case study provided and from it develop the items required
and also expand upon these items with analysis and resolutions you feel will help the
client. You can enhance your paper with the use of graphs, statistical data, articles, tables,
diagrams; whatever works to make your case stronger. All outside materials must be
documented with the MLA standard is the applicable methodology.
To be truly successful on this assignment it is important to start early and work together
as a group. Each portion is dependent upon each other’s member’s portion to arrive at a
cohesive plan. Calculations for the specific requirements are used to build upon and
correlate with other requirements. This also means that you should review the case study
early so as to assemble any questions you may have for the professor. I am sure the
professor will be less than impressed if questions are asked just a few days before the
assignment is due, especially as he will be unavailable the week before the assignment is
due. There is room for a truly successful paper. Included in the case study are questions
asked that add room for extra thought, additional ideas, and insightful recommendations all resulting in opportunities for additional marks.
Rules and Regulations
1 There is a maximum limit of 12 pages for this paper including the cover page. The
covering page must contain the names and of all the people in your group. A
“Table of Contents” is not necessary. It must include “Sources of Information”
(Bibliography). The bibliography and any Appendixes are in addition to the 12
page limit and do not count in the page total. Use 1 ½ line spacing.
2
Due Date: Due at the start of the class on the due date. Assignments received
after that point will be penalized 20% of the potential marks available for
each academic day that assignments are late. No extensions will be extended.
3
If your participation deserves a grade of “A” or even “A+”, I will do my best to
see that it is delivered. If you deserve an F for non-participation or sloppy work, I
will also see that it is recorded as such. The professor is not averse to giving good
marks to some students in a group and a mark much lower for those in the same
group who do not add value.
4
Assignment labs will be held in the last half hour or so of class beginning in week
3. I will discuss a specific section of the assignment and you are welcome to raise
any questions you may have on the assignment.
5
The specific requirements of the assignment are on the last page of the case study.
2
Case Study for Spring/Summer Term 2020 – Fin1011
Design and Compile a Financial Plan
for Nakita and Brian Potter
Due Date: 8:00 A.M. Wednesday, July 29th, 2020
(No extensions)
Case Study Assignment has 8 pages in total. Make sure you have all the required pages
Supplemental Information sheets with information to help complete this assignment are
contained on Blackboard under Course Content – “Assignment Clues” file.
Best of luck.
3
Case Study – Nakita and Brian Potter
You and Nakita had attended the same high school many years ago and knew each other
casually. You ran into her at a benefit dinner last week and she introduced you to her
husband Brian, whom she married three years ago. She was impressed to find that you
were now a financial planner with the firm of Princeton Financial Planners Inc. She
had mentioned that she and Brian would like someone to look at their financial affairs
and you offered to complete a comprehensive financial plan. She informed you that she is
also having financial plans completed by competing firms so it is important that you
provide a plan that is sufficient to win you their business. You met with Nakita and Brian
a few days later (week three of this course) to obtain the particulars about the state of
their finances and told them you would provide a detailed financial plan within the next
two months.
Brian Potter is a 47-year old entrepreneur and the sole owner of “Complete Brand
Consulting Corporation”, an extremely successful small Canadian qualified corporation.
He has operated the business successfully for the past 15 years and has a salary of
$92,000 per annum (each year). He feels the business is worth $415,000 if it were sold
today, and has estimated that the net worth of the business will grow by an average of 8%
a year. He would like any ideas you have regarding tax planning and the benefits he
could receive if he sold the business at retirement.
Brian has two children from his previous marriage; Norm who is 4 years old and Krista
who is 6. They live with their mother Marie, Brian’s estranged first wife. Brian pays
Marie $550 a month in alimony payments and a monthly total of $1,200 in child support
payments. He will cover 50% of the children’s 4 years of college/university education,
which currently costs $8,000 a year, and these costs are estimated to grow at 5% per year.
Both children will be starting university/college at age 18 (14 and 12 years respectively
from now). He would like to know more about ways to fund these costs. He was also told
by a friend that child support payments are no longer tax deductible and has asked you to
review this for him and give him the correct answer.
Nakita, is 35 years old and works at State Capital Bank as a Marketing Manager. She
earns $70,000 a year and is a member of her company’s defined benefit pension plan. She
has been with the bank for five years and is quite comfortable in her position and feels it
is quite secure. Nakita and Brian want to start a family over the next couple of years and
feel they will need to save $6,000 a year for future expenses.
The Potter’s purchased their home in mid-August 2015 for $440,000 and at that time
obtained a fixed-rate mortgage of $325,000 at 3.5% (5 year term, monthly pay, 25-year
amortization), renewal date: August 15th, 2020. They pay $1,622.63 a month in principal
and interest payments and when the mortgage renews at that time the principal owing will
be at $280,410. The principal owing on their last statement was $283.629. They have
talked to one bank and have been given the following choices for the renewal of their
mortgage.
4
Term
1 year
3 year
5 year
5 year
Type
Fixed/closed
Fixed/closed
Fixed/closed
Variable/open
Monthly
Rate
Payment
3.5%
$1,285
3.9%
$1,330
4.25%
$1,370
prime – .50%* $1,203
Amortization
20 years
20 years
20 years
20 years
* The variable mortgage rate fluctuates with the bank prime rate so that it is always .50%
(half a percent) less than the prime rate. The bank prime rate is currently 3.75%. All the
above allow 20% of the original balance to be paid on each anniversary date (once a
year). Note: For this paper use the rates listed, do not shop for other rates.
A real estate salesperson has told them that their house is now worth $485,000 and should
grow in value with the expected rate of inflation of 3%. Forty percent of the home’s value
is considered due to the worth of the land and 60% is the replacement cost of the building
structure. Brian and Nakita want your opinion on which mortgage is best for them, why
you suggest this is the best mortgage for them, and how this would affect their budget.
Nakita and Brian have unused RRSP contribution room of $30,000 and $70,000
respectively. Brian will continue to make the $20,000 contribution (as a lump sum in
February) each year and Nakita will continue her monthly contributions at the same rate
as this year.
Nakita gets some great benefits from working at State Capital Bank such as no
transaction fees on her bank accounts, lots of benefits (80% dental coverage, family plan
included), life insurance equal to one times her salary, and disability insurance to 70% of
her take home salary. Brian does not have any health benefits, life or disability insurance.
Nakita is a member of a non-contributory defined benefit pension plan that will pay her a
pension at age 65 of 1.5% of the average of her last 5 years of salary, times the number of
years she works with the bank. Nakita plans on retiring at age 55. If she takes her
retirement benefits earlier than age 65 she losses 5% per year. She expects her salary to
keep pace with inflation estimated to be 2%.
Nakita contributes $5,400 each year to her self-directed RRSP and will continue to do so
until she retires. She would be happy with a yearly return of 6%. Brian is a risk taker and
feels long-term he would like to see a return of 8% on his investments.
5
The Potter family lives well at the moment. Their current expenses are:
Brian
$ 2,000/year
Clothing
Fitness Club
Hair cuts/grooming $
Dining Out & lunches $
Property taxes
Property Insurance
Utilities
Telephone
Cable TV
Groceries
Cleaning service
Home repairs
Vacations
30/month
150/week
Nakita
Joint
$ 2,400/year
$ 110/month
$
90/month
$
30/week
$ 2,200/year
$ 480/year
$ 325/month
$
80/month
$ 100/month
$ 500/month
$ 90/month
$ 900/year
$ 6,600/year
Brian has a new Chrysler 300 (less than a year old) car purchased by his company for
$55,000 and used mostly for business. The lease costs $800 a month, insurance is another
$1,400 a year and gas cost $90 a week. It has a full bumper-to-bumper warranty so there
are no repair charges. He feels its resale value is around $42,000 today. He has
$200,000 in liability insurance, full collision and comprehensive insurance coverage.
Nakita has a low mileage 2010 Honda Civic that is in great condition and runs well. It is
painted orange so has a low resale value of $1,300 but it is paid in full. She uses the car
sparingly and spends $50 a month on gas. It costs $180 a year for repairs, $48 a year for
licensing and another $500 a year for insurance (she has $1,000,000 liability, full
collision and comprehensive with a deductible of $500). She really does not want to sell
the car but has asked you if she should.
Nakita has $390 outstanding on her VISA while Brian has $1,750 outstanding on his
VISA (Brian’s VISA is a company card). The monthly minimum payment on each card if
not paid in full each month is 5% per of the outstanding balance.
The Potter’s tax returns for last year contained the following:
Employment
CPP Premium
EI Premiums
RRSP Contributions
Charitable Donations
Alimony
Taxes
Brian
$ 92,000
2,749
20,000*
500
6,600
24,173.75
Nakita
$ 70,000
2,749
860
5,400
400
17364.62
*Note; He made his RRSP contribution as a lump sum payment in February of this year.
6
The following most recent statements (from the previous month) were also provided.
Credit Union Chequing acct
State Capital Savings acct (.25%)
State Capital Chequing acct
CIBC Canadian Balanced Fund-RRSP (5.6%)*
CIBC Canadian Equity Fund-RRSP
(7.3%)*
CIBC Canadian Resources Fund-RRSP (.34%)*
CIBC Money Market fund-RRSP
(.58%)*
State Capital GIC’s – RRSP (1.5% interest)
Balance owing on Mortgage
*
Brian
$ 7,000
Nakita
Joint
$ 6,200
$ 6,000
3,000
6,500
12,000
28,000
7,500
12,000
283,629
RRSP average annual returns over the last ten years.
Nakita wants to retire when she is 55 years old. Brian said he will retire at the same time
as Nakita. Nakita is in very good health and expects to live until the age of 90 as does
Brian.
Brian is adamant that he will pay 50% of the children’s education costs and both he and
Nakita feel that is their foremost priority. Retirement is also extremely important. The
Potter’s want to know how much money they will need to fund their retirement and how
they can budget for this eventuality. Brian, if needed, is prepared to sell the business or to
take a salary as an absentee owner but would like your opinion on what the options are
available, and especially if there are any taxation considerations if he sold the business.
You, Nakita and Brian agree on some assumptions:
• Brian expects an 8% return on his investments and Nakita expects 6%.
• Nakita is in the 35% marginal tax bracket and Brian is the 42% marginal tax
bracket.
• The expected rate of inflation should remain relatively constant at 2%.
• Both Nakita and Brian would be pleased if you can elaborate on any other
additional issues about their financial welfare that you can come up with from the
information given.
7
Specific Requirements
Prepare an almost complete comprehensive financial plan for Nakita and Brian Potter.
The plan must be prepared in a professional manner and at a minimum include the items
listed below in the order as shown:
Part 1
Part 2
Part 3
Part 4
Part 5
Part 6
Part 7
Part 8
Executive Summary
Personal Net-Worth Statement (Balance Sheet) and Analysis
Cash Flow Statement and Analysis
Education Plan for their children
Detailed Insurance Plan
Revised Budget
Recommendations and Summary
Work Allocation Schedule
Note: There are other items the Potter family has requested advice about and you will be
awarded bonus marks for providing the answers. Make sure reasoning accompanies your
suggestions.
•
•
•
•
•
•
What are Brian’s options if he wants to sell or be an absentee manager for his
business at retirement time? Are there any special tax considerations that may
lead him to choose one option over the other?
What mortgage type and rate should the Potter family take? Do an internet
search and get a viable and recognized source to support you suggestion.
Should Nakita sell the car or keep it?
Are the child support payments tax deductible?
Are the Potter family’s investment well positioned or should they make some
changes?
What will their retirement look like using your proposed budget?
Marks will be deducted for plans that are not presented in a professional manner. The
plan must be typed and all text is to be 12 point type and 1 ½ line spacing.
You are being graded on quality not quantity so try to keep your financial plan directed
towards meeting the needs of the Potter family and address their goals and their questions
as asked in the case study.
Each part should contain an analysis portion along with the actual specified item. As an
example; Cash Flow statements should provide insight for recommendations to acquire
the funds to accomplish their goals and identify areas of excessive spending.
Aside from the mortgage question, most of the materials required to complete the
assignment and get a great mark are included in the textbook. To adequately complete
some sections you will be required to read ahead in the text to gain the knowledge
necessary to complete that section (i.e. insurance).
1
The Complete Composition of Assignment Clues for Developing
the Comprehensive Financial Plan
It seems a taunting task to develop a financial plan, but the truth is it just takes some effort and a
little time. This paper includes the material you will require to complete the project and will give
you direction so that you can develop a plan that is of a high caliber. There are eight major
sections and additional sections can be added for the more adventurous sorts. The expectations of
each section along with some helpful hints are listed under each individual heading.
Getting Started
Assembling the Group
➢ Choose a name for your group (example, “Big Dog Financial Emporium”
➢ Send a note on Blackboard to the teacher with the group name and the member’s names
as listed on Blackboard.
➢ Obtain the contact information of all team members
➢ The teacher will then set up the team on the discussion boards on WebCT where all the
work is posted
Get Familiar with the Case Study and Start Posting Work
➢ Read the Case Study to familiarize yourself with the client’s situation and goals.
Highlight those items you feel are of importance
➢ Read the Case Study Assignment Clues document and attempt one of the sections
➢ Post your work as an attachment and ask the other team members for a review
Moving the Work Along
➢ Once the work has been posted another member of the team must review the posted work
and improve it significantly. Once the improvement has been posted and is deemed to be
of significant value the teacher will post his or her comments with helpful hints about
how to improve that section. If an significant improvement is not made then no
comments from the teacher will be made
The group once listed on the discussion boards will have a site that is confidential among its
members and the instructor.
2
Table of Contents
Section
Student Marking Key
Page
3
Executive Summary
5
Net Worth and Cash Flow Statements
7
Statement Analysis
8
Education Savings Plan Calculations
11
Education Savings Plan Programs
13
Insurance
18
Mortgage
22
Retirement
23
Monthly Budget
25
Recommendations (and summary)
26
Bonus Questions (specific client inquiries)
26
Investment Allocations
27
3
Student Marking Key
The student marking key is as below and there are a possible 128 marks available (so it is
possible to get a mark higher than 100%).
Assignment Review – FIN 1011 Financial Products and Services
Group Name: ____________________________________
Group Members:
___________________________________
___________________________________
___________________________________
___________________________________
Notice that the above has a section for each member as in many cases each member will
receive a mark that varies substantially from others and is based upon each member’s
contribution.
Spelling, grammar and composition
/20
Comments:____________________________________________________________________
______________________________________________________________________________
______________________________________________________________________________
______________________________________________________________________________
________________________________________________
Content
Executive Summary
/10
Net Worth and Cash Flow Statements
/10
Statement Analysis for Net Worth and Cash Flow
/10
Education Plan
/15
Detailed Insurance Plan
/15
Revised Budget
/10
Recommendations and Summary
/10
4
Bonus Material:
Retirement planning
Mortgage recommendation
Amanda’s car
Investment allocations
Selling of the Business (and taxation)
Child Support Query
Total
up to 10
up to 5
up to 3
up to 5
up to 3
up to 2
/100 – 128
5
Executive Summary
(Making it Work)
The Executive Summary (ES) in a financial plan serves a few purposes. It acts as;
1) A sales piece to immediately grab the attention of the perspective client and get them to
read the entire plan.
2) A document to enhance the professionalism of your firm; you and your team as financial
advisors. It is the first detailed written presentation showcasing your abilities and is a
large determinant factor in the client trusting in your abilities.
It is apparent that a strong executive summary is important to both the financial planning and
sales efforts.
The ES is written in a format that speaks directly to the client. It is a personalized letter using the
client’s names (in this case an address is not required). Do not use the client’s name in the third
person. Example; If the client’s name for argument sake is Jonathan and Marta Deiter use
“Jonathan and Marta” (this is written in the first person), as opposed to the “Deiter’s” (which is
referring to the client in the third person; “Your goals” as opposed to “The Deiter’s goals”, etc.
Use their first names throughout the entire paper, they will after all be in your office when you
give the presentation and you went to school with her. Writing in the first person is the same as if
you are talking to that person. In essence you are talking to them when you write in the first
person and are talking about them when writing in the third person.
Form of the Executive Summary
The ES is written in the form of a business letter. It includes the name (both if a couple) and the
standard salutation (addressed to both of them and including their last name). It should be dated
…
Purchase answer to see full
attachment