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…)
Getting the answers to your most important business questions is imperative for achieving success. Being able to find the answers to your questions can quickly give you insight into what business processes and procedures need to be fixed in order for you to lower your costs and maximize your returns. In this post we look at using Excel Pivot Tables versus the SumProduct formula to calculate which month had the highest production scrap within a given year. Being able to get this information can allow management to dig deeper into why a given month had such a high volume of scrap and help address issues that may have caused the spike. (more…)
For the past week or two, I have been working on building an online course through Udemy and it has been consuming most of my free time so I haven’t had a whole lot of opportunity to post anything here on my blog. That being said, I’ve been looking around for ways to generate random logical sample business data to help build up data sets and samples for my course. As a result, one thing that I’ve started to do is use the Index Randbetween CountA formula to pull data from one table located on a separate tab/worksheet that may hold a smaller set of data that will be required to build a full sample data set. (more…)
Excel Pivot tables can be a pretty handy tool for summarizing and making sense of your data. In this example, I’ve built a small table to show you how you can use pivot tables to calculate value differences between different dates. This can come in handy especially when capturing and reviewing day over day, month over month or year over year changes. In the case below, I’ve shown how you can monitor daily changes in your inventory valuation levels to capture changes in stock levels or value. You can then use this information to do further investigation from a transactional level to determine why your stock levels may have changed in that day (i.e. cycle counts, purchase order receipts, work order completions, customer RMAs/returns etc.). (more…)
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. (more…)
Excel dynamic charting is a great way to quickly answer questions without having to go through rows and rows of data. I’ve decided to start backwards this time by providing you guys with the final product/dashboard before giving you the tutorial on how to build it. This is a great way to learn. I haven’t hidden any of the formulas so feel free to play around with this to see if you can figure out what’s going on behind the scenes. (more…)