How to Group Weekly revenue into a customized Month (List)?
Hi Anaplanners,
For my organization, we have revenues recorded by broadcast period and we have to report it by Gregorian month. Since the default Anaplan 445 timeline is not the same as broadcast period. I have to create a module that allows me to create custom grouping of the Anaplan week into my custom month.
Then, I've created another module with sum function to group the revenue. However, I can only select one year instead of multiple years. one of the requirement is to be able to show forecasted revenue for the next 5 years. Does anyone have an idea how I can make that happen?
thanks,
Jim
Answers
-
Wow! What a great question - as it turns out, I just submitted an article that converts a weekly calendar to a Gregorian Calendar!!
You had posted a question several weeks ago about this so I went ahead and detailed out the steps. So thank you! I also gave you credit in this artcle.
Here's the link. It may seem a little lengthy but it's really not that bad - it took a lot longer to write this than it did to solve it.
If the link doesn't work let me know. I'll add it to this post.
0 -
Hi @JaredDolich,
Your model was a life saver, it just had one little problem I am trying to solve, your Time Setting module is only able to read the 445 week format from Anaplan itself but not Broadcast calendar. Using August 2020 for example, for Gregorian Calendar, it includes the last 2 days of week 31, 32, 33, 34, 35 and first day of 36; in 445 calendar, it contains week 31, 32, 33 and 34; and in broadcast calendar, it contains Week 31, 32, 33, 34 and 35 (Aug 24 - 30).
Broadcast Calendar:
445 Anaplan Time
In this scenario, when the module calculates the Day from End, it's calculating based off revenue from week 34 and not week 35. so the revenue won't be accurate.
0 -
Ah, okay. I used a 445 calendar, but you can use the calendar that fits your broadcast calendar.
Since August is a 5 week month in your broadcast calendar you'll need to adjust the 445,454, or 544 combination to match that calendar, as well as setting the correct fiscal month begin. I chose February but yours may be January judging by your screenshot. I believe yours would be a 454. And don't forget to configure for the 53rd week.
@jimfeng5 let me know if I've over simplified the challenge. I'm very confident we can get this to work.
0 -
@JaredDolich, I cannot express enough on how much I appreciate your help on this! As a new Anaplan modeler, building this model is truly a challenge for me. Thank you so much for guiding me through this.
I believe we may have oversimplified this. If you look at the attached calendar below, the broadcast calendar is actually a combination of 445, 454 and 544. For this reason, I had to create a module in my model to group Anaplan weeks into correct month to capture all the revenue in broadcast month, and I created another line called Other Revenue to store the stub period revenue calculated using "Days from Start" and "Days from End".
Using 2020 and 2021 as an example, below I'm laying out the # of weeks included in each broadcast month.
Jan 4
Feb 4
Mar 5
April 4
May 5
June 4
July 4
Aug 5
Sep 4
Oct 4
Nov 5
Dec 4
2021:
Jan 5
Feb 4
Mar 4
April 4
May 5
June 4
July 4
Aug 5
Sep 4
Oct 5
Nov 4
Dec 4
Broadcast Calendar:
0 -
I think we need to have a month to week mapping as below
Following on what @JaredDolich has detailed in his article
Now assuming we have this weekly sales, where Wk 31-34 is 700 each and Wk 35 sales is 900
The unadjusted monthly sales is 3700, less 5 days for Wk 31 adjustment, plus 1 day for Wk 35 adjustment
Regards
Andre
0 -
I have created a few more lines too assign each week under months. But this would require the end user to input the start week # and end week #.
0 -
If you prefer having 445 Month Start and End as input, I do not think you need to enter First Week and Last Week. Second, Third, Fourth Week number line items are not necessary either.
You can use:
- PERIOD function for the First Week Period : PERIOD('445 Month Start'),
- First Week Period + 1 / 2 / 3 for Second to Fourth Week Period,
- this logic to check if it is a 5-week month for Last Week Period : IF (Period End - Period Start + 1) / 7 = 5 THEN '1st Week' + 4 ELSE BLANK
0 -
Let's keep this conversation going!
I think we're really close and I think I finally understand the issue you have.
I really like @andre.lie idea to make your line items formulas rather than being dependent on someone entering them.
Is there any systematic way we can calculate how many weeks a month will have in the broadcast calendar, like if Sunday is within 3 days of the end of the month or number of holidays a month has, maybe there's a special calendar people in the broadcasting industry use?
If there's no systematic way, it means someone is making that decision and we need to capture that decision making in a system module that contains week count by month. From there we can calculate everything else.
We're so so close. Let's get some closure on this one!
0 -
@JaredDolich, @andre.lie, after our conversation, I looked into the creation of broadcast calendar and there is a logic that "The key link between the broadcast and Gregorian calendars is that the first week of every broadcast month always contains the Gregorian calendar first day of the month. For example, if January 1 falls on a Saturday, then the broadcast calendar year would begin on the preceding Monday, December 27. Broadcast January would then have five weeks, ending on January 30, and the four weeks of broadcast February would begin on January 31. The number of weeks in a broadcast month is based on the number of Sundays that fall in that month with the period ending on the last Sunday of the month."1
-
Okay! Now we're on to something.
Do you need to see that worked out in Anaplan, or do you think you've got it?
So, so cool! I just learned something new - Is that rule typical in the broadcasting industry?
0 -
@JaredDolich, yeah the broadcast calendar is uniform across the industry. Let me try to figure this out myself first. If I managed to figure it out, then I will post my solution up here, if I couldn't figure it out then I will ask you guys for help!1
-
Hi, Jim @jimfeng5I think I understood that logic! (I love this kind of a puzzle 😉)The Broadcast Month seems to be determined by the month of the end date of weeks.For example, the 35th week in 2020 is contained in August in the BM, because the week ends on 30th August.Anaplan calendar 4-4-5 sets that week September.(By the way, your image of the calendar has two 26th weeks ^^)So, you have to check whether the end of weeks and the end of 4-4-5 months are the same dates. See below and attached.
And I made a month dimensioned module and try x[SUM: Broadcast Month], but I was said that was illegal.So, once you get Broadcast Month in this way, you have to make a "fake month" list to wrap up the result.Or option B is the way of "Weeks count" in the module. Sum up next week's value into this month when next week is BM adjust week.I hope I am correct and this helps you a bit.Here is the time setting.
Regards,Taichi1
