Many businesses have their fiscal calendar that’s different from Gregorian calendar and for analysis/reporting purposes we need to easily translate between the two.
In this blog, I’ll show how to do just and then do a summary of expenses by fiscal quarters from a dataset of Gregorian calendar dates. Then I’ll also demonstrate turning that into Pivot charts for visualization.
Objective
Given dates and associated expenses, categorize the expenses into fiscal quarters, and show a summarized chart by that category. Fiscal month starts on October 1st.
This is the dataset: 
Solution
Steps we need to take are:
- Map the date to corresponding fiscal quarters
- Insert a Pivot chart using the date, expenses and summarize by the quarters.
The simplest way to do this is by one of my favorite functions CHOOSE() as step 1. Since we’re told October 1st is the starting fiscal month, we need to split up the year into 4 equal segments starting from October 1. Each segment will obviously contain exactly 3 months. CHOOSE() isn’t made for any date manipulation purpose, it’s a general purpose function that can be used for many purposes depending on how we set it up.
Before you think about Fiscal quarter, remember that since October would fall in the Gregorian calendar’s 4th quarter, AND October will be month#1 in our Fiscal calendar, we need to place October in the 4th segment. And since each segment is composed of exactly 3 months, the 4th segment will be composed of 1,1,1!
Okay, now we need to arrange the rest of the segments properly. Think of the quarters in a year as a carousel: 1<->2<->3<->4
Meaning after 4th quarter, the next quarter is back to 1st quarter (for next year for example), and so it repeats.
Now that we already established that our 4th fiscal quarter segment will contain 1,1,1 (see above), the 3rd segment must be 4,4,4 (see the carousel explanation above). Therefore, the whole sequence of months divided into 4 segments of 3 months each will be:
2,2,2,3,3,3,4,4,4,1,1,1
This is all we need to achieve the objective!
Given a date 1o/5/2020 for example, it’ll be mapped to quarter# 1 and the formula to do this would be:
=CHOOSE(MONTH(date),2,2,2, 3,3,3, 4,4,4, 1,1,1)
where date is the serial# for a date (short or long).
Since CHOOSE() returns a value from the array given to it (we gave it a series of 1s, 2s, 3s, and 4s above) based on the index we pass (index is 1-based, not 0-based as in C/C++/JScript/Python/Java), we can simply then pass it the number of the month from the date! We get that using MONTH(date).
So, 10/5/2020 returns 1. Exactly what we want. 1/11/2020 returns 2. Exactly what we want. And so on.
Once you plug it into the dataset above in a new column, you should fiscal quarter numbers as the results 1 through 4.
For visualization, I would like something more descriptive than just number 1 through 4 however for the quarters. Instead I want: Q1, Q2…Q4. That way, it’s much more readable.
Just append the word “Q” in front the CHOOSE() formula above. e.g. =”Q”& CHOOSE(MONTH(date),2,2,2, 3,3,3, 4,4,4, 1,1,1)
Your table with the sample dataset should now look like this (Quarter column is what we inserted and contains the formula):
We are now all set to create the pivot charts!
To create a summarized, interactive chart: We select the data range (all of the dataset above) and insert a Pivot Chart (Clustered Column) in a new worksheet.
Let’s create one more chart—a doughnut chart. For each of the charts, I added Quarter field to the Axis area, and Expense (as Sum) to the Values in Pivot fields canvas.
And they should look something like these:
There you have it…as an exercise, pick another month for your fiscal start and make additional reports.
NOTE: For more detailed explanation of this, actual worksheet, formulas used, etc. feel free to contact me directly as I may offer assistance in the topic(s) with help of donation (since I do not do this for a living and my time is extremely limited). Please see Home page for details/link.
