Monday Morning Mulling: December 2024 Challenge

On the final Friday of each month, we set an Excel / Power Pivot / Power Query / Power BI problem for you to puzzle over for the weekend.  On the Monday, we publish a solution.  If you think there is an alternative answer, feel free to email us.  We’ll feel free to ignore you.

The Challenge

Santa asked you to keep track of the present production of his elves to maintain maximum efficiency at his workshop.  While he usually lets them leave at their scheduled retirement date, this time, he was considering bringing in new replacements early to improve present production per year.

Santa needed you to forecast the elves annual present production by year for the next five [5] years.  Elves were identified by their Elf ID.  Each elf had a Start Date and a Retirement Date, along with an output rate of Presents per Day.  Elves that were replacing retiring elves had a Replacement ID which stated the Elf ID of the elf they were replacing.  

Additionally, Santa needed you to find the benefit to replacing the elves early as opposed to at their retirement.  He wanted you to find the difference in production before and after replacing the elves.  The question file was (and still is!) available for download here.

As always, there were some requirements:

• you could use a helper table for each section of the question, one [1] for the present production forecast and one [1] for the replacement benefit
• no LAMBDA or LAMBDA helper functions (e.g. LET, BYROW or MAP) were allowed
• no Power Query or VBA allowed; this was a formula challenge.

Suggested Solution

You can find our Excel file here, which shows our suggested solution.  The steps are detailed below.

CALCULATE THE REPLACEMENT DATE

The first step is to calculate the new ending date for elves that are being replaced.  For each elf, we need to search the Replacement ID column for a matching ID and take the corresponding Start Date:

This formula checks if the selected Elf ID appears in the Replacement ID column, and if it does, takes that Start Date.  Otherwise, the formula is just taking the selected row’s End Date.  It is important to subtract a day from the selected Start Date as you cannot have two [2] workers in the same role at the same time.

CALCULATE THE DAYS EMPLOYED

The next step is to calculate the days employed each year for each elf.  There are two important criteria we need to check.  Firstly, we need to check whether the start date of the elf is earlier than the end of the period being covered.  Secondly, we need to check that the smaller of the retirement date and the replacement date of the elf is later than the start date of the period.  If those criteria are satisfied, then we take the minimum of the period end date, the retirement date and the replacement date and subtract it by the maximum of the period start date and the elf’s start date.  You should add one [1] to this number as the elf is still working on their replacement date.  The formula this results in is as follows:

CALCULATE THE PRESENT PRODUCTION PER ELF

Now we can use the Days Employed to find the annual present production for each elf.  This is as simple as multiplying the daily present production by the days employed:

CALCULATE THE DAYS OVERLAPPING

Now that we have the forecast, we need to calculate the actual benefit of replacing the workers early.  There are different ways to approach this, but we will start off by finding the days in each period where the end and start date of the current and replacement workers overlap.  The formula we have used here is:

This formula first checks whether the selected Elf ID appears in the Replacement ID column; if not, it returns zero [0].  If it does, it replicates the calculation from the days employed section.  However, instead of using the Start Date and Replacement Date, it uses the Retirement Date and Replacement Date to calculate the overlapping days between the two of them.

CALCULATE THE REPLACEMENT BENEFIT

To calculate the replacement benefit we need to find the daily difference in production and multiply it by the number of overlapping days.  The formula that does this is as follows:

The first IF statement checks whether the selected Elf ID has a replacement code, so whether the corresponding elf is replacing another elf.  If it does, then it uses INDEX and MATCH to find the production rate of the retiring elf and subtracts it from the production rate of the selected elf.  This difference needs to be multiplied by the Days Overlapping.  However, it is not as simple as just multiplying them together.  The days overlapping we found was for the elf being replaced, so we need to search for it using INDEX and MATCH with the Replacement ID of the selected elf.

The Final Friday Fix will return in January with a new Excel Challenge.  In the meantime, please look out for the Daily Excel Tip on our home page and watch out for a new blog every business working day.  Happy New Year!!