1. Continuing simple models with balance sheets
Now that you have created income statement models, we can continue with balance statement models.
2. What is a balance statement?
Balance statements are the second part to modeling finances, and we will use income and balance statements to build our final model in the next section. These sheets contain three important parts: assets, liabilities, and equity. Under assets, you might see accounts receivable, which is money owed to the company, and other items like inventory and equipment. Under liabilities, you might include money owed to others, such as accounts payable, debts, and other income tax to pay later. Finally, equity includes money paid into shareholders and retained earnings.
3. Example balance statement
Here is an example balance statement. You can format this statement in a similar way to an income statement. In these models, the assets should be equal to equity and liabilities added together, so we have a way to check our finalized numbers after adding formulas.
4. Completing the model
To finalize our balance statement, you will complete the same steps as the income statement. First, format the cells into financial format by highlighting the cells, clicking format, number, and then financial. Next, you will create section subtotals by typing equals sum open parentheses, selecting the cells in that section, and close parentheses. The total equity and liabilities section is a sum of the subtotals for equity and liability. This sum should be equal to the sum we find in the total assets line to check the model.
5. Common size statements
To extend the two models you've learned, you can use these completed sheets to create a common size statement. Common size statements are converted to percentages to help compare and see where money is allocated. On income statements, you would convert each value to a percent of sales, while balance statements convert values into percents of assets or the combined total for equity and liabilities.
6. Create common size models
The formulas for the common size calculations are shown on the left, and here on the right, you can see how to enter these values. In the assets section for cash and securities in column F, you will type equals, click on the cell for 2018 in column E, and then divide by the total assets for 2018 in E eleven. We want to copy this formula to all the other asset rows, so we should use an absolute reference to the total assets in E eleven. That means the cell reference will not change when we copy it over. To do that, use the dollar signs before the E and the eleven to ensure the cell does not change when you paste the formula. You would repeat this process for the equity and liability section, using the total of both to convert to a percentage.
7. Model a balance statement!
Your turn to practice creating balance statements, including a common size version of assets and equity.