How to Create Common Size Financial Statements on MS Excel
By Warren Davies
A common size financial statement is a business document, typically a balance sheet or an income statement, that displays the financial figures of your business as a percentage of a constant value, such as your net sales or total assets. This standardizes your books, allowing you to compare your business with competitors regardless of the overall size of the businesses. You can create these in Excel using a simple formula.
Launch Excel. Type the date for which you're calculating the accounts into cell “B1,” and enter “% Terms” into cell “C1.” In cell “A2,” enter “Net Sales” if you're making a common size income statement, or “Total Assets” if you're making a common size balance sheet.
Enter the liabilities, costs or whatever else you wish to compare, starting from cell “A3” and working downwards. For example, in an income statement you might include aggregate figures for selling expenses, operating expenses and taxes, or you might break these categories down further.
Type the dollar figures for all the items you entered in column “A” into column “B,” just to the right of each item.
Enter the following formula into cell “C2”:
Select cell “C2” and then drag the small black square in the bottom right of the cell to the bottom of your list. This copies the formula down the column, leaving a series of decimal values.
Select all the cells you just copied from “C2” downwards, click the “Home” tab, and then click the “%” button in the “Number” menu. This converts all the decimals to percentages.
The dollar signs in the formula tell Excel to always look to that column and row, regardless of where the cell is moved or copied to. Therefore, to move the table to somewhere else on the spreadsheet, you have to change the formula so that instead of “$B$2,” it references the cell directly to the left of it, and then copy the formula down the list again.
Information in this article applies to Excel 2007 and 2010. It may vary slightly or significantly with other versions or products.
Warren Davies has been writing since 2007, focusing on bespoke projects for online clients such as PsyT and The Institute of Coaching. This has been alongside work in research, web design and blogging. A Linux user and gamer, warren trains in martial arts as a hobby. He has a Bachelor of Science and Master of Science in psychology, and further qualifications in statistics and business studies.