In-Market Inventory Calculator

Overview

In-market sales and inventory data may be stored in IFP sales databases. Reports may be created that calculate implied in-market inventory units from primary sales forecasts, in-market demand forecasts and an initial in-market inventory level.

This can be achieved in a very flexible manner using Crosstabulation Analysis.

Crosstabulation Definition

A comprehensive crosstabulation definition is shown below. This definition is written in a generic manner allowing the use of any 3 selected files.

However, files should be selected as follows:

File #1 = Primary sales forecast

File #2 = In-market inventory file

File #3 = In-market demand forecast

Note the following aspects of this definition:

Column 1

Type   = 14 (Selected File/Period Column)
Code   = CMCM01 (Current month value for file 1)
Header = Units|Month|F1~ (1st line = Units, 2nd line = Month, 3rd line = file 1 label)

Column 3

Type   = 7 (Variance Column X vs Column Y)
Code   = 0102 (Column 1 vs Column 2)
Header = Units|Month|F1~ (1st line = Units, 2nd line = Var. vs., 3rd line = file 2 label)

Column Header Merging

Columns 1 to 7 use exactly the same first header line (Units) to ensure that headers for these columns are merged.

Columns 1 to 2 use slightly different 2nd line headers of 'Month' and ' Month'  to ensure that headers for these columns are not merged.

Advanced Options

Note the use of Advanced Options > Data type override to set the data type to be used for each column. For example, columns 8, 9 and 12 have 'Data type override' set to Value.

Column Definitions

Option Selections

To create the report proceed as follows:

1. From IFP Home, select Crosstabulation Analysis > Custom File Selection and select any 3 files. Note that file 1 should normally be CY actuals. Files 2 and 3 may be any other files (typically plan and PY actuals).
   
2. Note that you should select the full year (January to December) for this report. You must not select any variance option 'Period totals only).
   
3. You may select any options for the following:
   • Report definition
   • Items
   • Levels of detail
   • Inclusion options (only one data type may be selected, normally units)
4. Crosstabulation Definition should be In-MarketInventoryCalculator

Example Report

A small part of the resulting report is shown below. The complete example report is attached to this topic (In-MarketInventoryCalculator.xls).

Adding Crosstabulation Definition to Your Database

The attached definition may be installed in any sales database as follows:

1. Save the attached file, In-MarketInventoryCalculator.ifp, in any convenient folder on your local server
2. In your sales database, select Reporting > Crosstabulation Manager
3. Select the General folder.
4. Click Tools > Import and select the file you saved in step 1.

Variants

Variants of the above report may easily be constructed.

Downloads

Below are example check reports and crosstab definitions for In-MarketInventoryCalculator.