PRODUCT

Retail Inventory and Sales Manager – Excel Template

PURPOSE

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. 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

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

 

HIGHLIGHTS

STEP 1: SETTINGS

Enter basic information about your business in the ‘Settings’ sheet.

Retail Inventory and Sales Manager - Excel Template - Business Information
Retail Inventory and Sales Manager – Excel Template – Business Information

 

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

Retail Inventory and Sales Manager - Excel Template - Location Information
Retail Inventory and Sales Manager – Excel Template – Location Information

 

Enter (up to 10) locations of your business. If you have only one location, enter one name. Do not leave this empty.

MESSAGE BOARD

Retail Inventory and Sales Manager - Excel Template - Message Board
Retail Inventory and Sales Manager – Excel Template – 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
Retail Inventory and Sales Manager - Excel Template - Products
Retail Inventory and Sales Manager – Excel Template – 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

Retail Inventory and Sales Manager - Excel Template - Starting Inventory
Retail Inventory and Sales Manager – Excel Template – 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

Retail Inventory and Sales Manager - Excel Template - Supplier Customer Information
Retail Inventory and Sales Manager – Excel Template – Supplier Customer Information
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

Retail Inventory and Sales Manager - Excel Template - Order Information
Retail Inventory and Sales Manager – Excel Template – Order Information
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.

Retail Inventory and Sales Manager - Excel Template - Order Details
Retail Inventory and Sales Manager – Excel Template – Order Details

 

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.
Retail Inventory and Sales Manager - Excel Template - Order Details
Retail Inventory and Sales Manager – Excel Template – Order Details
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.
Retail Inventory and Sales Manager - Excel Template - Inventory Availability
Retail Inventory and Sales Manager – Excel Template – Inventory Availability

STEP 8: INVOICE

You can create an invoice instantly by typing an order number. There can be maximum of 40 lines in one order.
Retail Inventory and Sales Manager - Excel Template - Invoice Settings
Retail Inventory and Sales Manager – Excel Template – Invoice Settings
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.
Retail Inventory and Sales Manager - Excel Template - Invoice
Retail Inventory and Sales Manager – Excel Template – Invoice
You can change the logo image and choose an image of your choice.
Retail Inventory and Sales Manager - Excel Template - Invoice Logo
Retail Inventory and Sales Manager – Excel Template – Invoice Logo
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.
Retail Inventory and Sales Manager - Excel Template - Data Refresh
Retail Inventory and Sales Manager – Excel Template – Data Refresh
The Dashboard sheet provides quick and easy access to key information on inventory for all locations together or one location at a time.
Retail Inventory and Sales Manager - Excel Template - Dashboard
Retail Inventory and Sales Manager – Excel Template – Dashboard
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.
Retail Inventory and Sales Manager - Excel Template - Dashboard
Retail Inventory and Sales Manager – Excel Template – Dashboard

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.

Retail Inventory and Sales Manager - Excel Template - Current Inventory at Location
Retail Inventory and Sales Manager – Excel Template – Current Inventory at Location
Retail Inventory and Sales Manager - Excel Template - Cumulative Inventory at Location
Retail Inventory and Sales Manager – Excel Template – Cumulative Inventory at Location

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.
Retail Inventory and Sales Manager - Excel Template - Product Inventory Report
Retail Inventory and Sales Manager – Excel Template – Product Inventory Report
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.
Retail Inventory and Sales Manager - Excel Template - Analysis
Retail Inventory and Sales Manager – Excel Template – Analysis
The following nine charts/tables update automatically.

1. Monthly Sales (Quantity and Cumulative Quantity)

Retail Inventory and Sales Manager - Excel Template - Monthly Sales Analysis
Retail Inventory and Sales Manager – Excel Template – Monthly Sales Analysis
2. Monthly Sales (Amount and Cumulative Amount)
Retail Inventory and Sales Manager - Excel Template - Monthly SalesAmount Analysis
Retail Inventory and Sales Manager – Excel Template – Monthly Sales Amount Analysis
3. Monthly Purchases (Quantity and Cumulative Quantity)
Retail Inventory and Sales Manager - Excel Template - Monthly Purchase Quantity Analysis
Retail Inventory and Sales Manager – Excel Template – Monthly Purchase Quantity Analysis
4. Monthly Purchases (Amount and Cumulative Amount)
Retail Inventory and Sales Manager - Excel Template - Monthly Purchase Amount Analysis
Retail Inventory and Sales Manager – Excel Template – Monthly Purchase Amount Analysis
5. Sales Amount by Customer
Retail Inventory and Sales Manager - Excel Template - Sales Amount by Customer Analysis
Retail Inventory and Sales Manager – Excel Template – Sales Amount by Customer Analysis
6. Purchase Amount by Partner
Retail Inventory and Sales Manager - Excel Template - Purchase Amount by Supplier Analysis
Retail Inventory and Sales Manager – Excel Template – Purchase Amount by Supplier Analysis
7. Profit in last 12 months
Retail Inventory and Sales Manager - Excel Template - Profit Loss Analysis
Retail Inventory and Sales Manager – Excel Template – Profit Loss Analysis
8. Top and Bottom 10 products
Retail Inventory and Sales Manager - Excel Template - Top and Bottom performing products
Retail Inventory and Sales Manager – Excel Template – Top and Bottom performing products
9. Sales Amount by Product Category
Retail Inventory and Sales Manager - Excel Template - Product Category Analysis
Retail Inventory and Sales Manager – Excel Template – Product Category Analysis

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.

 

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

US$25

CUSTOMER RATING

100% SATISFACTION

If you are not satisfied with the product, please e-mail support@indzara.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.

FREE UPGRADES

All future updates and upgrades are absolutely free.

LICENSE TERMS

PRODUCT SUPPORT

PRODUCT SUPPORT

OFFER

Offer Details

Special Offer - Buy One, Write Review, Get One Free

ADD A REVIEW

1 2 3 4 5

US$25