All data is there Just looking to check the correct answers!! and please give me a hint on what each ratio means in regards to the industry ratio and how the company can better that ratio. (biggest variance in each). Please do not upload a locked file as that would be counter productive, the last tutor I asked did it.

I am also having a hard time figuring out CFC ratio, how can I find principal debt?

Also not sure about my TIE ratio. Please help the work and date is already there I just want to interpret it.

A2 Donna Jamison, a recent UNC graduate with four years of for-profit health management experience, was brought in as assistant to the chairman of the board of Computron Diagnostics, a manufacturer of clinical diagnostic equipment. The company doubled its plant capacity, opened new sales offices outside its home territory, and launched an expensive advertising campaign. Computron's results were not satisfactory, to put it mildly. Its board of directors, which consisted of its president and vice president plus its major stockholders (who were all local business people), was most upset when directors learned how the expansion was going. Suppliers were being paid late and were unhappy, and the bank was complaining about the cut-off credit. As a result, Al Watkins, Computron's president, was informed that changes would have to be made, and quickly, or he would be fired. Also, at the board's insistence Donna Jamison was brought in and given the job of assistant to Fred Campo, a retired banker who was Computron's chairman and largest stockholder. Campo agreed to give up a few of his golfing days and to help nurse the company back to health, with Jamison's help. Jamison began by gathering financial statements and other data, shown below. The data show the dire situation that Computron Diagnostics was in after the expansion program. Thus far, sales have not been up to the forecasted level, costs have been higher than were projected, and a large loss occurred in year 2, rather than the expected profit. Jamison examined monthly data for year 2 (not given in the case), and she detected an improving pattern during the year. Monthly sales were rising, costs were falling, and large losses in the early months had turned to a small profit by December. Thus, the annual data look somewhat worse than final monthly data. Also, it appears to be taking longer for the advertising program to get the message across, for the new sales offices to generate sales, and for the new manufacturing facilities to operate efficiently. In other words, the lags between spending money and deriving benefits were longer than Computron's managers had anticipated. For these reasons, Jamison and Campo see hope for the companyprovided it can survive in the short run. Jamison must prepare an analysis of where the company is now, what it must do to regain its financial health, and what actions should be taken. Computron Diagnostics Statement of Operations Yr 1 Actual Yr 2 Actual Yr 3 Projected Revenue: Net patient service revenue Other revenue Total revenues Expenses: Salaries and benefits Supplies Insurance and other Provision for bad debts Depreciation Interest Total expenses Operating income Provision for income taxes Net income $3,432,000 $0 $3,432,000 $5,834,400 $0 $5,834,400 $7,035,600 $0 $7,035,600 $2,864,000 $240,000 $50,000 $50,000 $18,900 $62,500 $3,285,400 $146,600 $58,640 $87,960 $4,980,000 $620,000 $50,000 $50,000 $116,960 $176,000 $5,992,960 -$158,560 -$63,424 -$95,136 $5,800,000 $512,960 $50,000 $50,000 $120,000 $80,000 $6,612,960 $422,640 $169,056 $253,584 Computron Diagnostics Balance Sheet Yr 1 Actual Yr 2 Actual Yr 3 Projected Assets Current Assets: Cash Marketable securities Net accounts receivable Inventories Total current assets Property and equipment Less accumulated depreciation Net property and equipment Total assets $9,000 $48,600 $351,200 $715,200 $1,124,000 $491,000 $146,200 $344,800 $1,468,800 $324,000 $284,960 $640,000 $80,000 $1,328,960 $1,000,000 $359,800 $380,000 $220,000 $80,000 $1,039,800 $500,000 $460,000 $203,768 $663,768 $1,468,800 $460,000 $97,632 $557,632 $2,886,592 $1,680,936 $296,216 $1,977,152 $3,516,952 $8.50 100,000 40% $40,000 Other data: Stock price Shares outstanding Tax rate Lease payments $14,000 $71,632 $878,000 $1,716,480 $2,680,112 $1,220,000 $383,160 $836,840 $3,516,952 $145,600 $136,000 $120,000 $80,000 $481,600 $323,432 Liabilities and Shareholders' Equity Current Liabilities: Accounts payable Accrued expenses Notes payable Current portion of long-term debt Total current liabilities Long-term debt Shareholders' equity: Common stock Retained earnings Total shareholders' equity Total liabilities and shareholders' equity $7,282 $20,000 $632,160 $1,287,360 $1,946,802 $1,202,950 $263,160 $939,790 $2,886,592 $6.00 100,000 40% $40,000 $12.17 250,000 40% $40,000 $805,032 ANSWER Yr 1 Actual Profitability ratios Total margin Return on assets Return on equity Liquidity ratios Current ratio Days cash on hand Debt management (capital structure) ratios Debt ratio Debt to equity ratio Times-interest-earned ratio Cash flow coverage ratio Asset management (activity) ratios Fixed asset turnover Total asset turnover Days sales outstanding Industry Yr 2 Actual Yr 3 Projected Average 2.6% 6.0% 13.3% -1.6% -3.3% -17.1% 3.6% 7.2% 12.8% 3.6% 9.0% 17.9% 2.33 6.54 1.46 1.71 2.58 4.85 2.70 22.0 54.8% 1.2 3.35 0.88 80.7% 4.2 0.10 0.35 43.8% 0.8 6.28 1.99 50.0% 2.5 6.2 8.00 9.95 2.34 37.35 6.21 2.02 39.55 8.41 2.00 45.55 7.00 2.50 32.0 ebit $209,100 - lease payments depr expense $40,000 $18,900 - - interest expense lease payments debt principal Other ratios Average age of plant Earnings per share Book value per share Price/earnings ratio Market/book ratio $62,500 7.74 0.88 6.64 9.66 1.28 2.25 0.95 5.58 -6.31 1.08 3.19 1.01 7.91 12.00 1.54 cfc Computron Diagnostics Common Size Statement of Operations Yr 1 Actual Revenue: Net patient service revenue Other revenue Total revenues Expenses: Salaries and benefits Supplies Insurance and other Provision for bad debts Depreciation Interest Total expenses Operating income Provision for income taxes Net income $40,000 6.1 n/a n/a 16.20 2.90 Industry Yr 2 Actual Yr 3 Projected Average 100.0% $0 100.0% 100.0% $0 100.0% 100.0% $0 100.0% 100.0% 0.0% 100.0% 83.4% 7.0% 1.5% 1.5% 0.6% 1.8% 95.7% 4.3% 1.7% 2.6% 85.4% 10.6% 0.9% 0.9% 2.0% 3.0% 102.7% -2.7% -1.1% -1.6% 82.4% 7.3% 0.7% 0.7% 1.7% 1.1% 94.0% 6.0% 2.4% 3.6% 84.5% 3.9% 0.3% 0.3% 4.0% 1.1% 94.1% 5.9% 2.4% 3.5% Computron Diagnostics Common Size Balance Sheet Industry Yr 1 Actual Yr 2 Actual Yr 3 Projected Average Assets Current Assets: Cash Marketable securities Net accounts receivable Inventories Total current assets Property and equipment Less accumulated depreciation Net property and equipment Total assets 0.6% 3.3% 23.9% 48.7% 76.5% 33.4% 10.0% 23.5% 100.0% 0.3% 0.7% 21.9% 44.6% 67.4% 41.7% 9.1% 32.6% 100.0% 0.4% 2.0% 25.0% 48.8% 76.2% 34.7% 10.9% 23.8% 100.0% 0.3% 0.3% 22.3% 41.2% 64.1% 53.9% 18.0% 35.9% 100.0% Liabilities and Shareholders' Equity Current Liabilities: Accounts payable Accrued expenses Notes payable Current portion of long-term debt Total current liabilities Long-term debt Shareholders' equity: Common stock Retained earnings Total shareholders' equity Total liabilities and shareholders' equity 9.9% 9.3% 8.2% 5.4% 32.8% 22.0% 45.2% 31.3% 13.9% 45.2% 100.0% 11.2% 9.9% 22.2% 2.8% 46.0% 34.6% 19.3% 15.9% 3.4% 19.3% 100.0% 10.2% 10.8% 6.3% 2.3% 29.6% 14.2% 56.2% 47.8% 8.4% 56.2% 100.0% 10.2% 9.5% 2.4% 1.6% 23.7% 26.3% 20.0% 30.0% 50.0% 100.0% $0.19 $805,032 $1,341,720.00 Assets 2004 Income Statements Cash $9,000 Short-Term Investments. 2004 48,600 Sales Accounts Receivable 351,200 Cost Of Goods Sold Inventories 715,200 $3,432,000 Other Expenses Total Current Assets $1,124,000 Gross Fixed Assets Less: Accumulated Depreciation Net Fixed Assets 340,000 Depreciation 491,000 18,900 Total Operating Costs 146,200 EBIT $344,800 Total Assets 2,864,000 $3,222,900 $209,100 Interest Expense $1,468,800 62,500 EBT $146,600 Taxes (40%) Liabilities And Equity Net Income 2004 Accounts Payable 58,640 $87,960 $145,600 Notes Payable 200,000 Other Data Accruals 136,000 Stock Price Total Current Liabilities $481,600 Long-Term Debt Shares Outstanding 2004 $8.50 100,000 323,432 $0.88 DPS $0.22 203,768 Total Equity Total Liabilities And Equity EPS 460,000 Common Stock (100,000 Shares) Retained Earnings Tax Rate $663,768 Book Value Per Share $1,468,800 Lease Payments Ratio Analysis 40% $6.64 $40,000 2004 Current 2.3 Quick 0.8 Inventory Turnover 4.8 Days Sales Outstanding 37.4 Fixed Assets Turnover 10 Total Assets Turnover 2.3 Debt Ratio 54.80% TIE 3.3 EBITDA Coverage 2.6 Profit Margin Basic Earning Power 2.60% 14.20% ROA 6.00% ROE 13.30% Price/Earnings (P/E) Price/Cash Flow Market/Book ebit lease payments depr expense $209,100 $40,000 $18,900 - - interest expense lease payments debt principal $62,500 cfc - $40,000 $2.61 $0 9.7 8 1.3 A 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 Chapter 13 B C D E F G UNDERSTANDING HEALTHCARE FINANCIAL MANAGEMENT, 6ed Financial Condition Analysis This spreadsheet model performs some of the calculations contained in Chapter 13. We recommend that you use the model in the following manner: 1. First, recognize that you do not have to use this model at all to understand the financial and operating analysis. However, if you do use the model and experiment with it, this will increase your understanding of the concepts, and it will surely help you when you use spreadsheet models for other purposes, especially any problem sets or cases assigned for this course. 2. Start by reading the chapter in its entirety. 3. Now place the text along side your computer with this model on the screen. When you come to an explanation of a calculation in the text, see if the model has a matching calculation. 4. We assume that you know the basics of Excel, but that you have not encountered some of its features or that you may need a refresher or two. So, we have built in explanations of how to do some of the functions in the model. As a result, you will learn more about Excel at the same time you learn about the time value analysis. 5. Throughout this model, page numbers of the matching text calculations are provided in pink. Input data are in red on a yellow background and output data are in green on a beige background. You are encouraged to change the input data to learn more about the calculations in the model. FINANCIAL STATEMENTS The financial statements given below are the same as those used in the text. They will provide the input into the analysis. Bayside Memorial Hospital Balance Sheets (in thousands of dollars) Assets Cash Short-term investments Accounts receivable Inventories Total current assets Gross plant and equipment Accumulated depreciation Net plant and equipment Total assets Liabilities and equity Accounts payable Accrued expenses Notes payable Current portion of long-term debt Total current liabilities Long-term debt Capital lease obligations Total long-term liabilities Net assets (equity) Total liabilities and equity $ $ $ $ $ 2010 4,263 2,000 21,840 3,177 31,280 145,158 25,160 119,998 151,278 $ $ $ $ 2009 5,095 20,738 2,982 28,815 140,865 21,030 119,835 148,650 $ $ $ 2009 5,145 5,421 4,237 2,000 16,803 30,900 2,155 33,055 98,792 148,650 2010 108,600 $ 5232 3644 117,476 $ 2009 97,393 4622 6014 108,029 $ $ 58,285 $ 5,424 13,198 11,427 10,250 3,328 1,320 4,130 1,542 108,904 $ 8,572 $ 56,752 4,718 11,655 11,585 10,705 3,469 1,204 4,025 1,521 105,634 2,395 $ $ 1,368 $ 2,000 $ 1,248 2,100 $ $ $ $ $ $ 2010 4,707 5,650 825 2,150 13,332 28,750 1,832 30,582 107,364 151,278 $ $ $ $ Bayside Memorial Hospital Income Statements (in thousands of dollars) Net patient service revenue Premium revenue Other revenue Total revenues Expenses: Nursing services Dietary services General services Administrative services Employee health and welfare Provision for uncollectibles Provision for malpractice Depreciation Interest expense Total expenses Net income Lease payments Principal repayments $ $ $ RATIO ANALYSIS (PAGE 478) 2010 Profitability ratios Total Margin Return on Assets Return on Equity Liquidity ratios Current Ratio Days Cash on Hand Debt management (capital structure) ratios Debt Ratio Debt to Equity Ratio Times-Interest-Earned Ratio Cash Flow Coverage Ratio Asset management (activity) ratios 2009 Industry Average 7.3% 5.7% 8.0% 2.2% 1.6% 2.4% 5.0% 4.8% 8.4% 2.3 22.5 1.7 18.9 2.0 30.6 29.0% 40.9% 6.6 3.2 33.5% 50.5% 2.6 1.9 42.3% 73.3% 4.0 2.3 A 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 B Fixed Asset Turnover Total Asset Turnover Days Sales Outstanding Other ratios Average Age of Plant C D E F G 0.98 0.78 73.4 0.90 0.73 77.7 2.2 0.97 64.0 6.1 5.2 9.1 Note: The red tick indicates a comment box. Put pointer on cell to read comment. COMPARATIVE AND TREND ANALYSIS (PAGE 488) A picture is said to be worth a thousand words, and a ratio data can be expressed in graphical form. We illustrate graphing with a trend analysis of Bayside's ROE. We also assume that the industry average ROE is a constant, but in most real world situations we would have more years of data, both for the business and for the industry. We will make a "scatter diagram," with years on the horizontal axis and ROE on the vertical axis. First, we need the data arranged in the proper order, with years in the first column and ROEs in the second one. This is shown below: ROE Years 2009 2010 Bayside 2.4% 8.0% Industry 8.4% 8.4% The graph below shows that Bayside's ROE is below that of the industry average, but is up. ROE 10% 8% 6% RO E 4% 2% 0% 2009 2010 Ye ar TYING THE RATIOS TOGETHER: DUPONT ANALYSIS (PAGE 490) Bayside: 2010 2009 Industry Average ROE 7.98% 2.42% 8.4% = TM 7.30% 2.22% 5.0% x TATO 0.78 0.73 0.97 x EM 1.41 1.50 1.73 Here we see that Bayside's ROE has improved dramatically from 2009 to 2010. More importantly, the improvement was primarily due to the large increase in total margin. The hospital is below average in both expense control and debt utilization. COMMON SIZE ANALYSIS (PAGE 492) Common size analysis is useful when comparing businesses of different size or one business over time when the business is growing at a rapid rate, and hence is increasing in size. Bayside Memorial Hospital: Common Size Balance Sheets Assets Cash Short-term investments Accounts receivable Inventories Total current assets Gross plant and equipment Accumulated depreciation Net plant and equipment Total assets $ 151,278 $ 2010 2.8% 1.3% 14.4% 2.1% 20.7% 96.0% 16.6% 79.3% 100.0% 148,650 = Divisors 2009 Industry Average 3.4% 3.7% 0.0% 2.0% 14.0% 17.2% 2.0% 2.5% 19.4% 25.4% 94.8% 90.1% 14.1% 15.5% 80.6% 74.6% 100.0% 100.0% Liabilities and equity Accounts payable Accrued expenses Notes payable Current portion of long-term debt Total current liabilities Long-term debt Capital lease obligations Total long-term liabilities Net assets (equity) Total liabilities and equity 2010 3.1% 3.7% 0.5% 1.4% 8.8% 19.0% 1.2% 20.2% 71.0% 100.0% 2009 3.5% 3.6% 2.9% 1.3% 11.3% 20.8% 1.4% 22.2% 66.5% 100.0% Industry Average 3.9% 4.1% 3.2% 2.1% 13.3% 36.5% 0.9% 37.4% 49.3% 100.0% Here we see that Bayside has fewer current assets, but more fixed assets than the industry. Here we see that Bayside has much less long-term debt, but more equity than the industry. Bayside Memorial Hospital: Common Size Income Statements $ Net patient service revenue Premium revenue Other revenue Total revenues Expenses: Nursing services Dietary services 117,476 $ 2010 92.4% 4.5% 3.1% 100.0% 49.6% 4.6% 108,029 = Divisors 2009 Industry Average 90.2% 90.4% 4.3% 7.2% 5.6% 2.4% 100.0% 100.0% 52.5% 4.4% 50.7% 4.7% Bayside's revenue and A B C D E F General services 11.2% 10.8% 11.5% 197 Administrative services 9.7% 10.7% 10.2% 198 Employee health and welfare 8.7% 9.9% 9.2% 199 Provision for uncollectibles 2.8% 3.2% 2.8% 200 Provision for malpractice 1.1% 1.1% 1.0% 201 Depreciation 3.5% 3.7% 3.0% 202 Interest expense 1.3% 1.4% 1.9% 203 Total expenses 92.7% 97.8% 95.0% 204 Net income 7.3% 2.2% 5.0% 205 206 207 208 209 210 PERCENTAGE CHANGE ANALYSIS (PAGE 494) 211 212 Percentage change analysis is used to see what income statement items and balance sheet accounts are "out of control." 213 expense items and balance sheet accounts should grow at a rate that is equal to or less than revenue growth. 214 215 Bayside Memorial Hospital: Balance Sheet Percentage Change Analysis 216 217 Assets 2010 2009 Percentage Change 218 Cash $ 4,263 $ 5,095 -16.3% 219 Short-term investments 2,000 220 Accounts receivable 21,840 20,738 5.3% 221 Inventories 3,177 2,982 6.5% 222 Total current assets $ 31,280 $ 28,815 8.6% 223 Gross plant and equipment $ 145,158 $ 140,865 3.0% 224 Accumulated depreciation 25,160 21,030 19.6% 225 Net plant and equipment $ 119,998 $ 119,835 0.1% 226 Total assets $ 151,278 $ 148,650 1.8% 227 228 Liabilities and equity 2010 2009 Percentage Change 229 Accounts payable $ 4,707 $ 5,145 -8.5% 230 Accrued expenses 5,650 5,421 4.2% 231 Notes payable 825 4,237 -80.5% 232 Current portion of long-term debt 2,150 2,000 7.5% 233 Total current liabilities $ 13,332 $ 16,803 -20.7% 234 Long-term debt $ 28,750 $ 30,900 -7.0% 235 236 Capital lease obligations 1,832 2,155 -15.0% Total long-term liabilities $ 30,582 $ 33,055 -7.5% 237 Net assets (equity) $ 107,364 $ 98,792 8.7% 238 Total liabilities and equity $ 151,278 $ 148,650 1.8% 239 240 241 Bayside Memorial Hospital: Income Statement Percentage Change Analysis 242 243 2010 2009 Percentage Change 244 Net patient service revenue $ 108,600 $ 97,393 11.5% 245 Premium revenue 5232 4622 13.2% 246 Other revenue 3644 6014 -39.4% 247 Total revenues $ 117,476 $ 108,029 8.7% 248 Expenses: 249 Nursing services $ 58,285 $ 56,752 2.7% 250 Dietary services 5,424 4,718 15.0% 251 General services 13,198 11,655 13.2% 252 Administrative services 11,427 11,585 -1.4% 253 Employee health and welfare 10,250 10,705 -4.3% 254 Provision for uncollectibles 3,328 3,469 -4.1% 255 Provision for malpractice 1,320 1,204 9.6% 256 Depreciation 4,130 4,025 2.6% 257 Interest expense 1,542 1,521 1.4% 258 Total expenses $ 108,904 $ 105,634 3.1% 259 Net income $ 8,572 $ 2,395 257.9% 260 261 262 263 264 G expense structure is similar to the industry, but Bayside is doing a better job of controlling expenses. In general, Compare these values with Bayside's 8.7% growth in revenues to get a feel for which times and accounts need attention. Bayside's dietary and general services expenses need examining. They are growing much faster than revenues. The growth in net income is good news