Vertical and Horizontal Analysis, Ratio Analysis Assignment

Online tutoring services

Need help with this question or any other Accounting assignment help task? Click on the button below to to hire an expert cheap.

In the excel workbook attached I need Tab#3 and Tab#4 answered and fully completed this is the Vertical &Hori. Analysis as well as the Ratio Analysis In Tab#4 all yellow boxes must be answered as well as the text boxes: What is your conclusion about HCMH's profitabily Evidence indicating favorable profitibility Evidence indicating unfavorable profitability

Get Help With a similar task to - Vertical and Horizontal Analysis, Ratio Analysis Assignment

Login to view and/or buy answers.. or post an answer
Additional Instructions:
1. Instructions Hicks Community Mental Health (HCMH) Case Instructions Step 1. Please enter your name below. Name: Step 2. Before completing this case, please make sure to have completed the book readings and that you have reviewed the assigned PowerPoint slides for the session. Step 3. Review HCMH's financial statements (the Balance Sheet and Statement of Operations) on tab 2, "2. Financial Statements". Step 4. On this case assignment, you will perform a vertical and horizontal analysis on tab 3, "3. Vertical & Horiz. Analysis" by entering formulas in all of the YELLOW cells. Hint: Copying and pasting and dragging and dropping formulas will save you considerable time (but if you do this, make sure to use absolute and/or relative references as appropriate - use the Excel Help function to learn about absolute and relative references if needed). Please note the following in regard to the information given: 1. There are no Marketable Securities on the Balance Sheet. 2. The only category of Non-Operating Revenue is “Investment Income”. 3. For the Debt Service Coverage Ratio, use $700,000 as the principal payments. 4. Use Long-Term Liabilities for Long-Term Debt. Examples have been provided in the BLUE cells to get you started. Step 5. Perform a ratio analysis on tab 4, "4. Ratio Analysis" by entering formulas in all of the YELLOW cells. Examples are provided in the BLUE cells to get you started. Step 6. In addition to calculating ratios, tab 4 asks you to interpret the information from the balance sheet, income statement and your horizontal, vertical and ratio analyses in regard to HCMH’s profitability. In order to help you do this, your answer is organized into (a) evidence for a favorable interpretation, and (b) evidence indicating problems. Then, based on the insights you have provided in (a) and (b), draw a conclusion about the overall condition of HCMH’s profitability. To give you guidance, you have been provided with completed analyses of HCMH’s liquidity, efficiency (activity) and capital structure. Please review these before interpreting HCMH’s profitability and note how the ratios are not just restated, but instead information has been used from the balance sheet, income statement, and horizontal and vertical analyses, and even gone beyond in some cases. This is a new exercise for many of you. Its purpose is to move beyond the mechanics and to help you become comfortable not only calculating information, but to begin to develop skills in interpreting it so that you can make judgments about an organization’s financial condition. Please note that this step involves considerable judgment on your part, and there is no correct answer. Have fun, and give it a try! Case Presenters (HCMH CFO) and Respondents (HCMH Board): After preparing your notes on the HCMH's profitability, you will record a presentation with your conclusion and evidence indicating favorable and unfavorable profitability. Presenters: You are presenting your conclusion and analysis to the board. Record a 2-4 minute discussion of your presentation. Respondents: You are the asking questions or seeking clarification on points discussed in the CFO's presentation. You can respond with some of your own conclusions and analysis, but you also should pose 2-3 questions in response to the presenter's analysis. 2. Financial Statements Hicks Community Mental Health (HCMH) Case (Individual Assignment) Instructions Step 1. Review HCMH's financial statements below (the Balance Sheet and Statement of Operations). Step 2. Proceed to tab 3, "3. Vertical & Horiz. Analysis" and complete the required calculations in the yellow cells. Hicks Community Mental Health Balance Sheet June 30, 20X6, 20X7, 20X8 20X6 20X7 20X8 Assets Current Assets Cash $15,354,262 $14,917,043 $4,727,591 Net Accounts Receivable $6,805,997 $5,465,612 $8,424,123 Other Current Assets $5,422,448 $1,794,080 $990,856 Total Current Assets $27,582,707 $22,176,735 $14,142,570 Non-Current Assets Plant and Equipment $57,071,728 $48,348,185 $89,602,168 Accumulated Depreciation ($26,318,292) ($31,117,990) ($25,165,423) Net Plant and Equipment $30,753,436 $17,230,195 $64,436,745 Property $15,000,000 $15,000,000 $15,000,000 Investments $6,277,163 $6,876,544 $5,444,551 Other Assets $124,029 $145,205 $894,447 Total Noncurrent Assets $52,154,628 $39,251,944 $85,775,743 Total Assets $79,737,335 $61,428,679 $99,918,313 Current Liabilities $4,137,400 $6,276,728 $12,165,444 Long-Term Liabilities $2,800,000 $1,350,000 $26,870,000 Total Liabilities $6,937,400 $7,626,728 $39,035,444 Total Net Assets $72,799,935 $53,801,951 $60,882,869 Total Liabilities & Net Assets $79,737,335 $61,428,679 $99,918,313 Hicks Community Mental Health Statement of Operations For the Years Ended June 30, 20X6, 20X7, 20X8 6/30/20X6 6/30/20X7 6/30/20X8 Unrestricted Revenues and Gains Inpatient Revenue $43,658,011 $43,359,140 $40,711,231 Outpatient Revenue $24,515,805 $26,372,940 $30,471,883 Contributions, Donations, etc $1,947,818 $1,789,456 $0 Total Patient Revenue $70,121,634 $71,521,536 $71,183,114 Allowances & Discounts $20,856,802 $20,041,461 $22,042,302 Net Patient Revenue $49,264,832 $51,480,075 $49,140,812 Other Nonpatient Revenue $4,668,304 $4,374,037 $4,575,617 Total Operating Revenue $53,933,136 $55,854,112 $53,716,429 Expenses Interest Expense $335,132 $340,931 $2,250,094 Depreciation Expense $3,598,331 $3,905,469 $4,502,246 Other Expenses Labor $27,096,241 $28,766,944 $29,586,731 Misc. $13,548,121 $14,383,472 $14,793,365 Total Other Expenses $40,644,362 $43,150,416 $44,380,096 Total Operating Expenses $44,577,825 $47,396,816 $51,132,436 Operating Income $9,355,311 $8,457,296 $2,583,993 Investment Income $1,564,852 $2,339,526 $2,410,597 Total Non-operating Revenue $1,564,852 $2,339,526 $2,410,597 Excess of Revenues Over Expenses $10,920,163 $10,796,822 $4,994,590 Transfer to Parent Corporation $0 $0 $0 Increase in Unrestricted Net Assets $10,920,163 $10,796,822 $4,994,590 &A Page &P of &N 3. Vertical & Horiz. Analysis Hicks Community Mental Health (HCMH) Case (Individual Assignment) Instructions Step 1. Perform a vertical and horizontal analysis by entering formulas in all of the YELLOW cells. Copying and pasting and dragging and dropping formulas will save you considerable time (but if you do this, make sure to use absolute and/or relative references as appropriate - use the Excel Help function to learn about absolute and relative references if needed). Examples are provided in the BLUE cells to get you started. Step 2. Proceed to tab 4, "4. Ratio Analysis" and complete the required calculations in yellow cells. Hicks Community Mental Health Balance Sheet - Vertical & Horizontal Analysis June 30, 20X6, 20X7, 20X8 20X6 % of Total Assets 20X7 % of Total Assets 20X8 % of Total Assets % change from 20X6-20X8 Assets Current Assets Cash $15,354,262 19% $14,917,043 24% $4,727,591 5% -69% Net Accounts Receivable $6,805,997 $5,465,612 $8,424,123 Other Current Assets $5,422,448 $1,794,080 $990,856 Total Current Assets $27,582,707 $22,176,735 $14,142,570 Non-Current Assets Plant and Equipment $57,071,728 $48,348,185 $89,602,168 Accumulated Depreciation ($26,318,292) ($31,117,990) ($25,165,423) Net Plant and Equipment $30,753,436 $17,230,195 $64,436,745 Property $15,000,000 $15,000,000 $15,000,000 Investments $6,277,163 $6,876,544 $5,444,551 Other Assets $124,029 $145,205 $894,447 Total Noncurrent Assets $52,154,628 $39,251,944 $85,775,743 Total Assets $79,737,335 $61,428,679 $99,918,313 20X6 % of Total Liab. & Net Assets 20X7 % of Total Liab. & Net Assets 20X8 % of Total Liab. & Net Assets % change from 2006-2008 Current Liabilities $4,137,400 $6,276,728 $12,165,444 Long-Term Liabilities $2,800,000 $1,350,000 $26,870,000 Total Liabilities $6,937,400 $7,626,728 $39,035,444 Total Net Assets $72,799,935 $53,801,951 $60,882,869 Total Liabilities & Net Assets $79,737,335 $61,428,679 $99,918,313 Hicks Community Mental Health Statement of Operations For the Years Ended June 30, 20X6, 20X7, 20X8 6/30/20X6 % of Operating Revenue 6/30/20X7 % of Operating Revenue 6/30/20X8 % of Operating Revenue % change from 20X6-20X8 Unrestricted Revenues and Gains Inpatient Revenue $43,658,011 $43,359,140 $40,711,231 Outpatient Revenue $24,515,805 $26,372,940 $30,471,883 Contributions, Donations, etc $1,947,818 $1,789,456 $0 Total Patient Revenue $70,121,634 $71,521,536 $71,183,114 Allowances & Discounts $20,856,802 $20,041,461 $22,042,302 Net Patient Revenue $49,264,832 $51,480,075 $49,140,812 Other Nonpatient Revenue $4,668,304 $4,374,037 $4,575,617 Total Operating Revenue $53,933,136 $55,854,112 $53,716,429 Expenses Interest Expense $335,132 $340,931 $2,250,094 Depreciation Expense $3,598,331 $3,905,469 $4,502,246 Other Expenses Labor $27,096,241 $28,766,944 $29,586,731 Misc. $13,548,121 $14,383,472 $14,793,365 Total Other Expenses $40,644,362 $43,150,416 $44,380,096 Total Expenses $44,577,825 $47,396,816 $51,132,436 Operating Income $9,355,311 $8,457,296 $2,583,993 Investment Income $1,564,852 $2,339,526 $2,410,597 Total Non-operating Revenue $1,564,852 $2,339,526 $2,410,597 Excess of Revenues Over Expenses $10,920,163 $10,796,822 $4,994,590 Increase in Unrestricted Net Assets $10,920,163 $10,796,822 $4,994,590 &A Page &P of &N 4. Ratio Analysis Hicks Community Mental Health (HCMH) Case (Individual Assignment) Instructions Step 1. Perform a ratio analysis by entering formulas in all of the YELLOW cells below. Examples have been provided in the BLUE cells to get you started. Step 2. Interpret the information from the balance sheet, income statement and your horizontal, vertical and ratio analyses in regard to HCMH’s profitability. In order to help you do this, your answer is organized into (a) evidence for a favorable interpretation, and (b) evidence indicating problems. Then, based on the insights you have provided in (a) and (b), draw a conclusion about the overall condition of HCMH’s profitability. To give you guidance, you have been provided with completed analyses of HCMH’s liquidity, efficiency (activity) and capital structure. Please review these before interpreting HCMH’s profitability and note how ratios are not just restated, but instead information has been used from the balance sheet, income statement, and horizontal and vertical analyses, and even gone beyond in some cases. This is a new exercise for many of you. Its purpose is to move beyond the mechanics and to help you become comfortable not only calculating information, but to begin to develop skills in interpreting it so that you can make judgments about an organization’s financial condition. Please note that this step involves considerable judgment on your part, and there is no correct answer. Have fun, and give it a try! Ratio's 20X8 Ratio's % age LIQUIDITY RATIOS Desired Actual above/below 20X6 20X7 20X8 Standard Position Position Standard Current Ratio 6.67 3.53 1.16 1.84 Above Below -36.8% (current assets/current liabilities) Quick Ratio 1.48 Above ((cash+marketable securities+net AR)/current liabilities) Acid Test Ratio 0.11 Above ((cash+marketable securities)/current liabilities) Days in AR 68 Below ((net patient AR/(net patient revenues/365)) Days Cash on Hand 45 Above ((cash+marketable securities)/((operating expenses-depreciation expense)/365)) Average Payment Period 67 Below (current liabilities/((total expenses-depreciation expense)/365)) What is your conclusion about HCMH's liquidity? A-Excellent B-Good; there are minor problems C-Poor; there are significant problems Evidence indicating favorable liquidity. Use the financial statements, vertical & horizontal analysis, and the ratios to justify your position. - HCMH has collected almost 8 additional days (over $1M) of revenues relative to standard. - HCMH has over 3 times as much cash and marketable securities on hand than standard to cover their maturing liabilities. - Although cash has decreased 69% to ~$4.4M, it may be that this was used to help modernize/upgrade the plant and equipment and related processes. If this is so, HCMH is positioning itself by increasing capacity which may result in additional profits. Evidence indicating unfavorable liquidity. Use the financial statements, vertical & horizontal analysis, and the ratios to justify your position. - Cash dropped from $15.3 MM to $4.7 MM between 20X6 and 20X8. - Current liabilities increased from $4.1 MM to $12.2 MM between 20X6 and 20X8. - Current and quick ratio experienced large drops in 20X8 and are now over 25% below the standard benchmark. - HCMH has 8 days less cash on hand (~$1M) to meet its obligations than standard and average payment period is below standard benchmark. Ratio's 20X8 Ratio's % age PROFITABILITY RATIOS Desired Actual above/below 20X6 20X7 20X8 Standard Position Position Standard Operating Margin Ratio 0.04 Above (operating income/total operating revenues) Non-operating Revenue Ratio 0.09 Varies (non-operating revenues/total operating revenues) Return on Total Assets 0.04 Above (excess of revenues over expenses/total assets) Return on Net Assets 0.10 Above (excess of revenues over expenses/net assets) What is your conclusion about HCMH's profitability? (pick one) A-Excellent B-Good; there are minor problems C-Poor; there are significant problems Evidence indicating favorable profitability. Use the financial statements, vertical & horizontal analysis, and the ratios to justify your position. Evidence indicating unfavorable profitability. Use the financial statements, vertical & horizontal analysis, and the ratios to justify your position. Ratio's 20X8 Ratio's % age ACTIVITY RATIOS Desired Actual above/below 20X6 20X7 20X8 Standard Position Position Standard Total Asset Turnover 0.81 Above (total operating revenues/total assets) Fixed Asset Turnover 3.62 Above (total operating revenues/net plant and equipment) Age of Plant Ratio 9.74 Below (accumulated depreciation/depreciation expense) What is your conclusion about HCMH's activity? A-Excellent B-Good; there are minor problems C-Poor; there are significant problems Evidence indicating favorable activity. Use the financial statements, vertical & horizontal analysis, and the ratios to justify your position. - HCMH decreased their average age of plant by two years (from ~7.3 to ~5.6 years) so that its PP&E are over 3 years younger, on average than standard. Evidence indicating unfavorable activity. Use the financial statements, vertical & horizontal analysis, and the ratios to justify your position. - HCMH's ability to generate revenues on their assets is problematic and has been decreasing, and their total and fixed asset turnover ratios which are now ~33% and `75% below standard . Total operating revenues have stayed relatively flat from 2006 -2008 at around $50,000,000. - With a fixed asset turnover ratio unfavorably 77% below the benchmark, it can be estimated that HCMH should be earning closer to $230 million in total operating revenues rather than ~$50 million it now generates. - Though the age of the plant is in a favorable position compared to the benchmark, there could be problems if these new/renewed assets do not generate sufficient revenue. Ratio's 20X8 Ratio's % age CAPITAL STRUCTURE RATIOS Desired Actual above/below 20X6 20X7 20X8 Standard Position Position Standard Long-term debt to Net Assets Ratio 0.64 Below (long term debt/net assets) Net Assets to Total Assets Ratio 0.52 Above (net assets/total assets) Times Interest Earned Ratio 4.31 Above ((excess of revenues over expenses+interest expense)/interest expense) Debt Service Coverage Ratio 5.59 Above ((excess of revenues over expenses+interest expense+depreciation expense)/ (interest expense+principal payments)) What is your conclusion about HCMH's capital structure? A-Excellent B-Good; there are minor problems C-Poor; there are significant problems Evidence indicating favorable capital structure. Use the financial statements, vertical & horizontal analysis, and the ratios to justify your position. - Though HCMH increased long-term debt by 800% (to ~ $25 million), relative to standard, their long-term debt to net assets ratio reveals HCMH is in a favorable position to take on additional debt. - The capital structure of HCMH has changed considerably from ~9% debt to approximately ~40% debt of their total liabilities and net assets; this brought HMCH more in line with the benchmark of 64% debt. Evidence indicating unfavorable capital structure. Use the financial statements, vertical & horizontal analysis, and the ratios to justify your position. - While as noted above, the Long-term Debt to Net Asset ratios indicates that HCMH has the ability to take on additional debt relative to standard, the times interest earned and debt service coverage ratios indicate HCMH may have difficulty paying off this debt. These ratios are decreasing (to 3.2 and 4.0 respectively) and both are over 25% below standard of 4.31 and 5.59 respectively). &A Page &P of &N &A Page &P of &N
1. Instructions Hicks Community Mental Health (HCMH) Case Instructions Step 1. Please enter your name below. Name: Step 2. Before completing this case, please make sure to have completed the book readings and that you have reviewed the assigned PowerPoint slides for the session. Step 3. Review HCMH's financial statements (the Balance Sheet and Statement of Operations) on tab 2, "2. Financial Statements". Step 4. On this case assignment, you will perform a vertical and horizontal analysis on tab 3, "3. Vertical & Horiz. Analysis" by entering formulas in all of the YELLOW cells. Hint: Copying and pasting and dragging and dropping formulas will save you considerable time (but if you do this, make sure to use absolute and/or relative references as appropriate - use the Excel Help function to learn about absolute and relative references if needed). Please note the following in regard to the information given: 1. There are no Marketable Securities on the Balance Sheet. 2. The only category of Non-Operating Revenue is “Investment Income”. 3. For the Debt Service Coverage Ratio, use $700,000 as the principal payments. 4. Use Long-Term Liabilities for Long-Term Debt. Examples have been provided in the BLUE cells to get you started. Step 5. Perform a ratio analysis on tab 4, "4. Ratio Analysis" by entering formulas in all of the YELLOW cells. Examples are provided in the BLUE cells to get you started. Step 6. In addition to calculating ratios, tab 4 asks you to interpret the information from the balance sheet, income statement and your horizontal, vertical and ratio analyses in regard to HCMH’s profitability. In order to help you do this, your answer is organized into (a) evidence for a favorable interpretation, and (b) evidence indicating problems. Then, based on the insights you have provided in (a) and (b), draw a conclusion about the overall condition of HCMH’s profitability. To give you guidance, you have been provided with completed analyses of HCMH’s liquidity, efficiency (activity) and capital structure. Please review these before interpreting HCMH’s profitability and note how the ratios are not just restated, but instead information has been used from the balance sheet, income statement, and horizontal and vertical analyses, and even gone beyond in some cases. This is a new exercise for many of you. Its purpose is to move beyond the mechanics and to help you become comfortable not only calculating information, but to begin to develop skills in interpreting it so that you can make judgments about an organization’s financial condition. Please note that this step involves considerable judgment on your part, and there is no correct answer. Have fun, and give it a try! Case Presenters (HCMH CFO) and Respondents (HCMH Board): After preparing your notes on the HCMH's profitability, you will record a presentation with your conclusion and evidence indicating favorable and unfavorable profitability. Presenters: You are presenting your conclusion and analysis to the board. Record a 2-4 minute discussion of your presentation. Respondents: You are the asking questions or seeking clarification on points discussed in the CFO's presentation. You can respond with some of your own conclusions and analysis, but you also should pose 2-3 questions in response to the presenter's analysis. 2. Financial Statements Hicks Community Mental Health (HCMH) Case (Individual Assignment) Instructions Step 1. Review HCMH's financial statements below (the Balance Sheet and Statement of Operations). Step 2. Proceed to tab 3, "3. Vertical & Horiz. Analysis" and complete the required calculations in the yellow cells. Hicks Community Mental Health Balance Sheet June 30, 20X6, 20X7, 20X8 20X6 20X7 20X8 Assets Current Assets Cash $15,354,262 $14,917,043 $4,727,591 Net Accounts Receivable $6,805,997 $5,465,612 $8,424,123 Other Current Assets $5,422,448 $1,794,080 $990,856 Total Current Assets $27,582,707 $22,176,735 $14,142,570 Non-Current Assets Plant and Equipment $57,071,728 $48,348,185 $89,602,168 Accumulated Depreciation ($26,318,292) ($31,117,990) ($25,165,423) Net Plant and Equipment $30,753,436 $17,230,195 $64,436,745 Property $15,000,000 $15,000,000 $15,000,000 Investments $6,277,163 $6,876,544 $5,444,551 Other Assets $124,029 $145,205 $894,447 Total Noncurrent Assets $52,154,628 $39,251,944 $85,775,743 Total Assets $79,737,335 $61,428,679 $99,918,313 Current Liabilities $4,137,400 $6,276,728 $12,165,444 Long-Term Liabilities $2,800,000 $1,350,000 $26,870,000 Total Liabilities $6,937,400 $7,626,728 $39,035,444 Total Net Assets $72,799,935 $53,801,951 $60,882,869 Total Liabilities & Net Assets $79,737,335 $61,428,679 $99,918,313 Hicks Community Mental Health Statement of Operations For the Years Ended June 30, 20X6, 20X7, 20X8 6/30/20X6 6/30/20X7 6/30/20X8 Unrestricted Revenues and Gains Inpatient Revenue $43,658,011 $43,359,140 $40,711,231 Outpatient Revenue $24,515,805 $26,372,940 $30,471,883 Contributions, Donations, etc $1,947,818 $1,789,456 $0 Total Patient Revenue $70,121,634 $71,521,536 $71,183,114 Allowances & Discounts $20,856,802 $20,041,461 $22,042,302 Net Patient Revenue $49,264,832 $51,480,075 $49,140,812 Other Nonpatient Revenue $4,668,304 $4,374,037 $4,575,617 Total Operating Revenue $53,933,136 $55,854,112 $53,716,429 Expenses Interest Expense $335,132 $340,931 $2,250,094 Depreciation Expense $3,598,331 $3,905,469 $4,502,246 Other Expenses Labor $27,096,241 $28,766,944 $29,586,731 Misc. $13,548,121 $14,383,472 $14,793,365 Total Other Expenses $40,644,362 $43,150,416 $44,380,096 Total Operating Expenses $44,577,825 $47,396,816 $51,132,436 Operating Income $9,355,311 $8,457,296 $2,583,993 Investment Income $1,564,852 $2,339,526 $2,410,597 Total Non-operating Revenue $1,564,852 $2,339,526 $2,410,597 Excess of Revenues Over Expenses $10,920,163 $10,796,822 $4,994,590 Transfer to Parent Corporation $0 $0 $0 Increase in Unrestricted Net Assets $10,920,163 $10,796,822 $4,994,590 &A Page &P of &N 3. Vertical & Horiz. Analysis Hicks Community Mental Health (HCMH) Case (Individual Assignment) Instructions Step 1. Perform a vertical and horizontal analysis by entering formulas in all of the YELLOW cells. Copying and pasting and dragging and dropping formulas will save you considerable time (but if you do this, make sure to use absolute and/or relative references as appropriate - use the Excel Help function to learn about absolute and relative references if needed). Examples are provided in the BLUE cells to get you started. Step 2. Proceed to tab 4, "4. Ratio Analysis" and complete the required calculations in yellow cells. Hicks Community Mental Health Balance Sheet - Vertical & Horizontal Analysis June 30, 20X6, 20X7, 20X8 20X6 % of Total Assets 20X7 % of Total Assets 20X8 % of Total Assets % change from 20X6-20X8 Assets Current Assets Cash $15,354,262 19% $14,917,043 24% $4,727,591 5% -69% Net Accounts Receivable $6,805,997 $5,465,612 $8,424,123 Other Current Assets $5,422,448 $1,794,080 $990,856 Total Current Assets $27,582,707 $22,176,735 $14,142,570 Non-Current Assets Plant and Equipment $57,071,728 $48,348,185 $89,602,168 Accumulated Depreciation ($26,318,292) ($31,117,990) ($25,165,423) Net Plant and Equipment $30,753,436 $17,230,195 $64,436,745 Property $15,000,000 $15,000,000 $15,000,000 Investments $6,277,163 $6,876,544 $5,444,551 Other Assets $124,029 $145,205 $894,447 Total Noncurrent Assets $52,154,628 $39,251,944 $85,775,743 Total Assets $79,737,335 $61,428,679 $99,918,313 20X6 % of Total Liab. & Net Assets 20X7 % of Total Liab. & Net Assets 20X8 % of Total Liab. & Net Assets % change from 2006-2008 Current Liabilities $4,137,400 $6,276,728 $12,165,444 Long-Term Liabilities $2,800,000 $1,350,000 $26,870,000 Total Liabilities $6,937,400 $7,626,728 $39,035,444 Total Net Assets $72,799,935 $53,801,951 $60,882,869 Total Liabilities & Net Assets $79,737,335 $61,428,679 $99,918,313 Hicks Community Mental Health Statement of Operations For the Years Ended June 30, 20X6, 20X7, 20X8 6/30/20X6 % of Operating Revenue 6/30/20X7 % of Operating Revenue 6/30/20X8 % of Operating Revenue % change from 20X6-20X8 Unrestricted Revenues and Gains Inpatient Revenue $43,658,011 $43,359,140 $40,711,231 Outpatient Revenue $24,515,805 $26,372,940 $30,471,883 Contributions, Donations, etc $1,947,818 $1,789,456 $0 Total Patient Revenue $70,121,634 $71,521,536 $71,183,114 Allowances & Discounts $20,856,802 $20,041,461 $22,042,302 Net Patient Revenue $49,264,832 $51,480,075 $49,140,812 Other Nonpatient Revenue $4,668,304 $4,374,037 $4,575,617 Total Operating Revenue $53,933,136 $55,854,112 $53,716,429 Expenses Interest Expense $335,132 $340,931 $2,250,094 Depreciation Expense $3,598,331 $3,905,469 $4,502,246 Other Expenses Labor $27,096,241 $28,766,944 $29,586,731 Misc. $13,548,121 $14,383,472 $14,793,365 Total Other Expenses $40,644,362 $43,150,416 $44,380,096 Total Expenses $44,577,825 $47,396,816 $51,132,436 Operating Income $9,355,311 $8,457,296 $2,583,993 Investment Income $1,564,852 $2,339,526 $2,410,597 Total Non-operating Revenue $1,564,852 $2,339,526 $2,410,597 Excess of Revenues Over Expenses $10,920,163 $10,796,822 $4,994,590 Increase in Unrestricted Net Assets $10,920,163 $10,796,822 $4,994,590 &A Page &P of &N 4. Ratio Analysis Hicks Community Mental Health (HCMH) Case (Individual Assignment) Instructions Step 1. Perform a ratio analysis by entering formulas in all of the YELLOW cells below. Examples have been provided in the BLUE cells to get you started. Step 2. Interpret the information from the balance sheet, income statement and your horizontal, vertical and ratio analyses in regard to HCMH’s profitability. In order to help you do this, your answer is organized into (a) evidence for a favorable interpretation, and (b) evidence indicating problems. Then, based on the insights you have provided in (a) and (b), draw a conclusion about the overall condition of HCMH’s profitability. To give you guidance, you have been provided with completed analyses of HCMH’s liquidity, efficiency (activity) and capital structure. Please review these before interpreting HCMH’s profitability and note how ratios are not just restated, but instead information has been used from the balance sheet, income statement, and horizontal and vertical analyses, and even gone beyond in some cases. This is a new exercise for many of you. Its purpose is to move beyond the mechanics and to help you become comfortable not only calculating information, but to begin to develop skills in interpreting it so that you can make judgments about an organization’s financial condition. Please note that this step involves considerable judgment on your part, and there is no correct answer. Have fun, and give it a try! Ratio's 20X8 Ratio's % age LIQUIDITY RATIOS Desired Actual above/below 20X6 20X7 20X8 Standard Position Position Standard Current Ratio 6.67 3.53 1.16 1.84 Above Below -36.8% (current assets/current liabilities) Quick Ratio 1.48 Above ((cash+marketable securities+net AR)/current liabilities) Acid Test Ratio 0.11 Above ((cash+marketable securities)/current liabilities) Days in AR 68 Below ((net patient AR/(net patient revenues/365)) Days Cash on Hand 45 Above ((cash+marketable securities)/((operating expenses-depreciation expense)/365)) Average Payment Period 67 Below (current liabilities/((total expenses-depreciation expense)/365)) What is your conclusion about HCMH's liquidity? A-Excellent B-Good; there are minor problems C-Poor; there are significant problems Evidence indicating favorable liquidity. Use the financial statements, vertical & horizontal analysis, and the ratios to justify your position. - HCMH has collected almost 8 additional days (over $1M) of revenues relative to standard. - HCMH has over 3 times as much cash and marketable securities on hand than standard to cover their maturing liabilities. - Although cash has decreased 69% to ~$4.4M, it may be that this was used to help modernize/upgrade the plant and equipment and related processes. If this is so, HCMH is positioning itself by increasing capacity which may result in additional profits. Evidence indicating unfavorable liquidity. Use the financial statements, vertical & horizontal analysis, and the ratios to justify your position. - Cash dropped from $15.3 MM to $4.7 MM between 20X6 and 20X8. - Current liabilities increased from $4.1 MM to $12.2 MM between 20X6 and 20X8. - Current and quick ratio experienced large drops in 20X8 and are now over 25% below the standard benchmark. - HCMH has 8 days less cash on hand (~$1M) to meet its obligations than standard and average payment period is below standard benchmark. Ratio's 20X8 Ratio's % age PROFITABILITY RATIOS Desired Actual above/below 20X6 20X7 20X8 Standard Position Position Standard Operating Margin Ratio 0.04 Above (operating income/total operating revenues) Non-operating Revenue Ratio 0.09 Varies (non-operating revenues/total operating revenues) Return on Total Assets 0.04 Above (excess of revenues over expenses/total assets) Return on Net Assets 0.10 Above (excess of revenues over expenses/net assets) What is your conclusion about HCMH's profitability? (pick one) A-Excellent B-Good; there are minor problems C-Poor; there are significant problems Evidence indicating favorable profitability. Use the financial statements, vertical & horizontal analysis, and the ratios to justify your position. Evidence indicating unfavorable profitability. Use the financial statements, vertical & horizontal analysis, and the ratios to justify your position. Ratio's 20X8 Ratio's % age ACTIVITY RATIOS Desired Actual above/below 20X6 20X7 20X8 Standard Position Position Standard Total Asset Turnover 0.81 Above (total operating revenues/total assets) Fixed Asset Turnover 3.62 Above (total operating revenues/net plant and equipment) Age of Plant Ratio 9.74 Below (accumulated depreciation/depreciation expense) What is your conclusion about HCMH's activity? A-Excellent B-Good; there are minor problems C-Poor; there are significant problems Evidence indicating favorable activity. Use the financial statements, vertical & horizontal analysis, and the ratios to justify your position. - HCMH decreased their average age of plant by two years (from ~7.3 to ~5.6 years) so that its PP&E are over 3 years younger, on average than standard. Evidence indicating unfavorable activity. Use the financial statements, vertical & horizontal analysis, and the ratios to justify your position. - HCMH's ability to generate revenues on their assets is problematic and has been decreasing, and their total and fixed asset turnover ratios which are now ~33% and `75% below standard . Total operating revenues have stayed relatively flat from 2006 -2008 at around $50,000,000. - With a fixed asset turnover ratio unfavorably 77% below the benchmark, it can be estimated that HCMH should be earning closer to $230 million in total operating revenues rather than ~$50 million it now generates. - Though the age of the plant is in a favorable position compared to the benchmark, there could be problems if these new/renewed assets do not generate sufficient revenue. Ratio's 20X8 Ratio's % age CAPITAL STRUCTURE RATIOS Desired Actual above/below 20X6 20X7 20X8 Standard Position Position Standard Long-term debt to Net Assets Ratio 0.64 Below (long term debt/net assets) Net Assets to Total Assets Ratio 0.52 Above (net assets/total assets) Times Interest Earned Ratio 4.31 Above ((excess of revenues over expenses+interest expense)/interest expense) Debt Service Coverage Ratio 5.59 Above ((excess of revenues over expenses+interest expense+depreciation expense)/ (interest expense+principal payments)) What is your conclusion about HCMH's capital structure? A-Excellent B-Good; there are minor problems C-Poor; there are significant problems Evidence indicating favorable capital structure. Use the financial statements, vertical & horizontal analysis, and the ratios to justify your position. - Though HCMH increased long-term debt by 800% (to ~ $25 million), relative to standard, their long-term debt to net assets ratio reveals HCMH is in a favorable position to take on additional debt. - The capital structure of HCMH has changed considerably from ~9% debt to approximately ~40% debt of their total liabilities and net assets; this brought HMCH more in line with the benchmark of 64% debt. Evidence indicating unfavorable capital structure. Use the financial statements, vertical & horizontal analysis, and the ratios to justify your position. - While as noted above, the Long-term Debt to Net Asset ratios indicates that HCMH has the ability to take on additional debt relative to standard, the times interest earned and debt service coverage ratios indicate HCMH may have difficulty paying off this debt. These ratios are decreasing (to 3.2 and 4.0 respectively) and both are over 25% below standard of 4.31 and 5.59 respectively). &A Page &P of &N &A Page &P of &N

