Introduction
A balance sheet is a financial document that gives a summary of the company’s assets, liabilities and shareholders’ equity for a specified period of time. It is used by investors to gauge the financial health of companies. Most companies use financial software and applications to prepare balance sheet. However, it is also possible to prepare balance sheet in Microsoft Excel. Definition
This is a manual for making balance sheet in Microsoft Excel 2007. The Manual contains procedures used to develop a fully functional balance sheet template based on specific financial items.
The balance sheet is automated by the use of excel formulae. Consequently, it automatically calculates the total of each category of assets, liabilities and owner’s equity. Thus, the template reduces the time needed to prepare balance sheet. It also minimizes errors associated with manual making of balance sheet.
Purpose of procedures/instructions
The instructions are intended to be used by students, managers, auditors and accountants who are engaged in the making of balance sheet. The instructions are applicable to MS Excel 2007.
Operation
All the formulae have been programmed into the template. The user needs to edit the amount column by inserting or deleting values. The corresponding amount for items beginning with less should be negative. Warnings
The total cells should not be edited. These cells have already been automated. Editing these cells will result into an error or render the application nonfunctional.
Major Steps
- Categorize the items under current assets, fixed assets, other assets, current liabilities, long-term liabilities and owners’ equity
- Current assets
- Cash
- Investments
- Inventories
- Account receivables
- Prepaid expenses
- Other
- Fixed assets
- Property and equipment
- Leasehold improvement
- Accumulated depreciation
- Other assets
- Good will
- Current liabilities
- Account payable
- Accrued wages
- Accrued compensation
- Income tax payable
- Unearned revenue
- Other
- Long-term liabilities
- Mortgage payable
- Owners’ equity
- Investment capital
- Accumulated retained earnings
- Key in the items in the excel spreadsheet as illustrated below.
- Create five tables for each category; current assets, fixed assets, current liabilities, long-term liabilities and owners’ equity
Steps
- Click the Insert tab
- Click on Table
- Make sure my table has headers is checked
- Click OK
The table appears as follows;
- Rename current assets’ table to CurrentAssets, fixed assets’ table to FixedAssets, other assets’ table to OtherAssets, current liabilities’ table to CurrentLiabilities, long-term liabilities’ table to LongTermLiabilities and owner’s equity’s table to OwnersEquity
Steps
- Click Design tab
- Edit the name of the table on the property group
Steps
- Delete the row that is meant for total in each table created in step A
- Click Design tab
- Check Total Row in the Table Styles and Options Group
A row will be created named Total
- Rename the Total row created in step 4 to the appropriate category total
- Click on the cell containing the total for each category under 2007 column then click on the small icon that appears on the right side of the cell. Select Sum from the drop down functions.
- Repeat step 6 for all the rows containing the total under 2007 and 2008 column
- Type the formula for getting total assets by clicking appropriate cell under column 2007 and type equal mark (=) on the cell. Then click on total current asset cell under 2007 and press +. Click on total fixed assets cell and press + and finally click on other assets cell.
- Type the formula for getting the total owner’s equity by clicking on appropriate cell under column 2007 and type equal mark (=) on the cell. Then click on the total current liabilities cell under 2007 and press +. Click on the total long term liabilities and press + and finally click on total owner’s equity.
- Procedure 8 and 9 should be repeated for appropriate cells under 2008.
Troubleshooting
Perform the following procedures if total formula is not working
- Click on the appropriate total cell
- Check if there is any writing on the formula bar
- Delete the writings on the formula bar if they exist
- Type equal sign in the cell and click on appropriate cells that make up the total
Remember to press + after clicking appropriate cell
Review of major steps
- The first step involves grouping the items into five categories namely current assets, fixed assets, other assets, current liabilities, long-term liabilities and owners’ equity
- The second step involves keying the data into MS Excel according to the categories identified in step A
- The third step involves creating table for each category in Excel
- The fourth step involves renaming the tables created in step C
- The fifth step involves inserting total row and total formulae.
Conclusion
Although is not very powerful software for making balance sheets, it is helpful to students and companies that do not have the capacity to acquire and use financial software. Every attempt has been made to make the instructions as clear as possible so that student can follow the procedures and develop their own customized template.
