I need help in answering these questions 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, 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 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 17.5 1.46 20.3 2.58 21.7 2.70 22.0 54.8% 0.5 2.3 80.7% 1.8 -0.9 43.8% 0.3 5.3 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 0.45 -0.16 0.85 8.00 9.95 2.34 37.4 6.21 2.02 39.5 8.41 2.00 45.5 7.00 2.50 32.0 4.23 2.7 23.9 13.1 2.1 5.12 3.5 27.8 15.6 3.4 5.9 4.1 36.2 14.9 2.3 6.1 n/a n/a 16.20 2.90 Yr 2 Actual Yr 3 Projected Industry Average 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 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: 100.0% 0.0% 100.0% 100.0% 0.0% 100.0% 100.0% 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% 3.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 Yr 1 Actual Yr 2 Actual Yr 3 Projected Industry Average 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% 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% 11.2% 9.9% 22.2% 2.8% 46.0% 34.6% 10.2% 10.8% 6.3% 2.3% 29.6% 14.2% 10.2% 9.5% 2.4% 1.6% 23.7% 26.3% 31.3% 13.9% 45.2% 100.0% 15.9% 3.4% 19.3% 100.0% 47.8% 8.4% 56.2% 100.0% 20.0% 30.0% 50.0% 100.0% The company need to reduce both the current laibilities and long-term debt. As it can be noted in the analysis. The debt ratio has increased substantially overtime indicating a major risk of financial bankruptcy of the firm therefore, it is relevant that the compay rediuces the notes payables and accounts payable to improve its ability to meet the short-term financial obligations when they fall due. additionally, it can be noted that the company made a huge debt in term of long-term debt which increased the fianncial leverage of the company. This ais a clear indication of poor financial health of the company. to improve this, it is vital for the company to reduce its external borrowing by large extent. The company's profitability ought to be improved grately by taking the following actions: the company should increase its sales revenue overtime to cover up the increasing expenses. Given that the expenses remain constant, the profitability of the compny will increase hence an improvement in its financial health. Additionallly, the expenses such as supplies, salaries and benefits ought to be reduced by margin to reduce the generation of losses by the company and consequently increase the net income generation that improves the profitability position of the company hence an improvement in the company fianncial health. 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, oted in the analysis. nkruptcy of the firm to improve its which increased the the company should enses remain constant, margin to reduce fianncial health. 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 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 17.5 1.46 20.3 2.58 21.7 2.70 22.0 54.8% 0.5 2.3 80.7% 1.8 -0.9 43.8% 0.3 5.3 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 0.45 -0.16 0.85 8.00 9.95 2.34 37.4 6.21 2.02 39.5 8.41 2.00 45.5 7.00 2.50 32.0 4.23 2.7 23.9 13.1 2.1 5.12 3.5 27.8 15.6 3.4 5.9 4.1 36.2 14.9 2.3 6.1 n/a n/a 16.20 2.90 Yr 2 Actual Yr 3 Projected Industry Average 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 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: 100.0% 0.0% 100.0% 100.0% 0.0% 100.0% 100.0% 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% 3.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 Yr 1 Actual Yr 2 Actual Yr 3 Projected Industry Average 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% 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% 11.2% 9.9% 22.2% 2.8% 46.0% 34.6% 10.2% 10.8% 6.3% 2.3% 29.6% 14.2% 10.2% 9.5% 2.4% 1.6% 23.7% 26.3% 31.3% 13.9% 45.2% 100.0% 15.9% 3.4% 19.3% 100.0% 47.8% 8.4% 56.2% 100.0% 20.0% 30.0% 50.0% 100.0% The company need to reduce both the current laibilities and long-term debt. As it can be noted in the analysis. The debt ratio has increased substantially overtime indicating a major risk of financial bankruptcy of the firm therefore, it is relevant that the compay rediuces the notes payables and accounts payable to improve its ability to meet the short-term financial obligations when they fall due. additionally, it can be noted that the company made a huge debt in term of long-term debt which increased the fianncial leverage of the company. This ais a clear indication of poor financial health of the company. to improve this, it is vital for the company to reduce its external borrowing by large extent. The company's profitability ought to be improved grately by taking the following actions: the company should increase its sales revenue overtime to cover up the increasing expenses. Given that the expenses remain constant, the profitability of the compny will increase hence an improvement in its financial health. Additionallly, the expenses such as supplies, salaries and benefits ought to be reduced by margin to reduce the generation of losses by the company and consequently increase the net income generation that improves the profitability position of the company hence an improvement in the company fianncial health. 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, oted in the analysis. nkruptcy of the firm to improve its which increased the the company should enses remain constant, margin to reduce fianncial health