In writing about Historical Forecasts I want to know if you’ve been following this series? I’ve written in detail about all the steps to put together a project tracker that was easy to update, quick to use, and did most of the work automatically. It’s my strong belief that any Excel Workbook which you use frequently should conform to my 3-step Mantra: Update, Refresh, Share.
What this means is that you have to do a little more work up-front, in designing and building the workbook. This up-front work gives you automation. So that when you use the workbook, all you have to do is enter any updates, refresh the pivot tables, and that’s it, you’re ready to share the new data. No additional or separate steps are required to update any charts or other workbook elements. It’s all done for you automatically! Update, Refresh, Share!
The point of this mantra is that Excel can do most of the work for you if you set up the Workbook properly. I use this approach throughout this series of articles to show you how to do that. It does take a little extra work at the start. But the payoff is a fully automated workbook which is very quick to use.
And after you’ve used these techniques a few times, two things will happen. First, it will become much easier for you to implement other Excel trackers and dashboards using these techniques. It won’t be long before you are implementing these automated workbooks faster than you read my articles about them.
What Else?
Second, you’ll find many more opportunities to use these techniques. You’ll soon find yourself thinking how an Excel application might solve your current problem. Problems that you might have wanted to do, or maybe never even thought about doing, will seem much more achievable when you use automation techniques. I hope when this happens you’ll post a comment and tell me what new applications you’ve come up with.
Why do I bang on so much about automation? Because these days, there is so much work to do, that you simply do not have the time to fuss with all your dashboards and trackers every time there’s an update. To be effective, it has to be really quick. Update, Refresh, Share quickly.
And I think one more thing will also happen. By offloading from your brain the burden of so many routine and repetitive tasks, you’ll begin to see and understand things differently. Unburdened by mind-numbing details, your brain will come alive with new ideas and insights for improving your job. You’ll see possibilities that were always there, but hidden by a veil of silly, boring repetition. When it happens, post a comment to tell me about it. Those are very exciting career victories!
Series Summary
This is the eighth 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 totally for you. For workers in the Mobile RAN, you can download the tracker and begin using it immediately with very little modification.
And if you don’t work in Mobile Telecoms, that’s not a problem. Even if you are not looking for a project tracker, the techniques I show you can be used just about everywhere. I use these techniques for most of my dashboards because automation is so important to me.
In case you missed it
In the previous article, I showed you how to use the BTS Project Tracker to forecast the Project completion date. I introduced 2 Excel database functions, DMIN and DMAX. I haven’t talked about Excel databases before, so that was a completely new introduction. And a pretty easy one. Using DMIN and DMAX the way I did was about the simplest possible use of Excel Database capabilities. But the simple way I used them worked just fine for that application, finding the project start date and the latest date. There is much more they can do for you. You can use the previous article as a starting point experiment with Database functions on your own.
You can read about using Excel to Forecast at the link below.
Excel BTS Project Tracker – Forecasts
And if this is the first article that you’ve seen in this series, consider going back to read the very first article. You can decide for yourself if you want to read all the other articles. I think you’ll find many useful techniques in all the articles. But you can decide for yourself. Below is the link to the first article.
Excel Project Tracker (with the Coolest Excel Function Ever!).
You can download my tracker to follow along with this article
Click to Learn About Historical Forecasts
Approach
In this article, I extend the forecasting capability from the previous article. I want to create a history of those forecasts. Such a history helps you to gauge whether the forecast is optimistic, pessimistic or right on. I show you 2 new and very useful Excel functions which often work together: INDEX and MATCH.
What do I mean by “forecast is optimistic, pessimistic or right on”? Let me explain with an example. Imagine one week ago the tracker forecasted a 7 January completion. But after a full week of updates, today’s forecasted completion is 10 January. So in 7 days of progress, the project completion actually receded 3 days. If work was happening consistently and according to the forecast, it would be normal that the forecast would remain almost the same, 7 January. But in any project where work is done day-by-day, the completion date moves around a bit. What’s probably not normal is for you to see a clear summary of that movement. That clear summary is what we can derive from the Updates.
What I do in this article is to create a listing of the most recent forecasts. This gives you an early heads up if the project is slipping. It’s better you learn that earlier rather than later. When I finally add a dashboard to the Tracker I’ll include a chart showing the last 10 of those forecasted completion dates. The chart gives a pretty clear picture of whether the project is likely to complete on time, early, or late.
INDEX and MATCH Functions
To create that summary, I use Excel’s INDEX and MATCH functions. These two functions are so commonly used together that you’ll often see them written as one, like INDEX/MATCH. They work together much like a LOOKUP function. I’ve read that INDEX/MATCH is much faster than using LOOKUP, but have not been able to verify that. If you’re already familiar with INDEX/MATCH, do you have any information about that? Please post a comment if you do.
MATCH finds the position of a known value in an array. An array in Excel is a range of cells in a single column or a row. MATCH returns the position in the array at which that known value was found. Here is the formula for MATCH:
=MATCH(Lookup Value,Array,Match Type))
INDEX also takes a range and an offset value to find another value in the range that has the same offset.
In Excel, the terms “array” and “range” can be used somewhat interchangeably. An array is a special case of a range, a range having only one row or one column. An array will have one row and many columns, which you might call “wide”; or one column and many rows, which could be called “tall”. A range, of course, can have any number of rows and columns.
Here is the formula for INDEX:
=INDEX(Array,Row Number,[Column Number,Area Number))
Note that the Column Number and Area Number arguments are optional. Quite often, the Row Number argument to INDEX is a MATCH function. So MATCH finds the array position of a known value, the Lookup Value, and INDEX uses that value to find another value at the same position in the array but offset by some rows or columns. Can you see how similar that is to the LOOKUP functions?
Historical Forecasts Completion Dates
From the previous article, Excel BTS Project Tracker – Forecasting, I already have the forecast date for every date on which an update has been recorded. Those dates are laid out in column F of the Forecast worksheet. Now I want to be able to show those most recent dates as a grouping which automatically updates.
Using the data table which shows all the forecasted completion dates, I will grab the most recent ones. This is a dynamic list which changes every time updates are added to the Updates table of the Tracker.
On worksheet “Forecast” start a new data table adjacent to the previous one. I started in cell N6. This table will use Excel’s INDEX and MATCH functions to display the recent dates.
First, add a column with the values 0-9 in reverse order. These values will align with the 10 most recent Forecasted Dates. I’ll explain later how this column will be used.
Next, I have to do 2 things:
- Display the 10 most recent Update Dates.
- Display the Forecasted Date for each Update Date
I’ll start with the most recent Update Dates.
The Most Recent Update Date
Start by getting the date for the most recent update. I already know the value of this date because in the previous article I found it and assigned it a Named Range, Update_Latest. Also in the previous article, I created a data table containing all the update dates. I can use the first column of that data table as an array to be operated upon by a MATCH function. If I use the named range Update_Latest as the lookup value, MATCH will find it and return its position in the array. Here’s that formula, from cell O15:
=MATCH(UpdateLatest,$F$4:$F119,$AC$6:$AC$7))
If you put this formula in its own cell, it returns 45, which it the 45th position in the array. Because this array began in row 4, the 45th position is row 48. You can see for yourself that is is the correct date. I actually want to pass this position to an INDEX function.
10 Most Recent Update Dates
But before I do that, let’s think for a minute about the second most recent date. That’s the next date I need to have. How will I get that date, and all the succeeding older dates which I want? You can easily see that all the dates I want are stacked one on top of the other in column F. The second most recent date, after Update_Latest, is the row above Update_Latest. In other words, I could get the second latest date by subtracting 1 from the row number of the Update_Latest date. I could get the third latest date by subtracting 2, and so on.
Remember back to the first column of this data table, into which I entered the values 9 through 0. If I subtract the value of this column from the position returned by the MATCH, I’ll have the position in the array for all 10 of my latest dates. Here is the formula for the latest date:
=MATCH(UpdateLatest,$F$4:$F119,$AC$6:$AC$7)-N15)
When I “fill up” the formula, I get the position of each of the 10 latest updates. Pretty cool, eh? There’s one more advantage to this approach. It is generally considered a bad practice to embed numbers in formulas. The problem with doing this is that it is easy to forget why you did this, or what was the meaning of a particular value. In this case, it wouldn’t be so bad. But I want you to develop good Excel habits, so I’ll stick to using Best Practices myself.
Adding the Index Function
But I’m not ready to fill up the formulas, yet. The formula is not complete. I still need the INDEX function to return the actual update date. But most of the hard work already has been done. Here is the INDEX formula:
=INDEX($F$4:$F$119,MATCH(UpdateLatest,$F$4:$F119,0)-N6))
I’m using the 2-argument version of INDEX. Now when I fill down this formula, I get all 10 of the most recent update dates.
The last thing to do is to get the Forecasted Date associated with each Update Date.
Most Recent Forecasted Completion Dates
With the column of most recent Update Dates already created, it’s pretty simple to display the corresponding Forecasted Dates. I can use the recent Update Dates as an input to MATCH, without worrying about subtracting the 0-9 values. Here is that formula:</p
=INDEX($F$4:$F$119,MATCH(O6,$F$4:$I119,0),4))
The final argument is “4”, so that value in the 4th column of the array, column I, is returned.
Summary of Historical Forecasting
That concludes the effort of this article. The new data table shows the 10 most recent forecast dates, and these dates update automatically whenever project Updates are added to the Tracker.
Let’s review what I did. Seems like it went by pretty fast. First, I used a Pivot table to summarize the table of updates. Notice that this is the starting point for many of the things we automate in our Excel workbooks.
I then created a data table from that Pivot table. The advantage of a data table is it allows me to hardcode some aspects of the project, while still retaining the automatically updating characteristic of Pivot tables. That’s an important point. In this case, I hard-coded the formula which returned values from the Date Completed column of the Updates table. I had to guess approximately how long the project would last so that I wouldn’t have to add more formulas later. That would defeat some of my automation efforts.
Hard Coding?
It’s okay to hard code these formulas. in this case, such hard-coding does no harm. This worksheet will rarely if ever be seen by anyone else. It is simply an intermediate step to give me the results I want in a table.
This data table shows the cumulative completion percentage, the days required to complete the project, and the Forecasted Completion date. I need that information to get me what I want, the most recent forecast dates.
Once again, I summarized a table to create a new table. In other words, I summarized the Updates table with a pivot table, then summarized that Pivot table with my own data table, and lastly, I again summarized the data table with yet another data table. But this last summary table is the one I want, with the exact values I seek.
The final table uses the INDEX and MATCH functions, with parts of the preceding data table as array input arguments, to return the values I wanted, the recent Forecasted Completion Dates.
Payback
This may seem like a lot of work and too many intermediate steps. But remember our objectives: an automatically updating result with minimal steps required to use. So we’ve made a trade-off: extra development effort one-time, for a recurring reduction in effort. This is a good trade-off for any workbook you update frequently. Especially when you clearly understand the steps, new workbooks take far less effort to set up. In many cases, you’ll be able to use an existing workbook like this one as a template and save a huge amount of development effort on the next one.
By the way. I also created a chart. This chart shows whether each forecast date is better (time gained against the completion date) or worse (time lost against completion) compared to the previous forecast date. It also shows the cumulative change from the earliest forecast among these 10. And again, this chart updates automatically. I have not described the steps to create this chart. But now you already know everything about forecasting that I know.
Conclusion
Note that once again, this approach complies with my 3-Step Mantra: Update, Refresh, Share. Every time I add new updates to the update table, I refresh the pivot tables, and everything updates automatically. The latest forecast, and the chart to make that forecast pictorial update automatically.
Coming Up Next
Historical Forecasting in the tracker is looking pretty good now. Soon, I’ll add the chart to the future dashboard, along with time-based progress regional completion information and other ways to anticipate questions.
I have not yet thought too much about the dashboard, but that time is getting near. I’ll begin working that now. If I think of something the dashboard needs which is not yet available, of course, I’ll write about it. Otherwise, the next article might be about the Tracker Dashboard.
If you would like something special put on the dashboard, post a comment to bring it to my attention.
Please enjoy this article, and share it with your colleagues and friends. If you have any question or comments, or a better way to do Forecasts, please post a comment.
And please follow me on Twitter, @telecomvoices
Thanks for reading!
Leave a comment