1 2 Previous Next 15 Replies Latest reply on Aug 15, 2018 5:31 AM by Ankit Bansal

# Advanced calculation result vs budget

I have sales and budget data in my data and I would like to show the managers how much they need to increase/decrease the sales for the rest of the year to reach the budget.

For example if we have sales Jan-Jul on 500 and a budget for the same period on 1000. Then 500 needs to be additional sales for Aug-Dec to reach the budget.

In my data it means

I would like to make a calculation that looks at all full months of sales vs budget. I have Jan-Jul as full months.

We are -37 411 vs budget from Jan-Jul. The total budget for the upcoming months is 410 701.

This means that the budget has to be exeeded by 37 411 for the rest of the months to reach end of the year budget.

In percent this means that the budget has to be exeeded by 9,1% (new target (37411+410701) / old target (410701) = percent needed (109,1%)

My question is how I can do this calculation.

1. I first need to look at the sales-budget from -1 month from today to only look at full months

2. I then need to look at the budget from current month to end of the year.

3. last I need to do the following calculation: (calculation 1 + calculation 2) / Calculation 2

This formula is used in Another calculation, but I dont get it all to work in this case.

FLOAT(if [Sales_Month] >= MONTH(TODAY()) then [Sales] else NULL • ###### 1. Re: Advanced calculation result vs budget
1 of 1 people found this helpful
• ###### 2. Re: Advanced calculation result vs budget

hi,

In order to get the new budget for upcoming months you can apply the following calculation:

{ FIXED YEAR([Date]): SUM(

IF MONTH([Date]) > MONTH(NOW())

THEN [Sales_Planned]

END)}

+

{ FIXED YEAR([Date]): SUM(

IF MONTH([Date]) <= MONTH(NOW())

THEN [Sales_Planned]

END)}

-

{ FIXED YEAR([Date]): SUM(

IF MONTH([Date]) <= MONTH(NOW())

THEN [Sales_Fact]

END)}

This calculation will give the exact figure for budget amendment. In simple words sum the budget for upcoming months with budget diff of past and current months.

Trust this helps.

D

• ###### 3. Re: Advanced calculation result vs budget

Thanks Ankit Bansal for a great helpfile

Just one follow up question.

I relaise it would be more helpful to see how much I need to increase vs PY sales to reach the budget. Therefore I need to have the calculation below but -1 year.

So I would like a calculation showing future months (but last year sales).

{sum(if MONTH([Date])>=month(today()) then [Budget] end)}

• ###### 4. Re: Advanced calculation result vs budget

You can use date diff function:

IF DATEDIFF('year',[date], NOW()) = 1

THEN [Sales]

END

trust this helps.

D

• ###### 5. Re: Advanced calculation result vs budget

Thanks David Maning but how to I make it only show the value of the first 7 months (full months) like the calculation I got below?

{sum(if MONTH([Date])>=month(today()) then [Budget] end)}

• ###### 6. Re: Advanced calculation result vs budget

Daniel,

I did not get it. You want to compare %increase in budget with respect to PY sales?

Can you explain with mock data what exactly you are looking for?

Thanks,

Ankit Bansal

• ###### 7. Re: Advanced calculation result vs budget

Sorry Ankit Bansal your file was correct but I realised I wanted to see how much growth was needed for the rest of the year to reach the budget.

As you see in my example below I have 316 240 in sales from Aug-Dec PY. If I can have a calculation finding this sum I could change the "% add in budget" calculation you made to show how much growth I need to deliver.

Could I change the below calculation to show the future period but PY and sales instead of budget (the 316 240 I have below)

{sum(if MONTH([Date])>=month(today()) then [Budget] end)}

So I can change the "% add in budget" to

(([Added sales from previous period (37 411)]+[Future budget 410 701)])/[Future month sales PY (316 240)]) *100

Then I get the result that I have to growth 142% vs PY to reach the budget. • ###### 9. Re: Advanced calculation result vs budget

Thanks very much. It works great

• ###### 10. Re: Advanced calculation result vs budget

I was to Quick thinking everything was solved.

I will use this calculation on a page with multiple productgroups. Then the users can click on each productgroup to see the growth needed there.

But in your file nothing Changes when I use action to select group. I have attached an example of how my data looks with multiple Groups.

• ###### 11. Re: Advanced calculation result vs budget

Daniel,

I think you missed to attach file. Basically you need to make product group filter as context filter.

• ###### 12. Re: Advanced calculation result vs budget

Thanks Ankit Bansal

Now i attach the file. Can you help me explain further how to do the action/filter?

• ###### 13. Re: Advanced calculation result vs budget
1 of 1 people found this helpful
• ###### 14. Re: Advanced calculation result vs budget

Thanks it works fine.

I had to change all my filters to make it work. Will it effect the rest of the data or how does "add to context" work?

1 2 Previous Next