Final Friday Fix: April 2018 Challenge

On the final Friday of each month, we’re going to present an Excel problem for you to puzzle over so that you can get your “Excel fix”. Challenge your office colleagues to see who can solve the puzzle quickest. There’s no prizes at this stage, you’re playing for bragging rights only!

Changing the Forecast Horizon

Here’s a common problem that many accountants face almost monthly. You set a budget, maybe in cash terms, full time equivalents or other resources. And then something happens to mess it all up. It could be the planning horizon extends or contracts and / or the project is brought forward or delayed.

Wouldn’t it be good to be able to simply change your start and end dates and have the budgeted numbers reforecast automatically? Well, that’s this month’s challenge…

The Challenge

Imagine you had just finalised the budget for a project and (say) it started in Period 3 and ended in Period 8 as pictured:

Suddenly, your boss tells you the amounts need to be reallocated on a “similar basis” but for Periods 4 to 15. That’s pretty easy, as this duration is double the original project length, so you would just attribute half of each period’s amount to the new periods, viz.

But then your boss challenges you further. They want to be able to flex both the start and end periods and be able to see the result instantly. They even give you a starter file. Could you do it? Could you create a spreadsheet that would reallocate the amounts quickly and accurately?

The gauntlet has been thrown down…

Sound easy? Have a go. We’ll publish one solution in Monday’s blog. Have a great weekend!