Instructions Porter Co Pale Ale Inc Change hea Instructions Porter Co Pale Ale Inc ACCT EXCEL PROJECT PREPARING FRANCIAL STATEMENT IN EXCEL 2022-Winter ACCT 251 Excel Project... Instructions Porter Co Pale Ale Inc ACCT 251 EXCEL PROJECT 2 - PREPARING FINANCIAL STATEMENTS IN EXCEL 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 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 label and amounts are correct Trial Balance: Row 28- cells E and F: Income Statement: Row 16- cells H and J: Statement of Retained Earnings: Row 11-cell M Balance Sheet: Row 19- cells O and P: Row 14- cells Q and R: Row 18-cells Q and R: Row 19-cells Q and R: 3,252,570 3,252,570 Net Income 193,910 Ending balance, December 31 759,770 Total Assets 2,214,080 Total Liabilities 1,352,950 Total Stockholders Equity 861,130. 2,214,080 Total Liablities & Stockholders Equity 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. 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 receivable 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 Income Statement: Row 16-cells H and J: Statement of Retained Earnings: Row 11-cell M Balance Sheet: Row 19- cells O and P: Row 14-cells Q and R: Row 18-cells Q and R: Row 19-cells Q and R: This is the end of Part 1 Net Income 6,592,940 Ending balance, December 31 25,832,180 Total Assets 75,278,720 Total Liabilities 46,000,300 Total Stockholders Equity 29,278,420 Total Liablities & Stockholders Equity, 75,278,720 Part 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 Q Part 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 Q 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 e. Ensure the vertical analysis includes the liabilities and equity side (column U) 3. Answer the following questions in the Porter Co worksheet (rows 30-35) a. Pale Ale Inc. made 34 times more income than Porter Corporation ($6.6M versus $194k). 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 ($46M 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 it Pale Ale Inc. (2) and make the following changes: Pale Ale Inc. Scenario #2 Amounts: Service Revenue Accounts Receivable 60,000,000 20,000,000 Rent Expense Dividends Accounts Payable 10,000,000 12,000,000 22,478,500 5. For Pale Ale Inc. Scenario 2, calculate the ratios below in the cells noted. Ratio Cell Profit Margin 837 Return on Assets B38 Return on Equity 839 Earnings per Share 140 Current Ratio 841 Debt to Equity Ratio 842 "Assume all balance sheet amounts are the average for the year *Assume notes payable is long term, all other liabities are current *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 Profit Margin Cell B37 Return on Assets 83K Return on Equity 139 Earnings per Share 840 Current Ratio B41 Debt to Equity Ratio 842 *Assume all balance sheet amounts are the average for the year *Assume notes payable is long term, all other liabities 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 lines 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. Al 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 tities 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