Working with inventory can sometimes seem like a huge insurmountable job.  Determining how well each part is performing and forecasting when you’re going to run out of inventory for each part is not only really difficult but it’s nearly impossible.  There are way too many variables that are at play especially if you’re not working off of contracts with your customers.  That being said however, there are some ways that you can at least track and review your historical sales and usage trends for your inventory.  This will at least allow you to at a very basic level understand what’s actually been happening in the market in the months leading up to today.

 

The Purpose of the Excel Inventory Planning Dashboard

 

The purpose of this Excel inventory planning dashboard is very simple.  I want to provide you with a tool that allows you to review your sales and usage numbers for the past 12 months, 6 months and 3 months depending on which time period you feel is the most appropriate.  Based on the usage for the part selected, the user will be able to see how much the average monthly consumption is over the time period selected.  I’ve included minimum and maximum values as well that pull directly from an item information table that is included as part of the raw data set.  These minimum and maximum values will be displayed on the chart and will allow the user to see when they can expect to be below the minimum quantity required.  The dashboard also shows the exact day and month that the inventory will be depleted down to zero at the current consumption rate (it’s important to make note that the numbers in this dashboard do not include the current inbound supply as well as current demand levels and quantities).  The current on hand inventory will be uploaded and added as your raw data set whenever you feel appropriate.  Let’s have a look at the dashboard in action below:

 

3 Amazing Dashboard Techniques you can Learn by Downloading and Reverse Engineering the Dashboard

 

Before you can start to dissect this dashboard, you’ll want to unhide the calculations tab of the dashboard.  This can be done by right clicking one of the tabs, selecting unhide, and then selecting the calculations tab.  I am going to let you play with the dashboard and the settings on the calculations tab to learn how to perform the techniques below.

 

DOWNLOAD THE DASHBOARD

 

Amazing Dashboard Technique # 1: Learn How to Intelligently Use Shapes and Name Ranges to Build a Dynamic Dashboard Min/Max Level Display

 

Using shapes with name ranges in Excel is one of my favorite tricks when creating Excel dashboards.  It allows you to really make a visually appealing dashboard that displays dynamic data and information.  I have multiple name ranges setup as well as multiple shapes setup on the final dashboard that actually use a cell link to my calculations tab name range which of course changes dynamically based on the selection made by the dashboard user.  Here I want to show you how you can include shapes on your chart to display your min/max values without including every single data point associated to your min and max lines on your chart (data labels in this instance would show you every single data point).

 

 

 

Amazing Dashboard Technique # 2: Learn How to Use Option Buttons to Dynamically Switch your Monthly Average Usage Rate Between 3, 6 and 12 Months.

 

This one is a huge time saver.  Instead of creating three separate dashboards to display the monthly usage rate based on the past 3, 6 and 12 months averages, you can use the option buttons and refer to the option selected to generate and create your AVERAGEIFS formula and SUMIFS formula on the calculations tab.  This will allow us to make more accurate decisions based on the usage of the past 3 months and if required, we can go back and see the usage of the past 6 or 12.  Checkout what happens as the user clicks between Last 12 months, 6 months and 3 months.  You’ll notice that all of our data updates accordingly.

 

 

As the user switches between options, the values below on the calculations tab will update.  Our formulas, will then interact with these dashboard controls to update our numbers based on the selection.  To see this in action, you can download the dashboard.

 

Amazing Dashboard Technique # 3: Learn How to Calculate the Expected Stock Out Date Based on the User Selections.

 

This one is also pretty cool.  Being able to actually provide a date to the user based on their interaction with the dashboard makes for an amazing experience.  By review and dissecting the formulas, you’ll be able to determine exactly how to calculate the stock out date and display it for each part as well as each average monthly usage selection made by the user.  If the user wants to know when they will be out of inventory based on the last 3 months usage for ITEM1, they can do that.  They can also switch it up based on the average monthly usage rate from the past 6 or 12 months.

 

 

 

 

 

 

Download the Dashboard

 

DOWNLOAD THE DASHBOARD

 

Loading Your Data into the Dashboard

 

Loading data into the dashboard is very simple.  You’ll need to make sure that the fields match exactly the fields for each of the raw data set tables in the dashboard (I will outline them below).  Once you’ve got your data, you’ll want to highlight the current data in the data set and hit the delete key.  Next You’ll be able to drop your data directly into the table.  If you have fewer than the records that were previously in the sample data set, be sure to delete empty records in the table.

 

Load your On Hand Inventory Data

 

For the on hand inventory, you’ll need to download your most recent set of data for your on hand inventory.  The fields that you will need to include are the following and in the order shown below:

• ITEM NUMBER
• DESCRIPTION
• UOM
• QTY
• UNIT COST
• TOTAL COST
• CURRENT DATE

Do not change the names of the field headers and only delete the record data associated to the current table.   Also note that the date should be the same for all records in this data set (it will match the date that you pulled your inventory on).

Load your Usage Data

 

Here you will want to have a summarized consumption value for the entire month but displaying as the last day of the month (e.g. 31/01/2015).  You will want to have the last 12 months of usage data for each part number for the months prior to the current month of your current on hand inventory date.  This means that you will have 12 records for each item summarized as the total usage for each of the months.  Here are the dates that must be included and in the order mentioned below:

• ITEM NUMBER
• DESCRIPTION
• UOM
• QTY CONSUMED
• UNIT COST
• VALUE CONSUMED
• DATE

Again, make sure to only delete the records below the column headers and then replace the data with yours.  If there are any open rows at the bottom of the table, be sure to delete them after you’ve loaded your data.

Load your Item Information Data

 

This one is very simple.  You’ll follow the same directives as mentioned above and you’ll only need the following fields:

• ITEM
• DESCRIPTION
• MIN
• MAX

If you do not have any min/max values that you want displayed, simply leave them blank.  You do need to make sure to include all of your individual item numbers and no duplicates as this is the data set that will be used in your item number drop down on the dashboard.

 

Updating your Date Data Using Refresh All under the Data Tab

Once you’ve updated all of your data, you can then go to data tab of the quick access ribbon and update the pivot table that is used to summarize our dates for our calculations.  You can do this by simply hitting the refresh all button under the data tab.

Summary

 

This dashboard is a great but simple tool for measuring when you’ll be out of stock based on your historical consumption.  If you happen to download the dashboard, be sure to follow the instructions above for loading your own data set.  Have fun with the dashboard and if you’re excited to learn about how to build dashboards in Excel, review and dissect how all of the items interact with one another in order to build the final product.  If you have any questions, feel free to leave a comment down below.  Enjoy!

 

Disclaimer: By no means am I saying that this dashboard will be able to accurately forecast when you are going to run out of inventory.  This dashboard is intended strictly as a reference tool.  I am and will not be held responsible for any potential errors when using this dashboard or any decisions that are being made as a result of using this product.  This product is also mostly for the use of helping users dissect and learn how to use and create Excel dashboards.  Please use this dashboard at your discretion and make sure to clearly review your numbers on the dashboard to ensure its accuracy.