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
v-caliao-msft's avatar
v-caliao-msft
Icon for Microsoft Employee rankMicrosoft Employee
9 years ago

Hi TheG,

 

In your scenario, since FORECASTPF table and ACTUALPF table almost have the same table structure, you needn't to use two tables. You can add then into one table with FORECAST and ACTUAL column. Select Combine > Merge Queries from the Home tab on the ribbon.

 

Regards,

Charlie Liao

 

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.