Forecast with Multiple Variables Part 3
Glenn -
I also received clarity on the Flow Through Expenses for the Forecast piece. There will be one Flow Through Expense Column for each Fiscal year. Flow Through Expenses will take into account all Expenses for a full Fiscal Year in one column for each row. The only rows which should be included are "nnnn" through rows "hijk". So for example, let's say we are trying to calculate the Fiscal 2015 Flow Through Expenses for Row 21 which is "tttt". Assuming this project starts in Fiscal 2014, we would use the Year 1 number of $130,000. Similarly, if we are calculating the Fiscal 2016 Flow Through Expense for that same Row, we would use the entire Year 2 number of $132,000. In other words, Flow Through Expenses is just calculating one full year of expenses for a certain group of rows (nnnn thru hijk), but it would be using the first year's number to forecast for the year AFTER the project starts. So, at any point, if I were to select Fiscal 14 in a pivot table, it should have a Flow Through Expense column at the end. The same would go for Fiscal 15, Fiscal 16, etc. Additionally, if I wanted to select a Year 1 view or Year 2 view, it should also show the Flow Through Expenses for each of the views selected.
I'm not sure where the best place to put this column, perhaps after the months? Years?
Let me know if you have any questions about the Flow Through portion, I know it can seem confusing, but it's easier than what I originally thought it was.
Lastly, after we get the Flow Thru Expense, set up, I think we can set up the pivot table reporting,
Thanks!
I also received clarity on the Flow Through Expenses for the Forecast piece. There will be one Flow Through Expense Column for each Fiscal year. Flow Through Expenses will take into account all Expenses for a full Fiscal Year in one column for each row. The only rows which should be included are "nnnn" through rows "hijk". So for example, let's say we are trying to calculate the Fiscal 2015 Flow Through Expenses for Row 21 which is "tttt". Assuming this project starts in Fiscal 2014, we would use the Year 1 number of $130,000. Similarly, if we are calculating the Fiscal 2016 Flow Through Expense for that same Row, we would use the entire Year 2 number of $132,000. In other words, Flow Through Expenses is just calculating one full year of expenses for a certain group of rows (nnnn thru hijk), but it would be using the first year's number to forecast for the year AFTER the project starts. So, at any point, if I were to select Fiscal 14 in a pivot table, it should have a Flow Through Expense column at the end. The same would go for Fiscal 15, Fiscal 16, etc. Additionally, if I wanted to select a Year 1 view or Year 2 view, it should also show the Flow Through Expenses for each of the views selected.
I'm not sure where the best place to put this column, perhaps after the months? Years?
Let me know if you have any questions about the Flow Through portion, I know it can seem confusing, but it's easier than what I originally thought it was.
Lastly, after we get the Flow Thru Expense, set up, I think we can set up the pivot table reporting,
Thanks!
ASKER
In this example, yes you are correct. The real data has projects starting in FY15 and beyond. That's fine to insert them to the Far Right. This will just be the Datasheet, the reporting will come next.
I've added FlowThrough columns for FY2015 through FY2019 (columns IG though IK). They use a formula to check the Fiscal Year start value in column P against the year value shown in the "Flow Through" headers in row 1 of the group.
formula in IG15:
=IF(VALUE(RIGHT(IG$1,4))-$
So, only if the fiscal start year is at least one year greater then the reporting Fiscal Year for that FlowThrough column will the appropriate Year value be reported (again, using an OFFSET function to move to the correct column via the difference between the Fiscal Year start and FlowThrough year).
Please check by changing the "Date Completed" values (column J) for any example type to a future fiscal year and ensure that the FlowThrough values are as-requested.
NOTE: A couple of expense categories ("xxxx", "zzzz") derive their values from categories not in "nnnn" through "hijk" range. I did not use the same subtotalling formulas as in the rest of the workbook, as I believe you want to replicate the Year 1-5 values absolutely. So ALL Flow Through formulas use the same logic in retrieving the appropriate Year 1-5 value.
-Glenn
EE-Sample-FC.xlsx
formula in IG15:
=IF(VALUE(RIGHT(IG$1,4))-$
So, only if the fiscal start year is at least one year greater then the reporting Fiscal Year for that FlowThrough column will the appropriate Year value be reported (again, using an OFFSET function to move to the correct column via the difference between the Fiscal Year start and FlowThrough year).
Please check by changing the "Date Completed" values (column J) for any example type to a future fiscal year and ensure that the FlowThrough values are as-requested.
NOTE: A couple of expense categories ("xxxx", "zzzz") derive their values from categories not in "nnnn" through "hijk" range. I did not use the same subtotalling formulas as in the rest of the workbook, as I believe you want to replicate the Year 1-5 values absolutely. So ALL Flow Through formulas use the same logic in retrieving the appropriate Year 1-5 value.
-Glenn
EE-Sample-FC.xlsx
ASKER
Hi Glenn -
Thank you very much for this, it looks great!
Sorry, I should have provided more clarity with the sub-total/formula rows. Because they actually do derive their values from the rows above, they should follow the same sub-totaling formula, versus replicating the absolute values of Year 1-5, because I don't want those values from the rows above. I realize that some of the rows will be zero in the flow-through columns, but nonetheless it should subtotal correctly.
Thanks!
Thank you very much for this, it looks great!
Sorry, I should have provided more clarity with the sub-total/formula rows. Because they actually do derive their values from the rows above, they should follow the same sub-totaling formula, versus replicating the absolute values of Year 1-5, because I don't want those values from the rows above. I realize that some of the rows will be zero in the flow-through columns, but nonetheless it should subtotal correctly.
Thanks!
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Looks great!
You've done a fabulous job with this, I'm very grateful that you have a Finance/Accounting background too. Now onto the reports? I'm a pivot table guru, but the only thing I'm struggling with is how are we going to be able to filter by Yr 1 through Yr 5 and also the fiscal years. The way it is now, each year is a column header and to get something like that to work with a filter you would have to have the years going down the rows with a column header. You would almost have to transpose everything and flip it on it's side. The previous owner of this used combo boxes with advance filters because he wasn't familiar with pivot tables, and it was just too many formulas and memory to get it to work properly. It took 20 minutes for the file to even open and about 15 minutes to save each time. Again my goal is to simplify and streamline as much as I possibly can.
You've done a fabulous job with this, I'm very grateful that you have a Finance/Accounting background too. Now onto the reports? I'm a pivot table guru, but the only thing I'm struggling with is how are we going to be able to filter by Yr 1 through Yr 5 and also the fiscal years. The way it is now, each year is a column header and to get something like that to work with a filter you would have to have the years going down the rows with a column header. You would almost have to transpose everything and flip it on it's side. The previous owner of this used combo boxes with advance filters because he wasn't familiar with pivot tables, and it was just too many formulas and memory to get it to work properly. It took 20 minutes for the file to even open and about 15 minutes to save each time. Again my goal is to simplify and streamline as much as I possibly can.
I may need a graphic example of how you'd like to slice up the data on a PivotTable, even if it's basic.
ASKER
Hi -
I've attached the workbook with 3 very basic Pivot Examples which I will explain below, but I will be using the data for many different purposes, so I don't want you to try to replicate what I've done necessarily because I'm going to slice it and dice it many ways. The challenge I'm presenting to you is being able to filter by year, and/or Fiscal year and/or Fiscal Quarter, because the current setup of the Data table will make it difficult. I'm even open to other suggestions like Input Boxes/VBA Code etc. in order to pull up specific groups or lines of data.
1) The "Pivot" tab is set up with the "Expense" in the Row Labels and the individual months across the column labels. In the drop-down boxes in Rows 3 and 4, that's where you can filter by "Type" and "Name". You will see two other boxes highlighted in cells A1 and A2 which I just typed in with a question mark, because that's how I'd also like to filter this data- hence the challenge.
2) The "Pivot (2)" tab with the "Type" in the Row Labels and the "Expense" (summed by Forecast) is going across the column labels. I'm filtering by "Name", but again, I need to Filter/Toggle by Year, Fiscal Year or Fiscal Quarter (I manually added yellow highlighed cells in first two rows indicate how I'd like to be able to filter this data).
3) The "Pivot (3)" tab is actually not a pivot table, but an example of how this was set up using combo boxes and advanced filters, which I don't want to do unless we can do it with code or something that won't require 10,000 formulas. Do you see the Fiscal year Drop-down at the top of the sheet, which allows the users to select a Fiscal Year? I'd like to do that in a pivot table but I'd also like to do the same type of filter to select by Year 1, Year 2, etc. and Q1, Q2, etc.
I've attached the workbook with 3 very basic Pivot Examples which I will explain below, but I will be using the data for many different purposes, so I don't want you to try to replicate what I've done necessarily because I'm going to slice it and dice it many ways. The challenge I'm presenting to you is being able to filter by year, and/or Fiscal year and/or Fiscal Quarter, because the current setup of the Data table will make it difficult. I'm even open to other suggestions like Input Boxes/VBA Code etc. in order to pull up specific groups or lines of data.
1) The "Pivot" tab is set up with the "Expense" in the Row Labels and the individual months across the column labels. In the drop-down boxes in Rows 3 and 4, that's where you can filter by "Type" and "Name". You will see two other boxes highlighted in cells A1 and A2 which I just typed in with a question mark, because that's how I'd also like to filter this data- hence the challenge.
2) The "Pivot (2)" tab with the "Type" in the Row Labels and the "Expense" (summed by Forecast) is going across the column labels. I'm filtering by "Name", but again, I need to Filter/Toggle by Year, Fiscal Year or Fiscal Quarter (I manually added yellow highlighed cells in first two rows indicate how I'd like to be able to filter this data).
3) The "Pivot (3)" tab is actually not a pivot table, but an example of how this was set up using combo boxes and advanced filters, which I don't want to do unless we can do it with code or something that won't require 10,000 formulas. Do you see the Fiscal year Drop-down at the top of the sheet, which allows the users to select a Fiscal Year? I'd like to do that in a pivot table but I'd also like to do the same type of filter to select by Year 1, Year 2, etc. and Q1, Q2, etc.
ASKER
Sorry, forgot to attach the workbook with my last comment.
Copy-of-EE-Sample-FC-06-16-14.xlsm
Copy-of-EE-Sample-FC-06-16-14.xlsm
Oddly enough, Pivot (3) might be the easiest to implement. I might change the embedded combo boxes for data validated cells, but it's possible to use SUMIFS functions to retrieve this information (I tried it with FY2014 Forecasts). Just need to know what FYdata will actually be needed - FY Forecast, Flow-through, something else?
As for the other two tables, it can be a PITA trying to quickly change metrics that aren't truly filters. I've tried a few different methods: one was a Pivot on a Pivot, another was using GETPIVOTDATA, and another was using VBA. I'll need to dig through my knowledgebase and see what I can come up with for you. I'd prefer not to use VBA; I don't mind coding, but I'd rather keep the workbook macro-free if possible.
-Glenn
As for the other two tables, it can be a PITA trying to quickly change metrics that aren't truly filters. I've tried a few different methods: one was a Pivot on a Pivot, another was using GETPIVOTDATA, and another was using VBA. I'll need to dig through my knowledgebase and see what I can come up with for you. I'd prefer not to use VBA; I don't mind coding, but I'd rather keep the workbook macro-free if possible.
-Glenn
ASKER
PITA, lol! I will have to use this phrase! Before I caught on that you were saying , I was trying to figure out if you meant the animal rights group or the bread, or if it was a typo:-)
Exactly, "Pivot 3" is not actually a pivot though. It's just a report with all sorts of formulas all over the place to feed the filtering and combo-boxes, and it was/is a nightmare to maintain. Although it may be easy to implement, it's not easy to manipulate different views. As you know, one pivot table can give me all of those different views and more, which is why I preferred that option.
Anyway, the FY data that is actually needed is everything we have by month, which includes, Plan, Forecast, Actual, Flow Through - Everything. I'll need to toggle to each Fiscal Year and/or Year 1, Year 2, etc. and it should be able to pull up everything on that Data sheet pertaining to that year, including Forecast by Month if that's what I'm looking at.
Exactly, "Pivot 3" is not actually a pivot though. It's just a report with all sorts of formulas all over the place to feed the filtering and combo-boxes, and it was/is a nightmare to maintain. Although it may be easy to implement, it's not easy to manipulate different views. As you know, one pivot table can give me all of those different views and more, which is why I preferred that option.
Anyway, the FY data that is actually needed is everything we have by month, which includes, Plan, Forecast, Actual, Flow Through - Everything. I'll need to toggle to each Fiscal Year and/or Year 1, Year 2, etc. and it should be able to pull up everything on that Data sheet pertaining to that year, including Forecast by Month if that's what I'm looking at.
ASKER
Hi Glenn -
Just following up with you on this request - anything yet?
Just following up with you on this request - anything yet?
I have - I think - a solution for Pivot (3). I left the design in-place and modified the formulas to pick up the correct forecast total for any given project or year. If you need another measure (other than 20xx Forecast), let me know.
The other two pivot tables...nuthin' yet. But will figure something out soon.
-Glenn
EE-Sample-FC-06-23-14.xlsm
The other two pivot tables...nuthin' yet. But will figure something out soon.
-Glenn
EE-Sample-FC-06-23-14.xlsm
ASKER
Thanks much Glenn, Did you see my previous comment above this last one? The purpose of the pivot is that it's flexible enough to show several different views. I gave you different examples of the views that I'd like to obtain myself, but I don't necessarily need 3 different pivot tables. The one you replicated is not a pivot table at all, just another example of a view I'd lie to achieve with my data.
Once we solve the issue of how to filter by Fiscal Year and Year 1, Year 2, etc. my problem is pretty much solved, and I can accomplish the views on my own.
Let me know if you need me to clarify further.
Thanks!
Once we solve the issue of how to filter by Fiscal Year and Year 1, Year 2, etc. my problem is pretty much solved, and I can accomplish the views on my own.
Let me know if you need me to clarify further.
Thanks!
Yep, I'm hoping to achieve something similar with the other two sheets. Again, there may be PivotTables that drive them but the resulting sheets themselves will probably behave more like "[Not] Pivot (3)" :-)
ASKER
I guess my point is that I only need one pivot table built, from which I can manipulate all of my data into the different views I need. I don't need the different sheets built because pivot tables are my expertise. Those are just examples of views I should be able to pull from the same pivot table. My challenge is with the datatable. It's not formatted in a matter to do what I need in a Pivot Table to be able to filter by Fiscal Year.
So, if you can figure out one way that I can manipulate the same data to get several different views, that's what I need.
So, if you can figure out one way that I can manipulate the same data to get several different views, that's what I need.
I have a very preliminary idea for your other "Pivot views." See the attached example.
I've added a "Project View 2" sheet and it's based on a pivot table on "PV2_Src". Right now it only shows annual forecast totals which can be selected via a picklist. I can see variations on this for Plan, Flow Through, maybe Quarterly data. The "NAME" category is controled on the PV2_Src sheet, but that it might be possible to trigger that on this as well.
This kind of action is the only way you'll be able to select "years" or "fiscal years" to report. I mentioned before about using VBA to automate this (ex., quickly changing the columns in a PivotTable), but this does something similar without macros. It just needs a large, "farmable" PivotTable to get the data (and i'm using the GETPIVOTDATA function here as I mentioned before).
Hopefully, this is in the right direction, which is the point of presenting it in this form. Let me know what you think and we can go from there.
-Glenn
EE-Sample-FC-06-24-14.xlsm
I've added a "Project View 2" sheet and it's based on a pivot table on "PV2_Src". Right now it only shows annual forecast totals which can be selected via a picklist. I can see variations on this for Plan, Flow Through, maybe Quarterly data. The "NAME" category is controled on the PV2_Src sheet, but that it might be possible to trigger that on this as well.
This kind of action is the only way you'll be able to select "years" or "fiscal years" to report. I mentioned before about using VBA to automate this (ex., quickly changing the columns in a PivotTable), but this does something similar without macros. It just needs a large, "farmable" PivotTable to get the data (and i'm using the GETPIVOTDATA function here as I mentioned before).
Hopefully, this is in the right direction, which is the point of presenting it in this form. Let me know what you think and we can go from there.
-Glenn
EE-Sample-FC-06-24-14.xlsm
Hi,
Long time...over a month! Do you still have any issues with this latest solution? If so, let me know, otherwise can you close this question by accepting one or more of my previous submissions as an answer?
Thanks,
Glenn
Long time...over a month! Do you still have any issues with this latest solution? If so, let me know, otherwise can you close this question by accepting one or more of my previous submissions as an answer?
Thanks,
Glenn
ASKER
Hey Glenn -
My sincerest apologies, my favorite aunt passed away suddenly and it's been a very stressful month. In addition the projects from work ramped up and my manager went on vacation for 2 weeks, so this got put on hold. I have not forgotten about you and have received notifications about the Open Question, but just haven't had the time or mental energy to get back to you on this before now.
Everything else is great except I think we need to spend a little more time on the pivot tables because I'm just not quite sure if they are going to do what I need, but in any event, I will close this question and open another.
Again, please forgive me and I would still like to continue to work with you. Next week my head should be above water and I'll have more time to think about what I need.
My sincerest apologies, my favorite aunt passed away suddenly and it's been a very stressful month. In addition the projects from work ramped up and my manager went on vacation for 2 weeks, so this got put on hold. I have not forgotten about you and have received notifications about the Open Question, but just haven't had the time or mental energy to get back to you on this before now.
Everything else is great except I think we need to spend a little more time on the pivot tables because I'm just not quite sure if they are going to do what I need, but in any event, I will close this question and open another.
Again, please forgive me and I would still like to continue to work with you. Next week my head should be above water and I'll have more time to think about what I need.
ASKER
Per my last comment, I will open a new question about the pivot tables/reporting. Thank you again for all of your time and attention, you have been great!
I'm sorry to hear about your aunt's passing. Life has far more importance than this busy work.
I have a search that checks for any questions with "GlennLRay" in them, so just add my username somewhere in your post and I'll pick it up.
Those Pivots are gonna be a challenge; i can sense it.
Take Care,
-Glen
I have a search that checks for any questions with "GlennLRay" in them, so just add my username somewhere in your post and I'll pick it up.
Those Pivots are gonna be a challenge; i can sense it.
Take Care,
-Glen
Flow Through - Year column/value
FY2014 - N/A
FY2015 - Year 1
FY2016 - Year 2
FY2017 - Year 3
FY2018 - Year 4
FY2019 - Year 5
Perhaps your actual data has projects starting in FY2015, in which case the above isn't true. But if the above holds true for you actual data, a simple repeat/re-labeling of the "Year x" should suffice.
To avoid confusion with the actual FY summary columns (in lt. orange), I'm leaning to inserting the FlowThrough columns at the far right (IG through IK).