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 co
ect 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
Cu
ent Assets
Cash $15,354,262 $14,917,043 $4,727,591
Net Accounts Receivable $6,805,997 $5,465,612 $8,424,123
Other Cu
ent Assets $5,422,448 $1,794,080 $990,856
Total Cu
ent Assets $27,582,707 $22,176,735 $14,142,570
Non-Cu
ent 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 Noncu
ent Assets $52,154,628 $39,251,944 $85,775,743
Total Assets $79,737,335 $61,428,679 $99,918,313
Cu
ent 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
Cu
ent 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 Cu
ent Assets $5,422,448 $1,794,080 $990,856
Total Cu
ent Assets $27,582,707 $22,176,735 $14,142,570
Non-Cu
ent 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 Noncu
ent 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 XXXXXXXXXX
Cu
ent 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 co
ect answer. Have fun, and give it a try!
Ratio's 20X8 Ratio's % age
LIQUIDITY RATIOS Desired Actual above
elow
20X6 20X7 20X8 Standard Position Position Standard
Cu
ent Ratio 6.67 3.53 1.16 1.84 Above Below -36.8%
(cu
ent assets/cu
ent liabilities)
Quick Ratio 1.48 Above
((cash+marketable securities+net AR)/cu
ent liabilities)
Acid Test Ratio 0.11 Above
((cash+marketable securities)/cu
ent 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
(cu
ent 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.