Excel tax question

iMax

Hi guys,

I'm trying to put together a purchases book in excel & was wondering if it's possible for me to just enter the gross amount in one cell & have excel automatically populate the net & VAT cells automatically.

I think I'd need to be able to select a VAT rate (13.5% & 21%) as part of the item purchase description e.g. -> Supplier | Description | VAT Rate | Gross Price


Is that possible ?

barneystinson

iMax wrote: »

Hi guys,

I'm trying to put together a purchases book in excel & was wondering if it's possible for me to just enter the gross amount in one cell & have excel automatically populate the net & VAT cells automatically.

I think I'd need to be able to select a VAT rate (13.5% & 21%) as part of the item purchase description e.g. -> Supplier | Description | VAT Rate | Gross Price


Is that possible ?

Yes, you enter the details as you've described, and the next two columns headings will be Net amount, and VAT amount.

You enter a formula into the "Net amount" column =Gross Price / (1+VAT rate), and you enter a formula into "VAT amount", either =Gross Price - Net Price, or =Net Price x VAT Rate.

Strictly speaking though, a proper VAT invoice would show the net, VAT and gross amounts, so you shouldn't really need to do it that way...

iMax

Thanks Barney.

Is it possible to have a dropdown choice of vat rate & then autocalculate?

namenotavailablE

Yes- this is possible.

For the drop-down choice of VAT rates, you would use 'Data validation' - in Excel 2007, choose 'Data', 'Data Validation', set the 'Allow' field to 'List' and then type a comma separated list of VAT rates in the 'Source' field, pressing OK when done.
The auto-calculate part is done as per Barney's response.