Need assistance to code my Excel solver model

Question

Hello everyone,

I am using the Simplex LP solving method in my Excel solver model. You may have a look at my excel file downloadable from the link below.

https://www.yousendit.com/download/M3BseVd0Q1J1Yk1Pd3NUQw

As we are scaling up this model, we are finding it difficult to continue working with excel. The solver shall be used for N inputs (Project Revenue) and we would like the user to be able to configure some parameters (Cost, Utilization) at runtime. We are therefore looking into developing a small windows-forms application that uses Microsoft Solver Foundation underneath to achieve the same goals.

Since, I am new to this library, I would like to kindly request you to help me out in setting up the solver project and in migrating the core solver code using this library.

Best Regards,

Sachin.

Answer 1

Hello Sachin,

I was in the same boat when I first started with Solver. I was looking at moving an Excel Solver setup to a windows forms based one (our model was too large for Excel 2010 and wasn't as up to date as pulling from our database).

I wasn't sure where to begin, but a few others helped me get through my problem, which is here: My First Solver Post

I would suggest picking apart your existing Excel model, and list out your constraints, parameters, decisions, etc. in "topic terms" (not in Excel ranges) like you did in your post. Once you have that, those can pretty easily be ported to code. The moving to code was not the hard part for me, it was understanding the terminology and what goes where. The samples also helped me out to see how everything worked.

I hope this helps get you started. If you have more questions, I'll see what I can do to help when I have a bit more time.

Answer 2

Thank you Knif for your prompt reply.

I tried the following code. I do not have any error but the application blocks at the context.Solve(); call. Can anyone kindly tell me what am I doing wrong ?

internal void OptimizeForecast()
        {
            // Step 1: Create the context and the model.
            SolverContext context = SolverContext.GetContext();
            Model model = context.CreateModel();
 
            // Step 2: Define the decisions.
            // Given the constraints (bill rates, min and max utilizations), the solver needs to decide:
            //      - THE OPTIMUM NUMBER OF DAYS TO BE ALLOCATED TO EACH RESOURCE PROFILE
            Decision xDaysSA = new Decision(Domain.Real, "Number_of_days_R1");
            Decision xDaysC = new Decision(Domain.Real, "Number_of_days_R2");
            Decision xDaysM = new Decision(Domain.Real, "Number_of_days_R3");
            model.AddDecisions(xDaysSA, xDaysC, xDaysM);
 
            // Step 3: Define the constraints
            model.AddConstraints("NonNegative_Days_R1", xDaysSA >= 0);
            model.AddConstraints("NonNegative_Days_R2", xDaysC >= 0);
            model.AddConstraints("NonNegative_Days_R3", xDaysM >= 0);
 
            model.AddConstraints("Utilization_R1", 60 == (100 * xDaysSA / (xDaysSA + xDaysC + xDaysM)));
            model.AddConstraints("Utilization_R2", 30 == (100 * xDaysC / (xDaysSA + xDaysC + xDaysM)));
            model.AddConstraints("Utilization_R3", 10 == (100 * xDaysM / (xDaysSA + xDaysC + xDaysM)));
            model.AddConstraints("Zero", Model.Abs(this.revenue - 320 * xDaysSA
                - 430 * xDaysC
                - 750 * xDaysM) == 0);
 
            // Step 4: Define the goal (objective function)
            model.AddGoal("Revenue_Cost_Difference",
                GoalKind.Minimize,
                Model.Abs(this.revenue - 320 * xDaysSA
                - 430 * xDaysC
                - 750 * xDaysM));
 
            Solution sol = context.Solve();
 
            Report report = sol.GetReport();
            Console.WriteLine(report);
        }

Answer 3

Sachin,

Now, I'm by no means and expert with solver (the post I showed was really my only use of it), but you may want to look at your last constraint and your goal (below). They are the same formula, which could be the issue. This is just a thought, but since the model is constrained so that formula is always = 0, how can it minimize that same formula, when it can only be 0 due to the constraint? That's how I am seeing your code. In short, give it a try without that last constraint.

model.AddConstraints("Zero", Model.Abs(this.revenue - 320 * xDaysSA
                - 430 * xDaysC
                - 750 * xDaysM) == 0);

// Goal is same as above constraint
model.AddGoal("Revenue_Cost_Difference",
                GoalKind.Minimize,
                Model.Abs(this.revenue - 320 * xDaysSA
                - 430 * xDaysC
                - 750 * xDaysM));