Sales planning in Excel requires a lot of manual work and great effort.
- Creation of individual files for each sales group
- Selection of top N customers
- Submitting of files
- Consolidation of files
- Time for consultation
- Correction of errors.
Sales planning in Sesam provides a central application to insert plan data which uses existing actual data in the system and planning functions for calculations and data distribution.
The initialization of plan data is executed in two optional steps:
- Initialization of planned sales 2018 based on plan data 2017, forecast 2017 or with zero values.
- After that the planned sales for 2018 can be distributed on the months January to December. Sesam provides distribution functions to distribute the value for the plan year on monthly values: distributions for the actual sales 2016, the planned sales 2017, the actual sales for the last 12 months and a linear distribution.
The monthly plan values can be manually revised afterwards. Each month can be edited after distributing the value for the plan year. Alternatively, you can insert plan values for each month January to December without executing the steps “initialization” and “distribution” at all.
The sales plan for the plan year 2018 in Sesam uses the following characteristics:
- Company code
- Sales group (Account manager)
- Customer (top n or total)
- Product hierarchy 1
- Product hierarchy 2
For these characteristics, the following key figures are planned:
- Forecast 1-12 2017
- Plan 2018 total
- Planned sales 2018 January to December
For comparison reasons and as a data base for the distribution functions the following key figures are available:
- Sales 2016
- Plan 2017
- Sales 1-7 2017: Sales for the months January to July
- Forecast Δ 2016: Difference between forecast and sales 2016
- Plan 2018 Δ Forecast: Difference between plan 2018 and forecast
- Plan 2018 Δ Sum months: Difference between plan 2018 and sum of all months
Typical planning process
This chapter provides an overview of steps to execute in a typical planning process.
1. Optional: Select customer
2. Optional: Select relevant product hierarchy 1 or 2
Right-click on the product group you want to plan. In this example it is “filter module”.
In context menu select Filter > Keep Filter Value on Axis.
After that, you have created a filter for this product group.
3. Adjust forecast 1-12 2017
4. Configure initialization settings and execute initialization afterwards.
5. Execute one of the given four distribution functions.
6. Adjust planned sales for any of the months January to December, if needed.
7. Optional: Create comment
Right-click on cell to comment and from context menu choose: “Goto > Modify Comment”.
8. Remove filter on product hierarchy level
If you have created a filter in step 2 for a product hierarchy, you need to remove the filter.
Option 1: Through context menu:
Option 2: Through ribbon
Return to step 2, if you want to plan a different product hierarchy.
9. Go back to step 1 for the next customer.
Selection Mask: Variable Entry
After calling the Sesam planning application (SDP100) this selection mask is shown.
Plan data can only be entered for one sales group within one company code.
The selection of multiple company codes or multiple sales groups is not possible.
By clicking on the screenshot it will enlarge.
- Selection details:
- Selected company code – The company code, which was previously selected in the variable entry dialog, is displayed.
- Selected sales group – The sales group, which was previously selected in the variable entry dialog, is displayed.
- Customer selection area – This area allows to select one, none (for all other customers that are not top N) or all customers for which data is planned in the input area.
- Input area – In this input area you can input sales data for customers, product hierarchy 1 and product hierarchy 2.
By clicking on the screenshot it will enlarge.
The ribbon consists of the following tabulators:
Hint: Depending on the resolution of your monitor, the tabulator “Start” might be split into two tabulators “Start (1)” and “Start (2)”. However, the controls described in this manual are always available. They might be displayed at a different location: “Start (2)”.
New – Opens the analysis selection dialog.
Open – Opens an analysis that is saved as favorite.
Saves – Saves the inserted plan data.
Print – Prints the current analysis.
Export – Exports the current analysis to Microsoft Excel.
Send – Sends the current analysis by Email using the broadcaster.
Portfolio – The portfolio contains all reports for which a user is entitled. The reports are sorted according to business departments. The business departments are displayed by tiles.
Directory – The directory shows all reports for which a user is entitled. The reports are sorted according to business departments. The business departments are displayed by a directory structure.
Favorites – In the favorites list, you can access the reports that have been saved as favorites.
History – In the course of the history, all analyzes a user has recently called up are displayed. A maximum of 15 analyzes are shown in this folder.
Logout – A user can log out of the Sesam application by clicking the Logout icon.
4.2 Start 1
Paste – Inserts one or multiple cells from the clipboard.
Cut – Cuts one or multiple cells. In order to select multiple cells execute the following steps: Select top-left corner, press and hold “shift“, select bottom-right cell.
Copy – Copies one or multiple cells. In order to select multiple cells, execute the following steps: Select top-left corner, press and hold “shift“, select bottom-right cell.
- Edit plan data:
Save – Saves the inserted plan data forecast 1-12 2017, and monthly values January to December as well as corresponding comments. Saved plan data are restored upon next call of this application. Using “Save” does not release plan data (refer to “release”).
Refresh – Refreshes all calculation. E.g. the plan value for 2018 is calculated as sum of monthly values January to December.
Reset – All data is restored from the last saved version. All data that has been entered after the last save is lost.
Cleanse – Negative plan data is set to zero values. Furthermore, forecast is adjusted if it is set to less than sales for the actual year. The column “Plan 2018 Δ Sum months“ is set to zero and the column “Plan 2018“ is calculated as the sum of the monthly values January to December. As a result plan data without reference to a month is erased.
Release – With this functionality, plan data is finally released by the planner. After releasing the plan data no further changes are possible. This step can only be undone by an Administrator.
Variables – The variable dialog is called.
Filter – The filter overview is opened.
- Edit Forecast:
Clear – Clears forecast by inserting zero values for each row in column “Forecast 1-12- 2017”.
Restore – Restors forecast to originally calculated values: Sales 1-12 2017 / 7 x 11.8.
- Initialization: plan year 2018
You don’t have to insert plan data cell by cell. You have the option to let the system calculate plan data and you can revise them manually afterwards.
Plan 2017 – Initializes planned sales for 2018 based on planned sales for 2017. This setting is used when triggering the initialization process (button “Initialize“).
Forecast 2017 – Initializes planned sales for 2018 based on the forecast for 2017. This setting is used when triggering the initialization process (button “Initialize“).
Revaluation < 12% > – During the initialization process, sales data can be increased or decreased in regards to planned sales 2017 or forecast 2017. The value range for revaluation is -10% to +99%. The default setting is +12%. Example: Select “Plan 2017” as base for calculation and apply “+12%” for the revaluation setting. As a result, planned sales for 2018 are calculated as: Planned sales 2017 x 1.12.
Initialize – Based upon the settings “Plan 2017”, “Forecast 2017” and “Revaluation” planned sales 2018 (column “Plan 2018“) is calculated. During the initialization the current customer selection (refer to section “customer selection area”) and – if available – further characteristic filters (refer to “Filter”) are taken into account.
Initialize zero values – Planned sales 2018 (column “Plan 2018”) and the monthly values are initialized with “0“. During the initialization the current customer selection (refer to section “customer selection area”) and – if available – further characteristic filters (refer to “Filter”) are taken into account.
4.3 Start 2
- Distribution: Plan year on months:
There are four alternative options to automatically distribute planned sales 2018 on monthly values January to December: A linear distribution and sales patterns for the previous year, planned values 2017 and sales for the last 12 months.
Actual 2016 – Planned sales (column “Plan 2018“) is distributed on months January to December according to actual revenues January to December 2016.
Plan 2017 – Planned sales (column “Plan 2018“) is distributed on months January to December according to planned revenues January to December 2017.
Actual last 12 months – Planned sales (column “Plan 2018“) is distributed on months January to December according to revenues of the last 12 months. Example: If planning takes place in August, the proportional value of July 2017 is applied to December 2018.
Linear – Planned sales (column „Plan 2018“) is linearly distributed to months January to December.
Heading – Headings of lines and columns can be displayed as text (default), key, and text and key at the same time.
Zero row suppression – Opens the zero row wizard.
Factor 1,000 – Each key figure is divided by 1,000.
Custom format – Opens the properties of data dialog.
Edit – Opens the dialog to edit comments. Comments can only be created for one data row and not for individual months.
Unlock user – There are circumstances when your actual browser session is not closed upon leaving the application. As a result, the application is locked for your plan data:
Under these circumstances you cannot modify or create new data. You can unlock your user with this button. However, this functionality will not unlock released data (refer to section “Release“).
Request support – Your standard email client opens with a preconfigured email template. Please describe your support request as detailed as possible and attach screenshots where applicable.
Open quick reference – The quick reference is opened in your browser.
Show background information – A dialog with background information about the current application is opened.
Customer selection area
Using the customer selection area you can define a filter for the input area. You can select all customers, one particular customer or no customer at all (all other customers that are not within top n). All customers are selected by selecting row “Overall Result”. One customer is selected by clicking on the corresponding row. Select all customers that are not within the top n customers by selecting row “# Not assigned”. Based on the customer selection, you can insert plan data in the input area.
The key figures in the customer selection area are continuously updated when inserting plan data.
Hint: You can hide the customer selection area by using the symbol in the top-right corner. With the same button this area can later be made visible again.
By clicking on the screenshot it will enlarge.
In the input area the following characteristics are displayed: company code, sales group, customer, product hierarchy 1 and product hierarchy 2. Fields with a white background are ready for manual input. That means that their content is either calculated by the planning functions or can be edited by the planner.
Company code – Selected company code. You can modify the selected company code by re-opening the application or by selecting button “Variables” in the ribbon.
Sales group – Selected sales group. You can modify the selected sales group by re-opening the application or by selecting button “Variables” in the ribbon.
Product hierarchy 1 and 2 – Forecast 1-12 2017 and planned sales 2018 are inserted on the level of product hierarchy 2.
Customer – Customer who the plan data is created for. Use the customer selection area to select particular customers.
Product hierarchy 1 – Product hierarchy 1, this characteristic is relevant for planning and therefore is always visible. This setting cannot be modified.
Product hierarchy 2 – Product hierarchy “, this characteristic is relevant for planning and therefore is always visible. This setting cannot be modified.
Sales 2016 – Actual revenues for the year 2016.
Plan 2017 – Planned revenues for the year 2017.
Sales 1-7 2017 – Actual revenues for the months January to July 2017.
Forecast 1-12 2017 – Sales forecast for the year 2017. This forecast is automatically calculated in Sesam based on the revenues for January to July 2017. The forecast is calculated as follows:
Sum of actual revenues for January to July 2017 / 7 * 11,8
This forecast can be manually revised.
Forecast Δ 2016 – Difference between forecast 2017 and actual sales 2016.
Plan 2018 – Planned revenues for 2018. This column can be initialized through function “Initialize“ in the Sesam ribbon. Alternatively, the monthly values January to December can be manually inserted. Using function “Refresh” the sum of the months January to December is calculated and the result is written into this column. This column cannot be edited manually. The value can only be adapted by changing values for columns Jan to Dec.
Plan 2018 Δ Forecast – Difference between Plan 2018 and Forecast.
Plan 2018 Δ Sum months– Difference between planned sales for 2018 and the sum of the monthly values January to December. This value can be refreshed by clicking on button “Refresh” in the ribbon. Using function “Cleanse” the column “Plan 2018“ is calculated as the sum of months January to December. As a result, the column “Plan 2018 Δ Sum months“ is set to zero afterwards.
Jan to Dec – Monthly values for plan year 2018. These monthly values can be calculated automatically by using any distribution function available in the ribbon. The monthly values can be manually edited afterwards. They can also be edited without using any distribution function.
Comments – Comments can be created for individual rows, but not for individual months. Create a comment by either clicking on any cell in this column or by using the corresponding function in the ribbon. When using the button “Edit comments” a comment is created for the current row of the cursor.
In particular when working with filters there are problems with the commenting editor. The commenting editor can always be opened by the context menu using “Goto > Modify Comment”.
Data is split into various pages whenever it does not fit into a single one. Use the corresponding arrow buttons in the footer to navigate between pages.
Frequently Asked Questions (FAQ)
#2: Why can I not edit a comment?
There might be problems creating comments especially when using filters. If you experience a problem, use the context menu to create comment via “Goto > Modify Comment”:
#3: Why am I missing the option to distribute plan year values on months?
Depending on the resolution of your monitor the tabulator “Start” in the ribbon is split into two sections:
“Start (1)” and “Start (2)”.
The option to distribute values on months is on tabulator “Start (2)”.
#4: Can you please tell me how to edit the individual product groups for % percentage growth for 2018?
Note: The following steps work for filtering “product hierarchy 2” accordingly.
Step 1 (optional): Select customer
Step 2: For planning on product hierarchy 1:
Right-click on the product group you want to plan.
In this example it is “filter module”.
In context menu select Filter > Keep Filter Value on Axis
After that, you have created a filter for this product group.
Step 3: You can now select an individual growth value (e.g. 12%) and execute “Initialize”.
Step 4: When you’re done with this group you remove the filter for this group through context menu:
Or alternatively through the filter menu:
#5 I cannot enter any data into the data cells. The area that should be input-ready doesn’t allow any data entries. The rows are displayed in white and grey. What can I do?
Users lock the data segment when they plan data. Occasionally a lock is not erased, when the user leaves the planning application. Next time the user enters the application this lock prevents him from entering data.
Users can erase their own locks as follows: