Budget vs Actual Analysis

[amit_kothari]

Budget versus Actuals analysis (Or Forecast vs. Actual, Target vs. Actual, etc.) is a common process used to evaluate business performance of a particular dimension, such as product category, cost center, etc. It is not uncommon for budget data is tracked and stored at a higher grain than the actual transactional data, which can make it difficult to compare budget to actual, as you can’t directly join the two data sets

For example, consider the following use case:

• There are two data sources as shown below. Oracle has actual sales transaction data, while Salesforce.com ( SFDC) has sales forecast data

• The requirement is to be able to do Actual vs Target Revenue analysis using Date and Product Category dimensions

• The data in Oracle and SFDC is at different grain. In SFDC the forecast data is at product category and month level while in Oracle the transactions are at product and date level. So we can’t simply union the sales and forecast data

• The Category ID is system generated column so it can’t be used to join across Oracle and SFDC

Recommended solution

1. Identify the common dimensions / columns between the two data sources. In this case, we will use  Product_Category.Category_Name

2. Create a new table called Product_Category_Common in Incorta with distinct list of Category_Name values. This can be Incorta table  or a MV if needed. 

3. Join Oracle.Product_Category and SFDC.Product_Category table to Product_Category_Common using Category_Name. 

   

4. You can now create an insight to compare actuals to budget using an aggregated table or your preferred visualization, using the Product Category field from the Product_Category_Common table that we created: