We know there are plenty of situations in which you have a general understanding of what your costs, sales or any other targets may be. For example, sales incentives shouldn’t be higher than 10% of sales and cost of sales should be around 40%. Ideally you want to plan with those percentages in mind, but planning with percentages can be tricky when using Excel. It forces you to either plan with percentages or with nominal values. SAC Planning however allows for easy adjustment of percentages and real-time calculation of nominal values based on these percentages. In this quick start guide, we walk you through how it works.
Before we dive into how to make a financial plan with percentages, let us explain the example below. In this example the sales incentives are planned in percentages (we start with 8%). The costs are a result of this. Based on this example, which will change throughout this blog, we'll demystify the concept of planning with percentages using a simple three-step setup. Get ready for financial planning to become as easy and enjoyable as your favorite game.
Tip: We recommend having the member ID stay close to your original account. In this case our Sales Incentives account is LTOP_22700. The corresponding account we setup is LTOP_22700%
The real calculation is: Sales incentive% = Sales incentive / Sales. This formula would work and shows results, but it is not plannable on the sales incentive %. Therefore, the inverse formula should be used:
Inverse [sales incentive] := [sales]*[ sales incentive%])
Or in member ID’s:
This results in the following formula:
[LTOP_22700]/[LTOP_11100] | INVERSE([LTOP_22700]:=[LTOP_11100]*[LTOP_22700%])
The initially setup formula is always leading. Meaning: If new sales or incentives amounts are loaded or added, the percentage will be recalculated (Sales incentive% = sales incentive / sales).
Note: we used the “new model” with a measure “local currency” and added the account dimension.
After setup you need to build a planning enabled table. With this table we create a structured framework where we can put in and manipulate financial data. We will give you 4 examples in which we change the percentages slightly and see what happens. Let’s have a closer look at the example below. Here you see that we have changed the sales incentives % of sales in March 2023 to 10%. And that means that the amount changes immediately too.
In this second example we decided to increase the sales value to 150,000. The sales incentives percentage for March naturally changes to reflect this adjustment. Since March is part of a quarter, this tweak ripples through, causing changes in the quarterly values for both sales and sales incentives percentages. Now, here's the key part: even though you increased the sales value, the actual sales incentive amount remains the same. Why is this important? Well, it ensures that every time you add new sales or make plans, your sales incentives won't keep fluctuating wildly. In other words, it keeps things from getting too "dynamic" and helps you maintain a steady course in your financial planning.
In the next example we made a quarterly adjustment to your sales incentives, setting it at a steady 15%. As you change the incentive percentage on this broader timeframe, the monthly values for both sales incentives and sales incentive percentages adapt, considering the distributed weights of the initial state. This means that the changes cascade in a balanced manner across the months, ensuring a harmonious alignment of your financial strategy throughout the quarter.
In this last example we decided to make a yearly adjustment to the sales incentives by setting it at a fixed 200.000. As you tweak the incentive amount on this annual level, the monthly values for both the sales incentive amount and sales incentive percentages adapt in harmony. The adjustment considers how things were initially distributed, making sure your financial plan stays steady and consistent throughout the year.
This is just an initial impression of how inverse planning works for SAC Planning. It is simple and quick but above all, it happens in real time. We have seen how it is appreciated by the business in practice. We encourage you to try to set this up and experience it for yourself.
Want to see this happening in real time? Click on this link and watch it on McCoy TV.