How to solve Transportation problem in Excel?

Home

I think that most of you have heard about Transportation problem. We have N factories and M mines/producers. Each factory needs and each mine can provide particular amount of resources. We have to transport these resources from mines to factories. What is obvious it costs money and this cost depends on the distance between factories and mines. We have to find such an allocation that will minimize this cost.

In order to solve this problem we can use linear programming and one of the most popular algorithms are simplex or stepping stone algorithm. However, today I will not write directly about them but I will show how to solve this problem in Excel. Yes, I'm talking about good old Excel. Surprised?

Excel has an Add-in called Solver which will do a job for us. I'll explain how to do it using a simple example with 3 factories and 3 mines. Here is a table that shows costs of transport between mines and factories. For example, if we want to move 10 units from Mine 1 to Factory 1 then a cost will be 10 *c11.

Transportation Cost	Factory 1	Factory 2	Factory 3
Mine 1	c11	c12	c13
Mine 2	c21	c22	c23
Mine 3	c31	c32	c33

We also need another table with supplies and demands. Below is an example. The numbers is the first column shows how many resources each mine can provide and the numbers in the the first row shows how many resources are needed by each factory.

The last row and the last column show sums of allocated resources in each row and in each column. These columns are needed to easily configure Solver. In this example some resources have been already allocated and we need to optimally allocate remaining ones i.e. x12, x13....

Supply\Demand	150	50	50	Allocation sums for mines
40	10	x12	x13	10
110	x21	x22	x23	0
100	x31	x32	20	20
Allocation sums for factories	10	0	20

We also we have to define limitations and a cost function. The first limitation is that found allocations should be non negative i.e.

x12, x13 ... >= 0

Besides we want to allocate all resources available in mines and each factory should receive required amount of resources i.e.

40 = 10 + x12 + x13
110 = x21 + x22 + x23
100 = x31 + x32 + 20
150 = 10 + x21 + x31
50 = x12 + x22 + x32
50 = x31 + x32 + 20

Because we have a column and a row with allocation sums it will be very easy to enter these allocations into Solver. It is also worth saying that in general these limitations can be different, for example we can have more resources than needed. Of course, in this case formulas above would be also different.

A cost function is also easy. We want to minimise the following sum which is equal to total cost of moving resources from mines to factories:

c11 * 10 + c12 * x12 + c13 * x13 + ....

Now we have everything to solve a problem in Excel. Firstly we have to enable Solver. To do so open Excel options, select Add-ins. Then find Solver on the list and confirm with OK (this procedure can vary in different versions of Excel).

I've already prepared a spreadsheet with all required equations and data for you. You can download it here (you have to download this file locally and do not use online Excel application). To run Solver go to Data tab and select Solver in Analysis category. Then select Solve button and all missing allocations will be populated. Easy, isn't it? Now, a few words about using Solver.

Here is a screenshot with Solver Parameters. A cell in a red circle contains a cost formula. This formula will be minimized (see a green rectangle). Yellow rectangle contains cells that will be modified by an algorithm and finally blue rectangle contains six formulas explained in the previous post.

The next screenshot shows additional options of Solver. You can display this window by pressing Options button in Solver Parameters window. I want to point 2 selected options. Assume Linear Model tells Solver that it deals with linear programming problem and Assume Non-Negative tells Solver that we are interested only in non-negative results.

As you can see much more options are available. I encourage you to experiment with them and also with different costs, limitations, number of mines/factories and problems.

Interview Questions for Programmers by MK #2

Home

I prepared this question a long time ago in order to check knowledge of basic good practices.

Question #2
You have the following method which is responsible for updating a client in a database:

public void UpdateClient(
   int id,
   string name,
   string secondname,
   string surname,
   string salutation,
   int age,
   string sex,
   string country,
   string city,
   string province,
   string address,
   string postalCode,
   string phoneNumber,
   string faxNumber,
   string country2,
   string city2,
   string province2,
   string address2,
   string postalCode2,
   string phoneNumber2,
   string faxNumber2,
   ...)
{
   //...
}

Do you think that this code requires any refactoring? If yes, give your proposition. A database access technology doesn't matter in this question.

Answer #2
The basic problem with this method is that it has so many parameters. It is an error prone, difficult to maintain and use approach. I suggest to change the signature of this method in the following way:

public void UpdateClient(Client client)
{
   //...
}

Where Client is a class that models clients. It can look in the following way:

public class Client
{
   public int Id { get; set; }
   public string Name { get; set; }
   public string Secondname { get; set; }
   public string Surname { get; set; }
   public string Salutation { get; set; }
   public int Age { get; set; }
   public string Sex { get; set; }
   public Address MainAddress{ get; set; }
   public Address AdditionalAdddress { get; set; }
   /* More properties */
}

Address class contains details (country, city..) of addresses.

Comments #2
You may also write much more e.g.:

• It may be good to introduce enums for properties like 'Sex' which can take values only from the strictly limited range.
• UpdateClient method should inform a caller about the result of an update operation e.g. by returning a code.

However, the most important thing is to say that UpdateClient method shouldn't have so many parameters. Personally, if I see a code as above I immediately want to reduce the number of parameters. This question seemed and still seems to be very easy, however not all candidates were able to answer it. Maybe it should be more accurate. For example, I should have stressed that a candidate should focus ONLY on available code. What do you think?

Interview Questions for Programmers by MK #1

Home

Do you know series of posts titled Interview Question of the Week on a SQL Authority blog? If not or if you don't know this blog at all you have to catch up. I learned a lot of from this series so I decided to start publishing something similar but to focus more on .NET and programming.

