Articles, Blog

Inventory, Invoicing, Accounting & Reporting for Rental Business – Excel Template Tour

Inventory, Invoicing, Accounting  & Reporting for Rental Business – Excel Template Tour


welcome to the tour video of the rental business manager excel template in this video I will go through each of the sheets in the template and give you an overview of how the template works now let’s go and take a look so now i have the template open and have filled with some sample data so that i can illustrate how the template works so we begin in the settings sheet there is a list of instructions that you can use to follow how to use this template and it’s very easy however there is also detailed documentation and video demos and everything in the support page so please visit the support page for more information on this template this is where we would enter the business information such as address and email address etc then we have categories of our assets so imagine that we are in a Furniture Rental or a rental business which rents furnitures and dining items like spoons and forks and everything for parties so in such a scenario we want to categorize our assets and this helps in multiple ways which we will see in this template but in this example i have created 4 categories so we can have up to 30 categories and each category can have 50 assets within them so for example i have chairs tables linens and dining and you can have you know 50 types of chairs within the chairs category so we entered the list of categories first and then we go to the assets sheet this is where I have entered each individual asset for example under table we have red folding table yellow kids table, under chairs we have four different types of chairs blue folding chair red folding chair etc then we can categorize the assets we can write a description for the asset and this can also be populated on the invoice later and the starting quantities of when you begin using the template how many items do you have of each of these assets in your business available so this is the starting quantity how many units are you owning as a business and then we have some calculated columns will calculated automatically how many items are currently in stock and how many items are rented of course if it’s negative that means you have you have an over book quantity so that’s something you need to fix but this is these are calculated automatically and we don’t need to edit this we will see more about how this impacts the inventory very soon customer sheet is where we enter the lists of our customers and their contact information and this can be used to populate the invoice and we always have optional columns like custom columns in the template which you can rename just type in your own name for the field so whenever there is custom column please feel free to rename and then use it for your specific needs. green color columns are always calculated columns so please don’t edit them or delete them because they are important for the functioning of the template the order headers is the sheet where we would enter the orders so each line is a unique order and then we know that date the name of the customer the status of the order so you can either have a book status which means that it’s confirmed and if the customer cancels that you can cancel the order you can also do an inquiry order status does not actually reserve the items and reduce the inventory it is only for you to create a quote so that you can give it to your customer and say this is how much it will cost but it doesn’t reserve the items and it does not reduce the inventory only when you put it in the book status the inventory will be reduced we have a couple of types of rate type so we can have a ‘per day’ which means that you can enter the unit cost or unit price for a day of renting and then the template will automatically figure out how many days are being rented and all that but if you want to do a fixed-rate this specific rental it’s a hundred dollar rental for so many days so then you can also do fixed-rate the rent out and return date – as the name indicates this is the date when the items will be rented out will leave inventory and then will come back on the return date you can enter tax rates if it’s applicable otherwise put 0% you can you can have a separate invoice number you can type in and that will automatically populate in the invoice and then you can also track the payment due dates for each of the order. any other charges such as shipping charges and all that any order level discount Order notes so any note that you would want to appear on the invoice. Order details is where we will actually enter specific items being rented for example the order number one there are three different assets being rented blue folding chair, red folding chair, red folding table and we can also see how many units are being rented. two units of blue folding chair one Red folding chair and 2 Red folding table this all these three are part of one single order that’s the order number one we can enter the unit price of renting unit price of renting can be entered here and then if you have any discounts you can enter them here once again we have a custom column you can rename and use it for your own business as needed the green-colored columns are calculations and please do not edit them the rent out date, return date these things where we entered in the order header sheet will automatically come up here saving you time we don’t even have to enter them again for each line and then there are calculations on the amount before tax tax amount amount after tax so these things are automatically done saving again time the check availability at the top is where you can choose a specific item you can enter the rent out and return date and see how many items can be available to rent in that specific window for that specific asset, keep in mind that this is actually calculated based on all the items that you’ve entered in this table so if you have entered in this table the template thinks that is being rented and so the BOOKED orders and it’ll actually not show those items as available It’ll find out how many other items are available how many other units are available for red folding chair so this is the availability check simple easy-to-use now we can go to the calendar also to see the availability in a longer window for all the different assets at the same time and so this takes into account the items that have been entered here in the order and order detail sheet and you can choose a specific date here and then you can also Start Date so in this case I’ve started with 15th october so from 15th actober for 31 days we can see the availability the other thing is you can choose whether you want to see available quantity or rented quantity so when I choose rented so there are seven red folding tables rented out on october fifteenth and fifteen of them rented out from 22nd to 25th nothing is being rented for yellow kids table in this window so this is how you would read it when I choose available you can see here that whenever the quantity available is less than zero you will see the red-colored indicator meaning that this specific during this window the red folding table has been overbooked. overbooked meaning comparing to the number of items that you own in of red folding tables in your business you actually have ordered more to be rented so that’s why you see the you know check marks & Crosses so cross would appear whenever there is a negative quantity and so this is a very easy simple way to look at the availability of your inventory for all of the assets in a window of time as well as easily identifying overbooking so that you can correct that issue so if you remember at the beginning I was telling you that there is a negative here in stock now is negative this is because we actually have overbooking and that’s why we need to go and take a look at that specific order and then try to tell the customer that you don’t have that and then you reduce the number of quantities that are being rented that’s how you will fix this issue as a business owner now after the calendar you go to the invoice sheet this is where you can actually type in order number and then the invoice get automatically populated so in this case I have entered order number one you can see that the customer information populates here order information populates here. specific asset information will populate here and also the subtotal and all that order notes and the contact information everything gets populated in the invoice automatically so one of the things you’ll notice is that all of these things are customizable so if we want to change for example I don’t want the customer ID here i want the email to appear here it’s very easy just change it like that and it’s totally built to be flexible for your business needs so similarly order number if you don’t want the order number to come first order status to come first you can definitely change that very easily and if you want to change any of the formatting here also you can do that by you know for example if you want to click on this one and if you want to apply a different fill color or font you can definitely change all of those things so if you want to show any date you may want to come back to the formatting here and then choose a date format so that you can see the specific date format to be appeared on any specific value so the formatting works just like a regular Excel workbook you can apply and change any of the formatting ok you can also actually customize the fields that appear here so for example i can instead of the name coming first let’s say I want to choose the description comes first and then the name comes after that as an example so you can do that and you can change the column widths so that everything appears as you want it so this is the initial setup that you would do to make sure that the invoice actually works according to your needs now there are two pages. the first page has 25 line items so if you have an order where you have more than 25 line items you can definitely the template will automatically populate the second page so when you go and print the invoice or export to PDF make sure that you choose two pages if you have more than 25 items in order but that’s pretty much it so just type in the order and it will automatically populate then export or print as needed finally we have the report sheet which is also automated with all the current inventory numbers and as well as the financial – current amount and also amount that is past the due date there is a customizable report section where you can enter the start and end date and that the metrics will automatically calculate the quantity being rented rental revenue tax amount discounts other charges and expenses as well the expenses actually come from the expenses sheet where we can enter the date of expense so these are expenses that are operational expenses for the business and so you can track them here and then the report will actually summarize it along and with the other metrics the monthly metrics show the specific metrics listed here month by month for up to 12 months at a time and you can also see the same data in a trend by choosing a specific you know metric that is listed here. the top and bottom performing products so we can see the best performing asset categories so tables are actually contributing forty percent of the revenue for us and linens 36% etc. in terms of assets the red folding table contributes 38 percent of the revenue and then so on we can also choose whether its revenue or maybe we want to look at from rental duration. rental duration is the number of days of rental that are being done for a specific asset or a category the product performance you also have more flexibility to drill further into details such as I want to see the specific item being the product or the asset and see how many are currently on hand and how many are rented out and so we can see the metrics month-by-month for that specific asset so in a chart to see the trend final section of the report sheet is the customer performance i can see the top 10 customers driving revenue or the rental duration so if i want to change this to revenue we can definitely change that metric and then we can see the customers revenue numbers and who is contributing the most as well as you can choose a specific customer and then see how many items have been rented in the window that we are looking at and then the revenue and other information associated with that specific customer including the any past due amounts from this customer such a very very easy and simple way to look at one customer at a time the last sheet that we have in this is to add or retire assets so we know that sometimes doing a rental business you may have to retire announced that because it’s a broken or it’s too old then you can just type the asset ID choose to retire and then type the number of items retiring and so this will automatically reduce the available inventory on that specific effective date for that asset similarly we can add if we buy more assets to rent then you can just type in the asset ID use the type add and then put the number of items being added this will automatically add the available inventory to our calculations so that is all the template does. Again it’s a very simple and effective way to manage your rental business please visit the product page to learn more about the features of the template you can also see more about the how to use the template in the support page thank you very much for watching this video if you have any feedback please leave them in the comments now let’s go and take a look at how to get the template setup for your specific business in the initial setup video

