Reporting in Rental Business Manager Excel template is fully automated and very extensive. We have reporting to help us get an accurate view of business performance in multiple areas (inventory, sales & finance) from multiple dimensions (product and Customer). Reporting is available on inventory levels, accounts receivable, monthly metrics, top and bottom performing product and top performing customers.
First, we can see the current status of inventory and finance.
We can see the following metrics
Total inventory (quantity) on hand
Quantity rented Out
Accounts Receivable – Current: Amount due from Customers on Orders with Due Date >= Today
Accounts Receivable – Past Due: Amount due from Customers on Orders with Due Date < Today
Now, we move to looking at the past performance of business. First, we select our report Start and End dates.
Rental Qty: Total quantity rented on Booked orders.
Rental Revenue: Total Order amount on the Booked orders. Includes product level discounts. Does not include tax, order level charges and order level discounts.
Tax: Total tax amounts on Booked Orders
Discounts Given: Total amount of discount given to customers on Booked Orders
Other Charges: Total of other charges on all Booked orders
Expenses: Total of Expenses
We can view all the metrics by month for up to 12 months.
We can choose one of the metrics to update the chart that shows up to 12 months of trend.
Products – Top Performers
One of the important pieces of understanding business performance is knowing which products are rented the most and which ones are not. We have 2 ways of measuring this – Rental Revenue, and Rental Duration. This allows us to understand the true impact of the products to the business. First, we will see the top 10 Asset categories and bottom 10 Asset categories.
We can also see the top and bottom performing assets.
In addition to that, we would want to look at a specific product and understand its performance. We can choose a product and see its performance summary during the reporting window. We can see the current inventory status of the product.
We can also see the sales quantity trend by months of that product.
Another important aspect to understand is best customers.
We can choose one specific partner and see their details as well.
Finally, the Assets sheet, the Customers sheet and Order Headers sheet also have useful information that we can filter on to get to the data we need.
In the next article, we will discuss how we can add or retire assets for rental.
Rental Business Manager template enables simple and effective finance management for rental businesses. We have options to enter taxes and discounts. We can track payments made by customers on orders. In addition, we can also record operational business expenses. In this article, we will see these features in more detail.
When we enter the order line items in the Order Details sheet, we are entering the rental prices of products. We are also entering any product level discounts.
In addition to this, any order level charges can be entered in the Order Headers sheet. This will be added to the Total Order Amount. Also, any Order Level Discounts can be entered there. This amount will be deducted to calculate the Total Order Amount.
The template will use these to calculate the total order amount and subtotals in the Invoice and Report sheets.
Tracking Payments and Due Amounts
When the customer makes the payment, we enter the paid amount in the Order Headers sheet.
Due Amount will be calculated for each order as (Total Order Amount – Paid Amount).
You can filter on this DUE AMOUNT field to limit the data to only orders which are not fully paid yet (Filter to values other than 0).
Similarly, you can filter on PAYMENT DUE DATE field to see orders which are past due date. First, choose Date Filters —> Before.
Then, Choose Today.
Click OK and now the table will be filtered to orders which are past due dates.
We can see the overall current status of accounts receivable in the Report sheet.
When we choose a specific customer, we can see the financial status along with other metrics.
Tracking Operational Expenses
All the operational expenses can be entered in the Expenses sheet. For example, if you buy any stationery for your business or subscribe to any other business service, you can enter those expenses here.
These expenses will be summarized and reported in the REPORT sheet.
To view the invoice, let’s go to Invoice sheet and enter the order number at the top.
If we enter an invalid order number, we will see this warning message.
Let’s enter 1 as Order Number. The invoice will now auto-populate. It’s that simple. You can generate an invoice whenever needed. The data comes primarily from Order Details sheet and some information from Customers, Orders and Assets sheets.
Printing the Invoice
Press Ctrl+P to open the print preview dialog box. Preview the sheet and then click print.
By default, one page is printed that can handle up to 25 lines of the order. If the order has more than 25 lines, please change the number of pages in the Print Preview dialog box so that 2 pages can be printed. The template can handle up to 65 line items.
Exporting the Invoice as PDF
To export as PDF, Click File – Export as PDF.
Choose a place to store the file, name it as desired (recommend adding the order number and/or invoice number) and click Publish.
As we keep entering multiple orders (how to enter orders), things can get complex to know the exact availability of each asset on any given window. This is where the built-in calendar will help us. To illustrate, we have entered date for 15 orders in the Order Headers and Order Details sheet.
We have also entered specific assets being rented as part of these orders.
The calendar appears as below.
We can enter any start date and the calendar will display data for 31 days. We can choose to see either Available Quantity or Rented Quantity to display. In the above image, we have chosen AVAILABLE to display. The quantity displayed is the quantity available after fulfilling all the BOOKED orders entered in the Order Details sheet. we can see that three are 4 days where we have overbooking. The other days in this window have a check mark indicating there is enough inventory to fulfill orders.
If we choose to display RENTED quantity, the calendar will look like below.
That’s it. It is a very simple but effective calendar view. By default, it displays up to 30 assets. If we have more assets in our business, we can unprotect the sheet and then extend the table down for more rows.
Expanding the Calendar
This method applies to all tables in locked or protected sheets. First, we will unprotect the sheet using indzara as password.
Go to Review ribbon and click on Unprotect sheet. This pop up window will appear.
Enter indzara as password. Now, click on any cell outside the calendar. For example, click in cell AJ 45. Then, move your mouse cursor to AI 37. You will see a small downward arrow as shown below.
When you hover over that, your mouse cursor becomes double pointed arrow. Just click and drag down to as many rows as you need to expand. This will now expand the calendar functionality to the additional assets.
Please protect the sheet again to prevent any unintentional editing of formulas.
We will enter orders in 2 steps. First is the Order Header and then the Order Details. Order Header includes information that are unique for an order. For example, there will be only one Customer Name and one Order Date for each order. Order Details include the specific assets in the rental order. We will see how we can create them.
Creating a rental Order
Let’s start with entering a simple order where we are renting 3 products to our customer ‘Customer One’. First, we will enter in the Order Headers sheet.
The Order Number should be unique. In other words, each order should be entered in one and only one row. The field should not be blank. We can enter any method of numbering orders. The template does not limit that and does not create any pre-defined order numbers. Here, we have entered ‘1’ as order number. The order was placed on Aug 1st, 2016 and the customer wants to rent assets from 5th Aug to 16th Aug. Let’s understand that each order will have 3 dates. Order Date, Rent Out Date and Return Date.
Order Date is the date when the order is placed. (Aug 1st)
Rent Out Date is the date when the item will be rented out. (Aug 5th)
Return Date is the date when the rented item will return to the inventory. (Aug 16th)
Important: All the items within an order should have the same rent out and return dates. This implies that we cannot return some or partial items separately.
Order Status Orders can be put in one of 3 Order Statuses.
BOOKED: When the rental order is confirmed and we want to reserve the inventory for that order, we choose BOOKED status. This status will impact inventory calculations by reducing the inventory for those products on the order, between Rent Out Date and Return Date.
CANCELLED: When the rental order is cancelled and we want to make those products available in our inventory for other orders, we do so by choosing CANCELLED status. Such orders will not be counted for revenue reporting.
INQUIRY: When we want to provide a quote to our customers, we use the INQUIRY order status. This order will not impact inventory calculations. Items will not be reserved for such orders. They will also not be included in reporting.
In an example scenario, a customer can ask for a quote for a future date and we use INQUIRY status. A couple of days later, the customer confirms the order and we can change the status to BOOKED. A few days later, the customer requests a cancellation and we can change the status to CANCELLED.
Another important component of an order is how we are going to charge for rentals. We have options to choose from 2 rental types.
PER DAY when we charge for each day (rounded) or we want to enter a fixed rate for the entire rental duration. The template will use this to calculate order amounts.
TAX RATE: We can set the tax rate for the entire order. You can enter 0 if tax is not applicable.
INVOICE #: You can enter an invoice number that will appear on the invoice.
PAYMENT DUE DATE: Date when the payment for the order is due.
OTHER CHARGES: Any additional cost on the order. For example, shipping charges.
ORDER LEVEL DISCOUNT: Any additional order level discount amount. We will be entering product level discounts later.
ORDER NOTES: Enter any notes we would want to appear on the invoice.
PAID AMOUNT: Any amount that has already been paid by customer for this order.
Entering Order Details
Now, let’s enter the products or items that are part of the order. We go to the Order Details sheet. In this example, customer wants to rent 10 Blue folding chairs, 5 Red Folding Chairs and 1 Red Folding Table. There are 2 methods of checking rental availability of assets. We can check for availability before entering the order in the Order Details sheet.
At the top of the Order Details sheet, we can choose an asset and rental dates. The available quantity will be shown. This availability checker can calculate availability for rental periods up to 90 days. If we enter a longer window than 90 days, we will see an error message.
Important: Availability checker will consider all booked orders entered in the table, while calculating availability.
Now, once we have checked for availability for each of the 3 products and confirming that we have enough to rent, we can enter them in the table.
We enter Order Number, then choose Asset Category from drop down list. Then when we go to Asset Name column, its drop down list will show only applicable assets. For example, when I chose Chairs as my Asset Category, then Asset Name drop down will only show chairs. This helps in data entry, especially if you have a lot of assets. You can also just type Asset Name instead of choosing from drop down list. Enter Quantity of each asset being rented and the rental price for each asset.
If you are providing any discount, enter in Unit Discount field.
As an example, the first row entered in the table shows we are renting 10 Blue Folding Chairs. Each chair costs $2 to rent per day (since our order rate type was set up as PER DAY). But we want to give $1 discount per day. Now, let’s see how the template calculates amounts. When we scroll to the right on the Order Details sheet, we will see columns in green colored headers.
Please note that these are calculations and should not be edited. Any editing or deleting will break the workbook.
We can see that the data we entered in Order Headers sheet are brought automatically here for calculation purposes. This saves us time as we don’t have to enter them again and again for each line in our order.
Rental Duration shows the number of days the assets on the rental order will be rented out. In this case, from Aug 5th to Aug 16th will be 12 days (including both 5th and 16th).
Net Price will be Unit Price – Unit Discount. For Blue Folding Chairs, $2 – $1 Discount = $1.
Amount Before Tax is Net Price * Rental Duration * Quantity. So, $1 net price for 12 days for 10 blue folding chairs will be 1* 12 * 10 = $120.
Tax of 10% will be $12.
Amount After Tax is $120 + $12 to $132.
Similarly, each line item’s calculations are automatically done.
That’s it. We have completed entry for the rental order. In this case, we entered 3 rows as we are renting 3 different assets. If you have more in your order, please add more rows.
Enter our Business Information in the Settings sheet. This information will be used to auto-populate in our Invoice.
Step 2: Enter List of Asset Categories
Please enter groups in which you would like to categorize your assets or products.
There are two key benefits of categorizing your rental assets or products. 1) We can analyze our business performance by identifying categories that bring more revenue versus those that do not. 2) We can make data entry in orders easier by first selecting Asset Category and the Asset drop down list becomes a shorter list to pick from. We have entered 4 Asset categories for this demo.
Step 4: Enter the list of Assets or Products in Assets sheet
Let’s enter assets and its related information. Please make sure that your first asset is in row 6.
ASSET ID: Unique identification of Asset. This has to be unique. Please do not repeat the same ID or leave the field blank.
ASSET NAME: Name of the asset. This needs to be unique.
ASSET CATEGORY: Choose the Asset category to which each item belongs. The Asset Categories we had set in the Settings sheet will be used to populate this drop down list.
ASSET DESCRIPTION: Description of the rental asset , as needed in our business.
STARTING QUANTITY: This is the quantity of the asset owned by the business when we begin using the template. This is entered only once and does not have to be updated daily. If you have rented items out, please include them here. This number needs to reflect all the assets currently owned (rented out or not). When we decide to retire an asset in the future or buy more assets, we can handle that separately. We will discuss that later.
TAXABLE: In our business, if we have assets that are not taxable, we can enter NO. If tax is applicable, just leave it blank. By default, tax will be applicable.
Step 5: Enter Customer information
In the Customers sheet, we will enter the list of our customers.
Please make sure that your first asset is in row 5.
Customer ID and Customer Name should be unique here. Do not leave them blank.
Enter Shipping and Billing Addresses. Enter Email and Phone Number. These details can be displayed on the invoice automatically.
We can also add additional columns in this table. 2 Custom Columns are available that you can rename and use for your needs.
Step 6: Set up Invoice Sheet
Invoice sheet is the printable invoice that can be automatically created for any rental order. This sheet is very customizable to suit your needs. First, let’s update the logo. Right click on the existing logo and click on Change Picture.
Then, choose your logo image from your computer and click on Insert. Now, your logo will appear on the invoice when printing.
To change the size or other attributes of the logo image, press Ctrl+1 or click the Picture Tools — Format ribbon.
Then, let’s review the fields that are displayed on the invoice.
There are 4 key sections in the invoice: Customer Information, Order Information, Rental Asset Information and Order Total Information. The invoice sheet is fully automated and for each order, all we have to do is to type order number to create the invoice. However, before we begin creating invoices, it is recommended that we review the field displayed in the invoice and customize to suit our business needs.
Customer Information (8 fields)
Order Information (5 fields)
Rental Asset Information (8 fields)
Order Total Information (6 fields)
Please review all the fields to make sure that the invoice meets your business needs. All these sections are customizable.
If you do not need a certain field, just click on a field name and press the Delete key.
If you need to choose another field instead, click on the field name. You will see a drop down menu to choose from a list of available fields. Just select the one you need.
Once the fields are customized and everything looks good, let’s save the file by pressing Ctrl+S. Now, every time when we create our invoices, it will have the information we need. It’s just that simple.
Step 8: Set up Currency symbols
By default, the template does not display any currency symbols as this is meant to be a tool that can be used by any business in any country. However, it is set up to easily apply currency symbols as needed. Let’s go to Invoice sheet. Press Ctrl+G to open the Go To dialog box.
Find the name ‘CURR’ and click OK. Excel will now select all the cells for fields UNIT PRICE, UNIT DISCOUNT, NET PRICE, AMOUNT AFTER TAX and the Order Total section fields.
This is assuming you have not modified the invoice fields. If you have changed or re-ordered the fields, then, please select the appropriate cells you need to apply currency symbols.
Press Ctrl+1 to open the Format Cells dialog box.
Select ‘Currency’ in the Category and then choose the symbol from the drop down. Click OK. This will now apply the chosen currency to all the selected cells in the Invoice sheet. For other sheets, if you would like to apply currency formats, please select the cells and then press Ctrl+1.
The calculations in the template are not impacted by currency symbols. This is purely for visual presentation.
This page will provide the support information for the Rental Business Manager Excel Template. You will find links to articles and video tutorials on how to use the template. I am continuing to add material to this page. Please post any questions in the comments section below. I will be happy to answer.
The second version of this template was published in Oct 2016. Thanks to everyone who used the first version and gave valuable feedback.
The template can create invoices on demand based on the order details we have previously entered. Just type in an order number. You can also export the invoice to PDF or print. Learn all about invoices.
Rental Business Manager template enables simple and effective finance management for rental businesses. We have options to enter taxes and discounts. We can track payments made by customers on orders. In addition, we can also record operational business expenses. Learn about finance management features for rental businesses..
Reporting in Rental Business Manager Excel template is fully automated and very extensive. We have reporting to help us get an accurate view of business performance in multiple areas (inventory, sales & finance) from multiple dimensions (product and Customer). Reporting is available on inventory levels, accounts receivable, monthly metrics, top and bottom performing product and top performing customers. All about reporting.