In business, I often see disputes between the finance and operations departments on which metric is a more important indicator of performance. Operational leaders (more often than not) like to see their numbers in terms of units/quantities and the finance executives/leaders want to see everything in dollars or currency. This is where the Excel option button form control comes in handy (note that if you are part of a finance team, you can also use this logic to switch between local currency versus your company’s global standard currency). I’m going to show you how you can use the option button to switch between quantity/units and currency all in one graph.
Building an Excel customer receivables dashboard came to me when I realized that most companies seem to really make use of their invoice information to look at their customer credit performance. Reviewing how your customers are doing in terms of making their payments plays a significant role in having a successful company because this is your lifeline for actually getting the bills payed. This cash flow plays an integral part in the business and also allows your company to grow.
So, What Does This Dashboard Do Exactly?
The customer receivables dashboard allows you to load up your customer contact information (from your customer table) as well as load up your invoice details and due dates (from your invoice detail or invoice table) and provides you with an easy to use interface that allows you to select any customer (using a drop down validation list based on the customer table information that you loaded into the table) and will immediately display your customer’s main contact information as well as the breakdown of their past due invoices. This will provide you with information like number of past due invoices, total past due invoice amount, oldest invoice and matching customer PO number to that invoice and finally the breakdown and distribution of late invoices based on age group (1-30 days, 31-60 days, 61-90 days and greater than 90 days). I’ve also included a neat graph that will show you the distribution of past due invoices based on aging category. Finally within the centre portion of the dashboard you’ll notice that the invoice aging category with the highest value of past due invoices will display the percentage of the total past due invoices.
I’ve included a quick video of the dashboard being used to give you an idea of how the dashboard works and whether or not this is something that may be useful to you.
I haven’t hadn’t opportunity to post much lately but I was using this today and thought it would be something that people might like to see. Excel 2013 allows you to integrate with the Bing Map App to display location specific data sets. Do you have any thoughts or ideas on how this may be useful to you? Have a look! (more…)
I know, I know. I am starting to get lazy and I haven’t really done any full length posts but these image posts will at least give you some quick pointers. That being said this is just another visual lesson on how you can use Excel Sparklines with the merging tool to make large charts that represent your data. (more…)
I decided to do another short post to save you some time. This image/post takes a look at how to setup the camera tool and then how you can use it on a separate worksheet to begin the setup of a new dashboard. The Excel Camera Tool allows you to take an image of a range of cells and create a picture/object out of it. Once that picture is created, it will automatically continue to refer to the cells that the image was taken of and will update as the cells change. Feel free to leave any questions in the comments. (more…)
Before I get started, I had a reader mention that in versions 2007 and greater you can use Excel Tables to build dynamic data validation list which is definitely another great way of handling a validation list in Excel. (more…)
