answer in excel format if you can. I have attached the question in excel format.

9/1/2014 UNDERSTANDING HEALTHCARE FINANCIAL MANAGEMENT Chapter 13 -- Financial Condition Analysis Mini-Case Donna Jamison, a recent UNC graduate with four years of for-profit health management experience, was recently brought in as assistant to the chairman of the board of Computron Diagnostics, a manufacturer of clinical diagnostic equipment. The company had 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 help nurse the company back to health, with Jamison's assistance. 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 Drugs 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 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 Other data: Stock price Shares outstanding Tax rate Lease payments $9,000 $48,600 $351,200 $715,200 $1,124,000 $491,000 $146,200 $344,800 $1,468,800 $7,282 $20,000 $632,160 $1,287,360 $1,946,802 $1,202,950 $263,160 $939,790 $2,886,592 $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 $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 $6.00 100,000 40% $40,000 $12.17 250,000 40% $40,000 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 Yr 2 Actual Yr 3 Projected Industry Average 3.6% 9.0% 17.9% 2.70 22.0 50.0% 2.5 6.2 Cash flow coverage ratio Asset management (activity) ratios Fixed asset turnover Total asset turnover Days sales outstanding Other ratios Average age of plant Earnings per share Book value per share Price/earnings ratio Market/book ratio 8.00 7.00 2.50 32.0 6.1 n/a n/a 16.20 2.90 Computron Diagnostics Common Size 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 100.0% 0.0% 100.0% 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 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 Liabilities and shareholders' equity Current liabilities: Industry Average Industry Average 0.3% 0.3% 22.3% 41.2% 64.1% 53.9% 18.0% 35.9% 100.0% 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 10.2% 9.5% 2.4% 1.6% 23.7% 26.3% 20.0% 30.0% 50.0% 100.0% xperience, was manufacturer of les offices outside its re not satisfactory, lus its major how the expansion ng about the cut off have to be made, and n and given the job of ckholder. Campo h Jamison's assistance. how the dire situation been up to the Year 2, rather than she detected an losses in the early orse than final monthly ross, for the new y. In other words, nagers had anticipated. in the short run. its financial health, 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 A Ch 06 Mini Case B C D E F G H 3/8/2001 Chapter 6 Mini Case Situation Donna Jamison, a 1997 graduate of the University of Tennessee with four years of banking experience, was recently brought in as assistant to the chairman of the board of Computron Industries, a manufacturer of electronic calculators. The company doubled its plant capacity, opened new sales offices outside its home territory and launched an expensive advertising campaign. Computron's results wee not satisfactory, to put it mildly. Its board of directors, which consisted of its president and vice-president plus its major sotckholders (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 deteriorating situation and threatening to 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. OTHER DATA SECTION Year-end common stock price Year-end shares outstanding (in millions) Tax rate Lease Payments After Tax Cost of Capital 2001 $2.25 100,000 40% 40,000 13% 2000 $8.50 100,000 40% 40,000 11% Computron's Balance Sheets 2001 2000 Assets Cash and equivalents Short-term investments Accounts receivable Inventories Total current assets Net plant and equipment Total assets $7,282 $0 $632,160 $1,287,360 $1,926,802 $939,790 $2,866,592 $9,000 $48,600 $351,200 $715,200 $1,124,000 $344,800 $1,468,800 Liabilities and equity Accounts payable Notes payable Accruals Total current liabilities Long-term bonds Common Stock Retained Earnings Total Equity Total Liabilites and Equity $524,160 $720,000 $489,600 $1,733,760 $1,000,000 $460,000 ($327,168) $132,832 $2,866,592 $145,600 $200,000 $136,000 $481,600 $323,432 $460,000 $203,768 $663,768 $1,468,800 A 54 B C D E F G H A B C D E F G H 55 Computron's Income Statement 56 57 2001 2000 58 INCOME STATEMENT $5,834,400.0 $3,432,000.0 59 Net sales $5,728,000.0 $2,864,000.0 60 Cost of Goods Sold $680,000.0 $340,000.0 61 Other Expenses $116,960.0 $18,900.0 62 Depreciation $6,524,960.0 $3,222,900.0 63 Total Operating Costs ($690,560.0) $209,100.0 64 Earnings before interest and taxes (EBIT) $176,000.0 $62,500.0 65 Less interest ($866,560.0) $146,600.0 66 Earnings before taxes (EBT) ($346,624.0) $58,640.0 67 Taxes 68 Net Income ($519,936.0) $87,960.0 69 70 71 We can now use the above information to calculate two specific per-share data measures: earnings per share 72 (EPS) and dividends per share (DPS). Simply divide the totals by the appropriate number of shares outstanding. 73 74 Per-share Data ($5.20) $0.88 75 Earnings per share (EPS) $0.11 $0.22 76 Dividends per share (DPS) 77 78 The per share data gives managers and investors a quick look at some items that affect the price of the stock. 79 80 Statement of Retained Earnings (2001) 81 The statement of retained earnings takes the previous year's balance of retained earnings, adds the current year's 82 net income, and then subtracts dividends paid to common stockholders. The end result is the new balance of retained 83 earnings. Computron's statement is shown below: 84 2001 $203,768.0 85 Balance of Retained Earnings, Dec. 31, 2000 ($519,936.0) 86 Add: Net Income, 2001 ($11,000.0) 87 Less: Dividends to common stockholders 88 Balance of Retained Earnings, Dec. 31, 2001 ($327,168.0) 89 90 Information from the balance sheet and income statement can be used to construct the Statement of 91 Cash Flows, which is shown below for Computron. 92 93 Computron's Statement of Cash Flows for Years Ending Dec. 31, 2001 94 95 96 Operating Activities Net Income before preferred dividends ($519,936.0) 97 98 Noncash adjustments 99 Depreciation and amortization $116,960.0 100 Due to changes in working capital ($280,960.0) 101 Change in accounts receivable ($572,160.0) 102 Change in inventories $378,560.0 103 Change in accounts payable $353,600.0 104 Change in accruals ($523,936.0) 105 Net cash provided by operating activities 106 107 Long-term investing activities 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 A B C Cash used to acquire fixed assets Financing Activities Change in short-term investments Change in notes payable Change in long-term debt Payment of cash dividends Net cash provided by financing activities Net change in cash and equivilents Cash and securities at beginning of the year Cash and securities at end of the year D E F G ($711,950.0) $48,600.0 $520,000.0 $676,568.0 ($11,000.0) $1,234,168.0 ($1,718.0) $9,000.0 $7,282.0 H A B C D E F G H 123 Summary Data Relating to Operating Performance and Cash Flows 124 125 Now that the statements have been developed, we can use the data contained in them to calculate some items that 126 are of interest to managers, investors, and lenders. All of these items are used more extensively in subsequent 127 chapters, where we look in more depth at how historical financial statements are analyzed and how future financial 128 results are predicted. 129 130 Net Cash Flow 131 The sum of net income plus noncash adjustments. 132 133 NCF01 = Net Income + Depreciation 134 = -$519,936.0 + $116,960.0 NCF = 135 -$402,976.0 01 136 137 NCF00 = Net Income + Deprecitaion 138 = $87,960.0 + $18,900.0 NCF = 139 $106,860.0 00 140 141 Operating Cash Flow 142 Operating Cash Flow is NOPAT plus any noncash adjustments. 143 144 OCF01 = NOPAT + Depreciation 145 = -$414,336 + $116,960.0 146 OCF01 = -$297,376.0 147 148 OCF00 = NOPAT + Depreciation 149 = $125,460.0 + $18,900.0 OCF = 150 $144,360.0 00 151 152 Net Operating Working Capital 153 Those current assets used in operations are called operating working capital, and operating working capital less 154 operating current liabilities is called Net Operating Working Capital. 155 Operating Operating 156 NOWC01 = current current assets liabilities 157 = $1,926,802 $1,013,760 158 NOWC01 = $913,042 159 Operating Operating current 160 NOWC00 = current assets liabilities = $1,075,400 $281,600 161 162 NOWC00 = $793,800 163 164 165 166 167 168 169 Total Operating Capital The Total OperatingCapital is Net Operating Working Capital plus any fixed assets. TOC01 = TOC01 = = NOWC $913,042 $1,852,832 + + Fixed assets $939,790 A 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 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 216 217 218 219 220 221 TOC00 = = TOC00 = B C D NOWC $793,800 $1,138,600 + + Fixed assets $344,800 E F G Net Operating Profit After Taxes NOPAT is the amount of profit Computron would generate if it had no debt and held no financial assets. NOPAT01 = = NOPAT01 = NOPAT00 = = NOPAT00 = EBIT -$690,560 -$414,336.0 x x (1-T) 60% EBIT $209,100 x x (1-T) 60% $125,460.0 Free Cash Flow Computron's Free Cash Flow caluclation is the cash flow actually availabe for distribution to investors after the company has made all necessary investments in fixed assets and working capital to sustain ongoing operations. FCF01 = = FCF01 = NOPAT -$414,336.0 -$1,128,568.0 - Net Investment in Operating Capital $714,232 Market Value Added Market Value Added is the difference between the market value of Computron's stock and the amount of equity capital supplied by shareholders. MVA01 = MVA01 = = = MVA00 = = = MVA00 = Stock price $2.25 x x $225,000 # of shares 100,000 - Total common equity $132,832 $132,832 x x $850,000 # of shares 100,000 - Total common equity $663,768 $663,768 $92,168 Stock price $8.50 $186,232 Economic Value Added Economic Value Added represents Computron's residual income that remains after the cost of all capital, including equity capital, has been deducted. EVA01 = NOPAT Operating Capital x After-tax cost of capital = -$414,336.0 $1,852,832 x 13% = -$414,336.0 $240,868.2 EVA01 = -$655,204.2 EVA00 = NOPAT - Operating Capital x After-tax cost of capital H A B C D E F G H = $125,460.0 $1,138,600 x 11% 222 223 = $125,460.0 $125,246.0 224 EVA00 = $214.0 225 226 Return on Invested Capital 227 The Return on Invested Capital tells us the amount of NOPAT per dollar of operating capital. 228 229 ROIC01 = NOPAT Operating Capital 230 -$414,336.00 $1,852,832 231 ROIC01 = -22.36% 232 233 ROIC00 = NOPAT Operating Capital 234 $125,460.00 $1,138,600 ROIC = 235 11.02% 00 236 237 Conclusions 238 This spreadsheet model should have either refreshed your memory of or introduced you to spreadsheet models. 239 240 A second sheet in this file, found under the tab TAXES, introduces another spreadsheet application. You can access 241 the TAXES model by clicking on the tab in the lower section of the screen. 242 It is worth noting that the models developed for Chapter 6 are all relatively simple, and the tasks they perform, 243 244 could be done just as easily, or more easily, with a calculator. However, in Chapter 7 and subsequently, you will see 245 that spreadsheet models are extremely valuable, hence why they are terribly important to business today. 246 247 248 249 250 251 252 253 254 255 256 257 258 259