Adust daily figures to preserve monthly totals while switching from a weekly to a regular calendar

fxlepoutre

Hello community,

 

I am trying to adjust my daily sales forecast so that the monthly calendar total matches the monthly "weekly-calendar" total, while keeping my seasonality as close to reality as possible.

 

A simple picture will tell more than 100 words.

 

 

.Spreadsheet here: https://docs.google.com/spreadsheets/d/1rL4AnoCCTUAX9zXmF0tW6wRhvYhddUdm_buRgf_5G9s/edit?usp=sharing

 

I am trying to get the green column 'resultNetSales'.

In order to do that, I need the orange column 'sum of daily sales over calendar month' to compute the sum of all netSalesEuroByDay that have the same calendarMonth as the current day.

 

In Excel, I would simply use the " =SUMIF($F$6:$F,F8,$D$6:$D) " formula.

I would have expected the formula " netSalesEuroByDay[SUM: calendarMonth ] " to give me that result, but it keeps throwing a horrible error:

 

Any idea on what formula to put in my orange column?

Or another way of achieving the result column differently?

 

Thanks a lot,

FX

fxlepoutre

Hello @JaredDolich ,

 

I finally found another technique that solved my issue, using the "cumulate" formula, and resetting it at every month change.

It looks slightly dirty, but it happens to work fine and achieve the desired result of using my daily figures in the most accurate way possible.

 

So here are the line items that I ended with:

 

In a system module at the "Day" level:

 

- mid-month date  => using the 15th of the month assures me to always find the proper Gregorian calendar month for that day (should work well whatever your time settings).

- start => getting the first calendar day of the Gregorian calendar month

- end => getting the last calendar day of the Gregorian calendar month

- calendarMonth => Getting the Gregorian calendar month of the date, as a Time Period.

 

In a calculation module, at a Day level also:

- netSalesByDay => the input, which are the sales quantities, for every day of the 445 calendar.

- monthlySalesOver445Calendar => the monthly total of the 445 calendar sales, for the assigned Gregorian calendar month of each day

- cumulatedSalesOverGregorianCalendar => cumulates the daily sales, but resets every time we change calendar month.

- monthlySalesOverGregorianCalendar => returns the cumulated sum for the last day of the assigned Gregorian calendar month of each day, which corresponds to the total monthly sales of the Gregorian calendar month. (the orange column of the initial screenshot)

- adjustedNetSalesByDay => returns the adjusted daily figure, proportionally distributed according to the monthly sales (the green column in the initial screenshot).

 

Hope it can help someone else.

 

Cheers,

FX

JaredDolich

@fxlepoutre 

Great question - one that is asked a lot.

Next week, this article is scheduled to be published which converts a 445 calendar to a Gregorian.

https://community.anaplan.com/t5/Content-Sandbox/SCHEDULED-Convert-Values-from-a-445-Calendar-to-a-Normal-Monthly/ta-p/59839

 

The short story is that you need to create a system module at the daily level using the weekly calendar. From there it's a matter of mapping - which some call a calculation module. See if the article above helps. If not, I can give you more specifics.

 

fxlepoutre

Hello Jared,

Thank you for your quick answer and for sharing this article in advance.

I assume that I need to run this "adjusted sales" calculation in a Month time scaled line item, and then use that figure in my orange column as a MONTHVALUE("adjusted sales") ?

Correct me if I am wrong, but your method seems to be going from a month level to another month level, and doesn't seem to take into account the daily values. For instance, if the month ends a Friday, and there are no sales on Saturday and Sunday, there should be nothing moved to the next month. How do you handle that?

Thanks,

FX

Misbah

@fxlepoutre 

 

If you implement @JaredDolich 's solution I think you should be able to get the expected output. You may have to tweak it as per your requirement considering you are looking at daily timescale.You can change the dimensionality of the module and for few line items where you are pulling the daily Sales

 

Misbah

@JaredDolich 

 

One query on Planning Month to Gregorian Month Conversion article  - Is there a specific reason that we are keeping month values to be an input field in the system module. There can be cases where planning spoke model will  just have one year of a time scale and imagine what happens when there is yearly rollover process.This field has to be maintained every year.

 

Now we can argue that we could add Time Range to the system module for multiple years and set it up for all years but that still comes under maintenance. Here is what I was thinking - If we had created one more list to maintain Fake months along with the Codes system could have easily populated the month value irrespective of the year hence removing the maintenance. 

 

Great article and it is surely going to help people who want to see numbers in planning month vs book month

 

 

JaredDolich

@fxlepoutre 

Good query. The conversion from 445 to Gregorian happens at the daily level, so you should be good there. 

The trick is to determine the right allocation. In my example I divide by 7 and assume each day is equal which we know that is unrealistic. Ideally, you will have a % to total by day of week to better allocate the proportions.

The other nuance is what to do with the first and last month. Ideally, the entire year is preserved. Meaning if the 445 calendar year adds up to $1 million then the Gregorian Calendar year adds up to $1 million. Two issues with that:

1. 445 calendar is 52 x 7 = 364 days; whereas, Gregorian is 365 days (or, 366 depending on if there's a leap year).
2. 445 every 5 to 6 years will add an additional week, or 53rd week to make up for the missing day each year.

So it comes down to the level of complexity you're willing to resolve. A perfect solution will require a bit more work; whereas, a "close enough" solution can be managed rather quickly with some simple mapping as described in the article.

You're on the right track. I happen to agree that the daily allocation is necessary. I suppose you can further simplify it by manually entering the monthly allocations and bypass days altogether. That would guarantee that your calendars at the annual level would reconcile since you'll need to make sure what you take away from a month is given back to another.

Hope that helps. 

JaredDolich

@Misbah 

I love how you think! 

A faked out month would work but I suppose the trade off is that you can't use any of the built in time functions.

But to your point, using the calendar or time range, of course, means that we've added an administrative task for someone. But, some good news, is that there's no hard-coding so the only admin task would be to move the calendar forward or extend it.

Pros and Cons. You're absolutely right though - 

Misbah

@JaredDolich 

 

I don't think I articulated things pretty well. Apologies! I was not talking about changing the entire dimensionality of the module from Native to Fake time scale Here is what I was talking about.

 

It is mentioned that this is ok to keep "Month Number" Line item a manual field. I have listed out my reason why it might not be ok when there is a solution available with you

Solution to this:

Step 1: Create a list, Insert numbers from 1-12 and mention your months in the code of the list. It can be as per requirement but I am just keeping 1 as Jan and 12 as Dec

Step 2: Create one line items in the SYS Module  and call it Months without Year

            Months without Year = LEFT(NAME(ITEM(TIME)),3)

Step 3: In your existing Month Number Line Item write the formula

           Month number = FINDITEM(TIME, Months without Year)

This will ensure that we don't have to maintain this line item every year all the time.