Using the previous two assignments (create a balance sheet and create an income statement), analyze the...

Transcribed Image Text:

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 Google Sheet 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 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. Please use the LINK to access the template. Once complete, upload your own template to the assignment. Use your UVU email account to access the document 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 net worth. As of August, 2022 (Month, Year) Current Assets Checking Account Savings Account Cash (tax refund + cash + cash from friend for rent/utilities) Assets Total Current Assets Investment Assets Roth IRA Employer 401k Total Investment Assets Furniture Car Condo Ipad Graduation Ring TV Total Assets Personal Assets Total Personal Assets $ 250 $ 2,050 $ 1,535 $ $ 3,835 $ 1,250 $ 5,000 $ $ $ $ 6,250 $ 2,200 $ 15,000 $ 60,000 $ 500 $ 250 $ 820 ss $ $ 78,770 $ 88,855 Liabilities Short-Term Liabilities $ $ Credit card Medical (Dentist) SS Mortgage Student Loans Car Loan Funiture Loan Net Worth $ Total Short Term Liabilities $ 1,200 Long-Term Liabilities 800 400 $ 45,000 $ 23,000 $ 8,500 $ 650 $ Total Long Term Liabilities $77,150 Total Liability $ 78,350 $ 10,505 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 January, 2021 (Month, Year) to December, 2022 (Month, Year) Cash Flow Monthly INCOME Salary (gross) $ 2,700 Rent from roommate $ 450 Employer's contributio $ 125 Income Tax Refund $ 1,000 $ Total Income $ $ $ 4,275 EXPENSES Car Payment Medical Insurance Condo Insurance Car Insurance Monthly Groceries Monthly Utilities Gas Credit Card Birthday Present Fed, state, secruity tax Movie Vacation at the beach IRA, Employers 401K New Tire Dinner Outfit Mortgage TV Student Loan Payment Total Expenses Surplus or Deficit Cash Flow Monthly sssss $ $ $ $ 100 275 285 175 25 50 550 40 100 325 65 45 100 750 $ 500 150 is $ $ $ $ $ SSS $ $ $ $ $ $ s ss $ 555 $ 350 75 25 S $ 3,985 290 2 3 5 8 9 10 11 12 17 14 16 10 17- 18 19 20 21 22 23 24 25 20 27 9 For the ratio analysis, fill in the numerator and denominator for each ratio in words (the current ratio has been completed for you). 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. Emergency Fund Ratio Benchmark and summary for the ratio: Current Ratio Benchmark and summary for the ratio: Housing Ratio 1 Benchmark and summary for the ratio: Housing Ratio 2 Benchmark and summary for the ratio: Debt to Total Assets Ratio Benchmark and summary for the ratio: Net Worth to Total Assets Ratio Benchmark and summary for the ratio: Savings Rate Benchmark and summary for the ratio Current Assets Current Liabilities 0.000 0.000 0.000 WE 0,000 - 0.000 = 0.00% 0.000 M 0.00% 0.00% 0.00% 0.00% 0.00% 0.000 m 0.00% 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 Google Sheet 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 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. Please use the LINK to access the template. Once complete, upload your own template to the assignment. Use your UVU email account to access the document 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 net worth. As of August, 2022 (Month, Year) Current Assets Checking Account Savings Account Cash (tax refund + cash + cash from friend for rent/utilities) Assets Total Current Assets Investment Assets Roth IRA Employer 401k Total Investment Assets Furniture Car Condo Ipad Graduation Ring TV Total Assets Personal Assets Total Personal Assets $ 250 $ 2,050 $ 1,535 $ $ 3,835 $ 1,250 $ 5,000 $ $ $ $ 6,250 $ 2,200 $ 15,000 $ 60,000 $ 500 $ 250 $ 820 ss $ $ 78,770 $ 88,855 Liabilities Short-Term Liabilities $ $ Credit card Medical (Dentist) SS Mortgage Student Loans Car Loan Funiture Loan Net Worth $ Total Short Term Liabilities $ 1,200 Long-Term Liabilities 800 400 $ 45,000 $ 23,000 $ 8,500 $ 650 $ Total Long Term Liabilities $77,150 Total Liability $ 78,350 $ 10,505 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 January, 2021 (Month, Year) to December, 2022 (Month, Year) Cash Flow Monthly INCOME Salary (gross) $ 2,700 Rent from roommate $ 450 Employer's contributio $ 125 Income Tax Refund $ 1,000 $ Total Income $ $ $ 4,275 EXPENSES Car Payment Medical Insurance Condo Insurance Car Insurance Monthly Groceries Monthly Utilities Gas Credit Card Birthday Present Fed, state, secruity tax Movie Vacation at the beach IRA, Employers 401K New Tire Dinner Outfit Mortgage TV Student Loan Payment Total Expenses Surplus or Deficit Cash Flow Monthly sssss $ $ $ $ 100 275 285 175 25 50 550 40 100 325 65 45 100 750 $ 500 150 is $ $ $ $ $ SSS $ $ $ $ $ $ s ss $ 555 $ 350 75 25 S $ 3,985 290 2 3 5 8 9 10 11 12 17 14 16 10 17- 18 19 20 21 22 23 24 25 20 27 9 For the ratio analysis, fill in the numerator and denominator for each ratio in words (the current ratio has been completed for you). 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. Emergency Fund Ratio Benchmark and summary for the ratio: Current Ratio Benchmark and summary for the ratio: Housing Ratio 1 Benchmark and summary for the ratio: Housing Ratio 2 Benchmark and summary for the ratio: Debt to Total Assets Ratio Benchmark and summary for the ratio: Net Worth to Total Assets Ratio Benchmark and summary for the ratio: Savings Rate Benchmark and summary for the ratio Current Assets Current Liabilities 0.000 0.000 0.000 WE 0,000 - 0.000 = 0.00% 0.000 M 0.00% 0.00% 0.00% 0.00% 0.00% 0.000 m 0.00%

Answer rating: 100% (QA)

1 EMERGENCY FUND RATIO EMERGENCY FUND RATIO FORMULA TOTAL LIQUID ASSETSTOTAL MONTHLY EXPENSES ie Eme... View the full answer