- Insert Draw Page Layout Formulas Data Review View Arial V 10 A A V General 16 Biu V A Up 9 C123 Xfx All Cells Must Be 1 (80.15 KiB) Viewed 14 times
- Insert Draw Page Layout Formulas Data Review View Arial V 10 A A V General 16 Biu V A Up 9 C123 Xfx All Cells Must Be 2 (80.15 KiB) Viewed 14 times
- Insert Draw Page Layout Formulas Data Review View Arial V 10 A A V General 16 Biu V A Up 9 C123 Xfx All Cells Must Be 3 (60.88 KiB) Viewed 14 times
- Insert Draw Page Layout Formulas Data Review View Arial V 10 A A V General 16 Biu V A Up 9 C123 Xfx All Cells Must Be 4 (75.29 KiB) Viewed 14 times
- Insert Draw Page Layout Formulas Data Review View Arial V 10 A A V General 16 Biu V A Up 9 C123 Xfx All Cells Must Be 5 (71.3 KiB) Viewed 14 times
- Insert Draw Page Layout Formulas Data Review View Arial V 10 A A V General 16 Biu V A Up 9 C123 Xfx All Cells Must Be 6 (69.09 KiB) Viewed 14 times
- Insert Draw Page Layout Formulas Data Review View Arial V 10 A A V General 16 Biu V A Up 9 C123 Xfx All Cells Must Be 7 (31.31 KiB) Viewed 14 times
x ✓ fx All cells must be the proper width so the content is completely visible. ABC D E F G H 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: PaleAle Inc. Scenario #1 Amounts: Service Revenue 61,535,580 Utilities Expense 6,406,620 Cash 7,719,360 Accounts receivable 8,160,000 Common stock 5,169,360 Payroll Expense 35,925,420 Buildings 34,680,000 Other liabilities 324,360 58,650 Interest payable Land 39,339,360 Retained earnings 30,175,680 Supplies expense 890,460 8,423,670 Rent expense Dividends 1,316,820 Accounts payable 10,338,720 Equipment 13,770,000 Supplies 9,249,360 Salaries payable 7,209,360 Notes payable 51,069,360 c. Confirm each statement updated with the new amounts Trial Balance: Row 28-cells E and F: Income Statement: 165,881,070 Net Income Row 16-cells H and J: Statement of Retained Earnings: Row 11-cell M Ending balance, December 31 Balance Sheet Row 19- cells O and P: Row 14-cells Q and R: Total Assets Total Liabilities Instructions Pale Ale Inc C123 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 Ready Porter Co + 165,881,070 9,889,410 38,748,270 112,918,080 69,000,450 ** K e a
Paste C123 B I Xfx All cells must be the proper width so the content is completely visible. D E F G H 60 Row 14-cells Q and R: 61 Row 18-cells Q and R: Total Liabilities Total Stockholders Equity Total Liablities & Stockholders Equity 62 Row 19- cells Q and R: 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. a. Insert a new column to the right of J 68 69 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 70 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 Sheet a. Insert a new column to the right of Q and T 73 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 3. Answer the following questions in the Porter Co worksheet (rows 30-35) 78 a. PaleAle Inc. made 51 times more income than Porter Corporation ($9.9M versus $194k). Does that mean PaleAle is more profitable and a better investment than Porter Co? Why or why not? 79 80 b. PaleAle Inc. has 51% more debt than Porter Co ($69M versus $1.4M). Does this mean that PaleAle relies 81 on debt to finance its company more than Porter Co? Why or why not? 82 c. Which company do you think is a better investment? Why? 83 4. Copy the Pale Ale Inc worksheet to a new worksheet, name it Pale Ale Inc. (2) and make the following changes: 84 PaleAle Inc. Scenario #2 Amounts; 85 Service Revenue 95,000,000 86 Accounts Receivable 18,000,000 87 Common Stock 33,000,000 88 Payroll Expense 48,000,000 89 31,000,000 Rent Expense Dividends 90 19,000,000 91 Accounts Payable 11,217,750 92 5. For Pale Ale Inc. Scenario 2, calculate the ratios below in the cells noted. 93 Ratio Cell Instructions Porter Co Pale Ale Inc + Ready ABC 14 > Tili ME ME Merge & Center vi 69,000,450 43,917,630 112,918,080 $% S
ome V Paste 123 Page Layout Ready Insert Draw Wrap Text v ✓ A A General Arial BIU V Merge & Center $ 三天 V V X fx All cells must be the proper width so the content is completely visible. ABC D E G H 1 4. Copy the Pale Ale Inc worksheet to a new worksheet, name it Pale Ale Inc. (2) and make the following changes: 4 PaleAle Inc. Scenario #2 Amounts: Service Revenue 95,000,000 36 Accounts Receivable 18,000,000 37 Common Stock 33,000,000 38. Payroll Expense 48,000,000 89 Rent Expense 31,000,000 90 Dividends 19,000,000 91 Accounts Payable 11,217,750 92 5. For Pale Ale Inc. Scenario 2, calculate the ratios below in the cells noted. 93 Ratio Cell 94 Profit Margin 837 95 Return on Assets 838 96 Return on Equity 839 97 Earnings per Share 840 98 Current Ratio 841 99 Debt to Equity Ratio 842 100 Assume all balance sheet amounts are the average for the year 101 *Assume notes payable is long term, all other liabilities are current 102 *Pale Ale Inc shares outstanding are 2,500,000 103 6. Calculate the ratios below in the cells noted on the Porter Co worksheet 104 Ratio Cell 105 Profit Margin 837 106 Return on Assets 838 107 Return on Equity 839 108 Earnings per Share B40 109 Current Ratio B41 110 Debt to Equity o 842 111 Assume all balance sheet amounts are the average for the year 112 *Assume notes payable is long term, all other liabilities are current 113 Porter Co shares outstanding are 200,000 114 115 7. Answer the following question in the Porter Co worksheet (row 45): After reviewing the financial statements of Porter Co an Scenario #2, and the resulting vertical analysis and ratios of each company, which company do you think is a better investm Pale Ale Inc. + <> Instructions, Porter Co Formulas 10 Data Review View Tell me %
Excel File Edit View Insert Format Tools Data AutoSave OFF 2022-ACCT 251 Excel Project 2 (1) ~ ne Insert Draw Page Layout Formulas Data Review View Tell me Arial 10 V Α' Α' Wrap Text General In B I U✔ A V V Merge & Center $% 9 X✔ fx All cells must be the proper width so the content is completely visible. D E F G H L M name mu poyourarong sum, uiroure comm *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 eamings 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, except ratios, must be formatted as numbers with commas and no decimals or dollar signs. Ratios should be formatted as a percent (XXX%). EPS st Allcells 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. 29 All totals and subtotals must have total lines where appropriate. 30 The only bolded font that should be used is in the sides and the titles of the two main sections of the balance sheet 31 32 Instructions for Turning In Assignment: 33 Assignment must be turned in on Canvas in Excel format. 34 35 36 ste 3 ABC 37 138 1 Window Help Conditiona Formatting N
2022-ACCT 291 Excel Project (1) Draw View T 10A A P Pa ELUA $-% 9 Can Fe Cak Benton cao TH 819
-A ACCT 251 EXCEL PROJECT 2-PREPARING FINANCIAL STATEMENTS IN EXCEL (see instructions tab for detailed instructions) Peter Company sta Papers be ban a retallad songs matte 129141 Home 면 Reaty Antan met Page Layout ܀ com Per Co PA POL Formas Data Review 01 WES Dewe formal Preparece staat ee Share 27-0- Sut& FMA Sex Q8 Comments 500395 and dala 100%