(Solved): Create Balance SheetUse the following to create a balance sheet (excel template provided below) You ...

Create Balance Sheet

Use the following to create a balance sheet (excel template provided below)

• You have $250 in your checking account.
• You have $2,050 in your savings account.
• You purchased an iPad last summer and it is valued at $500.
• You bought a graduation ring and it is valued at $250.
• You purchased new furniture for your apartment. It is valued at $2,200 but you still owe $650.
• Your car is valued at $15,000 but you still owe $8,500.
• You owe $800 on a credit card.
• You just opened a Roth IRA and have put $1,250.
• You participate in your employer’s 401k program. You have saved $5,000 so far.
• You needed a root canal procedure at your dentist office. You owe $400.
• You just cashed your income tax refund check of $1,000.
• You purchased a new TV that is valued at $820.
• You have $85 in cash.
• You have a student loan balance of $23,000.
• You purchase a condo for $60,000 and you owe $45,000 on the mortgage.
• You and your best friend move into your new condo. They just paid you $450 cash for monthly rent and utilities.Create Income Statement

Create Income Statement

Use the following to create an income statement (excel template provided below).

• You pay a $350 car payment.
• You receive your monthly gross salary of $2,700.
• You receive a rent payment from your roommate of $450 per month.
• You pay your medical insurance of $75 a month.
• You pay your condo insurance of $25.
• You pay your car insurance of $100.
• You pay for monthly groceries of $275.
• Your monthly utilities are due. You owe $185.
• You pay $25 toward your credit card.
• Your monthly bill for gasoline comes in and you must pay $175.
• It’s your mother’s birthday. You purchase a $50 present.
• Taxes come out of your paycheck. Federal Tax $250 and State Tax $50 and Social Security $250.
• You go to a movie with a friend that costs $40.
• You put $100 into a savings account for a vacation at the beach.
• You put $200 in your Roth IRA and $125 in your employer's 401k. Your employer also matches your contribution 100% for the 401k.
• You hit a pothole and have a flat tire. You must pay $65 for a new one.
• You go out to dinner with friends. Your bill is $45.
• You find the perfect outfit for your date this weekend. You pay $100 for the outfit.
• You pay your condo mortgage payment of $750.
• You just received your income tax refund of $1,000. You decided to spend half on a flat-screen TV.
• You pay a $150 student loan payment.

Ratio Analysis of Financial Statements

Using the previous two assignments (create a balance sheet and create an income statement), analyze the financial situation with ratios. After analyzing your financial statements, use the attached excel template to complete the ratio analysis.

Calculate the following ratios, compare the calculated value to the benchmark AND provide a summary of what the calculated value means.

1. Emergency Fund ratio
2. Current ratio
3. Housing ratio #1
4. Housing ratio #2
5. Debt to Total Assets
6. Net Worth to Total Assets
7. Retirement Savings Adequacy ratio (savings rate)

For example, if my cash and cash equivalents (cash, money market fund, CDs, etc.) are $53,400 and my current liabilities (credit cards, car loan due within 1 year, etc.) are $39,679, then my current ratio would be (53,400/39, 679=1.3458). The benchmark for this ratio should be above 1. The value means that the client can pay for all of their current liabilities with the amount of cash on hand and still have some cash left. This is a very good place to be for this client.