In this data dominated world, vital business decisions are often attributed to the processes used in converting raw data into actionable insights. There is always a constant need for a reliable way to resolve important business issues and establish trends. But, before that, it is essential to have in-depth information about individual business processes and results.
For example, a manufacturer might be interested in knowing the growth of sales figures by passing month. Hospital management would like to know the relative success ratio of treatments with respect to the previous year. A software enterprise would like to determine the number of customers using a certain service over a time range.
All the above-given scenarios can be addressed only when one can have proper reporting techniques to compare results over a specific time range. The key lies in making optimal use of the MTD, QTD, and/or YTD values available in the reports.
This article provides a simple introduction to the time range calculations with respect to month, quarter, or year, along with ways to calculate using a simple excel tool as well as a sophisticated BI tool like BIRD.
What is MTD?
MTD or Month to Date implies the period commencing from the beginning of a month to a day before the current date. MTD data is essential to monitor business activities over a month and to draw comparisons with data from the same month of the previous year. Professionals can use MTD to track certain activities or marketing campaigns during a given month.
For example, a business intelligence tool provider might want to run campaigns for analytics in different domains like healthcare, retail, etc.
Keeping track of the ROI achieved over the campaigns gives a clear picture regarding the domain to focus on. This, in turn, is possible by implementing MTD calculations over the campaign data by each month.
Figure below shows how BIRD calculates different MTD values one month prior to the corresponding dates on X-axis
What is QTD?
The QTD or Quarter to Date refers to the time interval which captures relevant business activities between the beginning of the current quarter to the most recent date in the quarter. Like MTD, the information is gathered before the ending of the quarter, to give a proactive assessment of the company’s performance in the current quarter.
However, before making any interpretations using QTD, it is important to know whether quarter refers to given calendar year or fiscal year.
For example, QTD is used in a company’s quarterly financial statements to reveal an increase of stocks with respect to the beginning of the quarter.
What is YTD?
Like the above two methods, YTD (Year to date) refers to the period starting from beginning of the current year to a day before the current date. For example, in a healthcare organization, YTD data is essential to monitor patients’ health within the current year, to modify or improve current treatment facilities.
The screenshot from BIRD below shows a YTD data on sales trend for a given date wherein data for a given year is shown.
How to Calculate MTD, QTD and YTD using Excel?
While there are innumerous business intelligence tools that provide solutions to calculate MTD, QTD and/or YTD, they incorporate complex methods that are quite difficult to comprehend. A simple solution lies in using excel formulae.
Steps to calculate MTD using Excel
Firstly, consider a excel table containing sales data for each day for the month of January and February in the current year.
Secondly, Calculate MTD data for sales for each month using below given excel formula:
SUMIF (criteria_range, criteria, sum_range)
In this case, sum_range refers to the sales values available for a given range (start of the month to current date).
Criteria is established by considering whether the given date is less than or equal to current date. Current date can be calculated using the Today function.
The criteria_range refers to the date range during the specific month.
Thirdly, resultant data corresponding to the current date of the month gives the final MTD for the month. Note that MTD data keeps on increasing, and hence is easy to interpret.
Fourthly, Compare MTD data for each month to assess performance of the company in terms of sales.
Steps to calculate QTD using Excel
From the above given example, consider sales data for a single quarter, Q1. For sake of ease, consider the quarter as per calendar year, i.e. Q1 would comprise of the months of January, February and March.
Calculate QTD value for each row using below given excel formula:
SUMIFS(sum_range, criteria range1, criteria1, criteria range2, criteria2…)
Here the criteria are defined as the range between the start date of the current quarter and the end date, i.e. the current date of the quarter.
The obtained QTD data can be compared to same for previous year to monitor quarterly performance of the company.
BIRD Business Intelligence Tool to view YTD/MTD Data
Despite an innumerous number of tools providing functionality to calculate YTD, MTD, and/or QTD data, BIRD analytics platform proves to be the best among all, with its versatile features. For example, consider an insurance company monitoring vehicle insurance claims over a period with respect to the number of vehicles.
BIRD utilizes its own internal algorithms to calculate YTD data for that report. All a user has to do is to select time series analysis and click on the ‘YTD’ option (At the immediate top of the chart).
Another method provided by BIRD is to define a custom field on the Y-axis with a formula to calculate MTD, YTD, or QTD.
Figure below shows an example of custom field created in BIRD to calculate last month MTD value.
In order to survive in this fiercely competitive world, it is very essential for businesses to incorporate real-time methods in identifying timely trends and make future predictions. Methods like MTD, QTD, YTD, have enabled businesses to move from the retroactive analysis phase to a more proactive and realistic analysis phase.
Click here to capitalize on BIRD’s potential for an effective sales figure calculations.