Spreadsheets Tutorial : Continuing simple models with balance sheets

Key Takeaways

now that you have created income statement models we can continue with balance statement models Palace 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 here is an example of 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 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 parenthesis selecting the cells in that section and close parentheses the total equity and liabilities section is the 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 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 percent of assets or the combined total for equity and liabilities 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 and column F you will type equals click on the cell for 2018 and column E and then divide by the total assets for 2018 in E 11 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 11 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 11 to ensure that cell does not change when you paste the formula you would repeat this process for the equity and liabilities section using the total of both to convert to a percentage your turn to practice creating balance statements including a common size version of assets