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 employers 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.
• Its your mothers 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.

File Home Insert Draw Page Layout Formulas Data Review View Au H31 fx A B C D E F G For the income statement, fill in the income and expenses (grey cells) based on the facts given in the assignment. You are welcome to classify expenses as either discretionary \& non-discretionary or fixed \& variable. The formulas should calculate the total income and the total expenses. Create a formula to find the surplus or deficit. Income Statement From (Month, Year) to (Month, Year) For the balance sheet, fill in the assets and liabilities based on the facts given in the assignment. Only fill in the grey boxes. The formulas should add up all of the assets and liabilities. Create a formula to calculate ne worth. K26 A B C E F G H For the ratio analysis, fill in the numerator and denominator for each ratio in words (the current ratio has been completed for youl. Then calculate the ratios using the numbers from the balance sheet and income statement created in the two previous assignments. Last, fill in the suggested benchmark for the ratio. Fill in all grey cells. \begin{tabular}{|c|c|c|c|c|c|c|} \hline Emergency Fund Ratio & = & = & 0.000 & = & 0.00% \\ \hline \end{tabular} Benchmark and summary for the ratio: \begin{tabular}{|l|l|c|c|c|c|c|} \hline \multirow{2}{*}{ Current Ratio } & = & Current Assets & \multirow{2}{*}{0.000} & = & 0.00% \\ \hline \end{tabular} Benchmark and summary for the ratio: \begin{tabular}{|c|c|c|c|c|c|c|} \hline Housing Ratio 1 & = & & & 0.000 & = & 0.00% \\ \hline \end{tabular} Benchmark and summary for the ratio: \begin{tabular}{|c|c|c|c|c|c|c|} \hline Housing Ratio 2 & = & & & 0.000 & = & 0.00% \\ \hline \end{tabular} Benchmark and summary for the ratio: \begin{tabular}{|l|l|l|l|l|l|l|} \hline Debt to Total Assets Ratio & \multirow{2}{*}{=} & & = & 0.000 & = & 0.00% \\ \hline \end{tabular} Benchmark and summary for the ratio: Net Worth to Total Assets Ratio = +2 Benchmark and summary for the ratio: \begin{tabular}{l|l|l|l|} = & 0.000 & = & 0.00% \\ \hline \end{tabular} Benchmark and summary for the ratio: \begin{tabular}{|c|c|c|c|c|c|c|} \hline Savings Rate & = & & & 0.000 & = & 0.00% \\ \hline \end{tabular} Benchmark and summary for the ratio