Uw keuze?

Uw organisatie is aan het uitbreiden of aan het stroomlijnen.

Historische gegevens zijn aanwezig, oplossingsrichtingen zijn bekend en u heeft een beeld van de verschillende omstandigheden die zich kunnen voordoen. Om de knoop door te hakken is een overzicht nodig van de kosten, baten en risico’s van de verschillende oplossingen.

Uw keuze

Een enthousiast persoon begrijpt de processen en operaties van uw organisatie en maakt voor u een handig model om de verschillende scenario’s door te rekenen.

Wat wordt uw keuze?

Tagged with: , , , , , , , ,
Posted in Excel

Profit waterfall

McKinsey invented the profit waterfall and one of my customers wanted to use it to display his route from revenues to profits. We first gathered some data (simulated values here) concerning revenues, main activities and costs in a table like this:

t1

Microsoft “forgot” to include the Waterfall into Excel, but that did not stop us. Let me explain the steps it took to create it.

Step 1. Add a “Residue” column, starting at the second row and containing the difference between the previous and current and percentage.

t2

Step 2. Plot the “%” and “Residue” columns in a Stacked Bar Chart.

c1

Step 3. Adapt the bar colors and widths and format the chart.

c2

And that’s it folks! Rendering the residual values invisible proved to be the finesse. This waterfall is typical for a supermarket retailer; high cost of goods sold and very sharp profit margin.

 

Tagged with: , , , , , , , , ,
Posted in Excel, Logistics

Forecasting Transportation Demand

Recently, I was asked to setup a forecasting system for a trucking company. Typically, trucking companies get along with common sense, gut feeling and a dose of good luck. This is not always enough. They often suffer from low loading degrees, high charter costs, waiting times, late deliveries, and complaining customers. Forecasting the “erratic market fluctuations” can help them perform better and makes sense because these fluctuations are almost always caused by known factors. So an important part of the fluctuations are predictable!

In order to make useful demand predictions we need to understand the nature of the fluctuations. Depending on that, we can select the most suitable forecasting method. Let us first study some charts.

fc1

This chart shows random fluctuations. These are a fact of life and can be forecasted because the fluctuations remain within a certain bandwidth. That’s a good start, proceed to the next one.

fc2

In this graph we see a pattern that repeats itself over time. It is important to include this pattern into the prediction. The third chart shows us a rising trend line. We know that the global need for transport is rising steadily, so this comes as no surprise at all.

fc3

Transport planners can usually list the most important sources of fluctuations in the demand for transport, for example:

  • Daily patterns, usually one or more days of the week have higher or lower demands
  • Monthly seasonality, demand fluctuating per month of the year
  • Sales promotion by customers
  • Demand shift caused by holidays, e.g. around Christmas or Easter
  • End-of-quarter and end-of-year peaks, caused by Sales

It is important to identify these factors and study their behaviour and impact, in order to incorporate them into useful predictions. With a clear understanding of the so-called forecast profiles, the most suitable forecasting method can be selected.

The next table illustrates a useful classification of forecasting methods.

fc4

This table can be a guide to select the proper forecasting method. That is an important step, but please bear in mind that successful implementation also requires:

  • Acceptance by the planners, the forecasting system should fit their decision-making processes
  • Availability of historical data, preferably covering more than 3 years
  • Automatic detection of changes, because the influence of different factors can change over time (trends may break)
  • Continuous analysis of prediction errors and adjustment of the forecasting parameters
  • Measurable and traceable results

With a well designed demand forecast system, planners can make better decisions. They will be able to achieve a better match between demand and required resources like trucks, drivers and depot employees. Thus they will improve service levels, truckload utilization, charter costs and they will increase the efficiency of transhipment activities.

Tagged with: , , , , , , , , , ,
Posted in Logistics

Excellent Lookup

VLOOKUP is the traditional method for lookups in Excel, but I never got used to the approximate matches and the need for sorting the lookup data. I have found a better way to setup my lookups, using the INDEX-MATCH combination. Let me explain.

Consider the following Order data table.

IM order data table

Looks fine, but now we want to know where the products are going. The Customer ID does not tell much but the Customer data table below can help us out.

IM customer data table

Now, we use Excel’s MATCH function to check if a Customer ID in the Order data table has an exact match with one of the Customer ids in the Customer data table. If so then the function returns the corresponding line number in the Customer data table, if not then the function will return “N/A”.

The example below shows how the MATCH function is used. The Customer ID in the first Order is found in line number 8 of the Customer data table.

IM match

But we do not want line numbers, we need places. This is where the INDEX function comes in. This function is used to retrieve a value from a cell, depicted by a row and column number, in a table. The final result will look like this.

IM index

Working with the nested INDEX-MATCH combination will take some training, but I am sure that after a while you will appreciate its reliability and use. The above example concerns a one criteria lookup, but the INDEX-MATCH combination can be used for multiple criteria lookups as well. That’s really strong stuff. But hey, that could make another blog.

Tagged with: , , , , ,
Posted in Excel

Excel Bubble chart with four dimensions

Excel’s bubble chart uses three dimensions to position and size objects. The user can select the color of the bubbles. Would it not be nice to have a “fourth dimension”; with bubble colors corresponding to certain object values?

Consider the following table.

data table

Useful data, but not very expressive. The following chart is much more informative. That’s what I wanted to have.

bubble chart

This shows us that application “I” is expensive and important, but also that it is fairly old (larger bubbles are older) and that technical support (orange) is not optimal. We can also see that application “G” needs some attention as well and that we might want to invest a bit in application “J” to improve its technical support quality.

Out of the box, Excel’s Bubble chart cannot handle different bubble colors. So, I started to color them manually, but as more and more applications came in and data was updated, this became a nightmare. Finally I used the following pivot table to do the trick.

pivot table

I took the costs (Y) in the color columns and formatted the three color series and labels. Works well and might come in handy for you as well sometimes!

The only problem with this pivot table is that I had to refresh it all the time. So, later on I wrote a macro to colorize the bubbles automatically each time the data was updated. But hey, that could be another blog.

Tagged with: , , , ,
Posted in Excel

Treemaps are fun

Treemaps can be a very useful illustrations. This type of chart is not included in Excel but it can be added as an “App for Office”, which is what I did. The next examples describe how I used Treemaps recently.

The following data table contains annual Sales and Stock figures by Department.

data table tm1

Sales value divided by Stock value gives us the number of Stock Cycles per year and the values in the Color column are used to express the deviation from the norm, 16 cycles per year. Not a complex table but the Treemap below illustrates much more clearly what has been going on here.

treemap1

Obviously, the “Garden” department has the highest average stocks. Its red color tells us that the cycle speed for these products is below average. The “Tools” department clearly performs better. The “Sanitary” department seems to be in trouble, but then again its stock value is not huge.

Treemaps can be branched and drilling down to product level gives us the following table.

data table tm2

Now, my eyes are really starting to hurt me, let’s quickly switch to the Treemap.

treemap2

The Treemap shows that the “Garden” department contains seven products and that products “Y” and “W” have large stock values coupled with low Cycle speeds. Other products deserving management time are “I”, “K” and probably “T” as well.

Tagged with:
Posted in Excel