When I first began writing this article, I set out to create a Project Dashboard; a self-updating, all singing, all dancing dashboard that would automatically reveal everything anyone might possibly want to know about this project.  Having such a Dashboard would have fabulous consequences.  Like shortening all the project status meetings because the answer to every question is right there in front of you.  Like having an up-to-date Dashboard about 3 seconds after applying the most recent updates.  Like having a ready-made slide for the monthly or quarterly management presentation.

Having an automatically and instantly updating Project Dashboard is like a superpower.  It’s like X-ray vision.  It’s like bringing a gun to a knife fight.  You’ll crush all the status meetings.   Even if the project is running late, having all the answers is the best way to approach any situation.   People will begin to look at you as an expert. They will start to ask you unrelated questions.  I suggest you have your superhero cape dry-cleaned in advance.

But that will be on a later day.  One of the first things I wanted to add to the Dashboard was a project completion forecast.   But I haven’t made one, yet.  So that’s what I’ll do in this article, give the Project Tracker a forecast.  I’ll show you a couple new Excel functions to implement forecasting, and once again I’ll achieve complete automation in the BTS Project Tracker.

Series Summary

This is the seventh article in my “Excel BTS Project Tracker” series.  In this series, I’m developing a completely functional Project Tracker for Mobile Telecoms using Microsoft Excel.  With each new article in the series, I enhance the previous version of the Tracker and teach you some new Excel techniques at the same time.  If you work in Technology, this tutorial is for you.  If you work in the Mobile RAN, you’ll be able to download the tracker and begin using it immediately.

If you don’t work in Mobile Telecoms, that’s no problem.  With very little modification you can use the tracker for other kinds of projects.  It’ll work great with most any project that has relatively few steps, you aren’t overly concerned about dependencies, and the same steps get applied many times.

Even if you aren’t looking for a project tracker, the techniques I show can be used just about everywhere.  I use these techniques for all my Dashboards because automation is so important to me.  For example, these same techniques helped me to find Least Cost Routes for hundreds of thousands of voice calling destinations. Once again, the techniques I’m teaching you will be useful to you every single day.

In case you missed it

In the previous article, I showed you how to create Named Ranges which automatically adjusted their size.  I call these Dynamic Named Ranges.  Dynamic Named Ranges make the chart in the Tracker auto-updating and eliminate the need for several steps every time the tracker is updated.

But Dynamic Named Ranges can be used almost anywhere a static Named Range can be used.  The way to decide whether or not to use a Dynamic Named Ranges is to ask yourself if this range changes, does it force you to take additional steps to account for the changes?  If the answer yes, then you should probably use a Dynamic Named Range.  The trade-off is a little more complexity, but much less work every time the workbook is updated.

You can read about Dynamic Named Ranges at the link below.

Excel BTS Project Tracker – Dynamic Named Ranges

And if this is the first time you’ve seen this series, I suggest also reading the first article.  That should help you decide for yourself if you want to read the articles.  Below is the link to the first article.

Excel Project Tracker (with the Coolest Excel Function Ever!).

You can download the Project Tracker to follow along with this article

Download the Tracker

Approach

In this article, I build on the work done in the previous articles to create a forecast of the expected completion date for the overall project. I’ll show you how to use the routinely added updates to automate that forecast, just like all the other metrics in this tracker.To do this, you’ll need to learn a couple new Excel database functions: DMAX and DMIN.

Excel database functions are simply a way to manage and access a range of worksheet data, a table, similar to how a relational database would. If you’re familiar with SQL, then you’ll immediately understand database functions.  And if you aren’t already familiar with SQL, don’t worry: using Excel database functions is straightforward.  Certainly for this very simple application.

I use the database functions to get 2 important pieces of information from the project: when it started, and when is the latest update.  Having those values, plus the already known “Weighted Completion %”, allows the completion date to be forecasted anytime, automatically.  Remember, my 3-step Mantra is Update, Refresh, Share.  That’s what automation does.

