In this article, we will cover the reporting available in the Retail Business Manager Excel Template.

Retail Business Manager Excel Template – Product Page     Support Page

We have extensive reporting automatically generated, to help us get an accurate view of business performance in multiple areas (inventory, sales & finance) from multiple dimensions (product and partner).

VIDEO DEMO

There are six pages in the Report sheet.

Current Status

First, we can see the current status of inventory and finance.

Report - Current Status
Report – Current Status

 

We can see the following metrics.

  • Total inventory on hand Quantity of products currently on hand
  • Total inventory to come Ordered from suppliers already and will reach our inventory in future
  • Total inventory to Go Ordered by customers already and will leave our inventory in future
  • Products to re-order Products whose inventory on hand is <= Re-Order Point (To view such products, we can go to the Products sheet)
  • Inventory Value Value of inventory on hand calculated based on current purchase price of the products on hand
  • 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
  • Accounts Payable – Current Amount due to Suppliers on Orders with Due Date >= Today
  • Accounts Payable – Past Due Amount due to Suppliers on Orders with Due Date < Today

 

We can also see the breakdown of aging (1 to 30 days, 31 to 60 days, 61 to 90 days, >90 days) of those due amounts.

 

Summary

Now, we move to looking at the past performance of business. First, we select our report start and end dates.

Report - Enter Start and End Dates
Report – Enter Start and End Dates

 

We leave the REFRESH to be ON, by default.

 

Report - Summary view
Report – Summary view

 

Metrics

All the metrics will be calculated on Orders with Order Date from start date to end date

SALES

  • Sales Qty Total of quantity on Sale orders. Considers returns as well.
  • Sales Amount Total Order amount on the Sale orders. Includes product level discounts. Does not include tax, order level charges and order level discounts.
  • Sales Tax Total tax amounts on Sale Orders
  • Qty Returned from Customer Quantity returned by customers
  • Discount Amt Given Total amount of discount given to customers
  • Other Charges Total of Other charges on all Sale orders

 

PURCHASE

  • Purchase Qty Total of quantity on Purchase Orders. Considers returns as well.
  • Purchase Amount Total Order amount on the purchase orders. Includes product level discounts. Does not include tax, order level charges and order level discounts.
  • Tax Total tax amounts on Purchase Orders
  • Qty Returned to Supplier Quantity returned to suppliers
  • Expenses Total amount of expenses from start date to end date
  • Other Charges Total of other charges on all Purchase orders

 

PROFIT

  • Cost of Goods Sold is the sum of purchase price of products sold. Purchase price is the price of product as of Sale order date.
  • Gross Profit Sales Amount – Cost of Goods Sold
  • Net Profit Gross Profit – Operating Expenses

 

Monthly Metrics

We can view all the metrics by month for up to 12 months.

Report - Monthly Metrics Table
Report – Monthly Metrics Table

 

We can choose one of the metrics to update the chart that shows up to 12 months of trend.

Report - Metrics
Report – Metrics

 

Report - 12 Month Trend Chart - Metrics
Report – 12 Month Trend Chart – Metrics

 

Products – Top Performers

One of the important pieces of understanding business performance is knowing which products are selling the most and which ones are not. We have 3 ways of measuring sales – Quantity, Amount and Margin. This allows us to understand the true impact of the products to the business.

Report - Choose Sales Metric
Report – Choose Sales Metric

 

In this sheet, we will see the top 10 product categories and bottom 10 product categories.

Report - Top 10 Product Categories
Report – Top 10 Product Categories

 

Report - Bottom 10 product categories
Report – Bottom 10 product categories

 

We can also see the top and bottom performing products.

Report - Top 10 Products
Report – Top 10 Products

 

Report - Bottom 10 products
Report – Bottom 10 products

 

Product Performance

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.

Report - Product Summary
Report – Product Summary

 

We can see the current inventory status of the product.

Report - Product Inventory
Report – Product Inventory

 

We can also see the sales quantity trend by months of that product.

Report - Product Sales Qty - Monthly Trend
Report – Product Sales Qty – Monthly Trend

 

Partner Performance

Another important aspect to understand is best partners (customers and suppliers).

Report - Top 10 Customers
Report – Top 10 Customers

 

Report - Top 10 Suppliers
Report – Top 10 Suppliers

 

We can choose one specific partner and see their details as well.

Report - Partner Performance Summary
Report – Partner Performance Summary

 

Finally, the Products sheet, the Partners sheet and Order Headers sheet also have useful information that we can filter on to get to the data we need.