How do I create a Formula that finds a goal date based on forecasted sales?

[Chrisc0276]

Hello and thank you for reading.

I am trying to create a formula that will look at a sales forecast table and tell me when we will hit our sales goals based on the sales forecast percentage.   Please see the attached Excel file.  

Sometimes the Sum of the months sales do not hit the goal, so i need to determine when we will hit the goal based on the forecast.

Does anyone know how I would right a formula to get that answer?

[KGalloway]

Hi Chrisc0276,

I think the following will work as a solution. However, I do assume several things about your data. I will explain my assumptions and calculations below. Let me know if I can clarify anything.

The way "date sales goal reached" is calculated is by using the 5 fields in the bottom table:

sales as of 2-14-23: this is the date I used as "today". It represents the current sales as of today's date from the table at the top.

sales remaining: this is the difference between the sales goal (250) and the sales as of "today" (2-14-23).

sales rate per day: this is sales remaining divided by the number of days from the beginning of the year until "today" (2-14-23)

num days left until sales goal: this is the sales goal divided by the sales rate per day. (the numbers are rounded)

date sales goal reached: this is num days left until sales goal + "today" (2-14-23)

The final formula is date(makedate(2023, 2, 14) + (('$(vSalesGoal)' - sum(if([Event Date] <= makedate(2023, 2, 14), Amount))) / ((sum(if([Event Date] <= makedate(2023, 2, 14), Amount))) / (makedate(2023, 2, 14) - yearstart(today())))))

Notably, the makedate(2023,2,14) should be replaced with today().

Let me know if I can clarify anything or adjust my assumptions to better fit your need.

[Chrisc0276]

Thank you very much for the post.  A couple of things that complicate it.

1. the dates between sales are not equal.  but those are the exact planned dates. 

2. What I am looking to do is add each sale to the next planned sale until I get to the percent defined for that month.  each sales rep has to sell so much per month, and they have to plan their sales out for each month.  so what ends up happening is I will be able to manually look at the data and say, if our goal is for April is X, then we will hit that goal Y day, this is based on the actual dates the sales people say they will make a sale.  it can't be pro-rated, it has to be the exact date that the amount is either = to or greater than the defined % for that months percentage I am seeking. 

3. another way to look at is it, today is x, our goal for the month we are in is 50%, add up all the sales for this year, when we hit >=50%, tell me the date associated with that planned event that pushes to be >= to 50%.

do you think this is possible?  this is way over my head. 

[KGalloway · Accepted solution]

I think I got it. Here is what I did:

1. In the load script, I added Month, Sales Goal, and RunningSum as fields to the data:

sales_data_raw:
load * inline [
Event,Amount,Event Date
A,10,44958
B,11,44963
C,12,44966
D,13,44970
E,14,44986
F,15,45002
G,16,45004
H,17,45017
I,18,45026
J,19,45080
]
;

left Join

Load
Event,
month([Event Date]) as Month
resident sales_data_raw;

left join
sales_goals:
load * Inline [
Month, Sales Goal
2, .25
3, .5
4, .75
5, .95
]
;

NoConcatenate
final_data:
load
*,
rangesum(peek('RunningSum'), Amount) as RunningSum
resident sales_data_raw
order by Month, [Event Date];
drop table sales_data_raw;

2. In the charts, I again use a variable for the sales goal and I added a measure that checks if the RunningSum >= the sales goal. If it is, I put the date. This is seen in the straight table in the screenshot below.

3. I then created a pivot table that uses Month and Event Date as dimensions and the same if statement check as the only measure, but with an added min function: min(if((RunningSum / '$(vSalesGoal)') >= [Sales Goal], date([Event Date])))

This way, each month shows the first date that met or exceeded the sales goal for that month.

This feels closer to what you are trying to do. What are your thoughts?

[Chrisc0276]

that looks like it would work! thank you so much. I really appreciate it!

Chris