Related Questions

Similar orders to Vertical and Horizontal Analysis, Ratio Analysis Assignment
31
Views
0
Answers
Bank reconciliation, calculating payroll
Calculate payroll gross to net for each employee. Calculate the employer’s federal tax deposit due. Ucancy is a biweekly filer. The company matches payroll contributions up to 5%. What is the employer’s contribution? Reconcile the book balance to the bank balance using the bank statement provided....
47
Views
0
Answers
Help my exam, I send the question that I don't know to the tutor and the tutor give me the answer
I want a tutor to help with my accounting exam. I send the question that I don't know to the tutor and the tutor give me the answer. The exam time is at 03:00 pm AEDT time for 3 hours...
33
Views
0
Answers
I have an accounting exam for my finals this November 17 for a duration of three hours 8-11 Philippines
An accounting student that has free time this November 17 from 8-11 AM, the exam is all about basic accounting since I'm a 1st year accounting student. The topics include merchandising, ledgers, etc. (I will send the topics once you accept). The items will not exceed 100....
28
Views
0
Answers
I have an accounting exam for my finals this November 17 for a duration of three hours 8-11 Philippines
An accounting student that has free time this November 17 from 8-11 AM, the exam is all about basic accounting since I'm a 1st year accounting student. The topics include merchandising, ledgers, etc. (I will send the topics once you accept). The items will not exceed 100....