Question

ONLY NEED HELP WITH QUESTION 2, PLEASE SHOW WORK IN EXCEL

Dean and Brittany are both 32 years old and have a three-year old child, Eddie. They came to your office and asked you to build a financial statement analysis for 2017, based on the information provided.

• 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 2015; 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.
• Their total annual gross income was $98,207; total dedicated annual expenses were $55,085, anddiscretionary expenses were 3,808.
• Their savings last year included $4,890 towards their retirement savings accounts (no employer match); $47 reinvested money market investment interest; and $1,890 contribution to a 529 plan for Eddie.

Based on the above the information (total 5 points):

1) Make a balance sheet for Dean and Brittany (total asset; total liabilities; net worth and

2) Report the following financial ratios: (a) Current Ratio (b) Emergency Fund Ratio and (c) Savings Ratio

PLEASE SHOW ANSWER IN EXCEL. PLEASE SHOW ALL WORK AND FUNCTIONS IN EXCEL! Thank You!

Answer 1

2. (a)Current ratio = current assets / current liabilities

Current assets = Cash &cash Equivalents + investment securities + prepaid expenses + inventories

Current assets = 2350+12320+6250+4890+25000=50810

Current liabilities = short term loan + current portion of long term debt + tax payable + bank loan

Current liabilities = 6180+136000+17470+19000+10390=189040

Current ratio = 50810/189040=0.2688/1

(b) Emergency fund ratio = liquid assets / expenses

Emergency fund ratio = 50810/55085=0.9224/1

(c) Saving ratio = annual Saving / annual income

Saving ratio = (4890+47+1890)/98207 = .0695/1