Question

2. Using the trial balances data on the following page complete the following in Excel using two worksheets (Total 345 points): a. For worksheet 1: i. Enter the data as provided (36 pts) ii. Use the SUMO function to check your data entry, (12 pts) iii. Format the columns using appropriate formats ($ signs in first and last numbers in a column; numbers line up, commas for 000's, underlining of numbers only (not entire cell) before/after totals) (48 pts) b. For worksheet 2 you will be performing a financial statement analysis: i. Create a comparative Income Statement and Balance Sheet for the three years using formulas to refer to your original data in worksheet 1. 66 pts) Format the financial statements using appropriate formats. (40 pts) ii. Prepare a common sized income statement and balance sheet for the current year (Use the column to the left of the most recent income statement and balance sheet). (25 pts) iii. Prepare a trend analysis (use the columns immediately to the right of each year's financial statements) to show a year over year change and a year over year percentage change. (50 pts) Format appropriately. (50 pts)
You should use EXCEL formula

Answer 1

- 0 X AutoSum" AT A Clear Sort & Find & Filter Select Editing Al N O P Q Con = November (Autosaved) - Microsoft Excel Home Insert Page Layout Formulas Data Review View * Cut Book Antiqua - 11 - A A = = = Wrap Text General - 3 La Copy Pastel s in BI U - A E E E Merge & Center % , & Conditional Format Cell Insert Delete Format Formatting as Table Styles Clipboard Font Alignment Number Styles Cells for Balance Sheet A EF M 1 Balance Sheet Note: 2 December 31, 20Y6 Since We Know, 3 Assets Total assets - Total liabilities and Stock holders equity 4 Current Assets : Therefore, Retained earnings = 5 Cash $ 8,23,000.00 6 Marketable Securities $ 3,29,000.00 Total liabilities and stock holders equity $ 66,07,000.00 7 Accounts Receivable (Net) $ 7,14,000.00 Less: Total Liabilities $ (25,70,000.00) 8 Inventory $ 10,72,000.00 Less : Preferred stock $ (4,87,500.00) 9 Prepaid expenses $ 1,55,000.00 Less : Common stock $ (12,50,000.00) 10 Total Current assets $ 30,93,000.00 $ 22,99,500.00 11 Long term investments $ 8,24,000.00 12 property, plant and equipment $ 26,90,000.00 13 Total Assets $ 66,07,000.00 14 Liabilities 15 Current liabilities $ 8,80,000.00 16 Long Term Liabilities $ 16,90,000.00 17 Total Liabilities $ 25,70,000.00 18 Stock holders Equity 19 Preferred Stock $10 par $ 4,87,500.00 20 Common Stock, $5 par $ 12,50,000.00 21 Retained Earnings $ 22,99,500.00 22 Total Stock holders Equity $ 40,37,000.00 23 Total Laiabilities and stock holders equity $ 66,07,000.00 24 25 ™ Sheet814 Sheet816 Sheet822 Sheet823 Sheet828 Sheet829 Sheet838_Sheet833 Sheet834 Sheet836 Sheet1034_Sheel Ready Circular References K O + Type here to search o e @ 90% 0 uc 18:37 lfa 1x ENG 2010 29-02-20206 9 A