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's 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 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.
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.
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 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.
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). Also, note that the IF conditionals exit as early as possible to improve performance.
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.
@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!