Reporting in Retail Business Manager

Posted on

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.

Finance Management in Retail Business Manager

Posted on

In this article, we will review topics related to finance management in the Retail Business Manager Excel Template.

Retail Business Manager Excel Template – Product Page     Support Page

 

VIDEO DEMO

 

Order Related Costs

When we enter the order line items in the Order Details sheet, we are entering the purchase costs of products (in a purchase order) and sales prices of products (in a sale order). We are also entering any product level discounts.

Finance - Product costs
Finance – Product costs

 

Other Charges

In addition to this, any order level charges can be entered in the Order Headers sheet. This will be added to the Total Order Amount. Also, any order level discounts can be entered there. This amount will be deducted to calculate the Total Order Amount.

Finance - Order Level costs
Finance – Order Level costs

 

Entering Payments made

In a sale order when the customer makes the payment, we enter the paid amount in the Order Headers sheet. Similarly, for a Purchase order when we pay the supplier, we will enter the paid amount.

 

Finance - Order totals and Due Amounts
Finance – Order totals and Due Amounts

 

Due Amount will be calculated for each order as (Total Order Amount – Paid Amount). You can filter on this filed to limit the data to only orders which are still due (Filter to values other than 0).

 

Orders that are due
Orders that are due

 

Accounts Receivable and Accounts Payable Summary

We can see the overall current status of accounts receivable and accounts payable in the Report sheet.

Finance - Report
Finance – Report

 

We can also see the aging of the payments due (1-30 days, 31-60 days, 61-90 days, >90 days).

When we choose a specific partner, we can see the financial status.

Finance - Partner Summary
Finance – Partner Summary

 

Operational Expenses

All the other operational expenses not directly related to any one specific order, should be entered in the Expenses sheet. For example, if you buy any stationery for your business or subscribe to any other business service, you can enter those expenses here.

Operational Expenses
Operational Expenses

 

We calculate Net profit as (Gross Profit – Operational Expenses) and display that in the Report sheet.

Finance - Monthly Metrics
Finance – Monthly Metrics

Viewing and Printing Invoice and Purchase Orders

Posted on

Let’s learn how to view and print invoice and purchase orders in the Retail Business Manager Excel Template.

Retail Business Manager Excel Template – Product Page        Support Page

 

VIDEO DEMO

 

View Invoice

To view the invoice, let’s go to Invoice sheet and enter the order number at the top. If we enter an invalid order number or an order that is not a Sale or Quote order type, we will see this warning message.

Invoice - Invalid Order Number
Invoice – Invalid Order Number

 

Let’s enter S1 as Order Number. The invoice will now auto-populate. It’s that simple. You can generate an invoice whenever needed. The data comes primarily from Order Details sheet and some information from Partners, Orders and Products sheets.

Invoice - Sale Order
Invoice – Sale Order

 

 

Check Inventory Availability

Now, before we print this and send to customer, we need to make sure that there is enough inventory to fulfill this order. On the right side of the invoice, the information on inventory availability is displayed.

Inventory Availability - Yes
Inventory Availability – Yes

 

Now, we can see that the order can be fulfilled as we have both products available in enough quantity. (see INV FLAG)
In this example, the expected date of sending the order is May 6th.  Since inventory of product as of May 5th is greater than or equal to the all sales expected of the product on May 6th, we will see a check mark indicating that there would be no problem in fulfilling the order.

 

If the purchase order P1 reaches our inventory on 6th (Expected date = May 6th), then we see a warning sign.

Inventory Availability - Warning
Inventory Availability – Warning

 

Since the inventory of product as of May 5th is not sufficient, but we expect some purchase orders to reach our inventory on May 6th and that will help us fulfill all the sales orders on May 6th, then warning flag is shown indicating that it is possible to fulfill the order, but would be very close as it is dependent on when exactly the purchased products will reach and when the products need to be sent to customers.

 

If the purchase order P1 reaches our inventory on 7th (Expected date = May 7th), then we see a Not Available sign.

Inventory Availability - Not Available
Inventory Availability – Not Available

As inventory of product as of the end of May 6th will be negative – indicating that the inventory available as of May 5th + all purchases expected on May 6th will not meet the sales needs on May 6th, then Not Available flag is shown.

 

This is how we can evaluate the feasibility of fulfilling a sales order before agreeing with the customer. If we find that we don’t have enough inventory, we have two options. 1) We can create a purchase order such that it will arrive by the expected date in time to fulfill the sales order. 2) We can negotiate with the customer and change the expected date of the sales order assuming another purchase order is on its way.

Printing Invoice

Press Ctrl+P to open the print preview dialog box. Preview the sheet and then click print.

Printing Invoice
Printing Invoice

 

By default, one page is printed that can handle up to 20 lines of the order. If the order has more than 20 lines, we have to select the second page and add to print area first. Then, we have to change the number of pages to (1 to 2) in the Print Preview dialog box so that 2 pages can be printed. The template can handle up to 65 line items.

 

Exporting to PDF

To export as PDF, Click File – Export as PDF.

Export Invoice to PDF
Export Invoice to PDF

 

Choose a place to store the file, name it as desired (recommend adding the order number and/or invoice number) and click Publish.

Export to PDF - Save File
Export to PDF – Save File

 

Then, we can email the file to our customer.

 

View/Print/Export Purchase Order

To view a purchase order, let’s go to Purchase Order sheet and type our Order number. The purchase order form gets auto-populated.

The only differences between the Invoice and Purchase Order sheets are 1) fields displayed are slightly different and 2) inventory availability check is present in Invoice and not in Purchase Order.

Purchase Order - Sample
Purchase Order – Sample

Viewing, Printing and Exporting steps are exactly the same as in Invoice. Please follow steps outlined above under Invoice.

Entering Orders in Retail Business Manager

Posted on

In this article, we will see the different types of orders and how we can enter them in the Retail Business Manager Excel template.

Retail Business Manager Excel Template  –  Product Page     Support Page

Types of Orders

There are 5 types of orders that can be handled by this template. Before we look at the 5 types, let’s understand that each order will have 2 dates. Order Date and Expected Date.

Order Date is the date when the order is placed. Expected Date is the date when the inventory is impacted.

5 types of orders

  1. PURCHASE When we purchase products from our suppliers, we do so by entering a PURCHASE order. This order will impact inventory calculations by adding the purchased inventory on the Expected Date.
  2. SALE When we sell products to our customers, we do so by entering a SALE order. This order will impact inventory calculations by removing the sale inventory on the Expected date.
  3. QUOTE When we want to provide a sales quote to our customers, we use the QUOTE order type. This order will not impact inventory calculations.
  4. ESTIMATE When we want to create an estimate of purchase order to our suppliers, we use the ESTIMATE order type. This order will not impact inventory calculations.
  5. ADJUST Sometimes we may lose products due to damage or expiry or other reasons. We would want to adjust our inventory and then we can use ADJUST order type. We can create an ADJUST order and enter negative quantity values to reduce inventory or positive values to increase inventory as needed. This can be used to adjust our inventory numbers to ensure that the numbers match the inventory on hand.

 

Now, We will see how we can create them.

 

VIDEO DEMO

 

Creating a Purchase Order

Let’s start with entering a simple purchase order where we are purchasing 2 products from our supplier ‘ABC Company’. We enter order in two steps. First, we will enter in the Order Headers sheet.

Entering a purchase order
Entering a purchase order

 

The Order Number should be unique. In other words, each order should be entered in one and only one row. The field should not be blank. We can enter any method of numbering orders. The template does not limit that and does not create any pre-defined order numbers. Here, we have entered ‘P1’ as order number, to reflect that it is the first purchase order we are entering.

The order is placed on May 1st and the products on the order will reach our inventory on 5th May.

Additional Fields

Purchase Order - Additional Fields
Purchase Order – Additional Fields

 

  • PAYMENT DUE DATE Date when the payment for the order is due.
  • OTHER CHARGES Any additional cost on the order. For example, shipping charges.
  • ORDER DISCOUNT Any additional order level discount amount. We will be entering product level discounts later.
  • ORDER NOTES Enter any notes we would want to appear on the purchase order.
  • INVOICE # This number will appear on the invoice for sale orders and is not needed for Purchase orders.
  • TAX RATE Tax Rate % applicable for this order.
  • PAID AMOUNT Any amount that has already been paid to the supplier for this order.

 

Now, let’s enter the products or items that are part of the order. We go to the Order Details sheet.

Purchase Order - Order Details
Purchase Order – Order Details

 

We have entered 2 rows of data. There are only 4 input fields are Order Number, Product ID, Quantity and Unit Discount (optional).

Here, we are ordering 15 units of BR (Boys-Red shirts) and 25 units of GR (Girls-Red shirts). The unit price is auto-populated based on what we had entered in Prices sheet. Let’s see how the calculations work.

For BR, we don’t have any discount. So, Amount before Tax = 15 X 5 = 75. Tax is 10% and hence 7.5. So, Total Amount After tax = 82.5

