Question

1 Approved Answer

Posted on Oct 22, 2024

I need urgent help with this. AB C E G K L M 1 ACCT 251 EXCEL PROJECT 2-PREPARING FINANCIAL STATEMENTS IN EXCEL 2 3

I need urgent help with this. image text in transcribed

AB C E G K L M 1 ACCT 251 EXCEL PROJECT 2-PREPARING FINANCIAL STATEMENTS IN EXCEL 2 3 4 Instructions for Completion of Assignment: Part 1: Prepare a trial balance, 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 5 6 7 8 Part 1: Detailed Instructions 9 1. Open Excel Project 1 and ensure all label and amounts are correct 10 Trial Balance: 11 Row 28- cells E and F: 3,252,570 12 Income Statement: 3,252,570 193.910 13. Row 16-cells H and J: Net Income 14 Statement of Retained Earnings: 15 Row 11-cell M Ending balance, December 31, 759,770 16 Balance Sheet 17 Row 19- cells O and P: Total Assets 2,214,080 Total Liabilities Row 14-cells Q and R: 1,352,950 18 Total Stockholders Equity 861,130 19 Row 18-cells Q and R: Total Liablities & Stockholders Equity Row 19- cells Q and R: 2,214,080 20 21 22 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. 23 24 25 3. Copy the data from the Porter Co worksheet into the Pale Ale Inc worksheet 26 At this point, you will have worksheets with two different names but IDENTICAL data 27 8 4. Update the Pale Ale Inc worksheet as follows: 9 a. Update titles to reflect Pale Ale Inc instead of Porter Co 0 b. Change the amounts in Column B for each account to: PaleAle Inc. Scenario #1 Amounts; 1 Service Revenue 61,535,580 2 Utilities Expense 6,406,620 Cash 7,719,360 Pale Ale Inc + Instructions Porter Co 1 HOL ZX 22 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. 23 24 25 3. Copy the data from the Porter Co worksheet into the Pale Ale Inc worksheet 26 At this point, you will have worksheets with two different names but IDENTICAL data 27 28 4. Update the Pale Ale Inc worksheet as follows: 29 a. Update titles to reflect Pale Ale Inc instead of Porter Col 30 b. Change the amounts in Column B for each account to: 31 PaleAle Inc. Scenario #1 Amounts; 32 Service Revenue 61,535,580 33 6,406,620 Utilities Expense Cash 34 7,719,360 35 Accounts receivable 8,160,000 36 Common stock 5,169,360 37 Payroll Expense 35,925,420 38 Buildings 34,680,000 324,360 39 Other liabilities 40 Interest payable 58,650 Land 39,339,360 41 42 Retained earnings 30,175,680 Supplies expense 890,460 8,423,670 Rent expense Dividends 1,316,820 Accounts payable 10,338,720 13,770,000 Equipment 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:: Row 16-cells H and J: Porter Co Pale Ale Inc AD 43 44 45 46 47 48 49 50 51 52 53 54 55 345678 NNN. + 165,881,070 Net Income. 165,881,070 9,889,410 xfx All cells must be the proper width so the content is completely visible. D E 6 H c Confirm each statement updated with the new amounts Trial Balance Row 28-celle E and F 105.861.070 165.001.070 Income Statement Row 16-cells H and J Net Income 9.809.410 Statement of Retained Eamings Row 11-cell M Ending balance, December 31 38,748.270 Balance Sheet Row 19- cells and P 112,918.000 Tobe Assets Total Liables Row 14-cells Q and R 69,000 450 61 Row 18-cells and R Total Stockholders Equity 43917630 62 Row 19-cells Q and R 112,918,000 Total Lisbites & Stockholders Equity 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 68 69 b. Calculate the vertical analysis using Service Revenue with ABSOLUTE Reference (twill 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 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 formated as % with no decimals 77 3. Answer the following questions in the Porter Co worksheet (rows 30-35) a. PaleAle Inc. made 51 times more income than Porter Corporation ($9.9M versus $194k). Does that mean PaleA 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? c. Which company do you think is a better investment? Why? oworkabeet to a new worksheet, name it Pale Ale Inc. (2) and make the following changes: C123 51 52 53 54 55 56 57 58 59 60 ABC P 9 E J All must be the proper width so the content is completely visible. BC D E F G H 1 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 Amount Service Revenue $5,000,000 Accounts Receivable 18,000,000 Common Stock 33,000,000 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 Bato Cell 94 Profit Margin 037 95 Return on Assets 038 56 79 Return on Equity famings per Share 97 540 9.8 Current Ratio 1 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 liabides are current *Pale Ale Inc shares outstanding are 2,500,000 102 103 6. Calculate the ratios below in the cells noted on the Porter Co worksheet 104 Rato Cell 105 Profit Margin 837 838 106 Return on Assets 107 839 Return on Equity Earnings per Share 108 840 841 109 Current Ratio Debt to Equity Ratio 842 110 111 Assume all balance sheet amounts are the average for the year i 112 Assume notes payable is long term, all other liabiles are current 113 *Porter Co shares outstanding are 200.000 85 86 87 88 M N tion in the Parter Co worksheet (row 45): After reviewing the financial statements of Porter Co and Pale Ale Inc think in hatter? What O 0 112 Assume notes payable is long term, all other liabides are current *Porter Co shares outstanding are 200,000 113 114 115 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? 116 117 Review Formatting of All Worksheets 118 The trial balance should be completed in the green section. There are exactly enough lines highlighted in green 119 The multi-step income statement should be completed in the yellow section. There are exactly enough lines highlighted in yellow 120 121 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 122 All numbers, except ratios, must be formatted as numbers with commas and no decimals or dollar signs. Ratios should be formated as a percent DOCX%). EPS should be formatted as dollars (XXX) Alcells must be the proper width so the content is completely visible. 123 124 Cell height and font size may not be changed. 125 All totals, subtotals, and calculations must be populated with a formula and not with hard coded amounts 126 The Ste section of each report or financial statement must have merged cells so that each line is one cell for each report or statement 127 Include line sites and proper and consistent formating 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. 128 129 All totals and subtotals must have total lines where appropriate. 130 The only bolded font that should be used is in the stes and the sides of the two main sections of the balance sheet 131 132 Instructions for Turning In Assignment: 133 Assignment must be tumed in on Canvas in Excel format 134 135 136 137 138 139 140 141 142 143 144 . 4 ACCT 251 EXCEL PROJECT 2-PREPARING FINANCIAL STATEMENTS IN EXCEL (see instructions tab for detailed instructions) Parter Company al Prepare bial balance Prepare an Per 3301/21 Porter Co Pale Ale Inc Instructions A 197 37 19 34 15 1018 Custom ID- 32 Merge & C NA Cabe Take y A23 :x fa ACCT 251 EXCEL PROJECT 2-PREPARING FINANCIAL STATEMENTS IN EXCEL (see instructions tab for detailed instructions) Prepar . . Detro Palenc 40 11 14 17. 18 19 10 21 22 23 24 25 26 27 20 BIU- Be Fe una Qil A MAN AB C E G K L M 1 ACCT 251 EXCEL PROJECT 2-PREPARING FINANCIAL STATEMENTS IN EXCEL 2 3 4 Instructions for Completion of Assignment: Part 1: Prepare a trial balance, 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 5 6 7 8 Part 1: Detailed Instructions 9 1. Open Excel Project 1 and ensure all label and amounts are correct 10 Trial Balance: 11 Row 28- cells E and F: 3,252,570 12 Income Statement: 3,252,570 193.910 13. Row 16-cells H and J: Net Income 14 Statement of Retained Earnings: 15 Row 11-cell M Ending balance, December 31, 759,770 16 Balance Sheet 17 Row 19- cells O and P: Total Assets 2,214,080 Total Liabilities Row 14-cells Q and R: 1,352,950 18 Total Stockholders Equity 861,130 19 Row 18-cells Q and R: Total Liablities & Stockholders Equity Row 19- cells Q and R: 2,214,080 20 21 22 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. 23 24 25 3. Copy the data from the Porter Co worksheet into the Pale Ale Inc worksheet 26 At this point, you will have worksheets with two different names but IDENTICAL data 27 8 4. Update the Pale Ale Inc worksheet as follows: 9 a. Update titles to reflect Pale Ale Inc instead of Porter Co 0 b. Change the amounts in Column B for each account to: PaleAle Inc. Scenario #1 Amounts; 1 Service Revenue 61,535,580 2 Utilities Expense 6,406,620 Cash 7,719,360 Pale Ale Inc + Instructions Porter Co 1 HOL ZX 22 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. 23 24 25 3. Copy the data from the Porter Co worksheet into the Pale Ale Inc worksheet 26 At this point, you will have worksheets with two different names but IDENTICAL data 27 28 4. Update the Pale Ale Inc worksheet as follows: 29 a. Update titles to reflect Pale Ale Inc instead of Porter Col 30 b. Change the amounts in Column B for each account to: 31 PaleAle Inc. Scenario #1 Amounts; 32 Service Revenue 61,535,580 33 6,406,620 Utilities Expense Cash 34 7,719,360 35 Accounts receivable 8,160,000 36 Common stock 5,169,360 37 Payroll Expense 35,925,420 38 Buildings 34,680,000 324,360 39 Other liabilities 40 Interest payable 58,650 Land 39,339,360 41 42 Retained earnings 30,175,680 Supplies expense 890,460 8,423,670 Rent expense Dividends 1,316,820 Accounts payable 10,338,720 13,770,000 Equipment 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:: Row 16-cells H and J: Porter Co Pale Ale Inc AD 43 44 45 46 47 48 49 50 51 52 53 54 55 345678 NNN. + 165,881,070 Net Income. 165,881,070 9,889,410 xfx All cells must be the proper width so the content is completely visible. D E 6 H c Confirm each statement updated with the new amounts Trial Balance Row 28-celle E and F 105.861.070 165.001.070 Income Statement Row 16-cells H and J Net Income 9.809.410 Statement of Retained Eamings Row 11-cell M Ending balance, December 31 38,748.270 Balance Sheet Row 19- cells and P 112,918.000 Tobe Assets Total Liables Row 14-cells Q and R 69,000 450 61 Row 18-cells and R Total Stockholders Equity 43917630 62 Row 19-cells Q and R 112,918,000 Total Lisbites & Stockholders Equity 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 68 69 b. Calculate the vertical analysis using Service Revenue with ABSOLUTE Reference (twill 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 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 formated as % with no decimals 77 3. Answer the following questions in the Porter Co worksheet (rows 30-35) a. PaleAle Inc. made 51 times more income than Porter Corporation ($9.9M versus $194k). Does that mean PaleA 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? c. Which company do you think is a better investment? Why? oworkabeet to a new worksheet, name it Pale Ale Inc. (2) and make the following changes: C123 51 52 53 54 55 56 57 58 59 60 ABC P 9 E J All must be the proper width so the content is completely visible. BC D E F G H 1 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 Amount Service Revenue $5,000,000 Accounts Receivable 18,000,000 Common Stock 33,000,000 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 Bato Cell 94 Profit Margin 037 95 Return on Assets 038 56 79 Return on Equity famings per Share 97 540 9.8 Current Ratio 1 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 liabides are current *Pale Ale Inc shares outstanding are 2,500,000 102 103 6. Calculate the ratios below in the cells noted on the Porter Co worksheet 104 Rato Cell 105 Profit Margin 837 838 106 Return on Assets 107 839 Return on Equity Earnings per Share 108 840 841 109 Current Ratio Debt to Equity Ratio 842 110 111 Assume all balance sheet amounts are the average for the year i 112 Assume notes payable is long term, all other liabiles are current 113 *Porter Co shares outstanding are 200.000 85 86 87 88 M N tion in the Parter Co worksheet (row 45): After reviewing the financial statements of Porter Co and Pale Ale Inc think in hatter? What O 0 112 Assume notes payable is long term, all other liabides are current *Porter Co shares outstanding are 200,000 113 114 115 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? 116 117 Review Formatting of All Worksheets 118 The trial balance should be completed in the green section. There are exactly enough lines highlighted in green 119 The multi-step income statement should be completed in the yellow section. There are exactly enough lines highlighted in yellow 120 121 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 122 All numbers, except ratios, must be formatted as numbers with commas and no decimals or dollar signs. Ratios should be formated as a percent DOCX%). EPS should be formatted as dollars (XXX) Alcells must be the proper width so the content is completely visible. 123 124 Cell height and font size may not be changed. 125 All totals, subtotals, and calculations must be populated with a formula and not with hard coded amounts 126 The Ste section of each report or financial statement must have merged cells so that each line is one cell for each report or statement 127 Include line sites and proper and consistent formating 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. 128 129 All totals and subtotals must have total lines where appropriate. 130 The only bolded font that should be used is in the stes and the sides of the two main sections of the balance sheet 131 132 Instructions for Turning In Assignment: 133 Assignment must be tumed in on Canvas in Excel format 134 135 136 137 138 139 140 141 142 143 144 . 4 ACCT 251 EXCEL PROJECT 2-PREPARING FINANCIAL STATEMENTS IN EXCEL (see instructions tab for detailed instructions) Parter Company al Prepare bial balance Prepare an Per 3301/21 Porter Co Pale Ale Inc Instructions A 197 37 19 34 15 1018 Custom ID- 32 Merge & C NA Cabe Take y A23 :x fa ACCT 251 EXCEL PROJECT 2-PREPARING FINANCIAL STATEMENTS IN EXCEL (see instructions tab for detailed instructions) Prepar . . Detro Palenc 40 11 14 17. 18 19 10 21 22 23 24 25 26 27 20 BIU- Be Fe una Qil A MAN