hi this is ivan with simple sheets in this video we're going to do a step-by-step tutorial of our
customer sales transaction template this is one of over a hundred pre-built and smart templates that
we offer click the link in the description and try five free templates before you buy
for any business being able to track your products sales and even customers can give you insights
which will always lead to a competitive advantage with this template you can do exactly that with an
added bonus of having the capability of instantly producing invoices for your customers orders
let's get started the introduction sheet contains a short description about this template
below that we have a customization difficulty
an explanation of the sheets that come with this template and some instructions on how
you can utilize this template let's get into how you can use this template
the template already comes with placeholders so just follow along as i walk you through each part
beginning with the database tab it is divided into two main parts
namely customer details and product details
the customer details part is where you can input the name of your customer their address city post
code phone number and email address in the product details part you input the name of your product
its corresponding part number its unit price and the unit of measurement fitting for that product
lastly on the far right you can list down the different payment methods that your company
accepts it's important to make sure that you input all the pertinent items because the entries
in the database tab will be used for the drop down lists in the succeeding tabs that we will be using
moving to the transactions raw data tab make sure you scroll to the leftmost part of the sheet
it is here where you log all the transactions that you make you input the transaction date
the customer name using the drop down list the customer order number the invoice number product
and quantity note that if you want to have multiple products for a single invoice
you need to use the same invoice number for the different products notice when you input
the product using the drop down as well as the quantity the part number unit unit price and total
value are computed automatically so make sure to not put anything in these grayed out cells
the unit price control column has two options automatic or manual when you select automatic
on the drop down list it picks up the unit price that you placed in the database sheet
setting at the manual opens up the manual unit price column and the total value will pick up
whatever you put in there this is useful for instances wherein you offer clients a certain
unit price instead of a discount rate moving forward you can input the tax rate and it
automatically computes the total amount after taxes you can also input the discount rate if
any make sure to record taxes and discounts as a percentage of the total value lastly we have the
revenue collections part this is where you input all the times you receive payment from your sales
you start off by putting in the date of payment the amount paid to you by the customer and the
payment method using the drop down list the payment period in days shows how many days
the customer took before they were able to pay while the customer balance is computed
by subtracting the amount paid by the customer by the total amount after discount seeing a positive
value there means that the customer paid more than what is due for that item in the transaction while
seeing a negative means they still have payments due seeing a zero means they paid the exact amount
when you want to create the invoice for your transactions you can hop over the invoice
tab and input your company details on top you can even add the company logo if you have one
then all you have to do is in the invoice number cell select from the
drop down the correct invoice number of the transaction you want to make an invoice for
and all the pertinent details should appear in case you update anything
in the transactions raw data sheet or the database sheet you can hit the refresh button
lastly there is a provided print invoice button so when you're ready to print your invoice just
click on the button and print away to see your sales performance you can go to the dashboard tab
above you can see some quick stats like the number of customers you handle and products you carry the
running sales quantity the amount before and after discounts total revenue and outstanding balance in
the center chart area we have representations for the monthly sales amount versus the revenue
the customer's outstanding balance the payment type in days an analysis of the different payment
methods used and the accumulated quantity versus the sum of quantity on the left hand side we have
slicers for customer product and payment method so you can easily tailor the chart to what you
want to see suppose you just want to see what the trends are for customer 3 for products abb and abc
you just click on customer 3 avv and while holding the shift key press also abc and there you have it
on the right side you have more sliders for the months we only have a few here now because we
already added some filters using the slicers a while ago so it shows just the relevant months
and when you want to view a specific month just do the same thing
click on the months you want to view we also have slicers on the bottom right for the invoice number
in case you want to view them by certain invoices
to remove the filters just press the clear filter button on the top right of each slicer group
lastly we have a full screen macro button which when pressed hides the ribbon so your worksheet
is larger just a reminder that you can't undo changes done by macros so if you want
to see your ribbons again all you have to do is minimize the window then there you have it back
that's all for this template if you want to automate your excel
processes and enhance your level of productivity simple sheets advanced
templates and university is the best tool out there i'll see you guys on the next one