IN EXCEL FORMAT

Alignment Number 5 Sys 3 ABC D H 1 ACCT 251 EXCEL PROJECT 2 - PREPARING FINANCIAL STATEMENTS IN EXCEL K M N Instructions for Completion of Assignment: Part 1. Prepare a trial balance, muill-step income statement statement of retained earnings, and balance sheet for Pale Ale ina as of December 31, 2018 Part 2 Prepare financial analyses for Pale Ale Inc and Porter Co as of December 31, 2018 Part 1: Detailed instructions 1. Open Excel Project 1 and ensure all tabel and amounts are correct 0 Trial Balance Row 28. colls E and F 3.252.570 3.252.570 12 Income Statement 13 Row 16-cells H and J Net Income 193.910 14 Statement of Retained Earnings 15 Row 11 - Coll M Ending balance, December 31 759.770 16 Balance Sheet 17 Row 19-es and Tous Ascot 2,214 00 18 Row 14-cells Q and R Total Liabilities 1.352 950 19 Row 18-cells Q and R: Total Stockholders Equity 881 130 20 Row 19 cells and R Total Lois & Stockholm Equity 2.214,000 21 2. Copy the Porter Co worksheet from Excel Project 1 to the Porter Co worksheet in Excel Project 2 This copies all of the first assignment into the second assignment 24 25 3. Copy the data from the Portor Co worksheet into the Pale Ale Inc worksheet 20 Al this point you will have worksheets with two different names but IDENTICAL dati 27 4. Update the Pasi Ale Inc worksheet as follows 29 Updates to robot Pale Ale Inc inchead of Portor Co 30 . Change the amounts in Column B for each account to Instructions Porter Pale Ale in Arsity D78 K ABC D E F G H 28 1 4. Update the Pale Ale Inc worksheet as follows 29 a. Update titles to reflect Pale Ale Inc instead of Porter Co 30 b. Change the amounts in Column B for each account to 31 Pale Ale Inc. Scenario #1 Amounts 32 Service Revenue 33 Utilities Expense 4,271,080 34 Cash 5,146,240 35 Accounts receivabb 5,440,000 36 Common stock 3,446 240 37 Payroll Expense 38 Buildings 39 Other liabilities 216,240 40 Interest payable 39,100 41 Land 42 Retained earings Supplies expenso 593,640 44 Rent expense 5,615.780 45 Dividends 877 880 46 Accounts payable 6,892 480 47 Equipment 9.180,000 48 Supplies 6.168.240 49 Salaries payable 4,808,240 SO Notes payable 51 c. Confirm each statement updated with the now amounts 52 Trial Balance 53 Row 28- cells E and F 110,587,380 54 Income Statement 55 Net Income Row 16-colls Hand J 56 Statement of Retained Earnings 57 Row 11-coll M Ending balanco, December 31 Instructions Porter Co Pale Ale Inc 43 110,587 380 8,592 940 25 832 180 "" A Paste General BIU . Merge & Centet $ % Undo Clipboard Font Ahgnment 5 Number 078 Xfx ABC D E F G H 55 Row 16- cells Hand J: K Not Income 56 Statement of Retained Earnings 6.592 940 57 Row 11- cell M Ending balanco, December 31 58 Balance Sheet 25,832.180 59 Row 19- cells O and P Total Assets 60 75 278,720 Row 14-cells Q and R Total Liabilities 61 Row 18- colls Q and R 46,000,300 Total Stockholders Equity 62 Row 19- cells Q and R 29,278.420 Total Liablities & Stockholders Equity 75,278,720 63 64 This is the end of Part 1 65 66 Part 2: Detailed instructions 67 1. For both Porter Co and Pale Ale Inc perform a vertical (common size) analysis on the income Statement 68 a Insert a new column to the right of J 69 b. Calculate the vertical analysis using Service Revenue with ABSOLUTE Reference it will equal 100%) 70 c. Copy the formula from the Service Revenue line to expenses and income 71 d. Ensure each amount within the vertical analysis cells are formatted as % with no decimals 72 2. For both Porter Co and Pale Ale Inc perform a vertical (common size) analysis on the Balance Shoot 73 a. Insert a new column to the right of 74 b. Calculate the vertical analysis using Total Assets with ABSOLUTE Reference (it will equal 100%) 75 c. Copy the formula from the Total Assets % to all asset, liability, and equity lines 76 d. Ensure each amount within the vertical analysis cells are formatted as % with no decimals 77 e Ensure the vertical analysis includes the liabilities and equity sido (column U) 78 3. Answer the following questions in the Porter Co workshoot (rows 30-35) 79 a Pale Ale Inc made 34 times more income than Porter Corporation ($6.6M versus $194) Does that mean PaleAle is 80 more profitable and a better investment than Porter Co? Why or why not? 81 b. Pale Ale Inc. has 34% more debt than Porter Co (546M versus 51.4M). Does this mean that Pale Ale relles 82 on debt to finance its company than Porter Co? Why or why not? 83 c. Which company do you think is a botter investment? Why? 84 4. Copy the Pale Ale Inc worksheet to a new worksheet, name it Pale Ale Inc. (2) and make the following changes Instructions Porter CO Pale Ale in HE + ndo Clipboard Font Alignment 15 Number ABC D F G H c Which company do you think is a better investment? Why? J K 4. Copy the Pale Ale Inc worksheet to a new worksheet, name it Pale Ale Inc. (2) and make the following changes Pale Ale Inc. Scenario #2 Amounts Service Revenue 60,000,000 Accounts Receivable 20,000,000 Common Stock 15,000,000 Payroll Expense 40,000,000 Rent Expense 10,000,000 Dividends 12,000,000 Accounts Payable 22,478,500 5. For Pale Ale Inc. Scenario 2, calculate the ratios below in the cells noted Ratio Cell 5 Profit Margin 837 6 Return on Assets B38 7 Return on Equity 839 8 Earnings per Share B40 99 Current Ratio B41 00 Debt to Equity Ratio B42 01 Assume all balance sheet amounts are the average for the year 02 * Assume notes payable is long term, all other liabities are current 103 Pale Ale Inc shares outstanding are 3,000,000 104 6. For Porter Co, calculate the ratios below in the cells noted on the Porter Co worksheet 105 Ratio Cell 106 Profit Margin B37 107 Return on Assets 838 108 Return on Equity B39 109 Earnings per Share B40 110 Current Ratio B41 111 Debt to Equity Ratio B42 Instructions Porter Co Pale Ale Inc 16 Paste u M ald IM EM 2 117 B 1 Merge & Center $ % 683 Conditio Undo Formatti Clipboard Font Alignment Number 178 f ABC D E F G H J 110 Current Ratio K M N 841 111 Debt to Equity Ratio 342 112 * Assume all balance sheet amounts are the average for the year 113 Assume notos payable is long term, all other liabilities are current 1114 *Porter Co shares outstanding are 200.000 115 7. Answer the following question in the Porter Co worksheet (row 45). After reviewing the financial statements of Porter Co and Pale No Inc 176 Scenano #2, and the resulting vertical analysis and ratios of each company, which company do you think is a better investment? Why? 118 Review Formatting of All Worksheets 119 The trial balance should be completed in the green section. There are exactly enough lines highlighted in green 120 The multi-stop income statement should be completed in the yellow section. There are exactly enough lines highlighted in yellow 121 The statement of retained earnings should be completed in the grey section There are exactly enough lines highlighted in grey The balance shoot should be completed in the blue section There are exactly enough lines highlighted in blue 123 All numbers must be formatted as numbers with commas and no decimals or dollar signs, 124 All cells must be the proper width so the content is completely visible 125 Cell height and font size may not be changed 126 Al totals, sublotals, and calculations must be populated with a formula and not with hard coded amounts 127 The title section of each report or financial statement must have merged cells so that each line is one call for each report or statement 128 Include line lities and proper and consistent formatting of text and numbers throughout the reports (font type, font size, font color background color, etc) 129 Do not put any blank lines anywhere within the reports or financial statements 130 All totals and subtotals must have total lines where appropriate 131 The only bolded font that should be used is in the titles and the titles of the two main sections of the balance sheet 132 133 Instructions for Turning in Assignment: 134 Assignment must be turned in on Canvas in Excel format 135 1122 136 137 138