Assignment
Instructions
• Please create your own templates for the following financial statement analysis cases. Excel worksheets is required for cash flow and balance sheets.
• Double check if you include ALL the items. Missing one item would make your final calculation incorrect.
• This project is an INDIVIDUAL work. You need to be working on your own.
Dean and Brittany Case
Dean and Brittany are both 32 years old and have a three-year old child, Eddie. This is the beginning of 2017 and they came to your office and asked you to construct a financial statement analysis of the 2016, based on the information provided.
- Income: Dean earned $48,950 as a data analyst, and Brittany made $49,210 as a nurse supervisor. Dean and Brittany received $47 in interest from their money market account and they reinvested it.
- Taxes: Their W-2 forms indicate that they totally paid $6,891 FICA withholding, $9,879 federal income taxes, and $2,765 in state income taxes. Real estate taxes on their house were $1,759. Personal property taxes on their two vehicles were $289.
- Mortgage/loan payments: they paid $2,890 total mortgage interest and $450 principal last year. They were also required to pay mortgage insurance at $290. They totally paid $1,300 for their two car loans.
- Credit card payments: they are revolving credit card users, meaning they usually don't pay in full for the credit card balance, and instead, they paid minimum payments every month. Last year, they paid back $1,200 for their credit cards.
- Insurance: Medical insurance is provided by Dean's employer, but he has to pay a portion of the premium, which were $1,320. Their personal automobile policy premiums were $1,350. Homeowner insurance premiums for the year were $1,109. Brittany owns a life insurance policy on Dean's life, and she paid $390 in premiums last year. By the end of last year, this policy had a cash value of $8,000.
- Savings: Dean and Brittany contributed $3,890 to their IRAs, $1,000 to a 529 plan for Eddie, and $1,890 to their money market account. The money market account savings are for emergency purposes and they may change this saving amount based on current year's financial situations.
- Daily living expenses: They estimated that they spent $3,800 on food at home. Clothing expenditures were estimated to be $1,450; laundry and dry cleaning expenses were $280. Expenses for gas and maintenance for their vehicles were $3,890, and they have to drive to work everyday. Day care expenses for Eddie were about $5,000 last year.
- Utilities: Utilities for the year were $3,891, including gas, electricity, water, telephone, Internet access, and cable.
- Home maintenance: They spent $1,452 last year.
- Miscellaneous expenses: Unreimbursed medical expenses amounted to $421 (varies year by year).
- Assets: They have $2,350 in their checking account, $12,320 in their money market account, $6,250 in a mutual fund investment account, $4,890 in the 529 plan for Eddie, and $25,000 in their retirement accounts. Their house has a fair market value of $195,320. Dean owns a 2015 Honda with a fair market value of $18,670, and Brittany owns a 2012 MiniCooper valued at $24,450. The furniture and household goods had an estimated value of $15,420; they have sporting equipment estimated at $2,430.
- Liabilities: Dean and Brittany owed $6,180 on their Visa credit cards when they came to you, and they usually just pay the minimum required payment for credit card balance. The balance on their home mortgage was $136,000 at the end of 2016; their auto loan balance for Dean's car was $17,470, and for Brittany's car was $19,000. Brittany had a student loan which balance was $10,390.
Based on the above the information, make cash flow statement and balance sheet for Dean and Brittany, also report the following financial ratios.
1) Current Ratio
2) Emergency Fund Ratio
3) Debt Ratio
4) Broad Housing Ratio (Housing Ratio 2)
5) Savings Ratio.