Creating Common Size Balance Sheets in Excel is a practical method to analyze and compare the financial structure of a company over different periods or against competitors. This approach standardizes each line item on the balance sheet as a percentage of total assets or total liabilities plus equity, making it easier to identify trends, assess financial health, and make cross-company comparisons.
Understanding Common Size Balance Sheets
Before diving into the steps, let's define what a common size balance sheet is:
- Common size balance sheet: A type of financial statement where each item is listed as a percentage of total assets or total liabilities plus equity, allowing for straightforward comparison over time or among companies.
- This method helps in understanding the structural changes in the company's financial statements rather than focusing on the absolute numbers.
Why Use Common Size Balance Sheets?
- It provides a percentage basis to analyze the financial statement, enabling easier identification of shifts in company financial strategies.
- It aids in comparative analysis when looking at different companies of varying sizes within the same industry.
Steps to Create a Common Size Balance Sheet in Excel
Step 1: Collect and Prepare Your Data
- Ensure you have a traditional balance sheet from the period you want to analyze.
- Transfer or import the data into an Excel spreadsheet. Make sure to label your columns with the balance sheet items, such as ‘Cash’, ‘Accounts Receivable’, ‘Inventory’, ‘Total Assets’, ‘Liabilities’, and ‘Equity’.
Step 2: Identify Your Base Figures
Decide if you’ll base your common size on:
- Total Assets (for the asset side)
- Total Liabilities and Equity (for the liability and equity side)
Step 3: Set Up Your Spreadsheet
| Balance Sheet Item | Actual Value | Percentage |
|---|---|---|
| Cash | =C2/B2 | |
| Accounts Receivable | =C3/B3 | |
| Inventory | =C4/B4 | |
| Total Assets | 100% | |
| Liabilities | =C5/B5 | |
| Equity | =C6/B6 |
💡 Note: In Excel, the formulas in the Percentage column will automatically calculate the percentages based on the actual values divided by the total assets or liabilities and equity.
Step 4: Calculate Percentages
- For each line item under Assets, divide the item’s value by the Total Assets value and format the result as a percentage.
- Repeat the process for Liabilities and Equity, using Total Liabilities and Equity as the denominator.
💡 Note: In Excel, you can use the =[Item]/[Base] formula, where [Item] is the cell with the line item’s value and [Base] is the cell with the Total Assets or Total Liabilities and Equity value.
Step 5: Analyze Your Results
With your common size balance sheet now in place, analyze:
- Changes in the composition of assets or liabilities over time.
- Compare your company’s financial structure against industry benchmarks or competitors.
Step 6: Present and Interpret
Use visual aids like charts or graphs in Excel:
- Create pie charts to show the composition of assets or liabilities as percentages.
- Draw line or bar graphs to illustrate changes over time or differences between companies.
In wrapping up, the process of creating Common Size Balance Sheets in Excel is a valuable tool for financial analysis. By transforming raw data into a standardized format, you can easily spot trends, structural shifts, and compare financial health across companies or time periods. This not only simplifies complex financial data but also enriches decision-making processes. The steps outlined here are straightforward, making it accessible even for those new to Excel or financial analysis.
Why is it important to use a common size balance sheet?
+
It standardizes financial data, making it easier to compare financial structures over time or across different companies, especially when they vary in size.
Can common size balance sheets be used for income statements or cash flows?
+
Yes, you can apply the same principle to analyze income statements (as percentages of total revenue) or cash flow statements (as percentages of net cash flow).
How often should I update my common size balance sheet?
+
It’s recommended to update them with each financial reporting period, typically quarterly or annually, to keep your analysis current.
