[IN REVIEW] Convert Values from a 445 Calendar to a Normal, Monthly Gregorian Calendar
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.
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 the 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 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.
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 extra 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 the 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.
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.
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.
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.
Note the time scale for each module.
The data module is straight-forward. It simply contains the demand forecasts by week.
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:
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.
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
This the module where the transformation occurs. The rules are outlined in the user-story and we can use those as a test script.
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 start and end of the year also shows the correct transformation. We passed UAT!
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.
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 the first month of the 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.
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.