Price Discount Models

Overview

This topic shows how to manage price assumption data for a sales database.

The basic approach used here is as follows:

1. Import or enter via forecast editors assumptions on monthly gross prices for each year into a gross prices file (ASPG)
2. Import or enter via forecast editors assumptions on monthly discounts for each year into a set of percent discount files (DIS1, DIS2, DIS3)
3. Run a report summarising all assumptions for each item code and export the report into an Excel file
4. Import  calculated monthly prices after each set of discounts for each year into a set of discounted price files (ASP1, ASP2, ASP3)

Summary File Usage

Discount Percentage Files

These files must be assigned to Unit Rates in order to avoid percentages being converted from one currency to another when using them for importing and reporting purposes. A unit rates table simply contains a rate of 1 for all months for all years for all currencies. This will ensure that percentages are not 'converted' into different values when you run reports in a currency other than base currency.

Importing Price Model Assumption Data

The same import procedure may be used for both gross price and discount percentage data. Discount data must be entered in the templates as simple numeric values, e.g. 25.0 for 25%. Cells should not be formatted as percentages.

Here you should include ASPs only and not select Add to existing values.

You must select the relevant currency (normally Euros) for all types of price and discount percentage data.

You must select the mapping file Price Modelling\Prices or Discount Percentages from Price Template. 

The sample of imported data should look similar to that shown below:

Creating Assumptions Report

This report must contain all relevant assumptions on gross prices and discounts for the year concerned. The steps below relate to the case where 3 separate discounts are to be applied.

1. Select Analysis vs Time with the following files:
   
   Ensure that you select the Advanced option Rolling periods.
   Note that it is useful to define Data Favourites for these options, e.g. Gross to Net Assumptions CY.
2. Do not select a report definition
3. Select the required items (sales item codes only)
4. Select level of detail Item Code + Grand Total (to avoid including sub-totals)
   
5. Select the relevant currency (normally base currency) and include ASPs only.
6. Export the resulting report into an Excel file (see example, GrosstoNetAssumptions.xls provided for your database)
7. Remove the first header row in the report , so that month labels appear in first row (see example, GrosstoNetAssumptions_Edited.xls provided for your database).

Importing Calculated Net Price Data

1. Select Data File Manager > relevant target file for calculated prices, e.g. 2013 ASPN
2. Select Tools > Import > External data or file
3. Select target period, relevant currency (e.g. Euros), include ASP only
4. Select edited assumptions source file created above, e.g. GrosstoNetAssumptions_Edited.xls
5. Load the relevant field mappings file, e.g. Gross less Discount 1+2 (see note below)
   
6. Check that the preview of calculated ASPs looks reasonable
7. Ensure that all data are imported successfully (Grand Total row will not be imported)

Note:
Separate field mappings files are available for each combination of discounts.
Additive and multiplicative percentage models are available.
In additive (default) models discount percentages are effectively summed and applied to the gross price.
In multiplicative models successive discount percentages are applied to the reducing price.

Checking Calculated Net Price Data

This report should contain all relevant assumptions on gross prices and discounts together with the calculated ASP file for the year concerned. The steps below relate to the case where 3 separate discounts are to be applied.

1. Select Analysis vs Time with the following files:
   
   

   Note: It is useful to define Data Favourites for these options, e.g. Gross to Net ASP Files (3 Discounts).

2. Do not select a report definition
3. Select the required items (sales item codes only)
4. Select level of detail Item Code + Grand Total (to avoid including sub-totals)
5. Check that the resulting report shows expected prices, e.g. 2.97 less (3.00% + 2.36%) = 2.82