Business Intelligence

Question: Excel is known for its excellent formulae & function capabilities. As such I extensively use Excel to calculate the variance between the target sales amount and the actual sales amount for our salesagents. However, sometimes the formulae returns errors/error messages such as #DIV/0! Is there a way of trapping error messages, so that a [...]

{ 0 comments }

I am sure anyone that has played with Excel – and that’s just about everyone – has come across Pivot tables, and there will be users reading this that use Pivot tables ranging in skills and understanding from knowing what they are, to doing some seriously fancy stuff. Microsoft have now also introduced Power Pivot [...]

{ 0 comments }

Question:     I need to export the stock list to our database program for further analysis.  Last time I tried the routine, the names of the products were truncated because the wrong field size was used in the table. How can I return the number of characters in a text string? This will enable me to identify the maximum [...]

{ 0 comments }

Question: I usually export my inventory data into Excel for further analysis. However the products names appear in upper case when I do this. Is there a way of converting the product names to lowercase and then capitalizing each word – how do I do this in Excel? Answer: By using the proper function Why:  [...]

{ 0 comments }

Question: As an effective tool for working with large volumes of data, I usually use PivotTables to summarize, organize and view the same data in many different ways quickly and easily. However the data is usually in one data source/range. Is it possible to create a PivotTable based on multiple data consolidation ranges? Answer: Yes, with the PivotTable [...]

{ 0 comments }

If you haven’t read the blog post on this blog site by Sean Elliott about simplifying the work of a financial director, I would take a quick read here. Although he covers some product specific material, his message should resonate anyone who manages finances across the globe. He is a modern example of someone that [...]

{ 0 comments }

As an FD, looking at financial data is an everyday part of my working life. It stands to reason that I should be ever on the search to find smarter ways of interpreting the data more effectively. I am fortunate to work on and in a Business Intelligence (BI) company and am in the front [...]

{ 0 comments }

Question:  Is it possible to view different parts of the data worksheet at the same time?  For instance when I import the payroll summary report from the payroll system, I would like to see the top ten & bottom ten earners. We have over five hundred employees on our payroll and this analysis is crucial [...]

{ 0 comments }

Question:  Is it possible to view different parts of the data worksheet at the same time?  For instance when I import the payroll summary report from the payroll system, I would like to see the top ten & bottom ten earners. We have over five hundred employees on our payroll and this analysis is crucial [...]

{ 0 comments }