Tips of Accounting in Excel

Excel or MS. Excel is very useful tool for accounting. So, every accountant should learn excel deeply. In YouTube, there are lots of channel for teaching ms excel,  from where you can learn many things which may be useful for your accounting work.

Today, in this content you will learn some good tips of accounting in excel.

1st Tip : Accounting Data Analysis with  Pivot Tables 

In ms excel, there is option of pivot tables in above data menu. This is very good option. By using this, you can analyze all your accounting data. Suppose, you have data of different years sales in different branches. With pivot table, you can know the amount of sale according to our requirement. Suppose, we are interested to know the sale of 2005 relating to Chandigarh branch. This table will give us the answer. Like this, you can know specific amount of sale in different branches.


Following video tutorial will be helpful to use it.




2nd Tip : Use of VLookup Function For Getting Accounting Information

Vlookup function is very good function in ms excel. With this, you can get or search many accounting information. I try to teach it with simple example. Suppose, my business is to sell Accounting DVD in different countries. I have to get money after some time. But due to changing in foreign exchange rate, it affects my net profit. So, I will use Vlookup for converting my all sale in their currency. Now, just changing the rate of foreign currency, I will succeed to know sale in foreign currency. After this, I can analyze its effect on my net profit. My following video tutorial will be helpful to you to learn it deeply.




3nd Tip : Calculation of NPV

NPV is useful in capital budgeting in management accounting. We can easily calculate NPV in excel, see a video at here which explain this calculation in simple words..

4th Tip : Calculation of Commission through Dollar Formula

In accounting, we have to calculate different sales man's commission with different commission rates on sales.  for calculating of this, we can use simple dollar formula.

following is dollar formula for calculating commission

= Select the cell of total Sales * select the rate

{Important to Note : sign of dollar will be written before selecting rate and between row no. For example selected rate cell is B9, we will write $ before B9 and after B}

5th Tip : Calculate an Future Value of Your Investment 

From accounting report, we can find the amount of investment which may be monthly basis, period of investment and interest rates. On these basis, we can calculate future value of your investment. For example, you invest $ 1000 for 10 years with 5% rate of invest. What will be the future value of your $ 1000 after 10 years.

Following formula will be used

= - FV(rate, time, investment amount,,1)

or  = - FV (0.05,10,1000,,1)

Related : Accounting Tips