MONEY IS
IMPORTANT IN
THIS WORLD

Finances24.pro

How to Create an Accounting Spreadsheet


by Sue Smith

1. Open Microsoft Excel 2010 and start a new workbook by choosing "File" and "New." In the "Available Templates" section, choose "Blank Workbook" to begin.

2. Decide how many worksheets you want to use for your accounts. By default a new workbook contains three, with each one accessible from the tabs at the bottom of the window. You may wish to use one for each table you plan on using to record your finances. Depending on the complexity of your accounts, one sheet for expenses and one for income may be sufficient, together with one in which you calculate the balance together with information such as tax owed.

3. Create headings for each data item you plan to record in your worksheets. In the top row for each table you require, add a header in each column to represent the data item you want to list under it. For example, for income you may have headings for the date, product or service for which the income was received, customer name (including any account or other reference numbers you use) and optionally notes for recording relevant information.

4. Insert some income and expenses data. The easiest way to ensure you have all of the column headings you need is to enter some of the data you will be including in your accounts. This way you can edit the columns you have to accommodate your data. Format the columns in your worksheets by selecting each column in turn, choosing the launcher icon next to the Number group in the Home tab. From here you can apply a category to the

column you have highlighted, such as currency, text or date. Once you apply the formatting, any data in that column will be formatted accordingly, including data you enter later.

5. Within your worksheets, you can now apply calculations to work out the totals for your income and expenses. In Excel, you can set up a cell to calculate the sum of the values in all cells above it by clicking and dragging to select (a) all of the cells containing the values to be added and (b) an empty cell beneath them; then click the AutoSum button in the Editing group in the Home tab. Alternatively you can enter the sum formula into the cell manually, using the structure "=SUM" followed by the group of cells to be added in parentheses. For example: =SUM(A2:A10) This will sum all of the values between cells A2 and A10.

6. Calculate your accounting balances. If you have tables spread across different worksheets, use a new worksheet for your balance. In your balance sheet, you can calculate the balance for your business based on the data in your income and expenses tables, optionally including taxation calculations as well. For example, if you select a cell in the balance sheet and enter "=" then select your income sheet and click the total income cell, then enter "-" and select the expenses sheet, clicking the total for expenses, then press Enter, your total balance will appear in the new sheet.

  • You can use percentage calculations in Excel for taxation arithmetic.
  • Accounting spreadsheets are only reliable if every data item within them is accurate.
  • Microsoft Office: Add Numbers


Category: Small business

Similar articles: