You are here: Version 12.0 > Turn and Earn Report

Turn and Earn Report

Feature Reference#: 12319

This feature adds a Turn and Earn Report, a measure of profit and turns of your inventory. Distributors often use a Turn and Earn ratio for distinguishing items that are high performers from those that are low performers. When you use turn and earn analysis, you can accurately assess items with a high profit margin that may have a low turnover rate, making them potentially less profitable than items with a lower profit margin that may have a higher turnover rate.

Now you can run the Turn and Earn Report to determine item performance by using both profitability and inventory cost information. Turn and earn analyses can be performed by supplier line or product group. The system records information at the location item level. The report displays turn and earn ratios as well as turn and earn percentage values.

Calculating Inventory Value in Demand Year Maintenance

or

To calculate the average inventory value needed for the turn and earn analysis, the system stores an inventory value for each period for each item location.

Important! You will be responsible for collecting this information by saving inventory values at the same time each period. More information on saving and storing this information can be found in the following paragraphs.

When the Demand Year Maintenance option is selected and a Year value entered, a window similar to the following appears:

Field:

Description:

Update Inventory Value

Use this field to save the inventory values for a given period. When this checkbox is checked and the window is saved, the current inventory value for each item at each location is saved in the system for the period associated with today's date.

This checkbox can be checked multiple times per period. These multiple updates result in the inventory values for the current period to be added together and divided by the number of times that this Update Inventory Value checkbox was checked and run for the current period.

Inventory value is calculated by multiplying the Inventory Costing Basis (Standard, MAC, FIFO or Lot Costing) by Free + Allocated. If the System Setting, Use Lot Cost as Inventory Cost is enabled, individual items that are lot costed are valued by their lot costs [sum of (lot qty * lot cost)], while other inventory items are valued using the Inventory Costing Basis.

Note: If Special Costing is used on an order, then this cost assigned to the order is captured during the Update Inventory Value process. Because the special cost is displayed on the Inventory Value Report, this cost must be present on the Turn and Earn Report as well.

Generating the Turn and Earn Report

The Turn and Earn Report provides you with turn and earn ratios for supplier lines, product groups, or individual items for the selected period, year, or 12-month prior period. The turn and earn ratios help you assess your products using both profit and turns. Usually, turn and earn analyses are more meaningful for groups of items, such as a supplier line or a product line, than for individual items.

Note: The system only calculates turn and earn ratios for stock items.

If inventory is sourced from locations other than the sales location, turns and turn and earn ratios are more meaningful for a group of locations, such as company-wide or by purchase group. This is because sales information is used in conjunction with location inventory values.

Product Group

Profit Percent

Inventory Turns

T & E

A

30%

1

.30

B

10%

4

.40

Note: Even though the profit percentage is much higher on Product Group A, Product Group B is more favorable when inventory and profit is being considered in conjunction with profit.

When you select to run the Turn and Earn Report, the following criteria window appears:

Field:

Description:

Company ID

Your company ID. Initially, the information from the user record is the default value.

Report Basis

Determines if the Turn and Earn value is calculated for the company as a whole or for each location.

  • Company - Turn and Earn value is calculated for the company as a while. This is the default.
  • Location - Turn and Earn value is calculated for each locations.

Location ID

The Location To and From fields can be accessed only when the Report Basis field is set to Location. You can enter a range of locations on which to generate the report.

Report By

You can choose to run the report by:

  • Primary Supplier - Choose to sort the information on one or more primary suppliers.
  • Product Group - You can sort the report by a range of product groups.
  • Item - The system sorts the report information by the item range you select.

Primary Supplier ID

By entering a range of primary supplier IDs, you identify the primary suppliers on which the report is run.

Product Group ID

Enter a range of product group ID codes on which to generate the report.

Item ID Enter a range of item ID codes on which to report. These fields can only be accessed when the Report By field is set to Item.
Period Identifies the range of periods on which to base the report.
Year The system generates the information based on the year you enter.

 

When the parameters are complete and the report printed, a report similar to the following is generated:

Field:

Description:

Sales

Total dollar value of sales.

Cost

Total cost value of items sold.

Gross Profit

Total of sales minus all costs directly related to those sales.

Profit %

Equals Gross Profit x 100 / Sales

Turns

Equals (Cost of Goods Sold / Average Inventory Value) * (Number of Periods per Demand Year / Number of Periods of Sales Reported)

More information on the Turns calculation is explained in the next section.

Turn & Earn

Equals Profit % * Turns

Equals (Gross Profit * Cost of Sales) / Average Inventory Value

The Turn and Earn Ratio is calculated for only stock items; direct ship items are not included.

Average Inventory Value Identifies the range of periods on which the report is based.

When you run the Turn and Earn Report by supplier, the supplier’s name and ID information are displayed, and totals for each supplier are shown. If you sequence the report by product group, the product group description is shown with totals by product group. And if you generate the report by item, the item ID and description is listed with totals displayed for individual items.

No matter how the report is sorted (by supplier, product group, or item), the system displays three sets of values: the value of the current report period, the total value for the current report year, and the total value of the prior report year.

Sales values (Sales, Cost of Goods Sold, Gross Profit, and Profit %) are the total of invoiced sales of stockable items. Only stockable items are reported and direct shipment sales are excluded from the totals.

If there are no sales for a Supplier/Product Group/Item combination during the reported period/year, the Turns is reported as zero (0). If the Average Inventory Value for the Period/Year is 0, an asterisk (*) displays in the Turns column.

Calculating Turns

Inventory turns are only calculated for items you carry in stock. All turns figures are presented as the number of turns per year.

The formula for calculating an inventory turns is:

(Cost of Goods Sold / Average Inventory Value) * (No. of periods per demand year / No. of periods of sales reported)

Field:

Description:

Cost of Goods Sold

Total cost of invoiced lines, excluding direct ship sales.

Average Inventory Value

Average of the inventory values saved for each demand period.

  • For the Report Period, this is the inventory value for this period only, such as Period 5.
  • For the Report Year, this is the sum of the inventory values for all demand periods in the report year, divided by the number of periods that have inventory values. For example, if only the first five periods of the year have inventory values, the average number would be calculated using those five periods.
  • For the Prior Year, this is the sum of the inventory values for all demand periods in the year prior to the report year, divided by the number of periods with inventory values.

The second part of the Turns equation (number of periods per demand year / number of periods of sales reported) annualizes the turns.

Field:

Description:

Number of Periods per Demand Year

Total number of demand periods in the year.

Number of Periods of Sales Reported

Number of periods of sales being included in the turns calculation.

For example, a company has the demand period defined as one calendar month in a year. They wish to run a Turn and Earn Report for Period 6 (the month of June- the Report Period). The turns for Period 6 would be multiplied by 12/1. The turns for the whole year (January through June – The Report Year) would be 12/6. The turns for the whole of last year (the Prior Year) would be multiplied by 12/12.

If there are no sales for a Supplier/Product Group/Item during the reported period/year, inventory turns is reported as 0. If the Average Inventory Value for the Period/Year is 0, an asterisk (*) will display in the Turns column.

Turn and Earn Ratio

The Turn and Earn Ratio is calculated only for stock items and does not include direct ship sales. The formula is determined as:

Turn & Earn Ratio = Profit % * Turns
  = (Gross Profit * Cost of Sales) / Average Inventory Value