AutoSave OFF H 5 + 5 = wa Douglas Case rev 9_7_21(2) (1) Home Insert Draw Design Layout References Mailings Review View Tell me Share Q Comments X LO Calibri (Bo... V 11 21 AaBbcDdEe AaBbCcDdEe AaBb CcD AaBbccdde AaBb AaBbceDd Ee ' Aa A x APA AaBbCcDdE Heading 2 Paste B I Normal U v alb E Heading 1 No Spacing Title X Heading 3 Subtitle Styles Dictate Pane General instructions Please read through the questions below and solve each requirement using the MS Excel workbook. The workbook includes a worksheet for each requirement (Question 1 Personal Balance Sheet, questions 2,3,4 Ratios, Question 5 Common Stock Portfolio, etc.). Question 1. Mary understands that although the family doesn't maintain a formal accounting system to be able to produce a personal balance sheet, they do have records from which such a statement can be prepared. Mary knows that periodically, it would be wise to have a balance sheet that is up to date as the family may need it for a loan application, possibly for some investment opportunities, and for developing plans such as a personal financial plan, an estate plan or a formal retirement plan. According to AICPA's Statement of Position 82-1 Accounting and Financial Reporting for Personal Financial Statements, personal financial statements should present assets at their estimated current values and liabilities at their estimated current amounts. The estimated current value of an asset in personal financial statements is the amount at which the item could be exchanged between a buyer and seller, each of whom is well informed and willing, and neither of whom is compelled to buy or sell. 3 What is the family's net worth? Please prepare a personal balance sheet. The balance sheet "as of date" should be Dec. 31, 2020. PLEASE NOTE: Because the family owns several common stock investments, it might be helpful to review question 5 (below) and complete the question 5 worksheet in the Excel workbook and then enter the appropriate balance sheet value from that portfolio report. Page 3 of 8 2 of 3336 words IX English (United States) O Focus E B - + 143% AutoSave OFF H 5 + 5 = wa Douglas Case rev 9_7_21(2) (1) Home Insert Draw Design Layout References Mailings Review View Tell me Share Q Comments X LO Calibri (Bo... V 11 ' Aa 21 AaBbDdEe AaBbCcDdEe AaBbccdde AaBb AaBbceDd Ee AaBb CcD Heading 1 AaBbCcDdE Heading 2 Paste B I x Normal U v alb APA No Spacing X Title Heading 3 Subtitle Styles Dictate Pane Question 2. The current ratio is a measure of short-term debt burden. A current ratio of 2 indicates that for every $2 of liquid assets there is $1 of current liabilities (a current ratio = 2). A high current ratio indicates the ability to meet short-term obligations. What is the current ratio on December 31st for the Douglas family? Question 3. Debt burden is something that every family should monitor. One ratio is a debt ratio (liabilities/assets). In recent years, the Douglas family's debt ratio was running around 40%. What is their current debt ratio? Question 4. The liquidity ratio indicates the number of months that living expenses can be paid from liquid assets if an emergency arises (such as any event that would result in a serious decrease in income) If the family's monthly living expenses are about $6,000, what is their liquidity ratio? Question 5. The family's stock portfolio is in an investment account. John and Mary log into the account periodically, to see how their investments are performing. Typically, common stocks by sectors such as: . . . Communication Services Consumer Discretionary Consumer Staples Energy Financial Health Care Industrials Information Technology Materials Real Estate Utilities . Within the sectors, there are industries. Here is a summary of the family's portfolio in terms of sectors and industry: Page 4 of 8 2 of 3336 words X English (United States) O Focus E - 143% AutoSave OFF H 5 + 5 = wa Douglas Case rev 9_7_21(2) (1) Home Insert Draw Design Layout References Mailings Review View Tell me Share Q Comments X LO Calibri (Bo... V 11 ' Aav . = 21 AaBbcDdEe AaBbCcDdEe AaBbCcDdE AaBbccdded AaBb AaBb CeDd Ee AaBb CcD Heading 1 Paste B I x Normal APA U v alb 19 No Spacing X Heading 2 Title Heading 3 Subtitle Styles Dictate Pane worksheet. Here are some steps to follow: A. First by growing the cost of education by 4% per year (12 years into the future for Paul and 11 years for Marcy) and then calculating the present value of those future cash flows. Keep in mind that Paul will be going to school for 4 years and so too will Marcy. So, you will need to figure the future value of the cost of education for the first year, second year, third year, and fourth year - each year 4% more costly than the year before! B. Calculate the present value of the future costs of education using the investment yield prediction (8% - see below and notice in the data above that the investment fund has averaged 8% per year). 5 C. Once you have the present value of the future costs - you can subtract the current balance in these accounts to derive the "donation" that the grandparents will need to make.) Assume that the investment will grow at 8% per year as a result of investment yields. How much (in total) must the grandparents invest today to establish the education fund for Paul and Marcy? NOTE: There is more on how to approach solving question 6 at the end of this document. Question 7. Calculate the percentage of net worth represented by the home and the two other largest assets. Consider any loans attributed to those assets so that you show the following - the asset's net value/Total family net worth. Count both cars as one asset (Automobiles). Page 5 of 8 2 of 3336 words IX English (United States) O Focus E B E - + 143% AutoSave OFF H 5 + 5 = wa Douglas Case rev 9_7_21(2) (1) Home Insert Draw Design Layout References Mailings Review View Tell me Share Q Comments X LO Calibri (Bo... V 11 ' Aa to 21 AaBbcDdEe AaBbCcDdEe AaBbccdded AaBb AaBb CeDd Ee AaBb CcD Heading 1 AaBbCcDdE Heading 2 Paste B I Normal x U v alb Title No Spacing Subtitle X ALA Heading 3 Styles Dictate Pane This is called the "dominant" asset - other words, if a family is "car rich" then that would mean a significant percentage of their wealth (as defined by net worth) would be from the value of its cars. The cars would be the dominant asset. If the family's net worth is mostly from their retirement accounts, then we could say that a significant amount of their wealth is from pensions. Question 8. Jeff is considering applying for a home equity loan to finance the basement project. What is the maximum home equity loan the Douglas' could possibly get based only on equity (and ignoring cash flow considerations)? Assume that a bank is willing to loan up to 80% of the value of the home (between the mortgage and the home equity loan). Question 9. Home equity loans have many advantages. For example, the home equity loan may be the source of funds to help Jeff and Mary finish their basement. The interest on the loan will be tax deductible. The arrangement that Jeff and Mary are looking at will involve a 15-year payback period and would allow for them to draw down on any unused funds in a credit line arrangement. Please describe two disadvantages of a home equity loan and recommend an amount that Jeff and Mary should request for their line of credit. Question 10. Jeff and Mary have discussed the need to life insurance and one of their goals is to have a sufficient amount of coverage in the event of an untimely death. Part of the reason for maintaining life insurance coverage is the replacement of earnings and to provide funds for funeral and burial. Jeff's salary is $70,000 per year and Mary makes about $40,000. Funeral and burial expenses average $10,000 a year, according to personal finance articles. The couple also agrees that death benefit proceeds should be sufficient to pay off debts such as the balances owed for credit cards, vacation loans, and home improvement debt. Easy Method - a quick and easy method that assumes the life insurance will cover 70% of 7 years of the deceased person's annual income. DINK method - DINK is an acronym for DINK stands for double income, no kids. Since Jeff and Mary do have two young children, the DINK method's results may not be appropriate however, please calculate the life insurance need under this method as another point of information. DINK assumes that life insurance coverage is adequate if it covers funeral expenses and one half of the family debts upon an untimely death of one of the "bread winners." Page 6 of 8 2 of 3336 words X English (United States) O Focus E B E - 143% AutoSave OFF H 5 + 5 = wa Douglas Case rev 9_7_21(2) (1) Home Insert Draw Design Layout References Mailings Review View Tell me Share Q Comments X LO Calibri (Bo... V 11 E 21 AaBbcDdEe AaBbCcDdEe AaBb CcDdE AaBbccdde AaBb AaBbceDd Ee ' Aa A x ALA AaBb CcD Heading 1 Paste B I Normal E U v alb X Title No Spacing Subtitle = Heading 2 Heading 3 Styles Dictate Pane Family Need Method - a more detailed method that calculates life insurance needs by considering assets (such as checking and savings accounts and investments) and assuming that life insurance and liquid assets will cover estimated funeral costs and the future financial needs of dependents. For the Family Need method, assume that living expenses are 70% of their income (you need this to estimate an emergency fund). Be conservative and estimate an emergency fund of 6 months of living expenses. Keep in mind that when using the "Family Need" method, you have to run the analysis twice - once based on the what-if assumption that Jeff dies now and once based on the what-if assumption that Mary dies now. Assume that the retirement account on the balance sheet is 90% Jeff's funds and 10% Mary's. Ignore social security death benefits. Also, the assumption should be that the Mutual Fund investment should not be considered as a liquid asset because it is earmarked for the children's education and that also means you do not have to consider an amount for the children's education as the mutual fund will take care of that (assumes that the grandparents will make the gift (see question 6). What do you recommend for death benefit amounts for each person (Jeff and Mary)? What is your rationale for the amount of coverage you recommend? Question 11. Pretend you are a personal financial planning professional attempting to devise a comprehensive personal financial plan for Jeff and Mary. What other areas of Jeff and Mary's personal financial situation should be examined and what observations can you make about their current situation? Please mention at least 2 other concepts that they should take a careful look at. Please write a few paragraphs that answer this question. Feel free to pose additional questions that you would like to ask Jeff and Mary as a way of making sure you, as a financial planner, understand their situation. More Notes Regarding Requirement 6 of the Douglas Personal Financial Planning Case These steps and the worksheet below might help you solve requirement 6. I find it easier to set this type of thing up within an Excel worksheet and I like to utilize the FV and PV functions of Excel. However, you can also do this with the time value of money tables within the text or with a financial calculator. It's up to you. In any event, please show your work - how you derived your final answer. Page 7 of 8 2 of 3336 words EX English (United States) O Focus E B - + 143% AutoSave OFF DES 5- Douglas Case(2) Q Home Insert Draw Page Layout Formulas Data Review View Acrobat Tell me Share Comments Calibri Body v # Insert v = = LG General LIH 14 ~ A ar Av A 28- 0 0 DX Delete v PX Paste I U V V == == v $ % ) ES 70 Conditional Format Cell Formatting as Table Styles Format Sort & Filter Find & Select Analyze Data Create and Share Adobe PDF A2 - x fx Enter Name of Family Here D D E F G G H I J K L . N 0 P Q R S T T $0.00 $0.00 B 1 1 Personal Balance Sheet 2 2 Enter Name of Family Here 3 As of date: 4 Assets 5 Liquid assets 6 Checking account balance 7 Savings/money market accounts, funds 8 Cash value of life insurance 9 Other 10 Total liquid assets 11 Household assets and possessions 12 Current market value of home 13 Market value of automobiles 14 Furniture 15 Computer, electronics, camera 16 Jewelry 17 Other 18 Other 19 Total household assets 20 Investment assets 21 Savings certificates 22 Stocks and bonds 23 Retirement accounts 24 Mutual funds 25 Other 26 Total investment assets 27 Total assets 28 28 29 Liabilities 30 Current liabilities 31 Charge account and credit card balances 32 Loan balance (Cars) ( 33 Vacation Loan 34 Other 35 Total current liabilities 36 Long-term liabilities 37 Mortgage 38 Home Improvement Loan 39 Total long-term liabilities 40 Total liabilities 41 Net Worth 42 43 $0.00 $0.00 Carleans $0.00 $0.00 $0.00 $0.00 Q1 Personal Balance Sheet Questions 2, 3, &4 Question 5 Question 6 Question 7 Question 8 and 9 Question 10 Question 11 Ready 91% AutoSave OFF ES5= Douglas Case(2) Q Home Insert Draw Page Layout Formulas Data Review View Acrobat Tell me Share Comments Calibri (Body) v 12 LG Insert v . = = ab Currency H Ayu Or 0 DX Delete v PX Paste BI MA V $ %) EM V Conditional Format Cell Formatting as Table Styles 70 Format v Sort & Filter Find & Select Analyze Data Create and Share Adobe PDF B3 x fx B D D E F G H J L M N O 0 P P Q R S Times Times 1 1 2 2. Current Ratio 3 Liquid Assets 4 Current Liabilities 5 Current Ratio 6 7 8 3. Debt Ratio 9 Liabilities 10 Assets 11 Debt Ratio 12 13 14 4. Liquidity Ratio 15 Liquid Assets 16 Monthly Living Expenses 17 Liquidity Ratio 18 19 20 21 - 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 Q1 Personal Balance Sheet Questions 2, 3, 84 Question 5 Question 6 Question 7 Question 8 and 9 Question 10 Question 11 + Ready PH P 100% AutoSave OFF DES 5- Douglas Case(2) Q Home Insert Draw Page Layout Formulas Data Review View Acrobat Tell me Share Comments Calibri (Body) 12 v . = = LG # Insert v ab Date LIH Ayu Or DX Delete v Paste BI av A V V == == == $ % ) to 70 Conditional Format Cell Formatting as Table Styles Format v Sort & Filter Find & Select Analyze Data Create and Share Adobe PDF A B2 fx B B D E F F G . J K L M N o P Q R S T A i Stock Portfolio 2 2 As of Date: Number of Shares Cost Basis per Share Total Cost Basis Market Value Per Total Market Share Value Gain or Loss 3 Company Name Stock Symbol 4 Apple AAPL 200 $100 $20,000 $150| $30,000 $10,000 Sector Industry Technology Information Hardware, Storage & , Technology Peripherals Diversified Communication Telecommunication Services Services Information Technology Software Communication Interactive Media & Services Services 5 AT&T T 6 Microsoft MSFT 7 Alphabet GOOGL 8 Totals $20,000 $30,000 $10,000 9 What are your comments about the family's investment portfolio? Please keep in mind that their investment portfolio currently consists of 10 common stocks. 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 22 26 27 28 29 Q1 Personal Balance Sheet Questions 2, 3, &4 Question 5 Question 6 Question 7 Question 8 and 9 Question 10 Question 11 + Ready P 100% AutoSave OFF BESU- Douglas Case(2) Q Home Insert Draw Page Layout Formulas Data Review View Acrobat Tell me Share Comments Calibri v ab Insert v 11 ' LG Currency FO 1 === E D. DX Delete v PX Paste BI MA V $ % ) EM V Conditional Format Cell Formatting as Table Styles 70 Format Sort & Filter Find & Select Analyze Data Create and Share Adobe PDF B22 X fx E F G H I T J K L L M . N 0 O P R S A B C D 1 College Fund 2 Current cost of 1 year of college $25,000 3 Projected annual inflation of education costs 4% per year 4 Annual return on college investment fund 8% per year 5 6 Paul's projections Years (end Future Value of Education Present value of of year) Cost (4%) Education Costs (using 8% % 7 as the discount rate) 8 12 $40,026 $15,894.80 9 10 131 $41,627 $$15,306.10 11 12 12 14 $43,292 $14,739.21 13 14 15 151 $45,024 $14,193.31 16 17 Total $60,133.42 18 19 20 Marcy's projections Present value of Years (end Future Value of Education of year) Cost (49) Education Costs (using 8% % 21 as the discount rate) 22 111 23 12 13 25 14 26 Total $0.00 27 28 Total Present Value $60,133.42 29 Less amount already invested 30 Total Amount to be Contributed today $60,133.42 31 32 33 34 35 24 Q1 Personal Balance Sheet Questions 2, 3, 84 Question 5 Question 6 Question 7 Question 8 and 9 Question 10 Question 11 + Ready P 100% AutoSave OFF DES 5- Douglas Case(2) Q Home Insert Draw Page Layout Formulas Data Review View Acrobat Tell me Share Comments Calibri (Body) v 12 Insert v . = = X LG ab General TH 28 O FO DX Delete v PX v Paste BI av A. V V F = $ %) to 70 Conditional Format Cell Formatting as Table Styles Format Sort & Filter Find & Select Analyze Data Create and Share Adobe PDF A18 X fix F L M N o P Q R A D E H J 1 7. Calculate the percentage of net worth represented by the home and the three other largest assets. Consider any loans attributed to those assets so that 2 you show the following - the asset's net value/Total family net worth. If you consider the cars as one of their largest (top 3) assets, count both cars as one 3 asset (Automobiles). The net asset value = Asset Market Value less Related Loan (s). 4 5 This is called the "dominant" asset - other words, if a family is "car rich" then that would mean a significant percentage of their wealth (as defined by net 6 worth) would be from the value of its cars. The cars would be the dominant asset. If the family's net worth is mostly from their retirement accounts, then 7 8 we could say that a significant amount of their wealth is from pensions. 9 10 11 12 13 14 Net Worth $0 (from the balance sheet) 15 Related Loan(s) Percentage of 16 Asset Asset Value Amount Net Asset Value Net Worth 17 Home 0% 0 18 0% 19 12 0% 20 - 0% 21 $0 $0 $0 0% 22 22 23 22 24 24 25 26 20 2 27 20 28 23 29 30 30 31 22 32 22 22 33 + 34 33 35 30 36 37 38 39 40 Q1 Personal Balance Sheet Questions 2, 3, &4 Question 5 Question 6 Question 7 Question 8 and 9 Question 10 Ready 21 Total Question 11 + 100% AutoSave OFF DES 5- Douglas Case(2) Q Home Insert Draw Page Layout Formulas Data Review View Acrobat Tell me Share Comments Calibri (Body) v 12 LG Insert v . = = ab Currency FO PH 28 Ow DX Delete v Paste BI ar A v V V == == $ % ) to20 0 Conditional Format Formatting as Table Cell Styles Format v Sort & Filter Find & Select Analyze Data Create and Share Adobe PDF B3 fx A B D E E F F G H J J K L M N O P Q Jeff Mary $0 $0 $0 $0 John $10,000 Mary $10,000 $10,000 $10,000 $10,000 $10,000 Jeff Mary 1 2 EASY Method 3 Current Income (salaries) ( 4 4 Insurance need 5 Current Life Insurance Coverage 6 Additional Life Insurance Needed (EASY METHOD) 7 8 DINK Method = 9 Funeral Expenses 10 One-half of mortgage 11 One-half of auto loan - 12 One-half of credit card balance - Other Debts 13 Other Debts 14 Insurance Needed 15 Current Life Insurance Coverage 16 Additional Life Insurance Needed (DINK Method) 17 18 Family Need Method 19 What They Have Now: : 20 Bank Accounts (Checking) Investments, Savings, Portion of Deceased's Retirement Account 21 (not including home) 22 Life Life Insurance Policies (Death Benefit) 23 Total 24 What Dependents Need 2 25 Funeral Expenses 26 Mortgage and Loans 27 Credit Card Debt 28 Subtotal 29 Living Expenses 75% of family income x Number of Years dependents will need it 30 (assume 15 years) 31 Emergency Fund 32 Total of what the dependents will need 33 Additional Life Insurance Needed (Family Need Method) 34 35 Most Coverage required from the three methods 36 $0 $0 $10,000 $10,000 $10,000 $10,000 $ $0 $10,000 $0 $10,000 $10,000 $10,000 What do you recommend for death benefit amounts for each person (Jeff and Mary)? What is Q1 Personal Balance Sheet Questions 2, 3, &4 Question 5 Ready Question 6 Question 7 Question 8 and 9 Question 10 Question 11 + P 100% AutoSave OFF DES 5- Douglas Case(2) Q Home Insert Draw Page Layout Formulas Data Review View Acrobat Tell me Share Comments Insert v v v Ayu Or 0 DX Delete v PX EM V 70 Format v Sort & Filter Find & Select Analyze Data Create and Share Adobe PDF 0 P Q R S T V X Calibri (Body) 12 ' ab General PH LG Paste BI av A A E $ %) ES Conditional Format Cell Formatting as Table Styles A8 - X fx What other areas of the family's personal financial situation should be examined? A B D E G G H H J M N 1 11. Pretend you are a personal financial planning professional attempting to devise a comprehensive personal financial plan for Jeff and Mary. 2 What other areas of Jeff and Mary's personal financial situation should be examined and what observations can you make about there current 3 4 situation? Please mention at least 2 other concepts that they should take a careful look at. Please write a few paragraphs that answer this question. Feel free to pose additional questions that you would like to ask Jeff and Mary as a way of making sure you, as a financial planner, understand their situation. 7 8 What other areas of the family's personal financial situation should be examined? 9 10 11 12 13 14 15 16 17 18 Observations about the current situation. 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 Q1 Personal Balance Sheet Questions 2, 3, &4 Question 5 Question 6 Question 7 Question 8 and 9 Question 10 Question 11 + Ready 100% AutoSave OFF H 5 + 5 = wa Douglas Case rev 9_7_21(2) (1) Home Insert Draw Design Layout References Mailings Review View Tell me Share Q Comments X LO Calibri (Bo... V 11 21 AaBbcDdEe AaBbCcDdEe AaBb CcD AaBbccdde AaBb AaBbceDd Ee ' Aa A x APA AaBbCcDdE Heading 2 Paste B I Normal U v alb E Heading 1 No Spacing Title X Heading 3 Subtitle Styles Dictate Pane General instructions Please read through the questions below and solve each requirement using the MS Excel workbook. The workbook includes a worksheet for each requirement (Question 1 Personal Balance Sheet, questions 2,3,4 Ratios, Question 5 Common Stock Portfolio, etc.). Question 1. Mary understands that although the family doesn't maintain a formal accounting system to be able to produce a personal balance sheet, they do have records from which such a statement can be prepared. Mary knows that periodically, it would be wise to have a balance sheet that is up to date as the family may need it for a loan application, possibly for some investment opportunities, and for developing plans such as a personal financial plan, an estate plan or a formal retirement plan. According to AICPA's Statement of Position 82-1 Accounting and Financial Reporting for Personal Financial Statements, personal financial statements should present assets at their estimated current values and liabilities at their estimated current amounts. The estimated current value of an asset in personal financial statements is the amount at which the item could be exchanged between a buyer and seller, each of whom is well informed and willing, and neither of whom is compelled to buy or sell. 3 What is the family's net worth? Please prepare a personal balance sheet. The balance sheet "as of date" should be Dec. 31, 2020. PLEASE NOTE: Because the family owns several common stock investments, it might be helpful to review question 5 (below) and complete the question 5 worksheet in the Excel workbook and then enter the appropriate balance sheet value from that portfolio report. Page 3 of 8 2 of 3336 words IX English (United States) O Focus E B - + 143% AutoSave OFF H 5 + 5 = wa Douglas Case rev 9_7_21(2) (1) Home Insert Draw Design Layout References Mailings Review View Tell me Share Q Comments X LO Calibri (Bo... V 11 ' Aa 21 AaBbDdEe AaBbCcDdEe AaBbccdde AaBb AaBbceDd Ee AaBb CcD Heading 1 AaBbCcDdE Heading 2 Paste B I x Normal U v alb APA No Spacing X Title Heading 3 Subtitle Styles Dictate Pane Question 2. The current ratio is a measure of short-term debt burden. A current ratio of 2 indicates that for every $2 of liquid assets there is $1 of current liabilities (a current ratio = 2). A high current ratio indicates the ability to meet short-term obligations. What is the current ratio on December 31st for the Douglas family? Question 3. Debt burden is something that every family should monitor. One ratio is a debt ratio (liabilities/assets). In recent years, the Douglas family's debt ratio was running around 40%. What is their current debt ratio? Question 4. The liquidity ratio indicates the number of months that living expenses can be paid from liquid assets if an emergency arises (such as any event that would result in a serious decrease in income) If the family's monthly living expenses are about $6,000, what is their liquidity ratio? Question 5. The family's stock portfolio is in an investment account. John and Mary log into the account periodically, to see how their investments are performing. Typically, common stocks by sectors such as: . . . Communication Services Consumer Discretionary Consumer Staples Energy Financial Health Care Industrials Information Technology Materials Real Estate Utilities . Within the sectors, there are industries. Here is a summary of the family's portfolio in terms of sectors and industry: Page 4 of 8 2 of 3336 words X English (United States) O Focus E - 143% AutoSave OFF H 5 + 5 = wa Douglas Case rev 9_7_21(2) (1) Home Insert Draw Design Layout References Mailings Review View Tell me Share Q Comments X LO Calibri (Bo... V 11 ' Aav . = 21 AaBbcDdEe AaBbCcDdEe AaBbCcDdE AaBbccdded AaBb AaBb CeDd Ee AaBb CcD Heading 1 Paste B I x Normal APA U v alb 19 No Spacing X Heading 2 Title Heading 3 Subtitle Styles Dictate Pane worksheet. Here are some steps to follow: A. First by growing the cost of education by 4% per year (12 years into the future for Paul and 11 years for Marcy) and then calculating the present value of those future cash flows. Keep in mind that Paul will be going to school for 4 years and so too will Marcy. So, you will need to figure the future value of the cost of education for the first year, second year, third year, and fourth year - each year 4% more costly than the year before! B. Calculate the present value of the future costs of education using the investment yield prediction (8% - see below and notice in the data above that the investment fund has averaged 8% per year). 5 C. Once you have the present value of the future costs - you can subtract the current balance in these accounts to derive the "donation" that the grandparents will need to make.) Assume that the investment will grow at 8% per year as a result of investment yields. How much (in total) must the grandparents invest today to establish the education fund for Paul and Marcy? NOTE: There is more on how to approach solving question 6 at the end of this document. Question 7. Calculate the percentage of net worth represented by the home and the two other largest assets. Consider any loans attributed to those assets so that you show the following - the asset's net value/Total family net worth. Count both cars as one asset (Automobiles). Page 5 of 8 2 of 3336 words IX English (United States) O Focus E B E - + 143% AutoSave OFF H 5 + 5 = wa Douglas Case rev 9_7_21(2) (1) Home Insert Draw Design Layout References Mailings Review View Tell me Share Q Comments X LO Calibri (Bo... V 11 ' Aa to 21 AaBbcDdEe AaBbCcDdEe AaBbccdded AaBb AaBb CeDd Ee AaBb CcD Heading 1 AaBbCcDdE Heading 2 Paste B I Normal x U v alb Title No Spacing Subtitle X ALA Heading 3 Styles Dictate Pane This is called the "dominant" asset - other words, if a family is "car rich" then that would mean a significant percentage of their wealth (as defined by net worth) would be from the value of its cars. The cars would be the dominant asset. If the family's net worth is mostly from their retirement accounts, then we could say that a significant amount of their wealth is from pensions. Question 8. Jeff is considering applying for a home equity loan to finance the basement project. What is the maximum home equity loan the Douglas' could possibly get based only on equity (and ignoring cash flow considerations)? Assume that a bank is willing to loan up to 80% of the value of the home (between the mortgage and the home equity loan). Question 9. Home equity loans have many advantages. For example, the home equity loan may be the source of funds to help Jeff and Mary finish their basement. The interest on the loan will be tax deductible. The arrangement that Jeff and Mary are looking at will involve a 15-year payback period and would allow for them to draw down on any unused funds in a credit line arrangement. Please describe two disadvantages of a home equity loan and recommend an amount that Jeff and Mary should request for their line of credit. Question 10. Jeff and Mary have discussed the need to life insurance and one of their goals is to have a sufficient amount of coverage in the event of an untimely death. Part of the reason for maintaining life insurance coverage is the replacement of earnings and to provide funds for funeral and burial. Jeff's salary is $70,000 per year and Mary makes about $40,000. Funeral and burial expenses average $10,000 a year, according to personal finance articles. The couple also agrees that death benefit proceeds should be sufficient to pay off debts such as the balances owed for credit cards, vacation loans, and home improvement debt. Easy Method - a quick and easy method that assumes the life insurance will cover 70% of 7 years of the deceased person's annual income. DINK method - DINK is an acronym for DINK stands for double income, no kids. Since Jeff and Mary do have two young children, the DINK method's results may not be appropriate however, please calculate the life insurance need under this method as another point of information. DINK assumes that life insurance coverage is adequate if it covers funeral expenses and one half of the family debts upon an untimely death of one of the "bread winners." Page 6 of 8 2 of 3336 words X English (United States) O Focus E B E - 143% AutoSave OFF H 5 + 5 = wa Douglas Case rev 9_7_21(2) (1) Home Insert Draw Design Layout References Mailings Review View Tell me Share Q Comments X LO Calibri (Bo... V 11 E 21 AaBbcDdEe AaBbCcDdEe AaBb CcDdE AaBbccdde AaBb AaBbceDd Ee ' Aa A x ALA AaBb CcD Heading 1 Paste B I Normal E U v alb X Title No Spacing Subtitle = Heading 2 Heading 3 Styles Dictate Pane Family Need Method - a more detailed method that calculates life insurance needs by considering assets (such as checking and savings accounts and investments) and assuming that life insurance and liquid assets will cover estimated funeral costs and the future financial needs of dependents. For the Family Need method, assume that living expenses are 70% of their income (you need this to estimate an emergency fund). Be conservative and estimate an emergency fund of 6 months of living expenses. Keep in mind that when using the "Family Need" method, you have to run the analysis twice - once based on the what-if assumption that Jeff dies now and once based on the what-if assumption that Mary dies now. Assume that the retirement account on the balance sheet is 90% Jeff's funds and 10% Mary's. Ignore social security death benefits. Also, the assumption should be that the Mutual Fund investment should not be considered as a liquid asset because it is earmarked for the children's education and that also means you do not have to consider an amount for the children's education as the mutual fund will take care of that (assumes that the grandparents will make the gift (see question 6). What do you recommend for death benefit amounts for each person (Jeff and Mary)? What is your rationale for the amount of coverage you recommend? Question 11. Pretend you are a personal financial planning professional attempting to devise a comprehensive personal financial plan for Jeff and Mary. What other areas of Jeff and Mary's personal financial situation should be examined and what observations can you make about their current situation? Please mention at least 2 other concepts that they should take a careful look at. Please write a few paragraphs that answer this question. Feel free to pose additional questions that you would like to ask Jeff and Mary as a way of making sure you, as a financial planner, understand their situation. More Notes Regarding Requirement 6 of the Douglas Personal Financial Planning Case These steps and the worksheet below might help you solve requirement 6. I find it easier to set this type of thing up within an Excel worksheet and I like to utilize the FV and PV functions of Excel. However, you can also do this with the time value of money tables within the text or with a financial calculator. It's up to you. In any event, please show your work - how you derived your final answer. Page 7 of 8 2 of 3336 words EX English (United States) O Focus E B - + 143% AutoSave OFF DES 5- Douglas Case(2) Q Home Insert Draw Page Layout Formulas Data Review View Acrobat Tell me Share Comments Calibri Body v # Insert v = = LG General LIH 14 ~ A ar Av A 28- 0 0 DX Delete v PX Paste I U V V == == v $ % ) ES 70 Conditional Format Cell Formatting as Table Styles Format Sort & Filter Find & Select Analyze Data Create and Share Adobe PDF A2 - x fx Enter Name of Family Here D D E F G G H I J K L . N 0 P Q R S T T $0.00 $0.00 B 1 1 Personal Balance Sheet 2 2 Enter Name of Family Here 3 As of date: 4 Assets 5 Liquid assets 6 Checking account balance 7 Savings/money market accounts, funds 8 Cash value of life insurance 9 Other 10 Total liquid assets 11 Household assets and possessions 12 Current market value of home 13 Market value of automobiles 14 Furniture 15 Computer, electronics, camera 16 Jewelry 17 Other 18 Other 19 Total household assets 20 Investment assets 21 Savings certificates 22 Stocks and bonds 23 Retirement accounts 24 Mutual funds 25 Other 26 Total investment assets 27 Total assets 28 28 29 Liabilities 30 Current liabilities 31 Charge account and credit card balances 32 Loan balance (Cars) ( 33 Vacation Loan 34 Other 35 Total current liabilities 36 Long-term liabilities 37 Mortgage 38 Home Improvement Loan 39 Total long-term liabilities 40 Total liabilities 41 Net Worth 42 43 $0.00 $0.00 Carleans $0.00 $0.00 $0.00 $0.00 Q1 Personal Balance Sheet Questions 2, 3, &4 Question 5 Question 6 Question 7 Question 8 and 9 Question 10 Question 11 Ready 91% AutoSave OFF ES5= Douglas Case(2) Q Home Insert Draw Page Layout Formulas Data Review View Acrobat Tell me Share Comments Calibri (Body) v 12 LG Insert v . = = ab Currency H Ayu Or 0 DX Delete v PX Paste BI MA V $ %) EM V Conditional Format Cell Formatting as Table Styles 70 Format v Sort & Filter Find & Select Analyze Data Create and Share Adobe PDF B3 x fx B D D E F G H J L M N O 0 P P Q R S Times Times 1 1 2 2. Current Ratio 3 Liquid Assets 4 Current Liabilities 5 Current Ratio 6 7 8 3. Debt Ratio 9 Liabilities 10 Assets 11 Debt Ratio 12 13 14 4. Liquidity Ratio 15 Liquid Assets 16 Monthly Living Expenses 17 Liquidity Ratio 18 19 20 21 - 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 Q1 Personal Balance Sheet Questions 2, 3, 84 Question 5 Question 6 Question 7 Question 8 and 9 Question 10 Question 11 + Ready PH P 100% AutoSave OFF DES 5- Douglas Case(2) Q Home Insert Draw Page Layout Formulas Data Review View Acrobat Tell me Share Comments Calibri (Body) 12 v . = = LG # Insert v ab Date LIH Ayu Or DX Delete v Paste BI av A V V == == == $ % ) to 70 Conditional Format Cell Formatting as Table Styles Format v Sort & Filter Find & Select Analyze Data Create and Share Adobe PDF A B2 fx B B D E F F G . J K L M N o P Q R S T A i Stock Portfolio 2 2 As of Date: Number of Shares Cost Basis per Share Total Cost Basis Market Value Per Total Market Share Value Gain or Loss 3 Company Name Stock Symbol 4 Apple AAPL 200 $100 $20,000 $150| $30,000 $10,000 Sector Industry Technology Information Hardware, Storage & , Technology Peripherals Diversified Communication Telecommunication Services Services Information Technology Software Communication Interactive Media & Services Services 5 AT&T T 6 Microsoft MSFT 7 Alphabet GOOGL 8 Totals $20,000 $30,000 $10,000 9 What are your comments about the family's investment portfolio? Please keep in mind that their investment portfolio currently consists of 10 common stocks. 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 22 26 27 28 29 Q1 Personal Balance Sheet Questions 2, 3, &4 Question 5 Question 6 Question 7 Question 8 and 9 Question 10 Question 11 + Ready P 100% AutoSave OFF BESU- Douglas Case(2) Q Home Insert Draw Page Layout Formulas Data Review View Acrobat Tell me Share Comments Calibri v ab Insert v 11 ' LG Currency FO 1 === E D. DX Delete v PX Paste BI MA V $ % ) EM V Conditional Format Cell Formatting as Table Styles 70 Format Sort & Filter Find & Select Analyze Data Create and Share Adobe PDF B22 X fx E F G H I T J K L L M . N 0 O P R S A B C D 1 College Fund 2 Current cost of 1 year of college $25,000 3 Projected annual inflation of education costs 4% per year 4 Annual return on college investment fund 8% per year 5 6 Paul's projections Years (end Future Value of Education Present value of of year) Cost (4%) Education Costs (using 8% % 7 as the discount rate) 8 12 $40,026 $15,894.80 9 10 131 $41,627 $$15,306.10 11 12 12 14 $43,292 $14,739.21 13 14 15 151 $45,024 $14,193.31 16 17 Total $60,133.42 18 19 20 Marcy's projections Present value of Years (end Future Value of Education of year) Cost (49) Education Costs (using 8% % 21 as the discount rate) 22 111 23 12 13 25 14 26 Total $0.00 27 28 Total Present Value $60,133.42 29 Less amount already invested 30 Total Amount to be Contributed today $60,133.42 31 32 33 34 35 24 Q1 Personal Balance Sheet Questions 2, 3, 84 Question 5 Question 6 Question 7 Question 8 and 9 Question 10 Question 11 + Ready P 100% AutoSave OFF DES 5- Douglas Case(2) Q Home Insert Draw Page Layout Formulas Data Review View Acrobat Tell me Share Comments Calibri (Body) v 12 Insert v . = = X LG ab General TH 28 O FO DX Delete v PX v Paste BI av A. V V F = $ %) to 70 Conditional Format Cell Formatting as Table Styles Format Sort & Filter Find & Select Analyze Data Create and Share Adobe PDF A18 X fix F L M N o P Q R A D E H J 1 7. Calculate the percentage of net worth represented by the home and the three other largest assets. Consider any loans attributed to those assets so that 2 you show the following - the asset's net value/Total family net worth. If you consider the cars as one of their largest (top 3) assets, count both cars as one 3 asset (Automobiles). The net asset value = Asset Market Value less Related Loan (s). 4 5 This is called the "dominant" asset - other words, if a family is "car rich" then that would mean a significant percentage of their wealth (as defined by net 6 worth) would be from the value of its cars. The cars would be the dominant asset. If the family's net worth is mostly from their retirement accounts, then 7 8 we could say that a significant amount of their wealth is from pensions. 9 10 11 12 13 14 Net Worth $0 (from the balance sheet) 15 Related Loan(s) Percentage of 16 Asset Asset Value Amount Net Asset Value Net Worth 17 Home 0% 0 18 0% 19 12 0% 20 - 0% 21 $0 $0 $0 0% 22 22 23 22 24 24 25 26 20 2 27 20 28 23 29 30 30 31 22 32 22 22 33 + 34 33 35 30 36 37 38 39 40 Q1 Personal Balance Sheet Questions 2, 3, &4 Question 5 Question 6 Question 7 Question 8 and 9 Question 10 Ready 21 Total Question 11 + 100% AutoSave OFF DES 5- Douglas Case(2) Q Home Insert Draw Page Layout Formulas Data Review View Acrobat Tell me Share Comments Calibri (Body) v 12 LG Insert v . = = ab Currency FO PH 28 Ow DX Delete v Paste BI ar A v V V == == $ % ) to20 0 Conditional Format Formatting as Table Cell Styles Format v Sort & Filter Find & Select Analyze Data Create and Share Adobe PDF B3 fx A B D E E F F G H J J K L M N O P Q Jeff Mary $0 $0 $0 $0 John $10,000 Mary $10,000 $10,000 $10,000 $10,000 $10,000 Jeff Mary 1 2 EASY Method 3 Current Income (salaries) ( 4 4 Insurance need 5 Current Life Insurance Coverage 6 Additional Life Insurance Needed (EASY METHOD) 7 8 DINK Method = 9 Funeral Expenses 10 One-half of mortgage 11 One-half of auto loan - 12 One-half of credit card balance - Other Debts 13 Other Debts 14 Insurance Needed 15 Current Life Insurance Coverage 16 Additional Life Insurance Needed (DINK Method) 17 18 Family Need Method 19 What They Have Now: : 20 Bank Accounts (Checking) Investments, Savings, Portion of Deceased's Retirement Account 21 (not including home) 22 Life Life Insurance Policies (Death Benefit) 23 Total 24 What Dependents Need 2 25 Funeral Expenses 26 Mortgage and Loans 27 Credit Card Debt 28 Subtotal 29 Living Expenses 75% of family income x Number of Years dependents will need it 30 (assume 15 years) 31 Emergency Fund 32 Total of what the dependents will need 33 Additional Life Insurance Needed (Family Need Method) 34 35 Most Coverage required from the three methods 36 $0 $0 $10,000 $10,000 $10,000 $10,000 $ $0 $10,000 $0 $10,000 $10,000 $10,000 What do you recommend for death benefit amounts for each person (Jeff and Mary)? What is Q1 Personal Balance Sheet Questions 2, 3, &4 Question 5 Ready Question 6 Question 7 Question 8 and 9 Question 10 Question 11 + P 100% AutoSave OFF DES 5- Douglas Case(2) Q Home Insert Draw Page Layout Formulas Data Review View Acrobat Tell me Share Comments Insert v v v Ayu Or 0 DX Delete v PX EM V 70 Format v Sort & Filter Find & Select Analyze Data Create and Share Adobe PDF 0 P Q R S T V X Calibri (Body) 12 ' ab General PH LG Paste BI av A A E $ %) ES Conditional Format Cell Formatting as Table Styles A8 - X fx What other areas of the family's personal financial situation should be examined? A B D E G G H H J M N 1 11. Pretend you are a personal financial planning professional attempting to devise a comprehensive personal financial plan for Jeff and Mary. 2 What other areas of Jeff and Mary's personal financial situation should be examined and what observations can you make about there current 3 4 situation? Please mention at least 2 other concepts that they should take a careful look at. Please write a few paragraphs that answer this question. Feel free to pose additional questions that you would like to ask Jeff and Mary as a way of making sure you, as a financial planner, understand their situation. 7 8 What other areas of the family's personal financial situation should be examined? 9 10 11 12 13 14 15 16 17 18 Observations about the current situation. 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 Q1 Personal Balance Sheet Questions 2, 3, &4 Question 5 Question 6 Question 7 Question 8 and 9 Question 10 Question 11 + Ready 100%