In the excel file I need Tab 3 and 4 completed
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 20X620X720X8 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/20X66/30/20X76/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 Assets20X7% of Total Assets20X8% of Total Assets% change from 20X6-20X8 Assets Current Assets Cash$15,354,26219%$14,917,04324%$4,727,5915%-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 Assets20X7% of Total Liab. & Net Assets20X8% 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 Revenue6/30/20X7% of Operating Revenue6/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's20X8 Ratio's% age LIQUIDITY RATIOSDesiredActualabove/below 20X620X720X8StandardPositionPositionStandard Current Ratio6.673.531.161.84AboveBelow-36.8% (current assets/current liabilities) Quick Ratio1.48Above ((cash+marketable securities+net AR)/current liabilities) Acid Test Ratio0.11Above ((cash+marketable securities)/current liabilities) Days in AR68Below ((net patient AR/(net patient revenues/365)) Days Cash on Hand45Above ((cash+marketable securities)/((operating expenses-depreciation expense)/365)) Average Payment Period67Below (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.