Name: FIRE 312, Summer 2023 Term Exam 1 This exam contains three problems with several sub-problems. Each problem is on a separate worksheet. You can obtain 215regular points. The total points for the problems are distributed as follows: Problem 160pts Problem 280pts Problem 375pts Sum215pts In solving the problems, make sure that all your calculations and results are labeled and formatted properly so that they are easy to read and understand. Please save your work often. Save the file to your Desktop and continue to save to this file as you complete your exam. This means that you cannot cut and paste any of your pre existing files into this workbook. You cannot communicate with others either physically or electronucally while you are taking the exam. Good luck!In solving the problems, make sure that all your calculations and results are labeled and formatted properly Possession or distribution of this exam outside of the exam environment will be considered a grave violation of the Honor Code. This is an open book and open notes exam. You cannot use any other electronic file to answer the questions, however. You have 1 hour to complete the exam.Exams turned in late will be penalized 25 points, exams more than ten minutes late will not be accepted.
Problem 1 Given below are annual data for total assets for Johnson & Johnson and UnitedHealth Groupfor the period 2010-2020, as of December 31st of each year. Perform the following analysis: 1 25ptsShow average growth rates in percent (%) with two decimals! 2 10ptsWhat was that growth rate? Provide answers in the yellow space on the right. 310pts Graph the trend in assets for each company using SPARKLINE charts. 415pts Graph asset values for both companies in one chart using two different chart types in the same chart. One chart can be a column chart and the other ascatter chart with years on the X axis. Place an appropriate chart title, axes titles and a legend on the graph. Make sure that the legend identifies the companies. 5Label and format all results such that they are easy to read and identify S60pts Total Assets in $ million YearABC CorpXYZ Corp 2011102,90863,063 2012113,66467,889 2013121,34780,885 2014132,68381,882 2015130,35886,382 2016137,411111,254 2017141,208132,810 2018157,303139,058 2019152,954152,221 2020157,728173,889 2021174,894197,289 2022182,018212,206 Arithmetic Average Growth Rate Geometric Average Growth RateCalculate ARITHMETIC and GEOMETRIC AVERAGE GROWTH RATE in assets for each company using anarrayfunction. Use anIFstatement to show which company had higher compound annual growth rate in assets over the period shown in the table?
Problem 2 Using the income statement and balance sheet data presented below do the following 160ptsFill in the yellow cells in the balance sheet and the statement of casf flows USING FORMULAS 220ptsCreate common-size income statement and balance sheets for 2020 and 2021 with formulas linked directly to the income statement and balance sheet presented. Create the common-size income statement and balance sheet in the green-shaded area in columns G, H, and I. S80pts Round Corp.Round Corp. Income StatementCommon Size Income Statement For the Year Ended Dec. 31For the Year Ended Dec. 31 2021202020212020 Sales16,014,77514,960,680Sales Cost of Goods Sold12,408,06211,656,223Cost of goods sold Gross Profit3,606,7123,304,457 Selling and G&A Expenses1,570,8121,435,494 Depreciation1,032,8081,137,418 EBIT1,003,093731,544 Interest Expense341,150241,842 Earnings Before Taxes (EBT)661,943489,703 Taxes79,878149,331 Net Income582,065340,371 Notes: Shares Outstanding80,00065,000 Dividends per Share$0.98$0.96 Round Corp.Round Corp. Balance SheetCommon Size Balance Sheet As of Dec. 31,As of Dec. 31, Assets20212020Assets20212020 Cash252,994220,272 Accounts Receivable813,014762,162 Inventory1,819,8951,131,586 Total Current Assets2,985,9032,114,020 Gross Fixed Assets14,428,28911,003,450 Accumulated Depreciation2,885,9033,030,919 Net Fixed Assets17,314,19214,034,369 Total Assets20,300,09616,148,388 Liabilities and Owners' Equity20212020Liabilities and Owners' Equity20212020 Accounts Payable879,577705,882 Notes Payable37,56850,468 Total Current Liabilities917,145756,350 Long-term Debt3,148,2042,016,806 Total Liabilities4,065,3492,773,156 Common Stock5,435,6624,487,486 Additional Paid-in-Capital1,627,4451,212,463 Retained Earnings9,171,6401,613,445 Total Shareholder's Equity16,234,7477,313,394 Total Liabilities and Owners' Equity20,300,09610,086,550 Round Corp. Statement of Cash Flows For the Year Ended Dec. 31, 2021 Cash Flows from Operations Net Income582,065 Depreciation1,032,808 Change in Accounts Receivable50,852 Change in Inventory688,309 Change in Accounts Payable173,694 Total Cash Flows from Operations755,759 Cash Flows from Investing Change in Fixed Assets3,424,840 Total Cash Flows from Investing3,424,840 Cash Flows from Financing Change in Notes Payable(12,900) Change in Long-Term Debt1,131,398 Change in Common Stock948,176 Change in Additional Paid-in Capital414,982 Dividend Paid to Stockholders6,976,130 Total Cash Flows from Financing9,457,786 Net Change in Cash Balance13,638,385Make sure touse the same formattingas in the original income statement, except for numbers, which should bepercentages with one decimal.
Why is this page out of focus?
Because this is a Premium document. Subscribe to unlock this document and more.