Retail Inventory & Sales Manager – Excel Template (Multiple Locations)
The Retail Inventory and Sales Manager is suited for managing inventory and sales if you are running a business of buying products from suppliers and selling to customers.
This Excel document will assist in knowing the inventory levels of each product and understanding which products to re-order. You can also create invoices instantly and print or save as PDF. In addition, you can quickly view the purchases/sales patterns over time and identify the best performing products.
You need a copy of Microsoft Excel. All features are available in Microsoft Excel for Windows (2010 and 2013). Analysis sheet is not compatible with Excel 2007 for Windows. Since Excel for Mac doesn’t support Slicers and Pivot Charts, Analysis sheet is not available. Analysis Details sheet provides tables but not the charts in Microsoft Excel for Mac (2011).
PRODUCT TOUR VIDEO
- Enter and manage up to 2000 different Products
- Manage inventories in multiple locations (up to 10)
- Create and print invoices instantly (up to 40 different products in one invoice)
- Customize invoice with your own (up to 4) customer information columns
- Can handle 50,000 line items (if you have 10 line items per order, you can have 5000 orders)
- When limit is reached, you can archive the file and start fresh with a new copy
- Set tax rates differently for each product
- Set custom re-order points for each product (re-order points are same for each location)
- Can have a mix of products which are inventoried and products which are not
- Easy to print product, customer and order lists
- Easy to enter starting inventory of each product in each location
- Instant access to current and future inventory levels of each product
- Identify the products to be re-ordered
- Know if the sale orders can be fulfilled or not
- Easily understand the sales and purchase patterns
- Quickly see your top customers and suppliers
- Identify your best performing products and how different product categories contribute to sales
STEP 1: SETTINGS
Enter basic information about your business in the ‘Settings’ sheet.
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 3: PRODUCTS
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
STEP 5: PARTNERS
STEP 6: ORDERS
- 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
- 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.
STEP 8: INVOICE
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.
1. Monthly Sales (Quantity and Cumulative Quantity)
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
- 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.
If you are not satisfied with the product, please e-mail email@example.com and we will issue a full refund.
FILE DELIVERY PROCESS
You will be able to download the file immediately upon payment. You will also receive an e-mail immediately with the download link.
All future updates and upgrades are absolutely free.