Skip to content

PL/FC🔮DATASET

image-20221107164005052

Builds a PL/FC dataset from a baseline 12-month calendar or rolling Actual dataset. By PL we mean Plan (next year's budget), by FC we meant Forecast (modified forecast during year).

Input is a dataset with 12 months of Actual data, either calendar or rolling. Output is a dataset with both the Actual and the PL (or FC) data, calculated modifying the Actual data with the hypothesis inserted by the user. The user can then run this file in the app, check the results of the hypothesis and if necessary iterate.

Setting it up

This widget will simply tag the forecasted rows as PL (plan) or FC (forecast).

image-20221107122244984

In terms of seasonality, by default the monthly profile of the PL/FC file is the same as the profile of the actual data file. Future seasonality is the same as past seasonality. It is also possible to build the PL dataset with all identical months or to set seasonality. On "Set", you can provide a seasonality parameter for each month.

image-20221107122448830

Select what forecast to use when one dataset row matches more than one forecast condition. For instance, if the user wants China sales up 10% and Supermarket sales down 5%, the system needs to know what to do with China sales in Supermarkets. The choices are "All" (in this case it will apply the product of the forecasts, here 1.1 x .95) or "First" (in this case it will use the first forecast condition that the user has keyed in).

image-20221102110721854

If other metrics beyond Sales or Costs (typically discounts and/cogs) are present in the dataset, choose whether to set the forecast of these metrics proportionally to the change of Sales and Costs, or whether to set the forecast separately for each metric.

image-20221017150002630

Set the default forecast PL/FC vs AC change in units and in price. This will forecast will be applied to all rows of the dataset.

img

Now add the first dimension for which you want to forecast something different from the default above.

In this example, Return is added as an dimension with the value "No". When this condition is met units and prices should go up 5% more than the default (so 1.05 x 1.10 for units and 1.05 x .95 for price).

image-20221102111810811

The user can add other items (click on New item) to the Return dimension, and for instance specify that in case of Yes, units and prices will go up 10% more than defaults (so 1.10 x 1.10 for units and 1.10 x .95 for price).

image-20221102112223230

The user can add new dimensions (click on New dimension), here Category, and specify that Office Supplies forecast modify default by 1.10 in units and .90 in price.

image-20221102112359264

Sales of No Return and Office Supplies Category will be treated based on the setting of the "If collision between dimensions" widget. If "All" the app will calculate the product of the factors (here for units 1.05 x .90 for units). If "First" it will take the First forecast, here "Return: No" (1.05 for units ). The resulting factor will be multiplied by the default forecast.

You can also select multiple dimensions, and treat sales of No Return Technology Category as a single item and set their forecast modifier. Here +1.30 in volume and +1.30 in price. Note that since the intersection has been specified by the user, in this case there will be no multiplication of factors, even if "Multiply" has been selected. The Category=Office Supplies + Return=No condition pair overrides the separate Category=Office Supplies and Return=No conditions.

image-20221102112743167

Similarly in this case.

image-20221123123242447

The two conditions will be processed independently.

The rows where Category = Technology and Segment = Consumer will be modified as 1.1 (default) times 1.2 (condition), while the rows where Segment = Consumer (and Category != technology) will be modified as 1.1 (default) times 0.1 (condition).

The system will work similarly in the case of hierarchical items. For sales in Rome, only the "Rome" 1.2 multiplier will be applied, and it will not be multiplied by the 'Italy' 1.10 multiplier.

image-20221102113826518

You can add up to 6 sets of dimensions. For each set of dimensions you can add 5 items or sets of items each with a different set of forecasts.

image-20221107123013266

If you have chosen to set the monthly time profile yourself twelve widgets will show up. The widgets are initialized to 100 and the sum of their values must always be 1200.

image-20221107123454857

You can modify the widgets to increase of decrease the weights of the different months and create the seasonality you desire. You might want to model this in Excel first to save time. The app will give a warning and ignore your monthly modifiers if the total is not 1200.

image-20221107123814725

When you are done, hit the Submit button.

image-20221107123301264

Click on the download widget to get the PL/FC dataset. The dataset will have both PL/FC data and the 12 actual base months.

image-20221107123245141

You can load the dataset on the app to see how your hypothesis pan out together, and iterate until you are happy. For speed open a second, new, 'Mparanza session.

Output

This is the result. White is Plan, black is base year Actual.

waterfall

Notice the boost of Return = No, Category = Office Supplies sales.

multi_tier_bar

To use the file for your reporting, open it in Excel and delete all the AC (12 base month actual) rows in the Period column. Then concatenate the PL/FC file to your new monthly actual sales data.

During the year, as your view of the future changes and becomes more precise, you add your new FC (forecast) data to your dataset and to your reports. Open the file in Excel, delete the AC row, select the FC rows of the periods you want and collate them to you original AC + PL file.

year_over_year_column

Forecast data is shown hatched in the chart above per IBCS standard.

Limitations

Please note that the system does not manage PL sales of "new" items, at least not as separate rows in the PL dataset. "New" items are items not present in the Actual dataset, such as new products (aka product that will be launched), new countries (countries where there are no sales at the moment), new customers, new channels.

If 1000$ of sales are forecasted for a new product, or if 1000$ of sales are forecasted for a new country, this can be managed by setting the sales of the "father" dimension. Upping the sales of the relevant "Category" - father of "Product" dimension - and/or of the relevant "Region" dimension -father of the "Country" dimension - by 1000$. There will be no specific "Product" or "Country" PL row/s for the 1000$ forecasted sales of new product and/or the new country.