Streamline Your Financial Processes with Notion Invoices for Accounting

Notion invoices for Accounting

if you're looking for a great way to manage invoices for your business you've come to the right video in this video we're going to share with you how to use the power of notion to manage your invoices as well as send them we're first going to create the structure of the databases for companies and invoices then utilize formulas to create a dashboard that looks just like this and most importantly you'll be able to send notion Pages just like this to your customers to notify them how much they owe you and what are due dates before we get started with the build it's important to know that you can duplicate this exact template into your workspace to work alongside with us click that subscribe button for more notion business content and let's get started so to start off we're going to be creating two separate databases one is going to be for the companies that you're working with and then the other is going to store the invoices now if you already have a company's database inside of your notion workspace that's totally fine just go ahead and create the invoices database and Link everything up but so to create the company's database I'm going to do a slash and then I'm going to select page and then I'm going to create a table I'm going to create a new database and then I'm going to name this one companies once you have the company's database created you're going to start entering some properties that are relevant for your business but most importantly I've found for companies is being able to show what is your relationship with that customer so we're going to name this one relationship and then we're going to add some options one can be customer another could be Prospect former customer partner and then that should do it for now for the different types of relationships for the companies that you work with next up let's create a property for websites so we're going to to find the url and then we're going to name it website then next up we're going to create a number property and this is going to be used to show the company size and then let's add a few records here so we're going to put in this one that the company name is Google this one might be notion and then this one is Nike obviously you can put whatever you want in for this data and then let's put all these just as customer cuz we are doing invoices and we're trying to see how much they're paying us once you have a basic company's database set up come back to to the main invoices page and then we're going to create another page and for this one we're going to again select table and then new database and you're going to name this one invoices then remove the tags property and then we're going to create an ID property and with this ID property we're going to create a formula that's going to show us exactly which invoice this is in our system so to show you what I mean I'm going to go over here and press plus and then we're going to select formula rename the formula to invoice number and then select edit in quotes you're going to put invoice plus then two quotes with a space in between so we can give a space here in this formula then we're going to do a plus and then quotes number and then we're going to do another plus and then find that ID hit done and then once you hit done you'll see you have invoice number one invoice number two invoice number three so every single time you generate a new invoice it's automatically going to give you that number that you can then copy to the clipboard and simply paste into the name property here next up let's create a property to be able to recognize what is the amount of each one of these invoices so you're going to go ahead and you're going to create a number property switch the number format and because I'm in the US I'm going to select US dollar but if you're somewhere else around the world you can choose one of your currencies so select US dollar and then I'm going to change this number property to amount in said for the name for it then if you like you can even put a dollar sign here for the icon now let's create a status property to show whether or not our customers are paying our invoices so we're going to do plus then we're going to select status and we can leave the name as status here but then we're going to change some of these labels so for to-do instead of not started we're just going to rename this one draft for in progress we're going to name this to sent and then for complete we're going to name it to paid now let's create three new date properties for the first date property it's going to be used to show exactly when the invoice was issued to the customer so we're going to name it to date issued for the second date property we're going to be using it to show the customer when the invoice is due so we're going to name it due date and then finally we want to recognize in the system exactly when the customer has paid the invoice so we're going to create another date property and then we're going to name it paid date now that we have our date properties created let's go ahead and create a couple automations to automatically Tim stamp some of these dates so you're going to come over here to the right hand side and select the lightning bolt and then we're going to create a new automation for this first one we're going to name it set date issued to today and what this automation is going to do is whenever a page is added we're automatically going to timestamp with the date issued to today just to make sure that we don't miss out on the days that we're giving out invoices to our customers so for the trigger we're going to add a trigger and it's going to be when a page is added and then the action is going to be date issued set to today the next automation that we're going to create is whenever the status is changed to paid we're going to automatically timestamp the paid date filled with today as well so again select the lightning bolt select new Automation and then we're going to name this one set paid date to today the trigger on this one is going to be a little bit different than the last one we're going to select add trigger but for this one we're going to select status and then we want it to only fire whenever the status has been selected as paid so we're going to select done so status set to complete then we are going to timestamp the paid date with today once you've done that go ahead and select create and then you can test out those automations so for the first one Let's test out let's go ahead and move the status to paid and sometimes it can take a few seconds but it's automatically gone to November 8th 2023 the day that I'm filming this video and then also let's create a new invoice and using this invoice number we can go ahead and easily name it but you can see that the date issued is automatically November 8th 20123 as well now it's time to relate these invoices to the companies that they're associated with to do this we're going to create another property and we're going to create a relation and you're going to relate that company's database that you created at the start of this video you're going to set the limit to one page and you're also going to want to show on companies and what this means is that when we go back to that company's database you'll also be able to see the relation property for invoices on there so we're going to add that relation and then let's select some of those companies that we uh added here so let's do notion Nike and Google then when we select on one of these companies you're going to be able to see all the invoices that are associated with them once you've Associated some invoices with the company's database what you're going to want to do is let's go ahead and clean up this database view a little bit so here on ID this isn't really necessary to see here so we're going to hide in the view and then next next up let's add some amounts here so let's do $100 here 200 and then 300 and then now it's time to start preparing these invoices to be sent to your customers and the way that we're going to be sending these to our customers is by refining the pages through database templates so to create a database template we're going to come up here next to the new button and we're going to select the drop-down and we're going to create a new invoice template for the title we're going to give it invoice number and then let's hide some of these fields that aren't necessary to see so we're going to always hide the company's relationship let's always hide the ID let's always hide the invoice number then let's drag status up to the top and then it's time to start entering some data down here and what we're going to do is we're going to fill in some information such as your company name who you're billing to what are the line items that are going to be part of this invoice the total amount in some different ways to pay you so to start off let's enter your company information so you're going to do a slash and then do heading 3 and then you're going to enter your company name here then next up let's get some key information about your business so you're going to want to probably get your address on this invoice phone number would be good as well email and then web once you've entered in all this information you can also bold simply by highlighting and we're going to highlight and bolds address phone email and web and then let's create a divider property so we're going to do a slash and then search for divider then let's enter the information for who we're going to build to so we're going to do a heading three enter build two going to do a little space here so it looks a little better and then I'm going to copy this here down below to Bill two and so every single time you create an invoice your company information is going to stay the same but you're going to want to come here to the bill too before you send it over to the customer and make sure that this information looks correct keep in mind that this is a template so every single time that you're creating a new record in this notion database is going to have this as the base so you probably just want to keep it something like this where it's very recognizable that you need to go ahead and fill in that data for your customer who you're sending the invoice to but after you set up the bill to we're going to create another divider and this time we're going to start entering in some of the line items so we're going to do a slash and then we're going to do heading three and then we're going to name this description or you can also call it line items it's really up to you and then underneath description we're going to do a slash and we're going to create a table view database by searching data and then we're going to select table View and we're going to create a new database name this database line items then also name the view line items as well and while you're renaming The View you can also change the icon that's going to appear so we're going to select the list icon here and then let's remove the uh database title you can see that looks pretty clear there it's going to be recognizable for your customer to understand what this is for then let's remove the tags property by deleting it and then let's change name from name to item description we're going to add a property for the unit price so we're going to create a number property I'm going to select US dollar but again you can choose your own home currency name it unit price then we're going to create another property and this one is going to be named quantity and we can leave the number for format just in number and then for testing purposes at this point I'd recommend adding a line item here so I'm going to name this one opop CRM it's a CRM that I purely sell then you're going to give whatever you added here in the LI items a price so I'm going to enter $25 and then we're going to give the quantity of one and now let's create a formula to find the subtotal so you're going to go down to create a property and we're going to select formula and we're going to name it subtotal and then we're going to edit it and for this formula what we're going to do is we're going to get the unit price and we're going to multiply it by the quantity that will give you your subtotal and for the number format I'm going to choose us doll if you want to show taxes on your invoices here in Ocean then what you want to do is we're going to create another property and we're going to uh select number but we're going to change the number format into a percent and then we're going to change it to tax for the name then for this example let's say that the tax percent is 5% and then to calculate what is the tax cost we're going to create another formula so press plus formula and then I'm going to name this one tax cost and actually you should probably name this one here where it says tax to tax percentage but then let's go ahead and edit this formula and the way that we're going to find our tax is by getting that subtotal and multiplying it by whatever the tax percentage is after you've entered those two we're going to select done and then for the number format you're going to choose your currency and then that's going to give you whatever your tax cost is and now finally we need to find our total so our total is going to be our subtotal plus the tax so let's create a another formula property here and we're going to name it total edit then we're going to get that subtotal and we're going to add it with the tax cost select done and then you can see we have the $25 plus $125 equal $26.25 we also here want to change the number format to us for now because this is a database template we're going to want to remove these so make sure we delete all these items here and then underneath description we're going to do a divider and then I like to put the total here so your customer can easily see so it could be something along those lines and then let's put another divider underneath it and then let's make sure it's all bold so it really pops out and then of course for invoices you need some sort of method of getting paid so underneath the total we're going to do a heading three and we're going to name it payment methods and for each one of your payment methods you can enter it here onto the database template for this example I'm just going to show you a simple way of giving your uh Bank information for bank transfer so we're going to do a slash and then we're going to do call out and then for the icon go ahead and select it and then we're going to search for icons and then search for bank and then select this classical building and then next to our building icon we're we're going to say pay with bank transfer then you're going to press shift in return at the same time inside of this call out box and then let's enter some of our bank details so they can route money to us so it's going to be Bank name account number routing number and recipient name once you've entered in all that data go ahead and with pay with bank transfer you're going to bold and underline it and then we're also going to bold all four of these as well now besides pay with bank transfer there's obviously other ways for businesses to pay you such as sending a PayPal link or sending a stripe link so you're more than welcome to follow the same type of format of creating a call out box and providing those links there but once you're done with that we're going to create another divider and then for this final one we're just going to say a simple thank you to the customer so we're going to do slash heading three and then we're going to say thank you and then say something along the lines of please send the payment ing to the due date indicated on the invoice and then to finish it off you can put your company name in your website now that you have the database template completed let me show the benefit of what you just created so if we just go into one of our old invoices that we have created so far you can see that it's just a blank page but if we go ahead and select invoice number here it automatically fills in all that information that we just created together all you simply need to do for each invoice that you create and send to one of your customers is you're going to need to change the build to information make sure that you add the information for the line items so to show you what that looks like you're going to do for example like we entered the opop CRM before You' give it a price give it a quantity enter whatever the tax percentage is it points out that total and then for this total here you're going to put the total that's in the database and then You' obviously want to make sure that this amount on your side matches up so we're going to put this one to $26.25 and then when everything looks good and it's time to send it to your customer the way that you can do that is when you're on the page and selected on a record you can come up here to the share button and all you simply need to do is put that person's email or people's email into this invite box here select invite and then they'll receive this invoice from you you can also export this invoice by coming up here to the three circles selecting it and then selecting export and then you can select what the export format is going to be I think for most people it would be a PDF so select PDF export and then I have orted this and then you can see what the invoice looks like here whenever it's exported via PDF and then one last thing that you probably want to do with the database template that you just created is you're also want to come into it and you're going to want to set it as default so what you can do is hit the three circles and then set it as default and this makes it so every single time that you create a new invoice it's automatically going to give that data that we just created on the record now you're going to be working with zi to create a view just like this where you're able to see the outstanding balance for each one of the companies that you're working work with and also see what are the overdue invoices by those companies I'll let you take it from here ziki thanks Ben all right before we dive into creating the outstanding invoices dashboard let's add one more property to the invoices database to make building the dashboard easier later on this new property will help us identify if an invoice is overdue so basically we want to check if the invoice status is sent and if the due date has passed if both conditions are met we'll mark the invoice is overdue so let's go ahead and do that first we'll create a new formula property called overdue now let's give it a meaningful icon something like an exclamation mark and click on the formula property field to open the formula editor now to make things easier and keep our formula clean we'll use variables Ben and I recently shared a video where I explained variables and their benefits so I highly recommend watching it if you haven't already understanding how variables work is going to make it a lot easier to follow this tutorial and make writing your code a lot easier you can find the link in the description below so basically variables are like smaller more easily digestible building blocks the store information and make it easier to write clean code and consequently troubleshoot it a lot faster now before we start defining our variables let's outline our process with comments the first step is to extract invoices in status sent and and inves past du to start a comment just write slash asteris fill in your comment and close it with Asterix slat now as the second step we use both of these conditions together to extract invoices that are both in status sent and pass so in our second comment we'll say something like if invoices are sent in class do Mark as overdue using a Boolean data type now a Boolean data type is a data type that returns either true or false and in notion that result is represented as a checkbox which is either check if the value is true or empty if it's false now that we have our process outlined let's create our variables to do that we'll use the lets function the lets function allows to Define multiple variables at once as opposed to the let function which allows you to Define only one variable at a time so let's create a new row and Define our first variable which is invoice sent this variable will extract invoices that have already been sent to our clients so to do that we'll write status equals sent next we need a variable to store today's date which will then compare with the due date property so let's name this variable today now although there's no direct function for today's date in notion we can use the Now function to get the current date and time next we'll use the format date function to ex ract only the date from the current date time but here's the catch the format date function will convert the current date time into a string which is basically a sequence of characters like numbers letters and punctuation and this is a problem for us because we can't compare a string to a date so to convert this string back to a date we'll use the par State function now the par date function requires the string to be in a specific format to be able to convert it back to a date now this format is y y y y NN DT let's just enclose this in quotes all right unless they add the par date function now let's define our third variable to compare the due date with our new today variable so let's name this one past VI this one is actually really simp simple just write due dates less than today good so let's go ahead and test our variables in with sent okay this is working fine today is November 17th so that's working okay and past due is false which is also okay because we have no due date defined here now all of our variables seem to be working fine so let's go to the next step let's first copy our second commment here so we know that the first step is done and we're on to the next one now the final step is to use the IF function to determine if an invoice is overdue so if the invoice is sent and pass du we'll return true otherwise will return false so let's go ahead and write if invoice send and pass do true otherwise false okay so as you can see our formula now returns a check if an invoice is overdue and an empty check box if it isn't if the due date is not set the overdue checkbox is going to be unchecked as well now let's add a due date here let's say it was two days ago and change this status to send as you can see our formula now returns a check if an invoice is overdue and an empty checkbox if it isn't if the due date is empty the checkbox will also be empty all right now let's move on to creating our outstanding balance dashboard I already have the page ready for it so let's head over there and build dashboard from scratch okay so as you can see we already have our dashboard page ready this is where our dashboard is going to be below we have different sections like invoices awaiting payment draft and recently paid invoices I also have a helper table here on the right showing the summarized results relevant to our formula so we can quickly compare the output to the expected results so on our dashboard we want to show the total outstanding balance per customer and highlight which invoices are overdue we also want to show the invoice amount for each of these invoices so you can easily keep track of your financials and maybe give your customers a gentle nudge to pay up if needed now I've added some sample data in the invoices database so we can make this example a bit more realistic so first let's create a link toew of the company's database do that right slash link select link view of database and select companies as the underlying database next we create two formula properties the first one will extract the overdue invoices from the invoices database we'll use this property as a filter for our dashboard so it only shows companies with overdue invoices the second formula property is the one that we'll be using for our summary dashboard this one will show the outstanding balance per customer and use the first Formula property to show the overdue inves now we could have done this with just one formula but this approach allows for better filtering capabilities and makes the second formula a bit easier to write and truth be told there's no need to complicate our lives if there's an easier way so let's start with our first Formula and extract the overdue invoices now this formula is actually really simple we'll just use the invoices property and the filter function to filter out the list of invoices that are overdue so the formula is invoices. filter current overd equals true now the filter function allows us to tap into the connected database so in our cases that is the invoices database and extract specific records based on our filtering condition all right let's move on to the next Formula let's create a new formula field call this invoices dashboard and before we start writing the formula let's go over the process and outline it with comments first we'll have to define the currency in which the result should be displayed now even though we Define the currency in the invo of database we can only pull raw data from another database this means that the outstanding balance is not extracted in a specific currency but just as a number so to address this we'll manually Define the currency within our formula second we need to extract the outstanding balance and be overdue invoices with their respected invoice amongs next we'll Define The Styling and create a divider that will then use to format our dashboard and make it look a bit more professional organized and easy to read and lastly we'll combine and format all this data to create a dashboard okay now that we have the process down let's start writing the for formula let's first copy the first comment add it here now the concept is very similar to the formula in the invoices dashboard we'll be using the r function to Define our variables and then combine them for the final result the first variable will be for the currency so let's just name this one currency and I'll be using the US dollar symbol but feel free to use your preferred currency symbol instead okay let's copy the second comment now this second variable will help us filter out the invoices that haven't been paid yet so let's call this one out standing invoices we'll select the invoices property which is the relation property to the invoices database and the filter function inside the filter function we'll specify that the status should be equal to sent the third variable we'll use our second variable as the base to calculate the total sum of the unpaid invoices so as you can see you can use any previous variable within the variable you're currently defining so let's name our third variable outstanding balance we use the outstanding invites variable and apply the map function similar to the filter function the map function can also access connected databases the difference is that the filter function extracts database records whereas the map function extracts specific information from these records so in this case the filter function gave us a list of invoices while the map function can give us the total amount of these invoices par amount now at this point the outstanding balance variable is returning a list of the total amounts but we actually want to sum them up so let's add the sum function at the end moving on to the fourth variable we'll average our overdue invoices property add the total amounts for each of these invoices and format the result of bit so to do that let's name this variable overd invoices and amounts select the overdue invoices property and use the map function show both the invoices and their total amounts we'll first use the current function fun fact the current function is actually also a variable that is already integrated into the formula editor another example of an integrated variable is the Now function which might be a bit easier to understand so similar to howy Now function stores the current date time value the current function Returns the current value of whatever it is you're working with at this point of the formula so in our case the current value is the value of the overdue invoices property this means that the current function at this point Returns the list of overdue invoices okay so now that we've extracted the invoices let's add a column and space followed by the currency variable and the current amount to make the list of our overdue invoices a bit easier to read let's also add an arrow at the beginning of the map function now I know that this formula might be a bit Advanced but I hope I wasn't too fast and that I've been clear in my explanation watching the variables video I recommended at the beginning will definitely help you understand this example better as well now let's talk about the last two variables first one is for defining the style and the second one is for creating a divider so let's first copy paste the third comment we name the first variable header style Define the style we'll use square brackets and WR B for B you could also add colors and backgrounds but let's just keep this dashb clean and minimalistic and just B the text instead be sure to use square brackets or else the formula won't work now for the divider we'll use the repeat function the repeat function takes two arguments one what should be repeated and two how many times so in this case I'll use a DOT and repeat it 10 times okay now that we've defined all of our variables Let's test them one by one to make sure that they are working correctly okay okay okay everything looks good for now so let's move on to writing the final expression I'll just copy the last comment in the final expression we'll Define how the dashboard will look the first part of our Dash dasb is the outstanding balance so let's simply write outstanding balance apply the header style using the style function and our style header variable at the currency variable and the outstanding balance variable to start a new rle We'll add the back slend character is a special character used for ending the current line and starting a new one now in the new row We'll add our divider and to start a new row We'll add the back slash and character again next we'll write an expression to show the overdue invoices so let's write overdue invoices by r fyle to it and start a new role with the back SL a character now for the final part if there are no overdue invoices we want our dashboard to say no overdue invoices but if there are any we want to list them and display the total amount so to do that we'll use the IF function so our condition is if the overdue invoices is empty then display no overdue invoices otherwise show overdue invoices and amount variable now to show each invoice in its own row we'll use the join function the join function allows you to separate a list using whatever character you specify including spacing or new rows so to separate our invoices into individual rows will specify the back stash and character as our separator all right just click done and we've covered all the steps for our formulas now the final task is to switch the table View to a gallery view and add a filter to only display clients with overdue invoices here's what we need to do click on the three dots choose layout Gallery adjust the car preview to none go to properties and click on the invoice dashboard property so that it shows up on the gallery card now we just need to add a filter select overdue invoices and say is not empty and that's it our overdue invoice is summary. is now done so I'm handing the mic back to Ben now for closing words thanks for the amazing tutorial on formula ziki if you'd like more videos just like this to help you maximize your business's use of notion don't forget to subscribe and also leave a comment Down Below on what you'd like us to build next if you're just getting started in Ocean and don't have a CRM system yet go ahead and collect the free one in the description of this video so you can store all the companies contacts tasks projects and everything else that you need to run your business successfully thanks for watching and we'll see you in the next video