Creating a spreadsheet to help work out estimates for domestic (non-VAT) customers when you are VAT registered?

AJGriffin100

New Member
Joined
Mar 27, 2021
Messages
5
Office Version
  1. 365
Platform
  1. Windows
Hi

Potentially our small family owned business will have to go VAT registered and whilst this will make no difference to what we charge when working for other VAT customers it is going to impact working for domestic customers as we will need to charge VAT for domestic customers too. Rather than charging a straight 20% on top of what we usually charge I want to try and build a spreadsheet that will enable me to manipulate the data so I can see the best way of charging the customer so that my prices might not have to increase by 20% as I will be able re-coup back some VAT money on the materials and therefore didn't need to increase my whole price by 20% as I will get deductions due to being VAT registered - if that makes sense? We did some quick calculations with an accountant (although this was just an introductory call and she didn't share the spreadsheet) and she was showing something along the lines of Total cost of materials from a supplier then broken down in Gross and VAT and then total amount of materials charged to the customer (Gross and VAT) and then the cost of labour including VAT.

We often get a discount from the supplier so might charge eg £4000 kitchen supply inc VAT + £1000 labour including VAT to the customer. The original cost of the kitchen might have been £3000 including VAT so we can claim back from VAT from the original kitchen. There was then a some formulae she used to show we would have to increase our total price by 5% rather than 20% overall due to the money claimed back from VAT on materials.

I wanted to try and replicate this spreadsheet - if the above makes any sense at all!
 

Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,152,227
Messages
5,768,927
Members
425,506
Latest member
Coder33

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top