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.
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:
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)
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.
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.
To create the report proceed as follows:
- 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).
- Note that you should select the full year (January to December) for this report. You must not select any variance option 'Period totals only).
- You may select any options for the following:
- Report definition
- Levels of detail
- Inclusion options (only one data type may be selected, normally units)
- Crosstabulation Definition should be In-MarketInventoryCalculator
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:
- Save the attached file, In-MarketInventoryCalculator.ifp, in any convenient folder on your local server
- In your sales database, select Reporting > Crosstabulation Manager
- Select the General folder.
- Click Tools > Import and select the file you saved in step 1.
Variants of the above report may easily be constructed.
Below are example check reports and crosstab definitions for In-MarketInventoryCalculator.