For GR, we have a discount of 1 per unit. So, Amount before Tax = 25 X (5-1) = 100. Tax is 10% and hence 10. So, Total Amount After tax = 110

This order will now increase the inventory of these two products as of May 5th (expected date).

If you are just creating an estimate and do not want to update inventory calculations, then choose Order Type to be ‘Estimate’.

 

Creating a Sale Order

Entering a sale order is exactly similar to the purchase order except the Order Type is chosen to be ‘SALE’.  In the Order Headers sheet, we have entered a Sale order now where we are selling products to our customer ‘XYZ Company’.

Sale Order
Sale Order

 

In the Order Details sheet, we enter the products on the order and quantity

Sale Order - Order Details
Sale Order – Order Details

 

This order will now decrease the inventory of these two products as of May 6th (expected date).

If you are just creating a quote and do not want to update inventory calculations, then choose Order Type to be ‘Quote’.

 

Handling Supplier Return

Let’s say that we received the purchase order and found that 5 units of the product BR were damaged. So, we want to return them.

We will enter a new order PR1 as shown below. Order Type is still ‘Purchase’.

Purchase Order - Return to Supplier
Purchase Order – Return to Supplier

 

Then, we will add a row in the Order Details sheet where we will enter -5 quantity of product BR.

Purchase Order - Return to Supplier - Order Details
Purchase Order – Return to Supplier – Order Details

 

This will reduce the inventory of the product BR by 5 units.

 

Handling Customer Return

Let’s say that customer XYZ company returned 5 units of product GR to us. We will enter a new order SR1 as shown below. Order Type is ‘Sale’.

Sale Order - Returned by Customer
Sale Order – Returned by Customer

 

In Order Details, we will enter the quantity as -5.

Sale Order - Returned by Customer - Order Details
Sale Order – Returned by Customer – Order Details

 

This order will now increase the inventory of the product GR by 5 units as of May 10th (expected date).

 

Creating an ADJUST order

After we review the returned products, we realize that the product cannot be sold anymore and should be considered as waste. In order for our current inventory calculations to reflect that, we can enter a new order of type ‘Adjust’.

Adjust Order
Adjust Order

 

In the Order Details, we enter the quantity as -5 (negative value as we want to reduce the inventory). To ensure that the order doesn’t impact the finances, we enter Unit Discount the same as Unit Price. This will make the Amounts zero.

Adjust Order - Order Details
Adjust Order – Order Details

 

This order will now reduce the inventory of GR by 5 units as of May 11th.

Getting Started (Initial Setup) – Retail Business Manager

Posted on

In this article, we will discuss how we can do the initial setup of our Retail Business Manager Excel template.

Retail Business Manager Excel Template – Product Page   Support Page

One of the important features of our templates is that they are simple to learn and use. We can complete the initial setup in just a few minutes.

 

VIDEO DEMO

 

Step 1. Enter Business information

Enter our Business Information in the Settings sheet. This information will be used to auto-populate in our invoice and purchase orders.

Settings - Business Information
Settings – Business Information

 

Step 2: Enter product categories

Enter the list of Product Categories. This is helpful in analyzing our business performance later. Please enter groups in which you would like to categorize your products.

Settings - Product Categories
Settings – Product Categories

 

We have entered two product categories for this demo.

 

Step 3: Enter list of Products

Let’s enter 4 products and its related information in Products sheet.

Enter Products
Enter Products (click on image to enlarge)

 

  • ID Unique identification of product. This has to be unique. Please do not repeat the same ID or leave the field blank.
  • NAME Name of the product
  • DESCRIPTION Description of the product, as needed
  • STARTING INVENTORY This is the quantity of the product we have when we begin using the template. This is entered only once and does not have to be updated daily. To keep it simple for this demo, I have entered 0 as starting inventory.
  • RE-ORDER POINT The quantity of product at which you would like to replenish by ordering.
  • UNIT This is how we measure this specific product. This can be used to display on the invoice.
  • CATEGORY Product category to which this product belongs.
  • TRACK INVENTORY If we have products we don’t want to track inventory, we can just enter NO. If we want to track inventory, just leave it blank. By default, inventory will be tracked.
  • TAXABLE  If we have products that are not taxable, we can enter NO. If tax is applicable, just leave it blank. By default, tax will be applicable.

 

Step 4: Enter product prices

We will enter the purchase and sales prices in the Prices sheet. These prices will auto-populate on the orders later and help save us a lot of time.

Enter Product prices
Enter Product prices

 

