80.2K

Verified Solution

Link Copied!

how would i solve the highlighted portion using excel formulas with the given information?

4 John Green, a recent graduate with four years of for-profit health management experience, was recently thought in as assistant to the chairman of the board of Digital Diagnostics, a manufacturer of clinical diagnostic equipment. The company had donbled its plant capacity, opened new sales offices outside its home territory, and launched an expensive adrertising campaign. Digital's results were not satisfactory, 8 to put it mildly. Its board of directors, which consisted of its president and rice president plus its major 9 stockholders (who were all local business people), was most upset when directors learned hor the expansion 10 was going. Suppliers were being paid late and were unhappy, and the bank was complaining about the cut off 11 credit. As a result, Eddie Sauders, Digital's president, was informed that changes would hare to be made, and 12 quickly, or he would be fired. Also, at the board's insistence, John Green was brought in and giren the job of 13 assistant to Wendy Smith, a retired banker who was Digital's chairwoman and largest stockholder. Sanders 14 agreed to gire up a few of his golfing days and help nurse the company back to health, with Green's assistance. 15 16 Green began by gathering financial statements and other data, shom belor. The data show the dire sifuation 17 that Digital Diagnostics was in after the expansion program. Thus far, sales hare not been ap to the 18 forecasted lerel, costs have been higher than were projected, and a large loss occurred in Year 2, rather than 19 the expected profit. Green examined monthly data for Year 2 (not giren in the case), and he detected an 20 improving pattern daring the year. Monthly sales were rising, costs were falling, and large losses in the early 21 menths had turzed to a small profit by December. Thas, the annual data look tomenhat worse than final monthly 22 data. Also, it appears to be taking longer for the advertising program to get the message across, for the new 23 sales offices to generate sales, and for the new manufacturing facilities to operate efficiently. In other words, 24 the lags between spending money and deriving benefits were longer thanDigital's managers had anticipated. 25 For these reasons, Green and Sanders iee hope for the company - provided it can marire in the short rua. 26 27 Green must prepare an analysis of mhere the company is non, what it uaust do to regain its financial health, 28 and what actions should be taken. Green requested your group to complete this astigaed task for lim. 29 Use this Excel Workbook to perform the quantitative parts of the analyzis and prepare the report as a Word document. 30. The report should inclade only the interpretations of the quantitative results. Hor you found these results are to be shown in this Exeel Workbook. 31 Submit both filet via Blackboard as instructed. Digital Diagnostics Statement of Operations Yr l Actual Yr2 Actual Yr 3 Projected erenue: \begin{tabular}{|r|r|r|} \hline$3,432,000 & $5,834,400 & $7,035,600 \\ \hline$0 & $0 & $0 \\ \hline$3,432,000 & $5,834,400 & $7,035,600 \\ \hline \end{tabular} Other revenue Total rerenues Expenses: 40 Salaries and benefits 41 Supplies 42 Insurance and other 43 Drugs 44 Depreciation 45 Interest 46 Total expenses 47 Operating income 48 Provision for income taxes 49 Net income 5150 \begin{tabular}{|r|r|r|} \hline$2,864,000 & $4,980,000 & $5,800,000 \\ \hline$240,000 & $620,000 & $512,960 \\ \hline$50,000 & $50,000 & $50,000 \\ \hline$50,000 & $50,000 & $50,000 \\ \hline$18,900 & $116,960 & $120,000 \\ \hline$62,500 & $176,000 & $80,000 \\ \hline$3,285,400 & $5,992,960 & $6,612,960 \\ \hline$146,600 & $158,560 & $422,640 \\ \hline$58,640 & $63,424 & $169,056 \\ \hline$87,960 & $95,136 & $253,584 \\ \hline \end{tabular} 80 Other data: \begin{tabular}{|l|l|r|r|r|} \hline 81 & Stock price & & & \\ \hline 82 & Shares outstanding & $8.50 & $6.00 & $12.17 \\ \hline 83 & Tax rate & 100,000 & 100,000 & 250,000 \\ \hline 84 & Lease payments & 40% & 40% & 40% \\ \hline 85 & & $40,000 & $40,000 & $40,000 \\ \hline \end{tabular} 4 John Green, a recent graduate with four years of for-profit health management experience, was recently thought in as assistant to the chairman of the board of Digital Diagnostics, a manufacturer of clinical diagnostic equipment. The company had donbled its plant capacity, opened new sales offices outside its home territory, and launched an expensive adrertising campaign. Digital's results were not satisfactory, 8 to put it mildly. Its board of directors, which consisted of its president and rice president plus its major 9 stockholders (who were all local business people), was most upset when directors learned hor the expansion 10 was going. Suppliers were being paid late and were unhappy, and the bank was complaining about the cut off 11 credit. As a result, Eddie Sauders, Digital's president, was informed that changes would hare to be made, and 12 quickly, or he would be fired. Also, at the board's insistence, John Green was brought in and giren the job of 13 assistant to Wendy Smith, a retired banker who was Digital's chairwoman and largest stockholder. Sanders 14 agreed to gire up a few of his golfing days and help nurse the company back to health, with Green's assistance. 15 16 Green began by gathering financial statements and other data, shom belor. The data show the dire sifuation 17 that Digital Diagnostics was in after the expansion program. Thus far, sales hare not been ap to the 18 forecasted lerel, costs have been higher than were projected, and a large loss occurred in Year 2, rather than 19 the expected profit. Green examined monthly data for Year 2 (not giren in the case), and he detected an 20 improving pattern daring the year. Monthly sales were rising, costs were falling, and large losses in the early 21 menths had turzed to a small profit by December. Thas, the annual data look tomenhat worse than final monthly 22 data. Also, it appears to be taking longer for the advertising program to get the message across, for the new 23 sales offices to generate sales, and for the new manufacturing facilities to operate efficiently. In other words, 24 the lags between spending money and deriving benefits were longer thanDigital's managers had anticipated. 25 For these reasons, Green and Sanders iee hope for the company - provided it can marire in the short rua. 26 27 Green must prepare an analysis of mhere the company is non, what it uaust do to regain its financial health, 28 and what actions should be taken. Green requested your group to complete this astigaed task for lim. 29 Use this Excel Workbook to perform the quantitative parts of the analyzis and prepare the report as a Word document. 30. The report should inclade only the interpretations of the quantitative results. Hor you found these results are to be shown in this Exeel Workbook. 31 Submit both filet via Blackboard as instructed. Digital Diagnostics Statement of Operations Yr l Actual Yr2 Actual Yr 3 Projected erenue: \begin{tabular}{|r|r|r|} \hline$3,432,000 & $5,834,400 & $7,035,600 \\ \hline$0 & $0 & $0 \\ \hline$3,432,000 & $5,834,400 & $7,035,600 \\ \hline \end{tabular} Other revenue Total rerenues Expenses: 40 Salaries and benefits 41 Supplies 42 Insurance and other 43 Drugs 44 Depreciation 45 Interest 46 Total expenses 47 Operating income 48 Provision for income taxes 49 Net income 5150 \begin{tabular}{|r|r|r|} \hline$2,864,000 & $4,980,000 & $5,800,000 \\ \hline$240,000 & $620,000 & $512,960 \\ \hline$50,000 & $50,000 & $50,000 \\ \hline$50,000 & $50,000 & $50,000 \\ \hline$18,900 & $116,960 & $120,000 \\ \hline$62,500 & $176,000 & $80,000 \\ \hline$3,285,400 & $5,992,960 & $6,612,960 \\ \hline$146,600 & $158,560 & $422,640 \\ \hline$58,640 & $63,424 & $169,056 \\ \hline$87,960 & $95,136 & $253,584 \\ \hline \end{tabular} 80 Other data: \begin{tabular}{|l|l|r|r|r|} \hline 81 & Stock price & & & \\ \hline 82 & Shares outstanding & $8.50 & $6.00 & $12.17 \\ \hline 83 & Tax rate & 100,000 & 100,000 & 250,000 \\ \hline 84 & Lease payments & 40% & 40% & 40% \\ \hline 85 & & $40,000 & $40,000 & $40,000 \\ \hline \end{tabular}

Answer & Explanation Solved by verified expert

See Answer