Final Friday Fix: 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 needs 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, he is considering bringing in new replacements early to improve present production per year.

Santa needs you to forecast the elves annual present production by year for the next five [5] years. Elves are identified by their Elf ID.  Each elf has a Start Date and a Retirement Date, along with an output rate of Presents per Day.  Elves that are replacing retiring elves have a Replacement ID which states the Elf ID of the elf they are replacing.  Additionally, Santa needs you to find the benefit of replacing the elves early as opposed to at their retirement.   You can download the question file here.

Good Elf to you at this time of year!

As always, we have some requirements:

• you may 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) are allowed
• no Power Query or VBA is allowed; this is a formula challenge.

Sounds easy?  Then give it a go.  As always, we’ll publish our solution in Monday’s blog.  In the meantime, have a great weekend and a happy new year.