We would enter the Product ID, Effective From Date and then the two prices. The Effective From Date represents the date when the prices became effective. In this example, I am beginning to use the template on May 1, 2016. So, I will enter ‘01-May-2016’ as Effective From Date.

Purchase Price is the price we pay to purchase a product from our suppliers.

Sales Price is the price at which we sell the product to our customers.

If a product’s price changes in the future, 1) enter a new row for the product, 2) enter the date when new price becomes effective as Effective From Date and 3) enter the effective purchase and sales prices.

Note: please enter both purchase and sales price even if only one has changed.

 

Step 5: Enter Partner information

In the Partners sheet, we will enter the list of our suppliers and customers together. In case a partner is both supplier and partner, you will still enter that partner only once. Partner Name should be unique here. Do not leave it blank.

Enter Partner (Customer and Supplier) information
Enter Partner (Customer and Supplier) information

 

Step 6: Set up Invoice Sheet

Invoice sheet is the printable invoice that can be automatically created for any sales order. This sheet is very customizable to suit your needs.

First, let’s update the logo. Right click on the existing logo and click on Change Picture.

Invoice - Change Logo
Invoice – Change Logo

 

Then, choose your logo image from your computer and click on Insert. Now, your logo will appear on the invoice when printing.

To change the size or other attributes of the logo image, press Ctrl+1 or click the Picture Tools — Format ribbon.

Then, let’s review the fields that are displayed on the invoice.

 

Sections of the Invoice
Sections of the Invoice

 

There are 4 key sections in the invoice: Customer Info (8 fields), Order Info (5 fields), Product Info (8 fields) and Order Total Info (6 fields). If all the fields shown by default will meet our needs, we do not have to change anything. We can move to the next step.

All these four sections can be customized.

  • If you do not need a certain field, just click on a field name and press the Delete key.
  • If you need to choose another field instead, click on the field name. You will see a drop down menu to choose from a list of available fields. Just select the one you need. 
Customize the fields shown on invoice
Customize the fields shown on invoice

 

Let’s save the file by pressing Ctrl+S. Now, everytime when we create our invoices, it will have the information we need. It’s just that simple.

 

Step 7: Set up Purchase Order Sheet

Purchase Order sheet is the printable purchase order that can be automatically created for any purchase order. This sheet is very customizable to suit your needs, just like we did with the Invoice sheet. The only difference is that the default set of fields shown in Purchase Order slightly varies from Invoice.
First, we will update the logo as we did in the Invoice sheet. Then, let’s review the fields that are displayed on the Purchase Order.

Sections of Purchase Order
Sections of Purchase Order

 

There are 5 key sections in the PO: Our Business Info, Supplier Info (5 fields), Order Info (5 fields), Product Info (8 fields) and Order Total Info (6 fields). If all the fields shown by default will meet our needs, we do not have to change anything. We can move to the next step.

All the four sections (except Our Business info) can be customized.

  • If you do not need a certain field, just click on a field name and press the Delete key.
  • If you need to choose another field instead, click on the field name. You will see a drop down menu to choose from a list of available fields. Just select the one you need.

Let’s save the file by pressing Ctrl+S. Now, every time when we create our purchase orders, it will have the information we need.

 

Step 8: Set up Currency symbols

By default, the template does not display any currency symbols as this is meant to be a tool that can be used by any business in any country. However, it is set up to easily apply currency symbols as needed.

We can choose a sheet where we want to apply currency symbols. Let’s take Invoice sheet.

a. Press Ctrl+G to open the Go To dialog box.

Go to Currency Cells
Go to Currency Cells

 

b. Find the name ‘CURR’ and click OK.

Excel will now select all the cells for fields UNIT PRICE, UNIT DISCOUNT, TAX, AMOUNT AFTER TAX and the Order Total section fields. This is assuming you have not modified the invoice fields. If you have changed or re-ordered the fields, then, please select the appropriate cells you need to apply currency symbols.

c. Press Ctrl+1 to open the Format Cells dialog box.

Change Currency Symbols
Change Currency Symbols

 

d. Select ‘Currency’ in the Category and then choose the symbol from the drop down. Click OK. This will now apply the chosen currency to all the selected cells in the Invoice sheet. Please follow the same procedure in other sheets if you need to see currency symbols.

The calculations in the template are not impacted by currency symbols. This is purely for visual presentation.

Retail Business Manager – Excel Template – Support

Posted on

Welcome to the support page for Retail Business Manager Excel Template. The following articles will provide information on how to use the template. Please follow the links provided to read more about each topic.

 

If you prefer to watch video demonstrations, instead of reading articles, please watch this playlist.