Exam 1 (1)

Virginia Commonwealth University **We aren't endorsed by this school
FIRE 312
Oct 26, 2023
Uploaded by SuperSummerEagle9 on coursehero.com
This is a preview
Want to read all 4 pages? Go Premium today.
Already Premium? Sign in here
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 215 regular points. The total points for the problems are distributed as follows: Problem 1 60pts Problem 2 80pts Problem 3 75pts Sum 215pts 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 Group for the period 2010-2020, as of December 31st of each year. Perform the following analysis: 1 25pts Show average growth rates in percent (%) with two decimals! 2 10pts What was that growth rate? Provide answers in the yellow space on the right. 3 10pts Graph the trend in assets for each company using SPARKLINE charts. 4 15pts 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 a scatter 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. 5 Label and format all results such that they are easy to read and identify S 60pts Total Assets in $ million Year ABC Corp XYZ Corp 2011 102,908 63,063 2012 113,664 67,889 2013 121,347 80,885 2014 132,683 81,882 2015 130,358 86,382 2016 137,411 111,254 2017 141,208 132,810 2018 157,303 139,058 2019 152,954 152,221 2020 157,728 173,889 2021 174,894 197,289 2022 182,018 212,206 Arithmetic Average Growth Rate Geometric Average Growth Rate Calculate ARITHMETIC and GEOMETRIC AVERAGE GROWTH RATE in assets for each company using an array function. Use an IF statement 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 1 60pts Fill in the yellow cells in the balance sheet and the statement of casf flows USING FORMULAS 2 20pts Create 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. S 80pts Round Corp. Round Corp. Income Statement Common Size Income Statement For the Year Ended Dec. 31 For the Year Ended Dec. 31 2021 2020 2021 2020 Sales 16,014,775 14,960,680 Sales Cost of Goods Sold 12,408,062 11,656,223 Cost of goods sold Gross Profit 3,606,712 3,304,457 Selling and G&A Expenses 1,570,812 1,435,494 Depreciation 1,032,808 1,137,418 EBIT 1,003,093 731,544 Interest Expense 341,150 241,842 Earnings Before Taxes (EBT) 661,943 489,703 Taxes 79,878 149,331 Net Income 582,065 340,371 Notes: Shares Outstanding 80,000 65,000 Dividends per Share $0.98 $0.96 Round Corp. Round Corp. Balance Sheet Common Size Balance Sheet As of Dec. 31, As of Dec. 31, Assets 2021 2020 Assets 2021 2020 Cash 252,994 220,272 Accounts Receivable 813,014 762,162 Inventory 1,819,895 1,131,586 Total Current Assets 2,985,903 2,114,020 Gross Fixed Assets 14,428,289 11,003,450 Accumulated Depreciation 2,885,903 3,030,919 Net Fixed Assets 17,314,192 14,034,369 Total Assets 20,300,096 16,148,388 Liabilities and Owners' Equity 2021 2020 Liabilities and Owners' Equity 2021 2020 Accounts Payable 879,577 705,882 Notes Payable 37,568 50,468 Total Current Liabilities 917,145 756,350 Long-term Debt 3,148,204 2,016,806 Total Liabilities 4,065,349 2,773,156 Common Stock 5,435,662 4,487,486 Additional Paid-in-Capital 1,627,445 1,212,463 Retained Earnings 9,171,640 1,613,445 Total Shareholder's Equity 16,234,747 7,313,394 Total Liabilities and Owners' Equity 20,300,096 10,086,550 Round Corp. Statement of Cash Flows For the Year Ended Dec. 31, 2021 Cash Flows from Operations Net Income 582,065 Depreciation 1,032,808 Change in Accounts Receivable 50,852 Change in Inventory 688,309 Change in Accounts Payable 173,694 Total Cash Flows from Operations 755,759 Cash Flows from Investing Change in Fixed Assets 3,424,840 Total Cash Flows from Investing 3,424,840 Cash Flows from Financing Change in Notes Payable (12,900) Change in Long-Term Debt 1,131,398 Change in Common Stock 948,176 Change in Additional Paid-in Capital 414,982 Dividend Paid to Stockholders 6,976,130 Total Cash Flows from Financing 9,457,786 Net Change in Cash Balance 13,638,385 Make sure to use the same formatting as in the original income statement, except for numbers, which should be percentages with one decimal.
Why is this page out of focus?
Because this is a Premium document. Subscribe to unlock this document and more.
Page1of 4
Uploaded by SuperSummerEagle9 on coursehero.com