Using the Option Button Form Control To Build a Dynamic Inventory Valuation Trend Dashboard
This week I decided to build an Excel Dynamic Dashboard using the option button. Reviewing inventory valuation trends using historical data is a great way to oversee and monitor how much inventory you are carrying in your facility at any given moment. It can also act as an indicator of whether or not you are meeting inventory reduction initiatives and targets. In this dashboard I decided to use the option button as a means of allowing the user to select a period (year) which will in turn display the inventory valuation trend for that time frame. I also included the average value of the inventory within the dashboard which also updates dynamically based on the user’s selection.
Here are a few other things you may want to add to the dashboard to answer more questions:
- Add another data sheet and calculate and add the inventory turns and days on hand for that period to the dashboard.
- Build in a comparison chart showing which year the company/facility performed the best.
- Display a top 5 list for highest inventory valuation by year/month.
- Display the bottom 5 list for lowest inventory valuation by year/month.
- Use this data to find reasons for high levels of inventory and develop plans to reduce the value and implement JIT type systems
This is of course just a short list of things that you could build into this type of dashboard. I challenge you this week to find the solution to only displaying inventory for the months you have data for (i.e. for the sake of this dashboard, I included data for the last 3 months of 2013 which technically we would not have). Feel free to leave comments with the solution.
Until next week!
Cheers,
Download the Free Dashboard!
