Part 1 & Part 2.

Instructions for Completion of Assignment: Part 1 Prepare a trial balance, multi-step income statement statement of retained earnings, and balance sheet for Pale Ale Inc as of December 31, 2021 Part 2 Prepare financial analyses for Pale Ale Inc and Porter Co as of December 31, 2021 Part 1: Detailed Instructions 1. Open Excel Project 1 and ensure all label and amounts are correct Trial Balance Row 28 cells E and F 3.252.570 3.252,570 Income Statement Row 16-cells Hand J Net Income 193,910 Statement of Retained Earnings Row 11- cell M Ending balance, December 31 759,770 Balance Sheet Row 19. cells and P Total Assets 2214 080 Row 14-cells Q and R Total Liabilities 1,352,950 Row 18-cells Q and R Total Stockholders Equity 861,130 Row 19 cells Q and R: Total Liablities & Stockholders Equity 1,352,950 2 Copy the Porter Co worksheet from Excel Project 1 to the Portor Co worksheet in Excel Project 2 This copies all of the first assignment into the second assignment 3 Copy the data from the Porter Co worksheet into the Pale Ale Inc worksheet At this point, you will have worksheets with two different names but IDENTICAL data 4. Update the Pale Ale Inc worksheet as follows: a Update titles to reflect Pale Ale Inc instead of Porter Co b Change the amounts in Column B for each account to Pale Ale Inc Scenario #1 Amounts Service Revenue 41,023.720 Utilities Expense 4,271,080 Cash 5,146 240 Accounts receivab 5,440,000 Common stock 3,446,240 Payroll Expense 23,950,280 Buildings 23,120,000 Other liabilities 216.240 Interest payable 39.100 Land 26 226,240 Retained samings 20.117 120 Supplies expenso 593,640 Rantanen 5615 7R At this point, you will have worksheets with two different names but IDENTICAL data 4. Update the Pale Ale Inc worksheet as follows: a Update titles to reflect Pale Ale Inc instead of Porter Co Change the amounts in Column B for each account to Pale Ale Inc Scenario #1 Amounts Service Revenue 41,023,720 Uities Expense 4,271,080 Cash 5,146,240 Accounts receivab 5,440,000 Common stock 3,446,240 Payroll Expense 23,950,280 Buildings 23,120,000 Other liabilities 216,240 Interest payable 39,100 Land 26,226,240 Retained earnings 20,117,120 Supplies expense 593,640 Rent expense 5,615,780 Dividends 877,880 Accounts payable 6,892,480 Equipment 9,180,000 Supplies 6,166,240 Salaries payable 4,806,240 Notes payable 34,046,240 e Confirm each statement updated with the new amounts Trial Balance Row 28- cells E and F 110,587,380 Income Statement Row 16- cells Hand J Net Income Statement of Retained Eamings Row 11- call M Ending balance December 31 Balance Sheet Row 19- cells and P Total Assets Row 14-cells Q and R Total Liabilities Row 18-cells Q and R Total Stockholders Equity Row 19- cells Q and R Total Liabilities & Stockholders Equity This is the end of Part 1 110,587,380 6,592,940 25,832 180 75 278,720 46,000,300 29.278,420 75, 278,720 Part 2 Detailed instructions 1. For both Porter Co and Pale Ale Inc perform a vertical (common sel analysis on the income Statement art 2: Detailed Instructions 1. For both Porter Co and Pale Ale Inc perform a vertical (common size) analysis on the Income Statement a. Insert a new column to the right of J b. Calculate the vertical analysis using Service Revenue with ABSOLUTE Reference (It will equal 100%) c. Copy the formula from the Service Revenue line to expenses and income d. Ensure each amount within the vertical analysis cells are formatted as % with no decimals 2. For both Porter Co and Pale Ale Inc perform a vertical (common size) analysis on the Balance Sheet a. Insert a new column to the right of b. Calculate the vertical analysis using Total Assets with ABSOLUTE Reference (it will equal 100%) c Copy the formula from the Total Assets % to all asset, liability, and equity lines d. Ensure each amount within the vertical analysis cells are formatted as % with no decimals 3. Answer the following questions in the Porter Co worksheet (rows 30-35) a. PaleAle Inc. made 34 times more income than Porter Corporation (36.6M versus S194k). Does that mean PaleAle is more profitable and a better investment than Porter Co? Why or why not? b. Pale Ale Inc. has 34% more debt than Porter Co (546M versus $1.4M). Does this mean that PaleAle relies on debt to finance its company than Porter Co? Why or why not? c. Which company do you think is a better investment? Why? 4. Copy the Pale Ale Inc worksheet to a new worksheet, name 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 Proht Margin 337 Return on Assets 338 Return on Equity 839 tarnings per Share 840 Current Ratio 141 Debt to Equity Ratio 142 - Assume all balance sheet amounts are the average for the year Assume notes payable is long term, all other abilities are current * Pale Ale Inc shares outstanding are 3,000,000 6. For Porter Co, calculate the ratlos below in the cells noted on the Porter Co worksheet Ratio Cell 337 Return on Assets 038 Profit margin Pale Ale Inc shares outstanding are 3,000,000 6. For Porter Co, calculate the ratios below in the cells noted on the Porter Co worksheet Ratio Cell Profit Margin 537 Return on Assets BA Return on Equity 839 Eamines per Share 8.40 Current Ratio 541 Debt to Equity Ratio BA2 Assume al balance sheet amounts are the average for the year Assume notes payable is long term, all other liabilities are current * Porter Co shares outstanding are 200,000 7. Answer the following question in the Porter Co worksheet (row 45): After reviewing the financial statements of Porter Co and Pale Ale Inc Scenario #2, and the resulting vertical analysis and ratios of each company, which company do you think is a better investment? Why? Review Formatting of All Worksheets The trial balance should be completed in the green section. There are exactly enough nes highlighted in green The multi-step income statement should be completed in the yellow section. There are exactly enough lines highlighted in yellow The statement of retained earnings should be completed in the grey section. There are exactly enough lines highlighted in grey The balance sheet should be completed in the blue section There are exactly enough lines highlighted in blue All numbers must be formatted as numbers with commas and no decimals or dollar signs All cells must be the proper width so the content is completely visible Cell height and font size may not be changed All totals, subtotals, and calculations must be populated with a formula and not with hard coded amounts The title section of each report or financial statement must have merged cells so that each line is one cell for each report or statement Include line titles and proper and consistent formatting of text and numbers throughout the reports (font type, font size, font color, background color, etc). Do not put any blank lines anywhere within the reports or financial statements All totals and subtotals must have total lines where appropriate The only bolded font that should be used is in the titles and the titles of the two main sections of the balance sheet Instructions for Turning In Assignment Assignment must be turned in on Canvas in Excel format ACCT 251 EXCEL PROJECT 2 - PREPARING FINANCIAL STATEMENTS IN EXCEL (see Instructions tab for detailed instructions) Power Corpory francia Free como he was 11 ACCT 251 EXCEL PROJECT 2 - PREPARING FINANCIAL STATEMENTS IN EXCEL (600 Instructions tab for detailed instructions) Plan Presentato Prepare and storend how Prepare and shoob 11 Instructions for Completion of Assignment: Part 1 Prepare a trial balance, multi-step income statement statement of retained earnings, and balance sheet for Pale Ale Inc as of December 31, 2021 Part 2 Prepare financial analyses for Pale Ale Inc and Porter Co as of December 31, 2021 Part 1: Detailed Instructions 1. Open Excel Project 1 and ensure all label and amounts are correct Trial Balance Row 28 cells E and F 3.252.570 3.252,570 Income Statement Row 16-cells Hand J Net Income 193,910 Statement of Retained Earnings Row 11- cell M Ending balance, December 31 759,770 Balance Sheet Row 19. cells and P Total Assets 2214 080 Row 14-cells Q and R Total Liabilities 1,352,950 Row 18-cells Q and R Total Stockholders Equity 861,130 Row 19 cells Q and R: Total Liablities & Stockholders Equity 1,352,950 2 Copy the Porter Co worksheet from Excel Project 1 to the Portor Co worksheet in Excel Project 2 This copies all of the first assignment into the second assignment 3 Copy the data from the Porter Co worksheet into the Pale Ale Inc worksheet At this point, you will have worksheets with two different names but IDENTICAL data 4. Update the Pale Ale Inc worksheet as follows: a Update titles to reflect Pale Ale Inc instead of Porter Co b Change the amounts in Column B for each account to Pale Ale Inc Scenario #1 Amounts Service Revenue 41,023.720 Utilities Expense 4,271,080 Cash 5,146 240 Accounts receivab 5,440,000 Common stock 3,446,240 Payroll Expense 23,950,280 Buildings 23,120,000 Other liabilities 216.240 Interest payable 39.100 Land 26 226,240 Retained samings 20.117 120 Supplies expenso 593,640 Rantanen 5615 7R At this point, you will have worksheets with two different names but IDENTICAL data 4. Update the Pale Ale Inc worksheet as follows: a Update titles to reflect Pale Ale Inc instead of Porter Co Change the amounts in Column B for each account to Pale Ale Inc Scenario #1 Amounts Service Revenue 41,023,720 Uities Expense 4,271,080 Cash 5,146,240 Accounts receivab 5,440,000 Common stock 3,446,240 Payroll Expense 23,950,280 Buildings 23,120,000 Other liabilities 216,240 Interest payable 39,100 Land 26,226,240 Retained earnings 20,117,120 Supplies expense 593,640 Rent expense 5,615,780 Dividends 877,880 Accounts payable 6,892,480 Equipment 9,180,000 Supplies 6,166,240 Salaries payable 4,806,240 Notes payable 34,046,240 e Confirm each statement updated with the new amounts Trial Balance Row 28- cells E and F 110,587,380 Income Statement Row 16- cells Hand J Net Income Statement of Retained Eamings Row 11- call M Ending balance December 31 Balance Sheet Row 19- cells and P Total Assets Row 14-cells Q and R Total Liabilities Row 18-cells Q and R Total Stockholders Equity Row 19- cells Q and R Total Liabilities & Stockholders Equity This is the end of Part 1 110,587,380 6,592,940 25,832 180 75 278,720 46,000,300 29.278,420 75, 278,720 Part 2 Detailed instructions 1. For both Porter Co and Pale Ale Inc perform a vertical (common sel analysis on the income Statement art 2: Detailed Instructions 1. For both Porter Co and Pale Ale Inc perform a vertical (common size) analysis on the Income Statement a. Insert a new column to the right of J b. Calculate the vertical analysis using Service Revenue with ABSOLUTE Reference (It will equal 100%) c. Copy the formula from the Service Revenue line to expenses and income d. Ensure each amount within the vertical analysis cells are formatted as % with no decimals 2. For both Porter Co and Pale Ale Inc perform a vertical (common size) analysis on the Balance Sheet a. Insert a new column to the right of b. Calculate the vertical analysis using Total Assets with ABSOLUTE Reference (it will equal 100%) c Copy the formula from the Total Assets % to all asset, liability, and equity lines d. Ensure each amount within the vertical analysis cells are formatted as % with no decimals 3. Answer the following questions in the Porter Co worksheet (rows 30-35) a. PaleAle Inc. made 34 times more income than Porter Corporation (36.6M versus S194k). Does that mean PaleAle is more profitable and a better investment than Porter Co? Why or why not? b. Pale Ale Inc. has 34% more debt than Porter Co (546M versus $1.4M). Does this mean that PaleAle relies on debt to finance its company than Porter Co? Why or why not? c. Which company do you think is a better investment? Why? 4. Copy the Pale Ale Inc worksheet to a new worksheet, name 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 Proht Margin 337 Return on Assets 338 Return on Equity 839 tarnings per Share 840 Current Ratio 141 Debt to Equity Ratio 142 - Assume all balance sheet amounts are the average for the year Assume notes payable is long term, all other abilities are current * Pale Ale Inc shares outstanding are 3,000,000 6. For Porter Co, calculate the ratlos below in the cells noted on the Porter Co worksheet Ratio Cell 337 Return on Assets 038 Profit margin Pale Ale Inc shares outstanding are 3,000,000 6. For Porter Co, calculate the ratios below in the cells noted on the Porter Co worksheet Ratio Cell Profit Margin 537 Return on Assets BA Return on Equity 839 Eamines per Share 8.40 Current Ratio 541 Debt to Equity Ratio BA2 Assume al balance sheet amounts are the average for the year Assume notes payable is long term, all other liabilities are current * Porter Co shares outstanding are 200,000 7. Answer the following question in the Porter Co worksheet (row 45): After reviewing the financial statements of Porter Co and Pale Ale Inc Scenario #2, and the resulting vertical analysis and ratios of each company, which company do you think is a better investment? Why? Review Formatting of All Worksheets The trial balance should be completed in the green section. There are exactly enough nes highlighted in green The multi-step income statement should be completed in the yellow section. There are exactly enough lines highlighted in yellow The statement of retained earnings should be completed in the grey section. There are exactly enough lines highlighted in grey The balance sheet should be completed in the blue section There are exactly enough lines highlighted in blue All numbers must be formatted as numbers with commas and no decimals or dollar signs All cells must be the proper width so the content is completely visible Cell height and font size may not be changed All totals, subtotals, and calculations must be populated with a formula and not with hard coded amounts The title section of each report or financial statement must have merged cells so that each line is one cell for each report or statement Include line titles and proper and consistent formatting of text and numbers throughout the reports (font type, font size, font color, background color, etc). Do not put any blank lines anywhere within the reports or financial statements All totals and subtotals must have total lines where appropriate The only bolded font that should be used is in the titles and the titles of the two main sections of the balance sheet Instructions for Turning In Assignment Assignment must be turned in on Canvas in Excel format ACCT 251 EXCEL PROJECT 2 - PREPARING FINANCIAL STATEMENTS IN EXCEL (see Instructions tab for detailed instructions) Power Corpory francia Free como he was 11 ACCT 251 EXCEL PROJECT 2 - PREPARING FINANCIAL STATEMENTS IN EXCEL (600 Instructions tab for detailed instructions) Plan Presentato Prepare and storend how Prepare and shoob 11