Excel Database Functions

In this article, I introduce you to Excel Database Functions, DMIN, and DMAX.  I’ll use DMIN to find the earliest update in the Tracker.  I’ll make it a Named Range called Start_Date.  I should say that it was completely possible to do this much earlier in the series, even in the very first article.  But at that time, the Tracker did not need a start date, so I didn’t bother.  Now, Start_Date is required to calculate a forecast.

Maybe you wonder why I don’t just manually enter the start date?  It would only be required one time, so it’s not a bunch of extra work.  True. However, if you enter a date manually, what happens if you change your mind?  You would have to find the start date and re-enter a new date.  Not hard, but how would up keep track of those changes?  With the approach I’m taking, all dates are already in the Updates Table, so nothing is lost, and no manual changes ever will be required.  It’s a much more reliable, and reliably automatic, approach.

Similarly to the start date, I’ll use DMAX to get the latest Update date, which I’ll call Update_Latest.

Having the start date and latest date is part of what makes the forecast possible. Both values are already available in the Updates table.

As a high-level summary, to calculate the forecast date, the Start_Date can be subtracted from Update_Latest to give the project duration thus far.  What also is needed, is the project completion percentage.  But I’ve already set up the Tracker to calculate the Weighted Completion %, which is the same thing.  Dividing the project duration by the Weighted Completion % percentage tells us how many days the entire project will take.  To calculate Forecasted Completion Date, simply add the total duration to the Start Date.

These values are automatically updated by the workbook, no manual effort required. Of course, Start_Date is unlikely to change often. But Update_Latest will probably change whenever updates are added. So your time savings could be significant.

Requirements

The requirements remain pretty much unchanged since since the previous article. I include them here only for reference.

1. It should be easy for workers in the field to submit Updates.
2. The PM should be able to easily import the Updates to the project tracker.
3. Project status should show automatically, without requiring further calculations.
4. Completion status should reflect the different levels of effort among the Tasks.
5. Completion status should be displayed for each defined region.
6. Progress should be displayed by Week.
7. No manual editing should be required for the Chart to reflect the latest project updates.

Procedure

Before I show you how to forecast the completion date, I should say that there is often more than one way to do anything in Excel.  If you find an easier way than this, please post your version in the comments so that we can all learn from you.  I’m always happy to learn a faster, simpler, better way to do something.

To begin, I have to determine the date of the first update.  I have to assign a range as a Database, so I’ll use the Updates table for that range.  Then, I’ll use a DMIN function with the “Date Completed” field as the Criteria.  Here is the DMIN formula:

=DMIN(Database,field name,Criteria)

I used the Updates table as the database.  The field name is the Column header for the “Date Completed” column.  The Criteria is a condition in the data. I used “Date Completed” as the Criteria.  I leave the Criteria value blank for this calculation.  (I won’t be covering the detailed usage of DMIN and DMAX is this article.)  Here is the exact formula I used:

=DMIN(Updates_Table,$H$1,$AC$6:$AC$7))

This returns the smallest value, which is the oldest date in column “Date Completed”.  I named this Range Start_Date, on the Updates worksheet cell T4.

To get the latest date, I need the DMAX function. DMAX uses exactly the same arguments as DMIN did earlier. But DMAX returns the largest value in the database range. I named this Range Update_Latest in cell T5.

If you downloaded the tracker, all this work can be see on the Updates worksheet, range AB3:AG8.

Forecast the Completion Date

We now already have what we need to forecast the project completion date.  Earlier in this article, I said the forecast completion can be derived from the formula “divide the difference of Update_Latest and Start_Date by the Completion %”.  I’ve already calculated all those components.

Completion % is shown in the Pivot Table from the previous article on worksheet “Dynamic Named Ranges”.  I’m going to add a link to that value and also make it a Named Range called Completion_Pct.  It’s now in cell T6.

Next, to forecast the project completion, enter the above equation into cell T7. Name this cell Latest_Forecast.

