Creating an invoice with Google Sheets is very simple, this will calculate tax, shipping and handling plus extra fees like PayPal and shopping fees for personal shoppers
After creating a new Google Sheet create a header on row 1 with the following Description Qty Unit Price Tax Total Price
You can now fill in some test data for Description, Qty, and Unit Price. We will use formulas to automatically calculate the Tax and Total Price.
Note: For columns D, E and F you should format them as “Currency” to automatically add the money symbol and decimal point.
Now we will be getting into our first formula, taxes!
Where I live the sales tax is 7% and we know if we multiply a number by 0.7 we get 7% of that number, but we need to also factor in the quantity as well. The formula would look like
What this does is it will multiply 3×7.99×0.7 giving us 1.68
Now we can get the total price by multiplying the unit price by the quantity and adding the tax, we can do this all in one formula by remembering the Order of Operations rule or PEMDAS (Parentheses, Exponents, Multiplication/Division, Addition/Subtraction).
You can add a few more items if you’d like but now here’s where we will tie into PayPal and shoppers fee’s and the likes.
Note: make sure if you are going to copy these formulas down to other rows you have a quantity of 0 since because if you do not it will just add 0.7 (or whatever your tax is) to the total price as seen here
Lets add all the total prices up to get a subtotal
For shoppers fee’s lets say you charge a flat $10 for anything less then $50 but 20% if the order is more then $50. How would you calculate that? With an IF statement that’s how.
=IF(F8 > 50, F8*0.2, 0)
This says if F8 (Subtotal) is greater then 50 then multiply 0.2 (add 20%), however if it’s not then put a 0 in the cell.
If the total is less then $50 however we’d want to change a flat rate of $10, so the formula would be
=IF(F8 < 50, 10, 0)
PayPal charges a flat $0.30 and 2.9% of the total as their processing fee, we can include this into our handling fee of $3
This says add the Subtotal, both Shoppers Fee’s (over $50 and under $50 since one of them will be 0), then multiply that by 0.029 (2.9%) and add $0.30 (both of these are PayPal’s fees) then add out $3 handling fee
Shipping is the next thing we will add. Since in the US we have the USPS and they have flat rate boxes, if it fits it ships, we can standardize the shipping rates. A small flat rate envelope is $8.10 so we can just add the number, no need for formulas here.
The last thing would be is the overall total, this includes everything we just did, Subtotal, Shoppers fees, Handling and PayPal fees and Shipping
And that’s it! We’ve created a little invoice that now you can print out to PDF or save for your records.