Tagged , , , , , , , , , , , , , , , , , , , , ,

14 thoughts on “Inventory, Invoicing, Accounting & Reporting for Rental Business – Excel Template Tour

  1. There are currently 30 Categories with 50 assets, is it possible to increase this or even better, have the 50 assets have 50 varieties ?

  2. This template is very nice but how could we monitor the payment, you have overdue account and current account.  My question is where we can put the payment date at the time the customer pay his account to reduce his payable.

  3. Yes, i've got it but can we add column for date paid and the official receipt number as tracking for the payment? how about if we are asking down payment during the placement of order?

  4. How about for retail and manufacturing, can we have a tracking payment and receivable? If can do it can i download the updated file? Since i already have those templates?

  5. May i suggest also sir, if we can generate an account statement by customer and supplier as will an ageing of receivables and payables both for retail and manufacturing because we have purchases and sales. I am hoping also if you made it, you can give me a link for download for the updated one because i already purchased you template dated 14th October 2017 those were rental, retail, and manufacturing. Thank and best regards…

  6. In case of multiple payment? Can we insert row and copy paste the original entry and break the invoice amount based on the actual payment? Let say 1000 then pay only 300. So the remaining amount is unpaid. In the following month it is fully paid. Maybe we have to put a column for date paid for easy tracking.

  7. It's a good and quiet helpful template, I'm planning to start, event rental business, so how can I get this template??
    Second thing please upload the video of costumer tracking (marketing) templates for this type of business…
    Regards,

Leave a Reply

Your email address will not be published. Required fields are marked *