Banking

Analyze performance through common-sized income statements with Excel

Perform percentage and comparison analyses in Excel by creating a common-sized income statement. Find out how in this tip by Mary Ann Richardson.

How do your company's profit margins compare with others in your industry? What percentage of revenue goes to salaries? What percentage of revenue goes to the cost of goods sold? You can perform this analysis in Excel by converting your income statement to a common-sized income statement that divides each item in the statement by total revenues. Follow these steps:

  1. Activate the Income Statement sheet and select the entire range containing the income statement.
  2. Click Copy.
  3. Right-click the Sheet tab, click Insert, and then click OK.
  4. Go to Edit | Paste Special.
  5. Click the Values check box under Paste and then click OK.
  6. Click the Income Statement sheet, press [Esc], and then click the cell containing the Total Revenues.
  7. Click Copy and then click the new Sheet1.
  8. Select the entire income statement range in the new sheet.
  9. Go to Edit | Paste Special.
  10. Under Operation, click Divide and click OK.
  11. With the income statement still selected, click the Percent Style button in the formatting toolbar.

Miss a tip?

Check out the Microsoft Excel archive, and catch up on our most recent Excel tips.

Help users increase productivity by automatically signing up for TechRepublic's free Microsoft Office Suite newsletter, featuring Word, Excel, and Access tips, delivered each Wednesday.

4 comments
cjsheth
cjsheth

click the new Sheet1. The new sheet is sheet2. I am not getting the changes made to the sheet instead getting #Ref# error.

bgr1212
bgr1212

What income statement is she talking about in tip?

icad
icad

Dear editors, you are kind to teach us those tips, but all those tips are useless without a sample file to download! It takes to much time to figure how to use them. Thanks and keep doing good.