Retail Inventory and Sales Manager - Excel Template

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. 

REQUIREMENTS

You need a copy of Microsoft Excel to open this file. The product has been tested in 

• Excel for Windows (2007 or 2010 or 2013)
• Excel for Mac (2011) - 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. Please e-mail at indzara at gmail if there are questions.

FEATURES

• 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

LIMITATIONS

The template is not capable of reading data from bar code scanners.

There are several formulas embedded in the document. When the size of your data approaches the limits of this template (2000 products and 50,000 line items), there is a 3 to 5 second lag while inserting new line item. After a new line item is inserted, everything else works instantly. If you notice a lag, you can save a copy of the current file and start entering new orders in a new file.

BUY

You can purchase the template by using the 'Add to Cart' button below and then choose Checkout. You will be redirected by ejunkie.com to PayPal where you can use your credit card or PayPal account to purchase. You can download the file instantly after payment. An e-mail will also be sent to your e-mail address with a link that you can use to download later and the password to unlock the file. You will also receive all future upgrades of this product to your e-mail for free. If there are any questions about the purchase process, please e-mail me at indzara at gmail.com. If you are not satisfied with the product after purchase, please e-mail within 60 days and we will refund the entire amount.

US $25

HOW TO USE THE TEMPLATE

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.

The template has several Excel Tables (link to Microsoft help). If you type data in the row after the last row of the table, then the table expands and includes your new row of data automatically. For deleting rows from a table, select cells from rows that you would like to delete. Right Click and select Delete---> Table Rows. This is the recommended way of adding and deleting data from tables.

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. 

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. 

CHANGING CURRENCY

In the ‘Invoice’ sheet, from the Home ribbon, choose Find & Select --> Go To.

Choose CURRENCY_CELLS.

Press Ctrl+1 or click on the Number format menu. 

In the Format cells dialog box, choose the currency you would like to use. 

Now, the invoice will display the amounts in the chosen currency.