In: Accounting
Goup Number: 14 | |||||||||
Group Project 2 - Financial Condition Analysis | |||||||||
John Green, a recent graduate with four years of for-profit health management experience, was | |||||||||
recently brought in as assistant to the chairman of the board of Digital 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. Digital'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, Eddie Sanders, Digital’s president, was informed that changes would have to be made, and | |||||||||
quickly, or he would be fired. Also, at the board's insistence, John Green was brought in and given the job of | |||||||||
assistant to Wendy Smith, a retired banker who was Digital's chairwoman and largest stockholder. Sanders | |||||||||
agreed to give up a few of his golfing days and help nurse the company back to health, with Green's assistance. | |||||||||
Green began by gathering financial statements and other data, shown below. The data show the dire situation | |||||||||
that Digital 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. Green examined monthly data for Year 2 (not given in the case), and he 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 thanDigital's managers had anticipated. | |||||||||
For these reasons, Green and Sanders see hope for the company—provided it can survive in the short run. | |||||||||
Green 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. Green requested your group to complete this assigned task for him. | |||||||||
Use this Excel Workbook to perform the quantitative parts of the analysis and prepare the report as a Word document. | |||||||||
The report shpuld include only the interpretations of the quantitative results. How you found these results are to be shown in this Excel Workbook. | |||||||||
Submit both files via Blackboard as instructed. | |||||||||
Digital Diagnostics | |||||||||
Statement of Operations | |||||||||
Yr 1 Actual | Yr 2 Actual | Yr 3 Projected | |||||||
Revenue: | |||||||||
Net patient service revenue | $3,432,000 | $5,834,400 | $7,035,600 | ||||||
Other revenue | $0 | $0 | $0 | ||||||
Total revenues | $3,432,000 | $5,834,400 | $7,035,600 | ||||||
Expenses: | |||||||||
Salaries and benefits | $2,864,000 | $4,980,000 | $5,800,000 | ||||||
Supplies | $240,000 | $620,000 | $512,960 | ||||||
Insurance and other | $50,000 | $50,000 | $50,000 | ||||||
Drugs | $50,000 | $50,000 | $50,000 | ||||||
Depreciation | $18,900 | $116,960 | $120,000 | ||||||
Interest | $62,500 | $176,000 | $80,000 | ||||||
Total expenses | $3,285,400 | $5,992,960 | $6,612,960 | ||||||
Operating income | $146,600 | -$158,560 | $422,640 | ||||||
Provision for income taxes | $58,640 | -$63,424 | $169,056 | ||||||
Net income | $87,960 | -$95,136 | $253,584 | ||||||
Digital Diagnostics | |||||||||
Balance Sheet | |||||||||
Yr 1 Actual | Yr 2 Actual | Yr 3 Projected | |||||||
Assets | |||||||||
Current assets: | |||||||||
Cash | $9,000 | $7,282 | $14,000 | ||||||
Marketable securities | $48,600 | $20,000 | $71,632 | ||||||
Net accounts receivable | $351,200 | $632,160 | $878,000 | ||||||
Inventories | $715,200 | $1,287,360 | $1,716,480 | ||||||
Total current assets | $1,124,000 | $1,946,802 | $2,680,112 | ||||||
Property and equipment | $491,000 | $1,202,950 | $1,220,000 | ||||||
Less accumulated depreciation | $146,200 | $263,160 | $383,160 | ||||||
Net property and equipment | $344,800 | $939,790 | $836,840 | ||||||
Total assets | $1,468,800 | $2,886,592 | $3,516,952 | ||||||
Liabilities and shareholders' equity | |||||||||
Current liabilities: | |||||||||
Accounts payable | $145,600 | $324,000 | $359,800 | ||||||
Accrued expenses | $136,000 | $284,960 | $380,000 | ||||||
Notes payable | $120,000 | $640,000 | $220,000 | ||||||
Current portion of long-term debt | $80,000 | $80,000 | $80,000 | ||||||
Total current liabilities | $481,600 | $1,328,960 | $1,039,800 | ||||||
Long-term debt | $323,432 | $1,000,000 | $500,000 | ||||||
Shareholders' equity: | |||||||||
Common stock | $460,000 | $460,000 | $1,680,936 | ||||||
Retained earnings | $203,768 | $97,632 | $296,216 | ||||||
Total shareholders' equity | $663,768 | $557,632 | $1,977,152 | ||||||
Total liabilities and shareholders' equity | $1,468,800 | $2,886,592 | $3,516,952 | ||||||
Other data: | |||||||||
Stock price | $8.50 | $6.00 | $12.17 | ||||||
Shares outstanding | 100,000 | 100,000 | 250,000 | ||||||
Tax rate | 40% | 40% | 40% | ||||||
Lease payments | $40,000 | $40,000 | $40,000 | ||||||
ANSWER | |||||||||
Industry | |||||||||
Yr 1 Actual | Yr 2 Actual | Yr 3 Projected | Average | ||||||
Profitability ratios | |||||||||
Total margin | 4% | -3% | 6% | 3.6% | |||||
Return on assets | 6% | -3% | 7% | 9.0% | |||||
Return on equity | 13% | -17% | 13% | 17.9% | |||||
Liquidity ratios | |||||||||
Current ratio | 2.33 | 1.46 | 2.58 | 2.70 | |||||
Days cash on hand | 1.01 | 0.45 | 0.79 | 22.0 | |||||
Debt management (capital structure) ratios | |||||||||
Debt ratio | 55% | 81% | 44% | 50.0% | |||||
Debt to equity ratio | 0.49 | 1.79 | 0.25 | 2.5 | |||||
Times-interest-earned ratio | 1.35 | -1.9 | 4.28 | 6.2 | |||||
Cash flow coverage ratio | 1.65 | -1.24 | 5.78 | 8.00 | |||||
Asset management (activity) ratios | |||||||||
Fixed asset turnover | 9.95 | 6.21 | 8.41 | 7.00 | |||||
Total asset turnover | 2.34 | 2.02 | 2 | 2.50 | |||||
Days sales outstanding | 37.35 | 39.55 | 45.55 | 32.0 | |||||
Other ratios | |||||||||
Average age of plant | 6.1 | ||||||||
Earnings per share | n/a | ||||||||
Book value per share | n/a | ||||||||
Price/earnings ratio | 16.20 | ||||||||
Market/book ratio | 2.90 | ||||||||
Digital Diagnostics | |||||||||
Common Size Statement of Operations | |||||||||
Industry | |||||||||
Yr 1 Actual | Yr 2 Actual | Yr 3 Projected | Average | ||||||
Revenue: | |||||||||
Net patient service revenue | 100.0% | 100.0% | 100.0% | 100.0% | |||||
Other revenue | 0.0% | 0.0% | 0.0% | 0.0% | |||||
Total revenues | 100.0% | 100.0% | 100.0% | 100.0% | |||||
Expenses: | |||||||||
Salaries and benefits | 83.0% | 85.0% | 82.0% | 84.5% | |||||
Supplies | 7.0% | 11.0% | 7.0% | 3.9% | |||||
Insurance and other | 1.0% | 1.0% | 1.0% | 0.3% | |||||
Drugs | 1.0% | 1.0% | 1.0% | 0.3% | |||||
Depreciation | 1.0% | 2.0% | 2.0% | 4.0% | |||||
Interest | 2.0% | 3.0% | 1.0% | 1.1% | |||||
Total expenses | 96.0% | 103.0% | 94.0% | 94.1% | |||||
Operating income | 4.0% | -3% | 6.0% | 5.9% | |||||
Provision for income taxes | 2.0% | -1.0% | 2.0% | 2.4% | |||||
Net income | 3.0% | -2.0% | 4.0% | 3.5% | |||||
Digital Diagnostics | |||||||||
Common Size Balance Sheet | Industry | ||||||||
Yr 1 Actual | Yr 2 Actual | Yr 3 Projected | Average | ||||||
Assets | |||||||||
Current assets: | |||||||||
Cash | 0.3% | ||||||||
Marketable securities | 0.3% | ||||||||
Net accounts receivable | 22.3% | ||||||||
Inventories | 41.2% | ||||||||
Total current assets | 64.1% | ||||||||
Property and equipment | 53.9% | ||||||||
Less accumulated depreciation | 18.0% | ||||||||
Net property and equipment | 35.9% | ||||||||
Total assets | 100.0% | ||||||||
Liabilities and shareholders' equity | |||||||||
Current liabilities: | |||||||||
Accounts payable | 10.2% | ||||||||
Accrued expenses | 9.5% | ||||||||
Notes payable | 2.4% | ||||||||
Current portion of long-term debt | 1.6% | ||||||||
Total current liabilities | 23.7% | ||||||||
Long-term debt | 26.3% | ||||||||
Shareholders' equity: | |||||||||
Common stock | 20.0% | ||||||||
Retained earnings | 30.0% | ||||||||
Total shareholders' equity | 50.0% | ||||||||
Total liabilities and shareholders' equity | 100.0% |
Please add the work on how to do it on excel. Detailed explanation please. Thank you!