Microsoft Excel Formula has powerful tools for performing calculations, analyzing data, and managing information in spreadsheets. We have the various type of formula on the basis of that we make various type of reports, which give us accurate and error less report for any kind of decision making. This is useful tool for drafting Balance Sheet for any organisation, that may be Companies, Partnership Firm, LLP, Trust, Individual or HUF. Anybody can use the formula for drafting any report in 2 minutes.
Every Accounting Professional faces problem in drafting manually in Excel through using “= or + ” formula. Whatever size of the Balance sheet whether small or big there is too much chances of error in case of manually punched data. Drawback of traditional manner drafting the Balance Sheet in Excel is also a puzzled game due to last movement adjustment entries and booking of provisions etc, that’s why sometime we could not show correct figures in the financial statement due to matching principal with double entry system or say error in manually corrections. Some times due to compensating errors we could show the wrong figures in the Balance Sheet, it affect the correctness of financial statements.
The solution for above problem we have the magic tool for drafting Balance sheet in 2 minutes by using formula =VLOOKUP/ HLOOKUP/XLOOKUP, by using these formula there no chance of error and help us to pick the correct figure at once. The use of these formula will give us accurate figures with zero errors. We can use these formula anywhere in the spreadsheet, but use of above formula we have to take care of following conditions to get accurate report:-
1. We need to finalise the Trial Balance first, whatever adjustment required in the Balance Sheet, we need to take effect in Accounting Software and get the Final Trial Balance.
2. Formatting of Balance Sheet or any other report we need to grouping of data as per required format like Assets, Liabilities, Income and Expenses or as per requirement, we can use another sheet to link up all the data.
3. If possible the format of Report should be specifically coded or should be same as per dependent or Master data/Trial Balance.
4. Ensure that there should be no duplicate items of dependent data or master data, if it is required then we should separately classify the data.
5. If possible all the data should be in single Excel File, otherwise you need to remove $$ sign from sheet to get accurate report each time.
6. Give proper column or row number where we are putting the formula with correct sheet.
After above exercise we can make the Balance Sheet in 2 minutes by just copy pasting the Trial Balance on our prepared Link Sheet and the figures will come automatically in Balance Sheet formatted Sheet. This is very useful when we do not have any costly Financial Statement preparer software.
*****
Disclaimer:- The views expressed in this article are strictly personal. The content of this document are solely for informational purpose. It doesn’t constitute professional advice or recommendation. The Author does not accept any liabilities for any loss or damage of any kind arising out of information in this article and for any actions taken in reliance thereon.
Author Bio
Sir thanks for the post can you kindly share excel file to asarepiyush@gmail.com
Dear Piyush Ji, the formats differ for all the clients, my format will not useful for you, either you must learn how to put the formula Vlookup and use it where ever need. It is not only for the balance sheet drafting but also useful for cross checking or comparing any report. You can follow the conditions that I have mentioned, its very easy and useful.
Thanks.