Data Analysis actual vs forecast

Pivot tables are of extremely limited value when the data are not normalized. So I normalized it.

Sheet Data (2) shows an intermediate step. I removed the rows that were not of interest. and then made an Excel table of it. Then I clicked on Data > From Table / Range. This open PowerQuery.

I selected the Transform Tab and selected all the cells EXCEPT the Product cell. Then I clicked on Unpivot Columns. I went back to the HOME Tab and selected Close and Load.

This produced the Table in Normal Table 2. This is NORMALIZED Data and you can now do all kinds of pivot table analysis with it. In the future do data entry in this table - you can even delete the data (keep the headers) and copy / paste new data in. Tables "remember" formulas and copy them all the way down.

I decided to get fancy here/ In Cell I1 I made a drop-down list using Data Validation against the table in Column U. If you change product names you will have to update this table manually. I gave Cell I1 a static name: Sel_Product.

Cells I3 and I4 make composites for the combination of product name and whether it is Actual or Forecast. Columns J3 and J4 find the row in the pivot table where these composites are found.

Then I made a couple of named dynamic ranges. Dynamic ranges are created using Formulas > Name Manager and the Offset command.

The offset command takes 5 variables =OFFSET(Start Here, Go down x rows, Go right y columns and return m rows, and n columns)

The first of these named ranges is Plot_Series =OFFSET('Table Normal 2'!$L$5,0,0,COUNTA('Table Normal 2'!$L:$L)-2,1)

Start in Cell I5 and go down 0 rows and right 0 columns so you are still in Cell I5. then count the number of values in column L:L, Subtract two from that because we literally do not want to count the headers and filters. That's the number of rows I want. I only want one column.

This is standard procedure for overlaying a named dynamic range on a pivot table. Normally you don't want the Grand Total, but in this case we do.

This is the range the match command uses in cells J3 and J4.

I made a couple more ranges:

Plot_DOW =OFFSET('Table Normal 2'!$M$4,0,0,1,COUNTA('Table Normal 2'!$M$4:$T$4)-1) - this is another dynamic range to account for Friday possibly showing up or some other day of the week dropping off.

PLOT_Actual =OFFSET(Plot_DOW,'Table Normal 2'!$J$3,0)
Plot_Forecast =OFFSET(Plot_DOW,'Table Normal 2'!$J$4,0)

You will notice these last two ranges have only three parameters: a start range, go down and go right. Since the start point is an entire range, the offset command assumes that you want the same number of rows and columns returned. So this is merely the Plot_DOW range shifted down by the matching row numbers in Cells J3 and J4.

I made the chart "from scratch."

First I instated a blank chart. Then I right clicked on it and selected Select Data. I clicked Add to add a series and gave it the title Actual. In the series values I typed: 'Table Normal 2'!Plot_Actual. This plots the actual series no matter where it is or how big it is. I did a similar thing for Plot_Forecast.

On the Horizontal Axis Label I used Plot_DOW

I know I introduced a number of advanced topics here, I'd be happy to discuss any of them more with you.

If there is one take-away that you should get it is the importance of normalized data.

Although I enjoy VB Coding a lot, I try to avoid it as much as I can and let Excel do the "heavy lifting."

This is why I invoked a pivot table to group the data even though I did not use the pivot table directly for charting. I found that overlaying pivot table results with a named dynamic range and using them as the sources for charting gives me a lot more flexibility.

When dealing with pivot table issues, very often a helper column can solve the issue. When I looked back at the data in the table, I found that the dates in column A are not real dates. They are strings that look like dates. Fortunately, they can be converted to dates using the DATEVALUE() function. Unfortunately, if you do a manual entry or import the data some other way, the dates may come in as actual dates and DATEVALUE() of an actual date is an error.

So the first helper column I put in was Real Date with the formula =IF(ISNUMBER([@Date]),[@Date],DATEVALUE([@Date])) which says if the value is a date, use the date otherwise use DATEVALUE() to convert it into a date.


I then created two named static ranges in cells Y1 and Y2 with the names Start_Date and End_Date.

So the next helper column in the table is Date OK with the formula =AND([@[Real Date]]>=Start_Date,[@[Real Date]]<=End_Date) which is true if real date is between the dates inclusive.

Since the pivot table is based on an Excel table, it picked up the new columns when I refreshed it and I used Date OK as a filter on the table. Excel put this filter on row 1, so it did no push the pivot table down and I did not have to change my definition of Plot_DOW. (see below)

You can hide the helper columns if you wish.

For the purposes of aesthetics, I moved stuff around using CUT and paste. So the named static ranges still have the same name, but Plot_Series and Plot_DOW changed. I redefined these as
=OFFSET('Table Normal 2'!$M$10,0,0,COUNTA('Table Normal 2'!$N:$N)-6,1)
and
=OFFSET('Table Normal 2'!$N$9,0,0,1,COUNTA('Table Normal 2'!$N$9:$U$9)-1)
respectively.

Everything else has been moved off to the side where it could be hidden.