Income and Expenditure Account and Balance Sheet Format in Excel

Non-trading entities maintain their Income and Expenditure accounts to keep track of their incomes and expenses. Therefore, creating an Income and Expenditure Account and Balance Sheet format in Excel is widely common among users.

Typical Income and Expenditure Account and Balance Sheet formats look like the below screenshot.

In this article, we discuss Income and Expenditure Accounts and Balance Sheet format in Excel.

What Is an Income and Expenditure Account?

Non-trading entities prepare an account to find their balance status whether it’s in surplus or deficit; the account is known as an Income and Expenditure Account. This account depicts the final financial status of the entity. Also, the account is similar to the profit and loss account of a business institution.

Key Features of an Income and Expenditure Account

Some key features are worth maintaining while creating an income and Expenditure Account. Go through the below items to have a lucid view. Key features:

(i) Non-trading entities’ Income and Expenditure Accounts are similar to the Profit and Loss Accounts of trading entities.

(ii) Similar to all other financial accounts, Income and Expenditure Accounts are created by following the double-entry system of bookkeeping strictly.

(iii) At the end of a year, users create these types of accounts.

(iv) The balance sheet of this account depicts its type as surplus or deficit balance.

(v) Normally, the surplus or deficit balance from the income and expenditure account gets moved to a Capital Fund Account.

(vi) Not incorporating capital Income or Expenditure accounts is common in this kind of account.

Creating an Income and Expenditure Account and Balance Sheet Format in Excel

Generating an Income and Expenditure Account requires basic Excel operations. However, maintaining the key features or components of an Income and expenditure Account is sensitive as users need to follow the double-entry bookkeeping rules while entering entries. A typical Income and Expenditure and Balance Sheet contains 2 separate columns for each Income and Expenditure element. Both Income and Expenditure elements have their own Amount column with additional or discounted amount entries. Therefore, the Income and Expenditure Account and Balance Sheet look like the below image.

Users can add as many rows as they need to an account’s Balance Sheet. The format of entries may vary from user to user, but the basic Account and Balance Sheet format remains the same.

There are two kinds of Income and Expenditure and Balance Sheet formats depending on the final calculated amount. If incomes exceed expenditures, the balance is referred to as Surplus Balance. On the contrary, it’s referred to as Deficit Balance. Follow the below section to familiarize yourself with both formats.

Surplus Balance Sheet Format

The Surplus Balance occurs when the total income amount is greater than the expenditures. Deducting the total expenditure amount from the total income amount results in positive values. And it’s tradition to place the Surplus Balance figure under the Expenditure column entries. A typical surplus Income and Expenditure and Balance Sheet format looks like the below image.

Deficit Balance Sheet Format

Opposite of the Surplus Balance, a Deficit Balance happens when the expenditure amount crosses the accumulated income. Also, it’s the trend to place the Deficit Balance by deducting all the incomes from the expenditures and placing it under the Incomes column. Have a quick look at the Deficit Balance account format from the following picture.

Example of an Account and Its Balance Sheet

Suppose we have basic expenditures and incomes for an Income-Expenditure Account. Also, we have additional information regarding entries. The additional information indicates different phases of a transaction. The additional information is:

(i) Employees’ Salary Outstanding at the end of the month is $2,000.

(ii) Employees’ Salary Outstanding, in the beginning of the month, is $10,000.

(iii) Advance Office Maintenance payment is $2,000.

(iv) Office Rent Outstanding at the end is $5,000.

(v) Office Rent Outstanding, in the beginning, is $1,500.

(vi) Revenue Outstanding at the end of the month is $2,000

(vii) Revenue Outstanding at the beginning of the month is $15,000.

(viii) Revenue Advance, in the beginning, is $10,000.

(ix) Revenue Advance, in the end, is $3,500.

(x) Fees’ Outstanding, at the end of the month is $5,000.

(xi) Fees’ Outstanding, at the beginning of the month, is $5,000.

(xii) Fees’ Advance, in the beginning, is $1,000.

(xiii) Fees Advance, at the end, is $2,500.

Using this additional information, you can create an Income and Expenditure and Balance Sheet in Excel as depicted below.

And you know additional add amounts are added as well as the lesser amounts get deducted. In this instance, we demonstrate a Surplus Balance Account. However, a Deficit Balance Account is nothing but the total expenditure amount’s supremacy on incomes.

Download Excel Workbook

Practice with the attached workbook. Also, you can use it as a Template.

Income and Expenditure Account and Balance Sheet.xlsx

Conclusion

In this article, we discuss basic Income and Expenditure Account and Balance Sheet format in Excel. Users can download the attached Dataset to further clarify their understanding. Also, they can use the dataset as a Template to do the accounting on their own. Hope this article helps to provide a clear picture regarding the account and balance sheet. Comment, if there any further inquiries arise or have anything to add.

<< Go Back to Balance Sheet | Finance Template | Excel Templates

Get FREE Advanced Excel Exercises with Solutions!

2 Comments

ANOOP ASHOKAN Jan 16, 2023 at 7:56 PM

Sir / Madam
Good day to you
I am looking for an income and expenditure account in Indian format. Would it be possible for you to send it to me?
- Reply
  Rafiul Haq Jan 25, 2023 at 1:16 PM
  
  Thank you for your question. Here is your Indian Format.