How to Group Total sold from 4-4-5 to calendar month if data is weekly commencing monday?

jimfeng5
jimfeng5
in Modeling

Hi all,

 

My name is Jim and I am new to Anaplan and its community. 

 

I'm trying to build a model that tracks inventory sold for each calendar month. However, our inventory is tracked using 4-4-5 method and the data I have is aggregated weekly (no daily data) every Monday. To elaborate:

for Jan 2019, per 4-4-5, it's from Dec 31 2018 to Jan 27 2019, and Feb is from Jan 28 2019 to Feb 24 2019. If I were to capture the inventory for Jan using calendar month, How should I set up the model so that I can capture estimated inventory sold from Jan 1 - Jan 31?

 

Thanks,

Jim

Tagged:

Best Answers

  • JaredDolich
    JaredDolich
    Answer ✓

    @jimfeng5 

    This is a great question Jim. 

    I work primarily in the retail industry and I can tell you this is a very common question.

    The good news is that there are many ways to solve this.

    But all of them are going to be estimates unless you have a more accurate way to break up the sales into days.

     

    I have two suggestions to point you in the right direction.

    If you'd like an example of either just let me know. I can give you step by step instructions.

     

    Idea 1

    The first is to create a system module that only contains the time dimension set to months.

    You'll calculate the fringe days missing for each month

    Also, figure out how to handle the first and last months. 

    Calculate the daily average of the month (or weekly of the first and last week if you need more detail)

    Use these daily averages to add/subtract from the month to get the Gregorian equivalent.

     

    Idea 2

    Import the monthly sales (and number of days in the month) to a new module that has a calendar set to the Gregorian Calendar.

    Compute your daily averages and transfer the sales from month to month so the overall total for the year remains the same.

     

     

  • JaredDolich
    JaredDolich
    Answer ✓

    @jimfeng5 

    As promised, here's an idea of how you can covert your 445 calendar into a Gregorian calendar.

    Thanks for posing this question. I had a lot of fun with this one!

    Keep in mind this took me much longer to write up than it did to create. So if you decide to implement this solution, I promise it will go quick.

     

    Convert Values from a 445 Calendar to a Normal, Monthly Gregorian Calendar

     

    Summary

    As a supply chain Anaplan modeler, you might be asked by Finance to convert your 445-monthly forecasts into a normal, Gregorian, calendar since their General Ledger accounts are usually set up that way. This user-story will provide a solution using a one-year model that uses a 445-monthly calendar. This solution was inspired by a question asked by @jimfeng5  on this post.

     

    Assumptions

    This method converts weekly sales into daily average sales and adds or removes the extra days from each month. To improve accuracy, you could also add a system module by day of week that indexes the days to get a better allocation (for example, Sunday is 25% of the week).

     

    This method also assumes that the first month and the last of month of the year do not have the values from the prior or subsequent year respectively to calculate the right average. In this case, we will use the first week and the last week values of the current year respectively to calculate the daily average.

     

    User Story

    As a finance executive responsible for FP&A, I need the demand forecasts calculated by the merchandise planning team to be converted to monthly demand forecasts that use a Gregorian calendar, where each month starts on the 1st of the month and each month ends on its respective last day (e.g., January 31, February 29 (leap year), or September 30th). I don’t need granular data or the ability to drill up/down so the demand should roll up to total company for all products. I know this will be right when I see that months 2-11 reconcile exactly and the first and last months reconcile to the first and last week respectively. I expect the rules to work as described below.

    General Rules for months 2-11

    • When the first day of the month is contained in the first week, then remove the extra days from the first week of the current month.
    • When the last day of the month is contained in the last week, then remove the extra days from the last week of the current month.
    • When the first day of the month is in the prior month, then add the extra days of the last week of the prior month.
    • When the last day of the month is in the next month, then add the extra days from the first week of the next month.

    Beginning of Year for month 1

    • If the first day of the year is in the last week of last year, then add the extra days using the first week of the current year.
    • If the first day of the year is in the first week of the current year, then remove the extrad days using the first week of the current year.

    End of Year for month 12

    • If the last day of the year is in the last week of current year, then remove the extra days using the last week of the current year.
    • If the last day of the year is in the first week of next year, then add the extra days using the last week of the current year.

     

    Solution

    This solution will use the DISCO methodology outlined by @DavidSmith  and will require three modules; a data module that contains the demand forecasts, a system module that will provide properties of the month, and a calculation module that transforms the 445 data to Gregorian. The calendar uses the 445 set up which is described below.

     

    Calendar

    The model calendar in this case uses the 445 methodology with the Saturday nearest the end of January to mark the end of the year. The year begins in February, a typical starting month for many retailers. By using the built-in calendar, Anaplan will take care of leap years and the 53rd week.

    BP_445_Time Hiearchy.png

    Modules:

    The data module has already been summarized to the corporate level for all products. This solution, however, would work perfectly for dimensional modules such as product and location. The system module contains all the monthly properties we need to determine the transformations into the Gregorian calendar. Lastly, the calculation module contains the transformations.

    BP_445_Modules.png

    Note the time scale for each module.

    Data Module:

    The data module is straight-forward. It simply contains the demand forecasts by week.

    BP_445_Data001.png

    System Module:

    A system module is usually a one-dimensional module that contains the properties of that dimension. This module contains all the monthly properties we need to solve the transformation into a Gregorian calendar.

     

    First though, to help meet the user-requirement from our user-story, we can use a spreadsheet diagram to ensure we are meeting the user’s expectations:

    BP_445_SpreadsheetExample.png

    In this example, we show April and May as way to make sure we are using the right logic. The orange cells highlight the reconciliation points needed for each month. Notice how the end of the last month and beginning of the next month reconcile exactly. Once the user agrees, we can build the system module.

     

    Grid View

    BP_445_SystemGrid.png

    Blueprint View

     

    BP_445_SystemBP.png

     

    Line Items

    • Month – contains the period formatted value for the month
    • Days in Month – contains the number of days in the month using the 445 calendar
    • 445 Month Start – contains the first date of the 445 month
    • 445 Month End – contains the last date of the 445 month
    • Gregorian Month Start – contains the first date of the Gregorian Month
    • Gregorian Month End – contains the last date of the Gregorian Month\
    • First Week – week number of the first week in the 445 month
    • Last Week – week number of the last week in the 445 month
    • First Week Period – Week formatted line item that contains the first week of the 445 month
    • Last Week Period – Week formatted line item that contains the last week of the 445 month
    • Year Number End – the year at the end of the 445 calendar year
    • Year Number – the year at the beginning of the 445 calendar year
    • Month Number – manually entered monthly values (1-12). This is okay since these will never change.
    • Days from Start – Calculates the number of days to reconcile at the beginning of the 445 month
    • Days from End – Calculates the number of days to reconcile at the end of the 445 month
    • First Month? – Boolean that indicates if this is the first month of the 445 year
    • Last Month? – Boolean that indicates if this is the last month of the 445 year

     

    Calculation Module

    This the module where the transformation occurs. The rules are outlined in the user-story and we can use those as a test script.

     

    Grid View

    This view shows the final result with the adjusted sales at the end. For the end of April and the beginning of May we should see -120 and +120 respectively, and we do. Checking the beginning and ending of the year also shows the correct transformation. We passed UAT!

    BP_445_CalcGrid.png

    Blueprint View

    The blueprint view shows the transformation logic. Note that many of the line items are broken out for simplification purposes (PLANS methodology by @DavidSmith). Also note that the IF conditionals exit as early as possible to improve performance.

     

    BP_445_CalcBP.png

    Line Items

    • Original Sales – sales from the 445 month.
    • Start Days Delta – Number of days to reconcile at the beginning of the month
    • End Days Delta – Number of days to reconcile at the end of the month
    • Sales First Week – The first week of the 445 month’s sales
    • Sales Last Week – The last week of the 445 month’s sales
    • Start Sales to Use – Based on the beginning delta, this will determine which sales to use (see rules from user-story)
    • End Sales to Use  - Based on the end delta, this will determine which sales to use (see rules from user-story)
    • Start Adj to First Month – Adjustment to first month of year
    • End Adj to Last Month – Adjustment to last month of the year
    • Start Sales Adj – Adjustment to the start of the month for months 2-12
    • End Sales Adj – Adjustment to the end of the month for months 1-11

     

    Conclusion

    As part of connected planning you will be asked by Finance to convert your weekly forecasts into monthly aggregations using the Gregorian calendar because their General Ledger accounts are set up that way. This method allows you to meet that challenge.

     

    From here, actions to export the data to your data hub or actions that directly interface to the Finance model can be created. You can also map the demand forecasts to the specific general ledger account so saving the trouble for Finance to have to map your data.

Answers

Categories