Forum Discussion

TheG's avatar
TheG
Icon for Advocate I rankAdvocate I
9 years ago

Forecast V Actual Comparison Table

Hi Community,   I was looking for the best practice when deciding how to report on Actuals (from an accounting system exported into Excel) and Forecasts (written in excel spreadsheet system).   I...
Need Help
Tutorial Requests
TheG's avatar
TheG
Icon for Advocate I rankAdvocate I
9 years ago

Thanks v-caliao-msft

 

When i merge i dont get the complete matches probably as some account numbers are not in both tables...i guess i need to rework the table layout and split account numbers out, jobs, account names etc..

 

 

 

 

 

Anonymous's avatar
Anonymous
Not applicable
9 years ago

As noted above and based on the sample data screenshot, merging Actuals and Forecast could be problematic - you have multiple Actual records for July 2014, but only 1 Forecast record that I can see for that month.

  • TheG's avatar
    TheG
    Icon for Advocate I rankAdvocate I
    9 years ago

    HI Anonymous yep i think i need to re-organise my tables design.

     

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      9 years ago

      If this is more than a once-off analysis, I suggest stepping back briefly and deciding what you want to measure and visualise, and how flexible for future change it needs to be.  Then re-organise tables to suit - e.g. if you're only going to ever want monthly comparisons (not fortnightly, or drill down to individual Actual records), you might consider summarising Actual records to monthly granularity for your measures.  If you do decide on that, Charlie's v-caliao-msftsuggestion about joining the tables could then make for a simpler model to analyse.

      • TheG's avatar
        TheG
        Icon for Advocate I rankAdvocate I
        9 years ago

        Anonymous v-caliao-msft

         

        Thanks again guys for input...Yes i need to look at table design...i came up with this but i think the FORECASTPF table needs to be linked via ACCOUNTNUMBER to the ACTUAL table...at the moment i have linked the FORECASTPF transaction date to DATEDIM.

         

        I will have other FORECAST excel spreadsheets to add too.

         

         

        The structure above wont work that well for example if I want to compare the forecast [AccountNumber] against the Actual [AccountNumber]....How should i format the forecast table? The actual table is fine with this setup. I will have other forecast tables as mentioned like FORECASTCC, FORECASTSL, FORECASTEMW, etc....all with similar fields from an excel cashflow as shown in the FORECASTPF table above.

         

        here is a sample of FORECAST spreadsheet i am using as the source. I have to manipulate a little to convert top row to headers and then unpivot the date/values.

         

         

        Currently, FORECASTPF is connected to JOBS (via Jobnumber) and DATEDIM (Calendar Date). I think it needs to be connected to AccountNumber but i cant get that relationship to connect.