That is the Forecasted Date, and having it is pretty much what I set out to accomplish with this article. But I want to go a step further and show how to create a table which includes the forecasted completion date for every date of updates in the Update Table.  This is information which I’ll use in the next article.

Forecast Pivot Table

I like to group together common functionality, so I added a new worksheet to the workbook and called it “Forecast”.  Next, I’ll add a pivot table and a data table.

Setup a Pivot Table using the Updates_Table as the data source, drag field “Date Completed” to the Rows area, and field “Weighted_Completion” to the Values area.  Sort by increasing date.  We’ve done this so many times already that you can probably create Pivot Tables in your sleep now, right?

Now create a data table using both the above values. I did this starting at F3.  Make the first column the Update Date, a simple link to the first column of the pivot table.

Add a column which sums the value in the Weight Completion column of the Pivot Table.  I called this “Cumulative Completion %”.  This adds the completion percentage of each row to the cumulative sum of all rows above it.  Here is the basic formula, without all the error checking:

=SUM($B$4:B4)

See the first cell in the range argument has the “$”?  the $ means that the argument won’t change as I drag the formula down to subsequent rows: it will always stay B4.  Excel uses the “$” in ranges to say it is an “absolute” reference, meaning the reference does not change when the formula is dragged.  The second part of the range has no “$”, so the row number will increment when I drag or fill down. So the second row will have the formula =SUM($B$4:B5), and the 3rd,=SUM($B$4:B6) and so on.

Now add another column which subtracts the Start_Date from the “Date Completed” column, then divides the result by the cumulative completion percentage in this row.  I called this “Forecast Days to Complete”.  This result is the number of days required to complete the project.

Add a 4th column which is the sum of days required to complete and the Start_Date.  This column is the “Forecasted Completion Date” for every “Date Completed” value in the Updates table.

Lastly, fill the data table down a hundred rows or so.  Enough rows to accommodate all the update dates in the overall duration of the project.

Conclusion

You can conclude several things by looking at the data table we just created. One, the very first forecast date is not very meaningful.  That’s because Start_Date and Update_Latest are the same dates, so the “Forecast Days To Complete” is zero, which causes the “Forecasted Completion Date” to be the same as the Start Date.  That’s not very helpful!

Second, you can see that the “Forecasted Completion Date” jumps around a lot in the early days of the project.  That’s because there are so few updates that the baseline for the project can easily be altered. As more updates are added the Forecasted Completion Date should begin to settle down and will not change so much from update to update.

Third, can you see that this approach tends to be slightly conservative?  What I mean is that any Forecasted Date could be overly pessimistic.  Another update on the same date might come in from the field a little later.  Any update can only increase the “weighted completion %”, which would improve the actual Forecast date.  On the other hand, no update will every cause the Forecasted Date to slip.  So this approach actually protects you a little from being overly optimistic about the actual completion date.

Lastly, note that this Tracker is using artificial, mocked-up data.  So it might not generate realistic forecasts.  Your own tracker, used for real projects, should give much more reassuring results.

Coming Up Next

The tracker now has a decent forecasting capability.  This is likely to make it much more useful to you already.  But thinking ahead of my Project Dashboard I want to be able to show a recent history of Forecasted Completion Dates. That history will give us another indication if the forecast is accurate, or might change significantly before the project actually completes.  It will also help you, as the Project Manager, to anticipate some of the likely questions you’ll have to answer about the project.  So yet again, the automation mindset saves you time.

I would’ve put the Forecast History feature into this article, but it is already too long.  And Forecast History requires you to learn 2 more new Excel functions, INDEX and MATCH.  Watch for that article very soon.

Please enjoy this article. If you have any question or comments, or a better way to do Forecasts, please post a comment.

Follow me on Twitter:[twitter_follow screen_name=”telecomvoices” show_count=”false” size=”large”]

Thanks for reading!

Send me the Tracker