Projecting Sales Based on Key Assumptions

[msedlak]

I have attached a spreadsheet with the following:

1. Data Tab
2. Key Assumptions and a mock up with description

In my business we receive referrals from medical networks. Those referrals then go on to have various visits. In the assumptions tab, I have a plug for average visits for each referral category. In addition, I have outlined the timing of those referrals.

What I need to determine is a way to project future month visits based on prior and current month referrals.

This should be a fun analysis to piece together. I have hit a wall with the analysis.

Thank you in advance for any time spent on this.

MarkVisits Estimator Template.xlsx (167.1 KB)

[MudassirAli]

@msedlak It seems like a good scenario to work with but I need some clarification on the concepts. In Excel file it is mentioned Visits Non-Surgical 9 and then if referral date start date is 1/2/20 and it is non-surgical, the # of visits will be 4.5 in period 1 and 2.25 in Period.

What’s the rationale behind 4.5 & 2.25 visits in different periods?

It would be helpful if you could pick one customer from the excel and perform the mock-up of your result so we can better understand how to approach the problem.

Thanks.

[Rens]

Indeed, I agree with Mudassir. More input is needed.

=> How do you map the referrals to periods? E.g. if a patient has 3 referalls on 1/2/2020, how do you know if those visits will be in period 1 or period 2?

[msedlak]

I was making this too complicated. I have simplified the model.

Let’s just look at nonsurgical

• Avg # of visits = 9
• Avg length of total visits = 60 days
• Avg time between visits = 7 days

I built out a table showing the timing of visits for 1/2/20 referral date

I hope this helpsVisits Estimator Template.xlsx (72.3 KB)

[Greg]

Hi @msedlak. Can you share your work-in-progress PBIX file to give the forum members a head start?
Greg

[MudassirAli]

@msedlak Is the Avg # of Visits a hardcoded number because I am getting 1 as Avg # of visits?

[msedlak]

I was not able to formulate how to structure this in PBI, so I don’t have file started to share.

The avg # of visits is hardcoded, the data by date is total referrals by customer.

I struggle building tables with multi date dimensions, so I was not able to get it set up in PBI.

Does that make sense @MudassirAli?

[MudassirAli]

@msedlak I have made some draft computations and attaching the file so you can let me know whether I am in the right direction or not.

Projecting Sales Based on Key Assumptions.pbix (42.0 KB) Visits Estimator Template.xlsx (73.6 KB)

[msedlak]

The next step from your table would be taking the referrals that come in and building a date table where the visits land on the projected day based on the Avg Days Between Visits.

For example Customer #4714 has 2 referrals on 1/10/20, on 1/17/20 there would be 2 visits associated with these 2 referrals and then another 2 visits on 1/24/20, and so on.

This is the trick that I was not able to solve for, how do I take the referrals that come in and fill in a separate date table where the visits land on the projected day, but stop after 9 visit avg/referral is hit.

[MudassirAli]

@msedlak This means after 1/10/2020 the visits will be on the following dates:

1/17/2020
1/24/2020
1/31/2020
2/7/2020 & so on…

[msedlak]

That is Correct!

[MudassirAli]

I will work on this one and will update you.

Thanks

[EnterpriseDNA]

Hi @msedlak , we’ve noticed that no response has been received from you since the 16th of December. We just want to check if you still need further help with this post? In case there won’t be any activity on it in the next few days, we’ll be tagging this post as Solved. If you have a follow question or concern related to this topic, please remove the Solution tag first by clicking the three dots beside Reply and then untick the checkbox. Thanks!

[msedlak]

Hello,

MudassirAli said he was working on it. I have not heard back since that post.

What is protocol here, you prefer to close it out?

Mark

[MudassirAli]

@msedlak I am really sorry. Your post was lost somewhere among all the posts and I didn’t get the chance to look at this. It’s a general message you get and you don’t need to close it unless you have the solution.

[msedlak]

@MudassirAli Please don’t apologize. I have not been able to solve this, and I appreciate any help you can offer. I also understand if you don’t have time.

[MudassirAli]

@msedlak It’s a good scenario I will try to solve it myself as I know one member is expert in this kind of scenario. If I can’t solve it, I will pass it on to the other member to have a look at it.

[Melissa]

Hi @msedlak,

I created a list of dates for expected next visits based on your given variables:
AvgDays = 7 and AvgNumVisits = 9, you can adjust that should this need ever arise.

CreateDates = Table.AddColumn(RemoveBlanks, "Expected Visit", each 
    let
        AvgDays = 7,
        AvgNumVisits = 9,
        FromDate = Date.AddDays( [Start Date], AvgDays),
        Source = List.Generate(
                () => [x = 0, y = FromDate], 
                each [x] < AvgNumVisits, 
                each [x = [x] + 1, y = Date.AddDays( [y], AvgDays)]
            ),
        CreateTable = Table.FromRecords(Source)
    in
        try CreateTable[y] otherwise {}
    ) 

.
This means that every single record, is filled down for 9 Expected Visits.

.
Next created a simple measure to calculate the expected number of total visites for each date.

Expected Total Visits = 
CALCULATE(
    SUMX( 'Data', 'Data'[# of Referrals] ),
    ALLEXCEPT( 'Data', 'Data'[Expected Visit] )
)

With this result.

Here’s the sample file: Projecting Sales Based on Key Assumptions.pbix (76.9 KB)

Note that if you add the xlsx filepath to FileLocation Parameter and update the ‘current’ selection all queries will be restored. I hope this is helpful.

[EnterpriseDNA]

Hi @msedlak, did the response provided by @Melissa help you solve your query? If not, how far did you get and what kind of help you need further? If yes, kindly mark the thread as solved. Thanks!*

[msedlak]

@Melissa Thank you for working on this. I am just coming back from Christmas, and will review today and get back to you.