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 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's 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 30). 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.
Solution
This solution will use the D.I.S.C.O. methodology 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.
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.
Note the time scale for each module.
Data Module:
The data module is straight-forward. It simply contains the demand forecasts by week.
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:
In this example, we show April and May as a 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
Blueprint View
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 Text - Converts the first week to a text format.
- Last Week Text - Converts the last week to a text format.
- Year Number Text - Converts the year into a text format.
- 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 start and end of the year also shows the correct transformation. We passed UAT!
Blueprint View
The blueprint view shows the transformation logic. Note that many of the line items are broken out for simplification purposes (PLANS methodology). Also, note that the IF conditionals exit as early as possible to improve performance.
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 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.
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.
Contributing author Mark Warren.
Comments
-
@karank ,
Great question!
In a 445 calendar all months are either 4 weeks or 5 weeks, or 4*7 = 28 days or 5*7 = 35 days. Since February is a 4 week month, it will have 28 days, regardless of the leap year.
In a Gregorian Calendar, that same February will have 29 days as shown in the system module.
This is one very nice feature of Anaplan that it is aware of the leap years.
Our calculation will take the leap year into account and make sure the 445 sales are correctly translated into a Gregorian calendar, which in this case is 29 days.
To get the number of days we can use the DAYS() function. Something @rob_marshall taught me last month. I usually counted up the weeks and multiplied * 7. Little did I know that the DAYS() function worked this way. Another point for Anaplan!
2 -
0
-
Thanks this looks good but what are the complete formulas in these 4 line items
The whole view of the Module would be good as your outline doesn't give a final solution without the summaries and full formulas
thanks
0 -
Thank you for posting this! How about if you are doing the reverse in an already established Model? From Calendar to 445? Thank you
0 -
Excellent article for resolving conversion 445-monthly forecasts in a Gregorian calendar. 🤝
0 -
Both the Start and End Sales Adj calculated values do not result in the true monthly amount, but a proportional amount of the week only. That is, your calculation takes the number of start or end days and divides it by 7, which you then multiply by the week's value. It does not actually retrieve the sum of the adjustment days. Hasn't this resulted any any concerns with your customers?
0 -
Hi Jared, Thanks for this, please can you supply full formula for:
- Start Sales Adj
- End Sales Adj
0 -
How can one use this to aggregate from sales on a day basis to monthly sales on such a calendar
0
