Collaborate on Hotel Bill Format in Excel for Small Businesses with Ease Using airSlate SignNow

Hotel bill format in excel for small businesses

foreign [Music] hello and welcome to excel Highway your One Stop Shop for all your Excel needs in today's video I want to share with you a bookkeeping file for small businesses you can use this file for managing your income your expenses and your cogs or your cost of goods sold if you're new to this channel I am specializing in Excel and Google Sheets and posting content all the time so make sure you hit that subscribe button on the bottom so you don't miss out on anything all right let's start so the file has basically four sheets there's the list sheet which has all the drop down lists transaction where it has all the transactions in it summary sheet and a dashboard with some charts let us start so in the lists I have four tables transaction type income expenses and cogs and what's important for something to work in the future that you'll see is that the income expenses and cogs are types are also the headers over here so this is just a table if you're not familiar how to build a table it's very easy I used I like to start with the header and say one two three and just select all of them go to insert here on the top click on table and make sure that my table has headers and that's it you have a table okay I just take that out because we don't need that anymore all right and adding is very simple the leading is very simple and I like to use headers tables because you could then you know reference them very easily so that's the first part you got the four tables you'll notice just for uh formatting issues I added here uh company logo icon that you can change and that will just flow with us throughout the file there's another button here called new transaction we'll get that in a second all right so transaction this is where you log basically all your transactions the date the amount the expense type which is what you have over here and the description which is basically one of those and finally the remarks if you have any now I built a small user form that when you press it it gives you this header add new transaction the date of today but you can change it of course the amount the type where you select one of them like income you get the options you select expenses you get those options and this is all coming from the lists sheet you click the amount like 150 raw material I can add a remark and click on submit I'm going to get this pop-up message and if I go all the way down I see it over here and you'll notice that it gives a negative sign for expenses or cogs and a positive sign for income um also if I don't fill out the all the fields I get this pop-up message not all fields are filled out great transaction anyway I can still do that but I don't recommend and I have a cancel button that just goes away now if you want to learn how to build this simple user form click on dedicated video that I posted a week ago actually so you could just dive into the VBA code I used to build this so check it out on the top so I'm moving on with the transactions is the summary sheet and the summary sheet you have a pivot table um very simple pivot table you just take that the transaction let's say area I click on insert pivot table go to new just change the the range let's say to 10 000 lines and then you can just format as you wish I used a type underneath the type I use the description the amount and you can of course change the formatting if you want specific currency like US dollars or anything else so you can do that um and the transaction date now transaction date you can just right click anywhere click on group and then you can select months and years which is very comfortable of course you can also change the order like I want to I want to see the income first for example so I'm just dragging that and you can expand and collapse and you'll notice that we have also a blank and some um uh blank columns for dates so what I do is when I create a slicer or a timeline in this case so insert a timeline transaction date in any case that I recommend you do that you can select years and then once I select a certain year it's going to show me everything about that here let me just and then I can just duplicate that also to show me quarters or months it's very it's very uh very friendly to use the pivot table timelines so you can just work with that and just change and you can see a summary per type like income cogs or expenses you can see the summary for the month so that's what you can do it's just what I did over here okay so I can just shift the quarters and see I can just select all of them I can select 2013 I can select two years to see what's going on so you can play with that and it will be very friendly for you to see how it looks so the income is positive the expenses of cogs are negative and you get final results the last cheat here is a dashboard again just an example of what you can do very easily again you have the two slicers and now you have basically three charts that I build the charts are coming from here so I have three simple pivot tables here it has the description and the sum of the amount and I just wanted to see income so I've added a filter and filtered income and the expense breakdown same thing with expenses and this is with the clock so basically it's three different pivot tables each for each type and here you just see the charts so you can basically it's very simple there's a pie chart a donut chart and a column chart you can do whatever you want you can just click on to do this you once you click the pivot table click any cell insert a chart and you can insert you see this you can also of course in the design change if you want to change it later to pie chart or whatever you want to see and you can just see it that way and as always you can just change the formatting change the colors add text whatever you want and of course what's nice is that these are all connected to the same slicers if you want to connect a slicer to one or more pivot table just right click on it click on report connections and you see that it is connected to all three pivot tables in the dashboard it's not connected to the summary so it could have different um different years you see here it's 2022 here's 2023. um so yeah so this is the the file [Music] I do recommend that you you check out how to create the new transaction form because it's just easier if you don't if you're you don't want to do it you can always manually just key in date and whatever number you want and whatever text you want but that's just a way and just remember to once you do that click on data refresh also all the pivot tables will be updated but um I'd really recommend just uh follow that descript the the guide that I built for the user for because it's super friendly and easy and it's not a lot of work to do it so I hope you enjoyed the video if you did please hit that subscribe button give me a like and I'd be happy to read your comments and this file will be available on my patreon page so you're more than welcome to join and get all the files all the videos uh or on my Etsy shop that will be also available for one-time purchase thank you have a good one