90.2K

Verified Solution

Link Copied!

ACCT 251 EXCEL PROJECT 2 - PREPARING FINANCIAL STATEMENTS IN EXCEL 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 Part 1: Detailed Instructions 1. Open Excel Project 1 and ensure all label and amounts are correct Revision 2/24/21: Trial Balance: Row 28- cells E and F: 3,252,570 3,252,570 Income Statement: Part 1 #1 and #2 Row 16- cells H and J: Net Income 193,910 Statement of Retained Earnings: Row 11- cell M Ending balance, December 31 759,770 THESE STEPS HAVE Balance Sheet: Row 19- cells O and P: Total Assets 2,214,080 BEEN COMPLETED 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 2,214,080 FOR YOU 2. Copy the financial data from the Porter Co worksheet from Excel Project 1 to the Porter Co worksheet in Excel Project 2 (rows 4-28 in Project 1 to rows 4-28 in Project 2) This copies all of the first assignment into the second assignment. 3. Copy all the data from the Porter Co worksheet into the Pale Ale Inc worksheet At this point, you will have worksheets with two different named tabs but the data in them will be IDENTICAL 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 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 110,587,380 6,592,940 c. Confirm each statement updated with the new amounts Trial Balance: Row 28- cells E and F: 110,587,380 Income Statement: Row 16- cells H and J: Net Income Statement of Retained Earnings: Row 11- cell M Ending balance, December 31 Balance Sheet: Row 19- cells O 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 Liablities & Stockholders Equity 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 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 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. PaleAle Inc. has 34% more debt than Porter Co ($46M versus $1.4M). Does this mean that Pale Ale 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 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 Profit Margin B37 Return on Assets B38 Return on Equity B39 Earnings per Share B40 Current Ratio B41 Debt to Equity Ratio B42 Assume all balance sheet amounts are the average for the year * Assume notes payable is long term, all other liabiities 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 Cell Profit Margin B37 Return on Assets B38 Return on Equity B39 Earnings per Share B40 Current Ratio B41 Debt to Equity Ratio B42 * Assume all balance sheet amounts are the average for the year Assume notes payable is long term, all other liabiities 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. 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. ACCT 251 EXCEL PROJECT 2 - PREPARING FINANCIAL STATEMENTS IN EXCEL (see instructions tab for detailed instructions) Porter Company financial data: Prepare a balance sheet below: Prepare an income statement below: Porter Company 12/31/2021 Prepare a trial balance below: Porter Company Trial Balance Prepare a retained earnings statement below: Porter Company Statement of Stockholders' Equity Service Revenue 1,206,580 Income statement Utilities Expense 125,620 As of December 31, 2021 For the period ended December 31, 2021 Cash Debit Credit Revenue 151,360 160,000 Assets Cash Accounts receivable Cash 151,360 Service Revenue For the period ended December 31, 2021 Beginning Balance 591,680 Add: net income for the period 193,910 Less: dividends (25,820) Ending balance, December 31 759,770 1,206,580 Common stock 101,360 Accounts receivable 160,000 704,420 181,360 Payroll Expense Buildings Supplies Equipment 125,620 704,420 680,000 Expenses Utilities Expense Payroll Expense Supplies expense Rent expense Total Expenses 270,000 202,720 141,360 1,150 1,001,360 6,360 Accounts receivable Supplies Equipment Buildings Land Porter Company Balance Sheet December 31, 2021 Liabilities 151,360 Accounts payable 160,000 Salaries payable 181,360 Interest payable 270,000 Notes payable 680,000 Other liabilities 771,360 Total Liabilities Stockholders Equity. Common stock Retained earnings Total Stockholders Equity 2,214,080 Total Liabilities & Stockholders Equity Other liabilities 6,360 680,000 17,460 Buildings Land 771,360 165,170 1,352,950 Interest payable Land 1,150 771,360 202,720 1,012,670 Accounts payable Salaries payable 591,680 Net Income 193,910 Retained earnings Supplies expense Rent expense 17,460 165,170 Interest payable Notes payable 141,360 1,150 1,001,360 101,360 759,770 861,130 2.214,080 Dividends 25,820 Other liabilities 6,360 Total Assets 202,720 Common stock 101,360 591,680 270,000 Retained earnings Accounts payable Equipment Supplies Salaries payable Notes payable 181,360 Dividends 25,820 1,206,580 141,360 1,001,360 Service Revenue Utilities Expense 125,620 704,420 Payroll Expense Supplies expense Rent expense 17,460 165,170 3,252,570 3,252,570 a. PaleAle Inc. made 34 times more income than Porter Corporation ($6.6M versus $194k). Does that mean Pale Ale is more profitable and a better investment than Porter Co? Why or why not? [answer here) b. PaleAle 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? [answer here) c. Which company do you think is a better investment? Why? [answer here) Profit Margin Return on Assets Return on Equity Earnings per Share Current Ratio Debt to Equity Ratio 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? [answer here} ACCT 251 EXCEL PROJECT 2 - PREPARING FINANCIAL STATEMENTS IN EXCEL (see instructions tab for detailed instructions) Pale Ale Inc Company financial data: Prepare a trial balance below: Prepare an income statement below: Prepare a retained earnings statement below: Prepare a balance sheet below: 12/31/2021 Part 2: 3 Questions: a. Pale Ale Inc. made 34 times more income than Porter Corporation ($6.6M versus $194k). Does that mean Pale Ale is more profitable and a better investment than Porter Co? Why or why not? [answer here) b. Pale Ale Inc. has 34% more debt than Porter Co ($46M versus $1.4M). Does this mean that Pale Ale relies on debt to finance its company than Porter Co? Why or why not? [answer here) c. Which company do you think is a better investment? Why? [answer here) Profit Margin Return on Assets Return on Equity Earnings per Share Current Ratio Debt to Equity Ratio 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? [answer here) ACCT 251 EXCEL PROJECT 2 - PREPARING FINANCIAL STATEMENTS IN EXCEL 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 Part 1: Detailed Instructions 1. Open Excel Project 1 and ensure all label and amounts are correct Revision 2/24/21: Trial Balance: Row 28- cells E and F: 3,252,570 3,252,570 Income Statement: Part 1 #1 and #2 Row 16- cells H and J: Net Income 193,910 Statement of Retained Earnings: Row 11- cell M Ending balance, December 31 759,770 THESE STEPS HAVE Balance Sheet: Row 19- cells O and P: Total Assets 2,214,080 BEEN COMPLETED 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 2,214,080 FOR YOU 2. Copy the financial data from the Porter Co worksheet from Excel Project 1 to the Porter Co worksheet in Excel Project 2 (rows 4-28 in Project 1 to rows 4-28 in Project 2) This copies all of the first assignment into the second assignment. 3. Copy all the data from the Porter Co worksheet into the Pale Ale Inc worksheet At this point, you will have worksheets with two different named tabs but the data in them will be IDENTICAL 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 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 110,587,380 6,592,940 c. Confirm each statement updated with the new amounts Trial Balance: Row 28- cells E and F: 110,587,380 Income Statement: Row 16- cells H and J: Net Income Statement of Retained Earnings: Row 11- cell M Ending balance, December 31 Balance Sheet: Row 19- cells O 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 Liablities & Stockholders Equity 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 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 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. PaleAle Inc. has 34% more debt than Porter Co ($46M versus $1.4M). Does this mean that Pale Ale 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 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 Profit Margin B37 Return on Assets B38 Return on Equity B39 Earnings per Share B40 Current Ratio B41 Debt to Equity Ratio B42 Assume all balance sheet amounts are the average for the year * Assume notes payable is long term, all other liabiities 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 Cell Profit Margin B37 Return on Assets B38 Return on Equity B39 Earnings per Share B40 Current Ratio B41 Debt to Equity Ratio B42 * Assume all balance sheet amounts are the average for the year Assume notes payable is long term, all other liabiities 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. 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. ACCT 251 EXCEL PROJECT 2 - PREPARING FINANCIAL STATEMENTS IN EXCEL (see instructions tab for detailed instructions) Porter Company financial data: Prepare a balance sheet below: Prepare an income statement below: Porter Company 12/31/2021 Prepare a trial balance below: Porter Company Trial Balance Prepare a retained earnings statement below: Porter Company Statement of Stockholders' Equity Service Revenue 1,206,580 Income statement Utilities Expense 125,620 As of December 31, 2021 For the period ended December 31, 2021 Cash Debit Credit Revenue 151,360 160,000 Assets Cash Accounts receivable Cash 151,360 Service Revenue For the period ended December 31, 2021 Beginning Balance 591,680 Add: net income for the period 193,910 Less: dividends (25,820) Ending balance, December 31 759,770 1,206,580 Common stock 101,360 Accounts receivable 160,000 704,420 181,360 Payroll Expense Buildings Supplies Equipment 125,620 704,420 680,000 Expenses Utilities Expense Payroll Expense Supplies expense Rent expense Total Expenses 270,000 202,720 141,360 1,150 1,001,360 6,360 Accounts receivable Supplies Equipment Buildings Land Porter Company Balance Sheet December 31, 2021 Liabilities 151,360 Accounts payable 160,000 Salaries payable 181,360 Interest payable 270,000 Notes payable 680,000 Other liabilities 771,360 Total Liabilities Stockholders Equity. Common stock Retained earnings Total Stockholders Equity 2,214,080 Total Liabilities & Stockholders Equity Other liabilities 6,360 680,000 17,460 Buildings Land 771,360 165,170 1,352,950 Interest payable Land 1,150 771,360 202,720 1,012,670 Accounts payable Salaries payable 591,680 Net Income 193,910 Retained earnings Supplies expense Rent expense 17,460 165,170 Interest payable Notes payable 141,360 1,150 1,001,360 101,360 759,770 861,130 2.214,080 Dividends 25,820 Other liabilities 6,360 Total Assets 202,720 Common stock 101,360 591,680 270,000 Retained earnings Accounts payable Equipment Supplies Salaries payable Notes payable 181,360 Dividends 25,820 1,206,580 141,360 1,001,360 Service Revenue Utilities Expense 125,620 704,420 Payroll Expense Supplies expense Rent expense 17,460 165,170 3,252,570 3,252,570 a. PaleAle Inc. made 34 times more income than Porter Corporation ($6.6M versus $194k). Does that mean Pale Ale is more profitable and a better investment than Porter Co? Why or why not? [answer here) b. PaleAle 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? [answer here) c. Which company do you think is a better investment? Why? [answer here) Profit Margin Return on Assets Return on Equity Earnings per Share Current Ratio Debt to Equity Ratio 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? [answer here} ACCT 251 EXCEL PROJECT 2 - PREPARING FINANCIAL STATEMENTS IN EXCEL (see instructions tab for detailed instructions) Pale Ale Inc Company financial data: Prepare a trial balance below: Prepare an income statement below: Prepare a retained earnings statement below: Prepare a balance sheet below: 12/31/2021 Part 2: 3 Questions: a. Pale Ale Inc. made 34 times more income than Porter Corporation ($6.6M versus $194k). Does that mean Pale Ale is more profitable and a better investment than Porter Co? Why or why not? [answer here) b. Pale Ale Inc. has 34% more debt than Porter Co ($46M versus $1.4M). Does this mean that Pale Ale relies on debt to finance its company than Porter Co? Why or why not? [answer here) c. Which company do you think is a better investment? Why? [answer here) Profit Margin Return on Assets Return on Equity Earnings per Share Current Ratio Debt to Equity Ratio 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? [answer here)

Answer & Explanation Solved by verified expert

See Answer