Creating a forecasting tracking tool RRS feed

  • Question

  • Hi all, 

    I am building a model which tracks forecasts of consumption.

    My users will use a template file and enter line items and Qty for each round of forecast. 
    (On a nutshell, let's assume we have a 2 Y project and repeating forecasts for the following 2 months)

    The way I built the system so far, is that a power-query imports all the templates located in a folder and imports them into a single power pivot table. 

    That final table consists many projects and their progress throughout the repeating assessments.

    Here are my concerns: 
    1) I want to be able to present the total Qty at the end of each project + slice it to by line items
    2) I want to show the behavior throughout time (like in a line chart)
    3) Not to double count anything

    I thought of 2 ways how doing it:
    1) Track only the changes only- so that the main table will show the first revision, and when something was added/substracted or the quantities have changed, it will add a line item with the delta. To my understanding, this way I can present the final Qty at any given moment, but to display the behavior I will need to create an accumulative graph.
    2) Import all the files and let the system search for the latest timestamp and display only it. That way I can also show the differences between the dates of the files (timestamps) in a graph wise rather easy.

    Appreciate your feedbacks which option is better.

    If you can, helping to clarify how doing it will be more than appreciated 

    Thanks a lot


    Tuesday, July 7, 2015 6:55 PM