Conventional software works just fine for date based analysis if your fiscal year starts on January 1st. Excel will happily let you group dates placed in pivot tables as row or column fields into months, quarters or years. You’ll have nice looking and accurate summary tools in no time – if your fiscal dates match standard calendar periods.
But what about the rest of us whose fiscal years don’t line up with that? Or our fiscal periods aren’t all the same number of weeks in duration? How do we get the same results?
To calculate if July 2nd in a particular year was actually fiscal June or fiscal July, or in which fiscal quarter it belongs, we typically had to resort to all manner of formulas and devices. And if your quarters are comprised of a four period followed by a five week period and then another four week period, well then you’re on your own buddy. Yup, good luck with that.
If this sounds like a challenge that you constantly have to combat, then you’ll love day 20 of the 30 Days to Become a Better Monarch Modeler series, because the topic today is Monarch’s time interval feature. New to v10, this brilliant feature offers us special abilities to handle dates related to both atypical calendar years and fiscal years. I like this feature so much I’d swear that it was devised just to appease me.
By configuring your Monarch models with your specific date needs, you’ll give your models the kind of easy to implement analytical abilities that would make even the most skilled Excel modeler tremble in fear.
Manipulate Space and Time with Monarch
We access the Time Intervals dialog, the master control of this feature, under the Options menu. It shows samples of how various date styles will be displayed in the Table and Summary windows, including:
- Calendar year
- Calendar half-year
- Calendar quarter
- Calendar month
- Calendar week
- Day
- ISO8601 year
- ISO8601 week
- Fiscal year
- Fiscal half-year
- Fiscal quarter
- Fiscal period, and
- Fiscal week
If you’d prefer to display a value in a different you change its “mask”.
If you need to customize the manner by which you define calendar weeks, you do that here. As make changes, Monarch shows you some dates to confirm the impact of your selection.
To make changes to the default fiscal year and its related periods, you do that here too.
You can choose to have your fiscal year consist of 12 calendar months. With this option, Monarch will calculate that your year begins on the first day of any month you choose.
Alternatively, you can define a year that contains 52 or 53 weeks. If you do, Monarch wants to know that your year starts or ends with reference to any day of the year that you specify. We’ll refer to this as “Day 1”.
Once that’s configured, you pick the day of the week that starts your fiscal week. We’ll refer to this as “Week Day”
The last requirement of this section is to tell Monarch that your year:
- Starts on the first Week Day on or after Day 1, or
- Starts on the Week Day closest to Day 1, or
- Ends on the day before the Week Day on or before Day 1, or
- Ends on the day before the Week Day closest to Day 1.
With that set up, Monarch wants to know if your year is divided into four 13-week quarter, or 13 four-week periods.
If you choose the former, you then select from either of a 4-4-5, 4-5-4 or 5-4-4 weekly pattern, and tell Monarch how to handle the extra week in 53 week years.
If you choose the latter, you simply tell Monarch which period gets the extra week in 53 week years.
And that’s it: you’ve defined custom date controls in your Monarch model.
Now it’s time to have some fun.
New Date Functions
Fourteen new functions have been added to Monarch to let us easily access proper date information according to the fancy fiscal year and calendar year customization. They are:
- CalendarWeek
- FirstDay
- FiscalDayOfYear
- FiscalHalfYear
- FiscalPeriod
- FiscalQtr
- FiscalWeek
- FiscalYear
- HalfYear
- IsoDayOfYear
- IsoWeek
- IsoWeekDay
- IsoYear and
- LastDay
I’ll let you refer to the online help for full descriptions, but the intended purpose of each function is pretty apparent just by the name. The ISO functions offer a good compromise between customized calendars and a predefined standard methodology.
As we’ve seen with calculated fields, any time that we utilize Monarch’s functions we introduce all kinds of possibilities, and these new functions add to our creative toolbox nicely.
Using the Intervals in Summaries
To take advantage of the new date powers at your disposal, first add a date field to your summary as a key field. Double-click its name in the key field list to display the dialog box and activate the Matching tab. The last option is labeled Time Interval. Select this and then pick one of the date styles (see the list above).
Monarch will give you a preview of the values it calculates based on your selection on the right side of the dialog.
Once again, Monarch shelters us from all kinds of complexity and gives mere mortals effective and easy to use tools that let us get back to doing what we’re supposed to be doing instead of forcing us to be some kind of expert technology master.
Your Task for Today
Make today the day that you master Monarch’s customized calendars. Test the effects of defining a fiscal year, building some calculations using the new functions and define a few summaries that employ different time intervals on the Matching tab of the field definition.
Now revise the calendar customization and review the effect of your changes to your calculations and summaries.
Becoming a better Monarch modeler means that you know what to expect your outcomes to be before you implement them, and that best way to know that is to practice, practice, and practice.
Master the Space-Time Continuum
Well that’s a bit of a stretch, but with a little up-front effort you’ll find that there’s little that you can’t analyze where dates are involved.
If today you fight with summarizing date based activity, fight no more. Instead, just excel with Monarch.
—
Continue your commitment to Become a Better Monarch Modeler with Part 21 of the series, or review Part 19.
