Please note that this template has been retired. A new and improved 'Retail Business Manager (Pro)' template is now available. Click here.
STEP 1: SETTINGS
Enter basic information about your business in the ‘Settings’ sheet.
You can set whether you would like to include tax when calculating total amounts for analysis. Choose Yes if you would like to include. This does not impact amounts shown on invoice. The amounts shown on invoice would include applicable tax amounts.
As there are three types of transactions (PURCHASE, SALE and TRANSFER FROM), you can set if you would like to apply tax only to SALE transactions.
STEP 2: LOCATIONS
Enter (up to 10) locations of your business. If you have only one location, enter one name. Do not leave this empty.
MESSAGE BOARD
If you have any errors in data entry, the message board will list them. The image below lists the possible error messages. These error messages appear also in respective worksheets.
STEP 3: PRODUCTS
Enter your products in the products table.
Click on the image to view in larger size
There are two custom columns at the end available for you to store any data you would like to. PR CUSTOM 1 will also automatically appear in the ‘Order Details’ sheet.
STEP 4: STARTING INVENTORY
When you begin using the template, you may have existing inventory of products. Enter that information. When the template calculates inventory levels, it will always include the starting inventory amounts. However, the starting inventory will not be included in the Analysis sheet where you would view the purchase and sales trends.
STEP 5: PARTNERS
Enter information about partners. For Purchase orders, your supplier is the Partner. For Sale orders your customer is the Partner. PARTNER ID and PARTNER NAME are unique identifiers for a partner.
Four custom columns are provided to add your own information. If you would like to show the custom columns in the invoice, you can enable that by choosing Yes in the Invoice sheet. You can control each column separately.
STEP 6: ORDERS
Enter each order in a separate row. Order number needs to be unique.
- Order Date: For Purchase orders, this is the date when the order is placed by you to your supplier. For Sale orders, this is the date when the order is placed by your customer to you.
- Order Type
- SALE: When your business sells to customers.
- PURCHASE: When your business purchases from your suppliers.
- TRANSFER FROM: When your business transfers products from one of its location to another. For such transactions, leave PARTNER as blank.
- Location: Location where the order will be serviced. For TRANSFER FROM transactions, this is the location where the products will be taken from.
- Transfer To: If it’s a TRANSFER FROM transaction, this is the location where the products will be transferred to. This needs to be entered if your order type is transfer from. If you are transferring products from location A to location B, you would enter A in LOCATION column and B in TRANSFER TO column. This ensures that the template can calculate inventories at each location correctly. For such transactions, leave PARTNER NAME as blank in the ‘Orders’ sheet.
- Order Comments: This will be displayed on the invoice. You may use this if you would like to enter some message for the customer on the invoice.
STEP 7: ORDER DETAILS
This is where all the order details are entered.
The following are the fields you would enter for each line item in your order.
- Order Number: You can use the drop down menu or type in order number. The order number must be present in the list of Order Numbers in the ORDERS sheet.
- Expected Date: Date when you expect the product to be available in your location if it’s a purchase order, date when you expect the product to leave your location if it’s a sale order type.
- Product Name: Allowable values are the Product Names entered in the PRODUCTS sheet
- Quantity: Number of units of products. The unit can be any numeric value. Even if your unit is not whole numbers, you can still use the Quantity field.
- Unit Price: In Purchase orders, this is the cost of buying one unit of the product. In Sale orders, this is the revenue from selling one unit of the product.
TAX and AMOUNT are calculated automatically. Several other information about the product and the order are also displayed in this sheet for convenience. The columns shaded in gray are calculated columns and they should not be modified.
A custom column OD CUSTOM 1 is provided that can be used it to track things specific to each order line item. You can enter comments or notes, for example. This column will not be used anywhere else in the template.
You can also quickly see the inventory available as of any date for a product at a location. When you have new ‘sale’ orders, this will be helpful to know if you have enough inventory to fulfill the sale order.
STEP 8: INVOICE
You can create an invoice instantly by typing an order number. There can be maximum of 40 lines in one order.
You can choose to have up to four extra columns (information about customers) to be shown on the invoices. You can control the display by just choosing Yes for each column. If you don’t choose Yes, the column will not be displayed on the invoice. You can change this setting for each invoice if you would like.
You can change the logo image and choose an image of your choice.
You can print the invoice or save as PDF
DASHBOARD
If you have added or modified ‘Order Details’ sheet, please refresh the data in the DATA ribbon.
The Dashboard sheet provides quick and easy access to key information on inventory for all locations together or one location at a time.
You can choose a specific product from the drop down list. If you type in a keyword in the search box, the drop down list shows the top 25 product names that match with that keyword. Once you choose a specific product, the other information on the sheet gets updated.
You will also see the two charts below, showing current inventory for the chosen product at each location and the cumulative inventory trend for the product in last 12 months at all locations together.
PRODUCT REPORT
The product report will display inventory information for up to 2000 products. It is automatically calculated. If you notice that the data has not been updated, please press Ctrl+Shift+Alt+F9 and it will recalculate all formulas.It shows the current inventory in all locations and also current inventory in the chosen location (selected in the ‘Dashboard’ sheet). It allows you to filter to products that need to be re-ordered.
Products which need to be ordered have their Product ID and Product Names highlighted in red font.
The default print area is set to only 1 page. If you are printing this and you have more than 1 page of products, please change printing options to print extra pages.
ANALYSIS & ANALYSIS_DETAILS
If you have added or modified ‘Order Details’ sheet, please refresh the data in the DATA ribbon. Once the data is refreshed, you can interact with the filters/slicers at the top to filter data as needed for analysis. This sheet does not function in Excel 2007. If you use Excel 2007, please use ‘Analysis_Details’ sheet.
The following nine charts/tables update automatically.
1. Monthly Sales (Quantity and Cumulative Quantity)
2. Monthly Sales (Amount and Cumulative Amount)
3. Monthly Purchases (Quantity and Cumulative Quantity)
4. Monthly Purchases (Amount and Cumulative Amount)
5. Sales Amount by Customer
6. Purchase Amount by Partner
7. Profit in last 12 months
8. Top and Bottom 10 products
9. Sales Amount by Product Category
If you use Excel 2007, you have to use ‘Analysis_Details’ sheet instead of the Analysis sheet. This is the sheet that drives the charts in the Analysis sheet. If you use 2010 or later versions, and if you would like to view the data behind the charts, then use ‘Analysis_Details’ sheet. Please note that this sheet is not protected. Please do not insert or delete columns without knowing the impact. If you are not familiar with Excel, please do not modify.
BACKUP & ARCHIVING
Since this is just a regular Excel file, all the care you should take with a normal regular Excel workbook should be taken. There is no special back-up of the data you enter. So, please save copies of your file on a regular basis so that you have a version that you can revert back to, if needed.
When the amount of data increases, the file becomes larger and slower (while opening or saving). It is recommended that the file is archived and a new copy is used for future use.
Archiving Process:
- Let’s assume your current file (named Retail_Inventory_Sales_2014) has all of 2014’s transactions. By the end of 2014 you would like to start a new file.
- Save (File-Save As) your current file with a new name. (Retail_Inventory_Sales_2015)
- No changes are needed to the Settings sheet, Products and Partners table.
- Update Starting Inventory table with the new current inventory. You can easily copy information from the Product Report.
- Remove old orders from ‘Orders’ sheet. Keep any future expected orders (Expected Date in 2015).
- Remove all old order details. Keep any future expected orders (Expected Date in 2015) in the Order Details sheet.
- In your Retail_Inventoy_Sales_2014 file, in the ‘Order Details’ sheet, remove the line items with expected dates in 2015.
Then, you can continue to use the new (2015) file for daily use and 2014 file can be used when needed for historical reference.