This is a first post from series which I called Interview Questions for Programmers by MK and in which I'm going to publish questions that I'd ask if I were a recruiter. Of course they are somehow based on my experience as a participant of many interviews.

Question #1
What is a meaning of using statement in the code below? What would you do if using keyword did not exist?

using(var file = File.OpenWrite(path))
{
   //...
}

Answer #1
In this example using statement is used to properly release resources (to call Dispose method) that are owned by an object of a class that implements IDisposable interface. It is a syntactic sugar and could be replaced by using try/finally block in the following way:

var file = File.OpenWrite(path);
try
{
   //...
}
finally
{
   if(file != null)
      file.Dispose();
}

How to build predicates dynamically using expression trees

Home

I'm working at the application which finds so called execution patterns in logs recorded by IntelliTrace historical debugger. An execution pattern is a sequence of methods calls that is widely used in the application and it is a kind of automatically generated documentation. The part of the algorithm is filtering of found patterns based on criteria like the length of a pattern or the number of different methods in a pattern.

At the beginning I used only 2 criteria so it was easy to handle all possible combinations of them i.e. use the first criterion, use the second criterion, use both and used none. Then I added 3rd criterion and I thought that for 3 criteria I still don't need a generic mechanism. However, shortly it turned out that I want to handle 5 criteria what gives 32 of possible combinations. This time I did it once and for all.

I decided to use expression trees to dynamically build an expression that verifies any combination of criteria. The code is quite simple. Firstly we need an enum for all criteria.

[Flags]
public enum Crieria : byte
{
    None = 0,
    CriterionOne = 1,
    CriterionTwo = 2,
    All = CriterionOne | CriterionTwo
}

We also need a class that will represent patterns.

public class Pattern
{
    public int FieldOne { get; set; }
    public int FieldTwo { get; set; }
}

Now we can write a code that will dynamically build needed expressions. I assumed that every criterion has a corresponding static method that knows how to check if a current pattern fulfils it or not. The final expression produced by CreateExpression method will be of the following form pattern => predicate1(pattern) && predicate2(pattern) && predicate3(pattern)....

public static class FilterBuilder
{
    public static Func<Pattern, bool> CreateExpression(Crieria filteringMode)
    {
        var param = Expression.Parameter(typeof(Pattern));

        var subExpressions = new List<MethodCallExpression>();

        if ((filteringMode & Crieria.CriterionOne) != 0)
            subExpressions.Add(Expression.Call(typeof(FilterBuilder), nameof(CriterionOnePredicate), null, param));

        if ((filteringMode & Crieria.CriterionTwo) != 0)
            subExpressions.Add(Expression.Call(typeof(FilterBuilder), nameof(CriterionTwoPredicate), null, param));

        //Other criteria...

        if (subExpressions.Count == 0)
            return p => true;

        Expression finalExpression = subExpressions[0];
        for (var i = 1; i < subExpressions.Count; ++i)
            finalExpression = Expression.And(finalExpression, subExpressions[i]);

        return Expression.Lambda<Func<Pattern, bool>>(finalExpression, param).Compile();
    }

    public static bool CriterionOnePredicate(Pattern p)
    {
        return p.FieldOne > 0;
    }

    public static bool CriterionTwoPredicate(Pattern p)
    {
        return p.FieldTwo < 0;
    }
}

The code can be made even more generic but I'll leave it as an exercise. When I finished this code I started to worry about performance. It is critical for me because my application needs to process large amount of patterns efficiently. I made the following simple test in which dynamically generated and static functions are executed 1 million times.

var iterations = 1000000;

var predicate = FilterBuilder.CreateExpression(Crieria.All);
MeasureIt<Pattern>((p) => predicate(p), new Pattern(), iterations);

predicate = FilterBuilder.CreateExpression(Crieria.CriterionOne);
MeasureIt<Pattern>((p) => predicate(p), new Pattern(), iterations);

MeasureIt<Pattern>((p) =>
{
   FilterBuilder.CriterionOnePredicate(p);
   FilterBuilder.CriterionTwoPredicate(p);
}, new Pattern(), iterations );

MeasureIt<Pattern>((p) => FilterBuilder.CriterionOnePredicate(p), new Pattern(), iterations);

In order to measure time of calculations I used MeasureIt method from my earlier post and I received the following results:

Total time: 54
Total time: 27
Total time: 18
Total time: 12

Dynamically generated predicates are 2-3 times slower than static ones. However, we are still talking here about dozens of milliseconds in order to make 1 million calls. For me it is acceptable.

What every blogger should do if using someone else's code #1

Home

The developer's work very often involves effective re-using of a code that was written by someone else. Sometime these are fully fledged libraries or frameworks but sometimes these are also small or even very small pieces of code found on a blog or a forum. All this stuff makes our life easier.

When I use someone else's code in my project I wonder if it is worth writing about it on my blog. And sometimes I hesitate. Why? Because it is a well know library and there is a lot about it in Internet. Sometimes because I don't have time to write a tutorial. And sometimes because I think that there is no much to write about,that it is a small piece of code so everybody can understand it on their own.

Now, I think that it is a wrong approach. Maybe it is small, but I used it so it means that I din't have to design, write and test it on my own. Maybe I don't have time to write a tutorial but this code actually saved me time that I'm lacking.

So, what should I (we) do? Just write a short note on our blogs and say that we used this and this in our projects. At least in this way we can thank the authors.

To start. In WPF there is a class GridSplitter that allows user to resize rows and columns in a grid. However, I wanted the same functionality for DockPanel but WPF doesn't provide it. I decided that I'll wrote it but firstly I goggled for something like that and I found this article. The DockPanelSplitter class has 300 lines of code, it is not complex but works well. Give it a chance.