Newsletter
Hints for Better Excel SpreadsheetsConditional Formatting for Maintenance Free Formatting & PresentationShading alternate rows is a very common and easy way to make a table with multiple columns more readable. This is usually achieved in Excel by applying Format, Cells, Patterns, Color on alternate rows. So what's the problem? Well, if the table is then sorted, or rows are deleted or added the shading will be scrambled in the process. Conditional formatting is great way to keep the desired shading without any effort. This is achieved by using a =ROW()=EVEN(ROW()) formula as a conditional format across the entire table as follows.
How does this work?
Therefore when these two formulae are combined in a “Formula Is” condition such as "=ROW()=EVEN(ROW())", if =ROW() is an even number, the formula returns True and applies the conditional format. if =ROW() is an odd number the formula returns False and the conditional format is not applied. We hope this tip makes things easier for you. Dashboards Charts and Gauge DesignGood dashboard, graph and chart design is critical to getting the most out of your Business Intelligence software investment. Good visual communication of data enhances insights and provides rapid communication of information to decision makers. We have below an example of a poorly designed chart, followed by an analysis of the problems and our proposed alternative solution. The chart below forms part of a dashboard which formed part of a BPM application presentation we attended.
Our analysis of the problems with this chart:
Our Proposed Solution:
Note, there is nothing fancy about our solution, but it is simple and communicates clearly and effectively.
Project Timing - Budgeting & ForecastingIn our experience, it's always good to start a budgeting & forecasting project during a quiet part of the year. Get it in for a forecast month so that if the project end-date slips, go-live can be shifted a month with no major business disruption. It also gives you the benefit of a soft start on a non-critical forecast, where everyone gets used to the system and transitions smoothly into the budget when this rolls around. On the other hand, implementing only at budget time can lead to long hours for the project team and, because the project end date (budget start) can’t change, final testing is sometimes only completed during the budget process. A lot of companies start their Business Intelligence / Performance Management purchase cycle in the latter half of the year leading up to the budget cycle. The vendor selection process and contract signing often take longer than expected, leaving only a couple of months to spare before the system has to be scoped, implemented, tested, trained and rolled out. In other words, getting the system in and ready for budgeting immediately whatever stop-gaps this may include. The compromise is often the planned rolling forecast which takes the backburner for another day. A lot of momentum is lost as resources focus on the budget and other new projects demand attention. Unless the initiative is focused back on completing the rolling forecast capability, then, apart from reporting, the model sits in stasis until the next budget. Whichever approach you take, we'll be happy to work with you to get the job done on time and on budget, but we also like to think that with a bit of foresight, we can help you remove some stress and accelerate your ROI. Retail Planning & why NASA chose stonewashedRecently we had a holiday on the NSW Central Coast and needing a few bits and pieces I ventured in to the local department store. Recent styles were “cheek by jowl” with Dickensian artefacts… clothes, crockery, string, stationary most anything you might need. A bit of a “one stop shop” and the price was right! It reminded me a bit of when I used to go shopping with my Mum 35 years ago at the discount food store with everything everywhere. Talk about retail aversion therapy! Being an engineer by background and more analytical than is good for me, I left the shop wondering if the store could survive as the overriding impression was that it had far too much stock. Simply and nostalgia aside, there seemed to be far too much money locked up in “them there” shelves. Surely there was a better way! Retailers are faced with the difficult problem of trying to match the stock they hold against customer demand in an environment of continual change, often driven by seasonal demand and fashion. The trick is to carry just enough stock so that each customer can find what they want when they want it so you don’t lose a selling opportunity but not so much stock that it sits on the shelf until it is disposed of in next years sale. One of the big issues is the “forest for the trees” problem. The buying habits of customers are diverse as they come in all shapes and sizes with different style and colour preferences. This means that there are an enormous number of combinations all being continually influenced by season and fashion. Retailers servicing multiple stores have this problem only magnified. With this amount of data it is easy to see how inventory managers have great difficulty seeing the “forest for the trees”. Faced with an economic and competitive landscape demanding tighter margins for survival it is imperative that only sufficient stock is held to satisfy customer demand. Retailers can no longer afford to make their decisions at the class or category level. Just because there is a run on jeans in one locale it doesn’t mean that the size 12, female stonewashed is moving in all stores or at all. In the world of retail, of stores and SKUs, there is a well known maxim “Retail is Detail” and we all know that the “devil is in the detail”. Size 12, female stonewashed jeans may not have been sold last week but if buyers are planning at the total jeans level this will not be visible leading to wrong buying decisions and sub optimal stocking levels. Thankfully the ever increasing power of computers and the application of NASA inspired techniques with fast and easy methods for seeing the data are making the buyer’s job easier and more accurate. The power of the latest computers is making it possible to plan at the SKU store intersection and spot trends at the lowest level but this is still overwhelming from a inventory buying perspective. To overcome this automated buying techniques are deployed based on optimal stock models. Algorithms that review the sales trends determine what the optimal stocking levels are for each SKU in each store and an inventory order is raised on this basis. All automation algorithms are not equal and some are clearly better than others in predicting future sales. At its simplest level the buying decision may be to replace the inventory from sales from the previous week. The difficulty with this approach is that it does not take into account the amount of stock on the shelf in the store, the changes in seasons, population demographics and fashions or unusual purchases. But how is it possible to forecast the correct stock levels when seasonal, fashion and unusual events are occurring? Over the years different techniques have been used such as Moving Average and Replacement but none of these do a good job in sorting out the “noise” from the “one off” exceptional sales like the pink shorts for the team or the cyclic run on pencils and pads prior to the return to school. This is where a NASA mathematician comes to an unexpected rescue. Rudolf Kalman observed that he could apply his linear filtering technique that strips unwanted noise out of a streams of data to the problem of trajectory estimation leading to its incorporation in the Apollo navigation computer. Through the correct application of these filters reductions in inventory of between 10% & 20% and improvements in stock turns of 10% are not uncommon. Shelf space and dollars freed from over stocking can be refocussed towards more profitable and faster moving items. So if next time you visit a store and everything is “just so” and you begin to yearn for the old cramped, nostalgic quaint experience that has “Gone with Gowings” remember NASA chose the stonewashed. Retailers Look to Kalman Filtering for GuidanceAbove we considered how techniques first used in NASA helped retailers plan their inventory. This week we are going to get behind the man and his thinking in a bit more detail. Rudolf E. Kalman, a graduate research professor emeritus at the University of Florida and ad personam chair at the Swiss Federal Institute of Technology in Zurich is considered the most influential researcher in the field of control and systems theory. He is best known for the linear filtering technique that he developed in the years 1959-1961 to strip unwanted noise out of a stream of data. The Kalman filter is widely used in navigational and guidance systems, radar tracking, sonar ranging, and satellite orbit determination (as we saw last week at NASA for the Apollo and other missions, for instance), as well as in fields as diverse as seismic data processing, nuclear power plant instrumentation, and econometrics. The Kalman filter, which is based on the use of state-space techniques and recursive algorithms, revolutionized the field of estimation and forecasting and while some of these concepts were also encountered in other contexts, such as optimal control theory, it was Kalman who recognized the central role that they play in systems analysis. During the 1970s Kalman also played a major role in the introduction of algebraic and geometric techniques in the study of linear and nonlinear control systems. His work since the 1980s has focused on a system-theoretic approach to the foundations of statistics, econometric modeling, and identification as a natural complement to his earlier studies of minimality and realisability." In simple terms Kalman filtering addresses an age-old question: How do you get accurate information out of inaccurate data? More pressingly, How do you update a "best" estimate for the state of a system as new, but still inaccurate, data pour in? The Kalman filter applies a sophisticated algorithm designed to strip unwanted noise out of a stream of data. Strangely this “noise” could be as diverse as unusual inventory movements. As we saw it should come as no surprise that recently the Kalman filter has proven to have a major contribution to planning some lines of inventory allowing retailers to optimize their stocking levels and subsequently significantly reduce inventory. Not only is the filter able to remove the noise caused for example by a mother buying 12 pink shorts for her daughter’s netball team but is able to manage in an environment of large amounts of data. The pink short purchase is an aberration or noise and tends to disturb the normal pattern of sales. This purchase would lead to stock model inaccuracies from the typically unsophisticated planning methods currently deployed such as Moving Average. Kalman filtering also has a way to link the sales over time such that it effectively uses each new observation to update a probability distribution with no need ever to refer back to any earlier observations. This has the interesting implication to planning in retail where there are typically large data sets. Once the Kalman filter has been tuned with some initial data it does no more work for the millionth estimate than it does for the first. The net result is an algorithm tailored to applications, where data keeps coming in and decisions have to be made quickly. What is it about the Kalman filter that makes it attractive from a retailer's point of view?As mentioned above the original idea of the Kalman filter when applied to missiles and spacecraft was that as new information became available on the position, velocity and acceleration of the vehicle, the Kalman filter only needed to process the latest data. It was no longer necessary to have to reprocess all of the flight telemetry data recorded since launch to work out the position of the vehicle. The application of this approach to retail point-of-sale data means that rather than having to process 52 weeks of sales data each week, only the most recent data needs to be processed. A single initial pass through 52 weeks of data is enough to calculate the filter’s coefficients. The filter can then be updated by addition of only the most recent week’s sales data. If you are having to estimate millions of SKU locations this is going to be pretty important. As new data becomes available, updating of the filter can occur at a greater speed than calculating a moving average model. All the more so with the advent of 64-bit servers. This all well and good, but why go to all this extra trouble to calculate a stock model using a relatively complex method? The main driver of retail inventory levels is forecast accuracy. The stock you order today is the stock you have to live with tomorrow! From real-life retail experience, a Kalman filter estimate is often more accurate three weeks out than a moving average from only one week out. This translates to a 35% improvement in stock turns over a moving average model. If you get 3.5 turns from a moving average, you will get 4.7 turns from a Kalman filter. There are probably better things to spend money on than unnecessary inventory.
|
