Excel Tutorial: How to Make a Balance Sheet
How to Create a Balance Sheet
Introduction to Balance Sheets
- The video introduces the process of creating a balance sheet for any company, specifically using "Elvis Products International" as an example.
- A new spreadsheet will be opened to replicate an existing balance sheet format. The speaker mentions that an income statement has been previously covered and offers a link for those interested.
Setting Up the Spreadsheet
- The title of the balance sheet is established, including the company name and date (December 31, 2016). Formatting tips are provided for a neat appearance.
- Key titles on the left side of the balance sheet are discussed, emphasizing the importance of listing assets in order: current assets versus fixed assets.
Understanding Assets and Liabilities
- Current assets include cash, accounts receivable, and inventory; fixed assets consist of plant and equipment minus accumulated depreciation.
- Liabilities are categorized into current liabilities (due within one year) and long-term liabilities (due after one year), with specific examples given for each category.
Owner's Equity Calculation
- Owner's equity is defined as common stock plus retained earnings, which contributes to total shareholders' equity.
- It’s crucial that total liabilities plus owner's equity equals total assets at the end of the balance sheet.
Data Entry Process
- The speaker begins entering data into the spreadsheet by copying figures from previous years (2015 and 2016).
- Instructions on calculating total current assets using Excel functions are provided. The SUM function is highlighted as essential for adding values efficiently.
Formatting Numbers in Excel
- Guidance on formatting numbers correctly in Excel is shared to ensure clarity in financial reporting. This includes removing decimals and applying comma formats.
Calculating Fixed Assets
- Steps to calculate net fixed assets involve subtracting accumulated depreciation from plant and equipment. This reflects non-cash expenses accurately.
Finalizing Total Assets Calculation
- Emphasis is placed on consistently using formulas throughout the spreadsheet to streamline calculations across different years.
This structured approach provides a comprehensive guide through creating a balance sheet while ensuring clarity in understanding key financial concepts.
How to Create a Balance Sheet
Calculating Total Assets
- The process begins by calculating total assets, which is the sum of current assets and fixed assets using a simple formula:
current assets + fixed assets.
- Current liabilities are then copied from the balance sheet and added to the new balance sheet. A similar formula (
=SUM(...)) is used to calculate total current liabilities for 2016.
Adding Long-Term Debt
- Long-term debt data is entered next, and it is added to current liabilities using a straightforward addition formula.
- To find total shareholders' equity, common stock and retained earnings are summed up.
Finalizing Liabilities and Equity
- The final step involves adding total liabilities to total shareholders' equity. An inconsistency in totals prompts a review of previous entries.
- Upon identifying an error in accounts payable, corrections are made, ensuring that total liabilities match total assets.
Formatting the Balance Sheet
- After calculations are complete, attention turns to formatting the balance sheet for clarity. This includes changing background colors and adding borders between sections.
- Key data points such as total assets, total liabilities, and owner's equity are bolded for emphasis.
Conclusion and Next Steps
- The video concludes with encouragement for viewers to ask questions in the comments section. It also hints at upcoming content on creating a cash flow statement after completing income statements and balance sheets.