Being a Reporting Analyst for a huge enterprise often means that there might be no single centralised data warehouse where all enterprise data resides but rather a number of business intelligence systems, each of which tackles a different set of business operations data. Thus, you get one system for your sales analysis, another for P&L reporting, and yet a third one for all supply chain data.
Yet, should you happen to be the analyst supporting the regional management, it is highly likely that you might have to show a number of sales, financial and supply chain KPIs in the same high-level overview scorecard.
And that’s where challenge # 1 ensues: measure units between systems are not the same and the only tool you have to address this is Excel. Of course you can tackle the problem by using some extra formulas that will divide (or multiply) as neeeded.
However, it’s much better for the efficiency of your spreadsheet if you have this as a step in your data prep model
• Have you ever had to combine data where numbers were stated in different measure units – e.g. actuals in millions and budgets in thousands?
• Did you ever have to convert expenses to negative numbers?
If yes, you might find the VBA snippet below a handy trick to have up your sleeve.
Happy VBA coding!