Calculating Year-to-Date

1. Calculating Year-to-Date

Let’s have a look at how we can calculate the YTD values by using a cookies business. We have 3 types of cookies chocolate, oatmeal and regular, and each cookie comes in packs of 5 and in packs of 10. The data ranges from January 2022 until June 2023. And the cookies sales per month are displayed in column E. As a short reminder, Year-to-date means we want to sum up all the sales from the beginning of the year, until the current moment for one specific product. The YTD values for March 2023 for example, is the sum of sales in January, February and March 2023. We can calculate the YTD sales using a SUMIFS formula. The first argument is the sum_range: this is the range we would like to sum - thus the sales per month. The next arguments consist of multiple criteria ranges and criteria. The first criteria range we want to select is the product column, and set it equal to the chocolate cookies. Let’s add a second criteria range for the pack size, and a third one for the year. If we press enter now and copy our formula, we notice that we are summing all sales for chocolate cookies for the year for a certain pack size. The last criteria_range we need to add is the months column. We only want to sum up the Sales per month for the months which are smaller or equal to the current month in the table. We can add this to the formula by wrapping it in quotation marks and adding an ampersand. Notice the less than or equal to sign is inside the quotation marks, but the ampersand is not. Let’s extend the formula to the rest of the table. That's it! We can do a quick check, and see that YTD is correctly calculated for the first rows of our table. After 6 months there is indeed 6600 dollar in 5 pack chocolate cookie sales for the year 2022. You can see that the YTD sales are increasing month after month. But whenever we get to a new year, you can see that the calculation starts all over again. And this is exactly the same for when we get to a new pack size, or even a new product. Having these values in your table will come in incredibly handy during your analysis, now, over to you!

2. Let's practice!

Create Your Free Account

By continuing, you accept our Terms of Use, our Privacy Policy and that your data is stored in the USA.

Case Study: Net Revenue Management in Excel

IntermediateSkill Level

4.9+

88 reviews

In this chapter, you will learn about the concept of Net Revenue Management (NRM) and will focus on two of its pillars: Brand Portfolio Pricing and Mix Management. You’ll calculate important KPIs such as net sales contribution and gross margin. You’ll also use brand Portfolio Pricing to identify the correct price for your consumers. Consecutively you’ll use Mix Management techniques to help Healthmax launch a new product and expand into a new subcategory.

Exercise 1: Net Revenue Management Exercise 2: Profit growth Exercise 3: Net sales Exercise 4: Gross margin Exercise 5: Profitability matrix Exercise 6: New category opportunity Exercise 7: New product launch

In the last chapter, you will focus on the three remaining pillars of Net Revenue Management (NRM). Trade Terms Management will help you to decide in which customers and products you should be investing to grow your business. You’ll combine that information with Pack Price Architecture to develop a new pricing strategy for a small shampoo bottle and use a Promotion Management technique to identify the most successful promotion. Finally, you’ll wrap it up by forecasting next year's growth by combining organic growth with the additional revenue of your NRM activities.

Exercise 1: Net Revenue Management: Pillar 2, 4 & 5 Exercise 2: Trade Terms Management Exercise 3: Price Pack Architecture Exercise 4: Promotion Management Exercise 5: Forecasting Exercise 6: Waterfall Exercise 7: Wrap-up