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.

Format-Income and Expenditure Account and Balance Sheet Format in Excel

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


Download Excel Workbook

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


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.

Format

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.

Surplus Balance-Income and Expenditure Account and Balance Sheet Format in Excel


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.

Deficit Balance


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 in the beginning of the month is $15,000.

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

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

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

(xi) Fees’ Outstanding, in 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.

Sample Account and Balance-Income and Expenditure Account and Balance Sheet Format

And you know additional add amounts are added as well as the lessed 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.

Read More: Balance Sheet Format of a Company in Excel (Download Free Template)


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. You can explore our Exceldemy site to get more Excel for finance related articles.


Related Articles

Maruf Islam

Maruf Islam

My self, Maruf Islam, an engineer and Excel & VBA Content developer on Exceldemy. I enjoy solving problems, finding workable solutions, and most of the part I really like to take on challenges. On Exceldemy I write articles discussing various way outs of Microsoft Excel's stuck ons.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo