Ever came across a situation where you are given/giving an invoice on which you have calculated say 10.3% tax, and then want to have a discount of some amount?
Let us see this with an example.

Invoice Amount = Rs. 1000
Tax = 10.3%
Total = Rs. 1103
Now you want a discount of Rs. 53, so ideally the Grand Total will become Rs. 1050.
Discount

Discount

But the question is, how much should now be the invoice amount, so as to amount to exactly Rs. 1050 as the Grand Total after adding 10.3% tax.
Little time consuming isn’t it?

Invoice Amount?

Invoice Amount?

Let’s save our time by using a feature in Excel called Goal Seek.

Suppose say the Invoice amount is in cell B1,
the Tax % is in cell B2,
the Grand Total formula is in cell B3.

  1. Go to the Data tab on the ribbon, Under the Data Tools section, click on What-If Analysis, and then on Goal Seek.
  2. In Set Cell, select cell B3.
  3. In To value, type in 1050.
  4. In By changing cell, select cell B1.
  5. Press OK.
Goal Seek

Goal Seek

A dialogue box will open with a summary of what calculations Excel has done, press OK again.

Final Invoice Amount

Final Invoice Amount

You will see Rs. 951.9492 in cell B1, which is your required amount to be then put as Invoice amount.

 

See these 2 videos to understand it even better.

 


Enjoy Seeking your Goals 🙂