Q3: Based on the financial statements, in EXCEL calculate the following ratios:
- Living Expenses Covered Ratio
- Debt Ratio
- Debt Service to Income Ratio
- Saving Ratio
- Investment Assets to Total Assets Ratio
MATURITY N/A N/A ASSETS - January 1, Current Year (all assets are community property) LOCATION BALANCE RATE OF INTEREST CHECKING First National Bank $2,646 0.25% SAVINGS First National Bank $6,348 1.0% MONEY MARKET American State $5,242 2.25% Bank ACCOUNT CDS First National Bank $6,000 3.2% E-Trade Bank $2,000 2.2% 5/1/2022 8/1/2020 CASH ON HAND $422 2 N Total SECURITY INVESTMENTS # Security Shares 150 Microsoft (MSFT) 100 Wal-Mart stock (WMT) 50 Barnes & Noble (BKS) 50 Apple Inc. (AAPL) 200 Fidelity Puritan Mutual Fund (FPURX) 75 T. Rowe Price Science/Tech (PRSCX) REAL PROPERTY Year Make Automobile: 2012 Nissan Automobile: 2014 Mercedes House: 2000 Cost of Basis Date Per Acquired Share 7/1999 49.63 7/2005 46.25 9/2006 36.15 10/2010 282.28 10/1995 12.24 7/2010 14.50 7,445 4,625 1,807 14,114 2,448 4,350 Current Value Per Total Share 125.35 18,803 103.17 10,317 4.50 225 183.34 9.167 21.30 4,260 36.31 2,723 Model Pathfinder CLS550 Cost Current Value $31,730 $5,850 $71,300 $22,325 $240,000 $285,000 (Replacement value = $300,000) PERSONAL PROPERTY Furniture & appliances Stereos, TVs, cameras, etc. Clothing & Jewelry Computer, printer, & software Baseball card collection Exercise equipment Toys, books, & children's games Miscellaneous household items Market Value $40,000 $8,000 $19,550 $7,500 $8,500 $1,800 $1,500 $6,300 Replacement Value $56,000 $17,000 $26,000 $10,750 $7,500 $2,800 $4,000 $10,000 How often paid Monthly Total # of Payments Date of First Payment 9/1/2015 60 $ $ Monthly 60 12/1/2014 LIABILITIES (all liabilities are community property debt) Original Current Int. To whom Property Amount Balance Payment Rate owed or Service of (as of Jan 1, Amount Account Purchased Current Year) GMAC 27,500 Nissan 4.75$ Pathfinder Texas Tech 67,000 Mercedes 5.50 Credit CLS550 Union Nationwide 210,000 House 5.25 Mortgage Co. Department 36,000 Student 6.50 $ of Education Loans Wells Fargo 78,000 Student 7.25$ Education Loans *Calculate the amounts that are missing using TVM calculations. $ Monthly 360 5/1/2009 $ Monthly 240 7/1/2006 A Monthly 240 7/1/2011 No CREDIT CARDS - January 1, Current Year Annual Interest Credit Outstanding Min. Grace Calculation Fee Rate Limit Balance Payment Period Method Discover $0 14.5% $2,500 $2,304 20% of Yes Av. Daily Balance or $50 Balance Visa $0 22.9% $10,000 $441 5% of Balance Yes Av. Daily or $25 Balance American $35 12.0% $10,000 $6,481 10% of No Av. Daily Express Balance or $20 Balance Beall's $0 18.0% $500 SO 3% of Balance Av. Daily or $40 Balance Dillard's $0 24.5% $3,000 $696 10% of Yes Av. Daily balance or $50 Balance Only minimum payments are made each month on all credit cards CURRENT YEAR INCOME Gross Income Chips Salary $72,000 Joanna's Salary $208,000 Interest $175 Dividends $260 Capital gains distributions $169 Sale of Securities Amana Trust Growth Mutual Fund (AMAGX) $5,456 1. Checking ($7/year), savings ($27 a year), money market account ($141 a year, paid in August). 2. Fidelity Puritan ($232) and T. Rowe Price ($28 a year). All dividends paid in December. 3. Microsoft ($83) and Apple ($190). Distributions paid in December. 4. 200 shares purchased May 16 at $17.06/share and sold December at $27.28/share. CURRENT EXPENSES Cash Flow Cash Flow Monthly Annually House Payment 1,160 13,920 Home repairs and maintenance 3,000 Homeowner's insurance Property Taxes 300 3,600 Utilities 350 4200 Cell Phones 150 1,800 Cable TV and Internet 205 2,460 Groceries 475 5,700 Food away from home 600 7,200 Education loan payment (Wells Fargo) Education loan payment (Dept. of Educ.) Auto loan payment (Nissan) Auto loan payment (Mercedes) Auto maintenance 1,500 Medical/dental expenses (not covered by insurance) 625 Health insurance Disability insurance Auto insurance (paid semi-annually) 2,500 Life insurance Federal Income Tax withholding 69,976 Social Security Tax withholding 12,722 Credit Card Payments 940 11,280 Clothing 300 3,600 Personal Care 175 2,100 Appliance, Furniture and Equipment 1,500 Entertainment 275 3,300 Vacation (taken in July) 5,000 Children's activities 200 2,400 Childcare 1,500 18,000 Charitable contributions 750 9,000 Gifts 200 2,400 Miscellaneous 150 1,800 Maid and Lawn Service 300 3,600 Joanna's contribution to her 401(k) *Calculate the blank fields using TVM calculations or find within the case study document. AUTO INSURANCE Description Company Policy Number Liability Personal Injury Protection Uninsured Motorist Collision Deductible Comprehensive Deductible Annual Premium Comments (1) 2012 Nissan Pathfinder AETNA Casualty 134-234-456-AOB6 25/50/25 $2,500 25/50/25 Actual Cash Value $200 Actual Cash Value $50 $773 Paid semi-annually in February and August. (2) 2014 Mercedes CLS550 AETNA Casualty 134-234-456-AOB6 25/50/25 $2,500 25/50/25 Actual Cash Value $200 Actual Cash Value $50 $1,727 Paid semi-annually in February and August HOMEOWNER'S INSURANCE Company American Property Insurers Policy Number/Type 33490-73798-99/HO-2 Dwelling $175,000 Other Structures $11,000 Personal Property on Premises $65,000 Personal Property off Premises $6,500 Additional Living Expenses (annual limit) $22,000 Comprehensive Liability (per occurrence) $100,000 Medical Payments (per person) $1,000 Property Damage to Others (per occurrence) $500 Deductible $550 Annual premium $1,500 LIFE INSURANCE Insured Chip Joanna Type of Life Insurance Group Term Group Term Face Amount $100,000 $250,000 Beneficiary Joanna Chip Owner Chip Joanna Annual premium Paid by employer $25/month (payroll deduction) DISABILITY INSURANCE Insured Joanna Policy number Employer Group Policy Definition of Disability Own Job Monthly Benefit 60% of gross monthly salary Waiting Period Sick 90 days Accident 90 days Benefit Period Sick To age 65 Accident To age 65 Premiums $15/month (payroll deduction) *Joanna has 30 days of sick leave built up at work. MEDICAL INSURANCE INSURED Chip (Halliburton) Joanna and children (Covenant Hospital) Company Metropolitan Life Health Care Plan (HMO) Policy Number Group #063-11168 Group #16852AGC Major Medical Maximum $500,000/person/year Unlimited Deductible $5,000/person/year $15 co-pay per doctor's visit $50 co-pay/ER room visit % Participation 80/20 Cap on participation $2,000/person/year Mental Health 80% for up to 30 visits a year $15 co-pay per visit Prescription Drugs No $10 co-pay/brand prescription $5 co-pay/generic prescription Dental No Annual Premiums Employer pays for employee. Employer pays for employee. $300/month paid for children (payroll deduction) Comments Chip's policy: Family members can be covered for $60/person/month. Coordination of benefits provision is included in the policy. Joanna's Policy: Must use HMO facilities and selected hospitals. Dependents can be covered for $100/person/month. Coordination of benefits provision is included in the policy. No RETIREMENT INFORMATION Person Covered Chip Joanna Type of retirement plan Qualified, non-contributory, 401(k) Qualified, defined contribution, 403(b) Vesting 3-year cliff vesting 2-6 year graduated vesting Beneficiary Joanna Chip Other information Formula: (average salary based on 401(k) - Employer match is dollar for the best 5 years of earning) x (total dollar up to 6% of employee contributions. years in plan) x 2% = amount Employee contribution is limited to 15% of earned monthly during retirement wages annually. Funds can be invested in any of the Vanguard family of mutual funds. Vesting Immediate Current Value $34,287 $161,282 ESTATE PLANNING INFORMATION Chip and Joanna would like to leave all of their assets to each other and the other debt free in case of death. Currently, Chip and Joanna have no formal estate plan except sweetheart wills. In his will, Chip would like to leave his baseball card collection to his brother Gill. They are wondering if they should set up trusts for the children. FINANCIAL GOALS Short Range (1 Year) Gl: Pay off Credit Cards: Apply an extra $200 per month towards the American Express card G2: Reduce discretional spending to prepare for reduced income: No TVM required G3: Purchase larger house: Have a total of $20,000 in four years in their money market account to apply towards a down nayment Intermediate Range (1-5 Years) G4: Establish an emergency fund: Have a total of $15,000 saved in their savings account in two years for an emergency fund. G5: Begin a college savings plan their children: Have a total of $20,000 in 5 years to apply for future college expenses. Long Term (over 5 years) G6: Start saving more aggressively for retirement - open an additional retirement account with $2000 and begin to make $300 monthly contributions until retirement. 1 7 4 8 FINANCIAL PRIORITIES Chip Joanna a. LIVING: paying monthly bills b. PLEASURE: spending money 4 5 C. RETIREMENT: invest in future 6 3 d. DISABILITY:protect against 6 e. DEATH: care for family 8 f. REDUCE TAXES: spend to save g. INVESTING: accumulating assets 5 7 h. CHILDREN: college funding 2 2 * Prioritized with 1 being most important and 8 being least important They expect to receive $40,000 from the estate of Joanna's grandfather within the next 3 months and would like to use this inheritance to fund some of the goals (let's see how far this can make this inheritance go). Use the following assumptions: Investment Return on GI N/A Investment Return on G2: N/A Investment Return on G3: 2.25% Investment Return on G4: 1.50% Investment Return on G5: 4.00% Investment Return on G6: 5.00% OTHER INFORMATION 1. Are you able to save regularly? Joanna's contribution to her employer's 401(k) plan is the only regular savings. 2. How much do you save annually? 3% of Joanna's salary in her 401(k) 3. Do you invest regularly? Chip has dabbled in some investing but it's not regular. Usually, we try to invest bonuses or other windfalls received. 4. Do you feel you are financially organized? Not really 5. Do you budget your money? No we often overspend 6. If you were to die, could your spouse handle the finances? Chip - Probably, Joanna - Yes 7. How do you feel about saving for retirement? Joanna - It is very important. Chip - It's important but we still have time. 8. If you had an extra $5,000 what would you do with it? Chip - Take a vacation, Joanna - Pay down the credit card 9. How do you feel about taking investment risks? Chip - aggressive risk taker, Joanna moderate risk taker. 10. How is your health? Chip very good, Joanna very good. MATURITY N/A N/A ASSETS - January 1, Current Year (all assets are community property) LOCATION BALANCE RATE OF INTEREST CHECKING First National Bank $2,646 0.25% SAVINGS First National Bank $6,348 1.0% MONEY MARKET American State $5,242 2.25% Bank ACCOUNT CDS First National Bank $6,000 3.2% E-Trade Bank $2,000 2.2% 5/1/2022 8/1/2020 CASH ON HAND $422 2 N Total SECURITY INVESTMENTS # Security Shares 150 Microsoft (MSFT) 100 Wal-Mart stock (WMT) 50 Barnes & Noble (BKS) 50 Apple Inc. (AAPL) 200 Fidelity Puritan Mutual Fund (FPURX) 75 T. Rowe Price Science/Tech (PRSCX) REAL PROPERTY Year Make Automobile: 2012 Nissan Automobile: 2014 Mercedes House: 2000 Cost of Basis Date Per Acquired Share 7/1999 49.63 7/2005 46.25 9/2006 36.15 10/2010 282.28 10/1995 12.24 7/2010 14.50 7,445 4,625 1,807 14,114 2,448 4,350 Current Value Per Total Share 125.35 18,803 103.17 10,317 4.50 225 183.34 9.167 21.30 4,260 36.31 2,723 Model Pathfinder CLS550 Cost Current Value $31,730 $5,850 $71,300 $22,325 $240,000 $285,000 (Replacement value = $300,000) PERSONAL PROPERTY Furniture & appliances Stereos, TVs, cameras, etc. Clothing & Jewelry Computer, printer, & software Baseball card collection Exercise equipment Toys, books, & children's games Miscellaneous household items Market Value $40,000 $8,000 $19,550 $7,500 $8,500 $1,800 $1,500 $6,300 Replacement Value $56,000 $17,000 $26,000 $10,750 $7,500 $2,800 $4,000 $10,000 How often paid Monthly Total # of Payments Date of First Payment 9/1/2015 60 $ $ Monthly 60 12/1/2014 LIABILITIES (all liabilities are community property debt) Original Current Int. To whom Property Amount Balance Payment Rate owed or Service of (as of Jan 1, Amount Account Purchased Current Year) GMAC 27,500 Nissan 4.75$ Pathfinder Texas Tech 67,000 Mercedes 5.50 Credit CLS550 Union Nationwide 210,000 House 5.25 Mortgage Co. Department 36,000 Student 6.50 $ of Education Loans Wells Fargo 78,000 Student 7.25$ Education Loans *Calculate the amounts that are missing using TVM calculations. $ Monthly 360 5/1/2009 $ Monthly 240 7/1/2006 A Monthly 240 7/1/2011 No CREDIT CARDS - January 1, Current Year Annual Interest Credit Outstanding Min. Grace Calculation Fee Rate Limit Balance Payment Period Method Discover $0 14.5% $2,500 $2,304 20% of Yes Av. Daily Balance or $50 Balance Visa $0 22.9% $10,000 $441 5% of Balance Yes Av. Daily or $25 Balance American $35 12.0% $10,000 $6,481 10% of No Av. Daily Express Balance or $20 Balance Beall's $0 18.0% $500 SO 3% of Balance Av. Daily or $40 Balance Dillard's $0 24.5% $3,000 $696 10% of Yes Av. Daily balance or $50 Balance Only minimum payments are made each month on all credit cards CURRENT YEAR INCOME Gross Income Chips Salary $72,000 Joanna's Salary $208,000 Interest $175 Dividends $260 Capital gains distributions $169 Sale of Securities Amana Trust Growth Mutual Fund (AMAGX) $5,456 1. Checking ($7/year), savings ($27 a year), money market account ($141 a year, paid in August). 2. Fidelity Puritan ($232) and T. Rowe Price ($28 a year). All dividends paid in December. 3. Microsoft ($83) and Apple ($190). Distributions paid in December. 4. 200 shares purchased May 16 at $17.06/share and sold December at $27.28/share. CURRENT EXPENSES Cash Flow Cash Flow Monthly Annually House Payment 1,160 13,920 Home repairs and maintenance 3,000 Homeowner's insurance Property Taxes 300 3,600 Utilities 350 4200 Cell Phones 150 1,800 Cable TV and Internet 205 2,460 Groceries 475 5,700 Food away from home 600 7,200 Education loan payment (Wells Fargo) Education loan payment (Dept. of Educ.) Auto loan payment (Nissan) Auto loan payment (Mercedes) Auto maintenance 1,500 Medical/dental expenses (not covered by insurance) 625 Health insurance Disability insurance Auto insurance (paid semi-annually) 2,500 Life insurance Federal Income Tax withholding 69,976 Social Security Tax withholding 12,722 Credit Card Payments 940 11,280 Clothing 300 3,600 Personal Care 175 2,100 Appliance, Furniture and Equipment 1,500 Entertainment 275 3,300 Vacation (taken in July) 5,000 Children's activities 200 2,400 Childcare 1,500 18,000 Charitable contributions 750 9,000 Gifts 200 2,400 Miscellaneous 150 1,800 Maid and Lawn Service 300 3,600 Joanna's contribution to her 401(k) *Calculate the blank fields using TVM calculations or find within the case study document. AUTO INSURANCE Description Company Policy Number Liability Personal Injury Protection Uninsured Motorist Collision Deductible Comprehensive Deductible Annual Premium Comments (1) 2012 Nissan Pathfinder AETNA Casualty 134-234-456-AOB6 25/50/25 $2,500 25/50/25 Actual Cash Value $200 Actual Cash Value $50 $773 Paid semi-annually in February and August. (2) 2014 Mercedes CLS550 AETNA Casualty 134-234-456-AOB6 25/50/25 $2,500 25/50/25 Actual Cash Value $200 Actual Cash Value $50 $1,727 Paid semi-annually in February and August HOMEOWNER'S INSURANCE Company American Property Insurers Policy Number/Type 33490-73798-99/HO-2 Dwelling $175,000 Other Structures $11,000 Personal Property on Premises $65,000 Personal Property off Premises $6,500 Additional Living Expenses (annual limit) $22,000 Comprehensive Liability (per occurrence) $100,000 Medical Payments (per person) $1,000 Property Damage to Others (per occurrence) $500 Deductible $550 Annual premium $1,500 LIFE INSURANCE Insured Chip Joanna Type of Life Insurance Group Term Group Term Face Amount $100,000 $250,000 Beneficiary Joanna Chip Owner Chip Joanna Annual premium Paid by employer $25/month (payroll deduction) DISABILITY INSURANCE Insured Joanna Policy number Employer Group Policy Definition of Disability Own Job Monthly Benefit 60% of gross monthly salary Waiting Period Sick 90 days Accident 90 days Benefit Period Sick To age 65 Accident To age 65 Premiums $15/month (payroll deduction) *Joanna has 30 days of sick leave built up at work. MEDICAL INSURANCE INSURED Chip (Halliburton) Joanna and children (Covenant Hospital) Company Metropolitan Life Health Care Plan (HMO) Policy Number Group #063-11168 Group #16852AGC Major Medical Maximum $500,000/person/year Unlimited Deductible $5,000/person/year $15 co-pay per doctor's visit $50 co-pay/ER room visit % Participation 80/20 Cap on participation $2,000/person/year Mental Health 80% for up to 30 visits a year $15 co-pay per visit Prescription Drugs No $10 co-pay/brand prescription $5 co-pay/generic prescription Dental No Annual Premiums Employer pays for employee. Employer pays for employee. $300/month paid for children (payroll deduction) Comments Chip's policy: Family members can be covered for $60/person/month. Coordination of benefits provision is included in the policy. Joanna's Policy: Must use HMO facilities and selected hospitals. Dependents can be covered for $100/person/month. Coordination of benefits provision is included in the policy. No RETIREMENT INFORMATION Person Covered Chip Joanna Type of retirement plan Qualified, non-contributory, 401(k) Qualified, defined contribution, 403(b) Vesting 3-year cliff vesting 2-6 year graduated vesting Beneficiary Joanna Chip Other information Formula: (average salary based on 401(k) - Employer match is dollar for the best 5 years of earning) x (total dollar up to 6% of employee contributions. years in plan) x 2% = amount Employee contribution is limited to 15% of earned monthly during retirement wages annually. Funds can be invested in any of the Vanguard family of mutual funds. Vesting Immediate Current Value $34,287 $161,282 ESTATE PLANNING INFORMATION Chip and Joanna would like to leave all of their assets to each other and the other debt free in case of death. Currently, Chip and Joanna have no formal estate plan except sweetheart wills. In his will, Chip would like to leave his baseball card collection to his brother Gill. They are wondering if they should set up trusts for the children. FINANCIAL GOALS Short Range (1 Year) Gl: Pay off Credit Cards: Apply an extra $200 per month towards the American Express card G2: Reduce discretional spending to prepare for reduced income: No TVM required G3: Purchase larger house: Have a total of $20,000 in four years in their money market account to apply towards a down nayment Intermediate Range (1-5 Years) G4: Establish an emergency fund: Have a total of $15,000 saved in their savings account in two years for an emergency fund. G5: Begin a college savings plan their children: Have a total of $20,000 in 5 years to apply for future college expenses. Long Term (over 5 years) G6: Start saving more aggressively for retirement - open an additional retirement account with $2000 and begin to make $300 monthly contributions until retirement. 1 7 4 8 FINANCIAL PRIORITIES Chip Joanna a. LIVING: paying monthly bills b. PLEASURE: spending money 4 5 C. RETIREMENT: invest in future 6 3 d. DISABILITY:protect against 6 e. DEATH: care for family 8 f. REDUCE TAXES: spend to save g. INVESTING: accumulating assets 5 7 h. CHILDREN: college funding 2 2 * Prioritized with 1 being most important and 8 being least important They expect to receive $40,000 from the estate of Joanna's grandfather within the next 3 months and would like to use this inheritance to fund some of the goals (let's see how far this can make this inheritance go). Use the following assumptions: Investment Return on GI N/A Investment Return on G2: N/A Investment Return on G3: 2.25% Investment Return on G4: 1.50% Investment Return on G5: 4.00% Investment Return on G6: 5.00% OTHER INFORMATION 1. Are you able to save regularly? Joanna's contribution to her employer's 401(k) plan is the only regular savings. 2. How much do you save annually? 3% of Joanna's salary in her 401(k) 3. Do you invest regularly? Chip has dabbled in some investing but it's not regular. Usually, we try to invest bonuses or other windfalls received. 4. Do you feel you are financially organized? Not really 5. Do you budget your money? No we often overspend 6. If you were to die, could your spouse handle the finances? Chip - Probably, Joanna - Yes 7. How do you feel about saving for retirement? Joanna - It is very important. Chip - It's important but we still have time. 8. If you had an extra $5,000 what would you do with it? Chip - Take a vacation, Joanna - Pay down the credit card 9. How do you feel about taking investment risks? Chip - aggressive risk taker, Joanna moderate risk taker. 10. How is your health? Chip very good, Joanna very good
