PDA

View Full Version : Simple Supply and Demand Model using Solver



rossmiddleto
09-13-2011, 05:49 AM
Hi All,

I have been using excel for a while and can program a bit of VBA but I need to learn how to use Solver in order to solve a simple demand and supply problem that I have.

A simplified version of this problem is in the attached sheet and I was hoping that someone could program this simple example for me so that I can have a go at expanding on it myself.

Instructions are also included in the attached sheet.

Much appreciated

Ross

p45cal
09-13-2011, 12:28 PM
You're going to be the victim of my exploration of user defined types (UDTs)!
I now rather hate them. Had problems passing them (or an array of them), as arguments. I'll be moving hurriedly on to classes next (not the classroom you understand, class modules).
The attached file has a button on the sheet, labelled Button, unimaginatively, the pressing of which gives results, both in the form you suggest and as a list. It's not been written with robustness in mind. I feel sure others will supply a better solution.

I hope this wasn't an exercise for you to learn Solver, for I struggled to work out how I might use Solver to do this.

The code is 'orrible - result of my convoluted experimentation with UDTs.

The logic behind it was simply to create a list of costmarkets, sorted cheapest at the top, then to go through the list using as much as possible of each one depending on whether supply or demand was the limiting factor, while keeping a tally of remaining supply and demand for each supplier/consumer.

Anyway, see attached.

rossmiddleto
09-14-2011, 03:16 AM
Massive thank you!

I have been messing around with your code and I can figure out how to expand the arrays to include more countries but arrays are a bit out of my league so any further messing is going to have to wait until I have a better grasp of what they are and how to create, store and manipulate them.

Do you know who I would be able to ask with regards to a Solver solution to this problem? My worry is that I am not going to hit the limit of my VBA knowledge when I come to implement your code and so I was wondering if there may be an easier solution using Solver?

Once again, I really appreciate your help.

Regards

Ross

p45cal
09-14-2011, 05:59 AM
Do you know who I would be able to ask with regards to a Solver solution to this problem? My worry is that I am not going to hit the limit of my VBA knowledge when I come to implement your code and so I was wondering if there may be an easier solution using Solver?
I'm a little reluctant to suggest another site, as VBAExpress is very good, however try Excel Questions - MrExcel Message Board (http://www.mrexcel.com/forum/forumdisplay.php?f=10) or if that link fails try this one (http://www.mrexcel.com/forum/forumdisplay.php?f=10).

You can't attach files there, so use a utility to display parts of your worksheet, for example Excel Jeanie Html (http://www.excel-jeanie-html.de/index.php?f=1) .

You can include links to files on the interweb hosted at sites such as box.net, imageshack, skydrive etc.

It may not need Solver, someone may be able to use the iterative calculations Excel can do.

Oh, and one more important thing, in each thread at the different sites, do provide links to where you've asked the same/similar question at other sites so you don't get slated for breaking cross-posting rules, and so you don't waste people's time if you've already had a good answer elsewhere.

rossmiddleto
09-14-2011, 06:07 AM
Hi p45cal, thank you for this advice :-)

I have worked your example into my actual problem and it works fantastically well - thank you very much!

I hate to ask this, but how easy would it be to add one constraint to the model. In order for the model to represent my real world problem, a maximum of 70% of a county's demand can be made up from one individual supplier before the rest of the demand needs to me made up from the next most expensive country. In my first example we were trying to order supply in terms of the cheapest first (progressively getting more expensive) whereas this time round it needs to be in terms of the most expensive first (getting progressively cheaper).

Please see my sheet attached with the real world example (sheet1).

Thank you massively again. I dont know how I would have done this without you.

Ross

p45cal
09-14-2011, 06:41 AM
In order for the model to represent my real world problem, a maximum of 70% of a county's demand can be made up from one individual supplier before the rest of the demand needs to me made up from the next most expensive country.


In my first example we were trying to order supply in terms of the cheapest first (progressively getting more expensive) whereas this time round it needs to be in terms of the most expensive first (getting progressively cheaper).

Are the above two things related? Because I don't see how the second helps solve the first.

Dealing for now with just the first (and I haven't looked at your latest workbook yet and I'm not going to code it just now) my approach would be to add a member to the
Type CountrySupplyDemand
Country As String
Quantity As Double
MaxAmountAllowed As Double
End Type Then add the following:
Sub CreateDemandArray()
With ThisWorkbook.Sheets("Sheet1")
Set DataSource = .Range("B18:C20")
ReDim DemandRemaining(1 To DataSource.Columns(1).Cells.Count)
i = 1
For Each cll In DataSource.Columns(1).Cells
DemandRemaining(i).Country = cll.Value
DemandRemaining(i).Quantity = cll.Offset(, 1).Value
DemandRemaining(i).MaxAmountAllowed = int(DemandRemaining(i).Quantity * 0.7)
i = i + 1
Next cll
End With 'ThisWorkbook.Sheets("Sheet1")
End Sub and I think lastly, change
DealSize = Application.Min(SupplyRemaining(SupplierIdx).Quantity, DemandRemaining(DemanderIdx).Quantity) to
DealSize = Application.Min(SupplyRemaining(SupplierIdx).Quantity, DemandRemaining(DemanderIdx).Quantity,DemandRemaining(DemanderIdx).MaxAmoun tAllowed)I've not tested or written this code yet, but perhaps you can see where it's going.

rossmiddleto
09-14-2011, 07:25 AM
Works like a dream!

Thank you so much for this. I am going to have a play with the various classes and see what I can come up with

Thanks again

Ross

rossmiddleto
09-14-2011, 08:22 AM
Sorry, me again. I would like to change the macro so that it allocates demand to the hightest paying demand country first. In your previous macro it allocates the cheapest first.

I have tried to work this out for myself by changing a few things in the below section of code but would really appreciate some guidence on how to do this.

Many thanks again

Ross


Sub CreateAndSortPriceArray()
With ThisWorkbook.Sheets("Sheet1")
Set PricesSource = .Range("d7:aa22")
ReDim PriceArray(1 To PricesSource.Cells.Count)
i = 1
For Each cll In PricesSource.Cells
PriceArray(i).Price = cll.Value
PriceArray(i).Supplier = .Cells(PricesSource.Row - 1, cll.Column).Value
PriceArray(i).Demander = .Cells(cll.Row, PricesSource.Column - 1).Value
i = i + 1
Next cll
'Sort the Array:
Dim TempCM As CostMarket
PrintPrices
Debug.Print "Start:"
For i = 1 To UBound(PriceArray) - 1
AllSorted = True
For j = UBound(PriceArray) - 1 To i Step -1
'Debug.Print j & " " & j + 1
If PriceArray(j).Price > PriceArray(j + 1).Price Then
AllSorted = False
TempCM = PriceArray(j)
PriceArray(j) = PriceArray(j + 1)
PriceArray(j + 1) = TempCM
End If
Next j
If AllSorted Then Exit For
PrintPrices
Next i
End With 'ThisWorkbook.Sheets("Sheet1")
End Sub

p45cal
09-14-2011, 09:05 AM
Again, I'm not going to code this now, but instead of changing how the array is sorted, perhaps it might work if instead of working down the array, we work up it?
Try changing:
For i = 1 To UBound(PriceArray)
to
For i = UBound(PriceArray) to 1 step -1

rossmiddleto
09-16-2011, 02:45 AM
Once again I cannot thank you enough.

Much aprpeciated!

rossmiddleto
09-26-2011, 09:06 AM
Hello

Could you do me a quick favour and have a very brief look at the attached spreadsheet.

I have used your macro and added in some sample data to test. I am not sure if it is allocating supply to the highest paying demand first (followed by the next highest paying demand country and so on).

Would you be able to have a quick look and see if it is working correctly?

Really appreciate the help

Regards

Ross

p45cal
09-26-2011, 04:24 PM
Could you do me a quick favour and have a very brief look at the attached spreadsheet.
Quick? Brief?! Ho ho ho.

Check the attached after a run and see if it's giving the correct results.

The suggested change in msg#9 was applied to the wrong part! My fault for being ambiguous.

Where I have changed the code I've added:
'***********
except for where I've deleted several lines.

There are comments on the sheet too.

rossmiddleto
09-27-2011, 01:22 AM
Ok so maybe this isn’t quick and easy but an Excel genius like you can debug things like this in minutes whereas it would take me weeks lol!

I feel like we are almost there in terms of the functionality. I have made those advised changes to the column and row counts but the macro isn’t dishing out supply in the way that it should.

What it needs to do is to look at the prices table and dish out supply to the highest paying demand country first until either that supply country has no supply left or until the demand for the demanding country has been satisfied. The macro then needs to find the next highest paying market in the prices table and dish out demand to that demanding country (up until that countries demand constraint or that supply country has run out of supply) remembering all the time what demand and supply has been used up.

In the attached table, Yemen to Argentina is the highest prices market (cell U9) so Yemen would supply 1.16 tons to Argentina. However, as Argentina only has a demand of 0.41 tons, there would be leftover supply. The next highest paying market is Trinidad to Belgium so demand from Belgium would be satisfied from Trinidad up until Trinidad’s supply constraint of 2.5 At this point; Belgium sill has an unsatisfied demand requirement of 3.5. This demand requirement will have to wait its turn to be satisfied until another supplier to Belgium is the highest price (as the macro identifies the next highest paging market after each constraint is hit). It just so happens that Yemen to Belgium is the next highest priced market and so the left over supply from Yemen (that wasn’t allocated to Argentina) will be allocated to Belgium.

I am sorry for making this so complicated and really appreciate all of your help.

I would really like to show you my appreciation so is there some kind of charity that I can donate to you on your behalf? If you pick your charity then I will make a donation ASAP.

Regards

Ross

Aussiebear
09-27-2011, 03:44 AM
I would really like to show you my appreciation so is there some kind of charity that I can donate to you on your behalf? If you pick your charity then I will make a donation ASAP.

Regards

Ross

Make the donation to VBAEXPRESS.Com. After all this is where you found a number of solutions to your issue. We need assistance as well as anybody else.

Ted

p45cal
09-27-2011, 06:16 AM
Yes, send any donation to VBAExpress!

Now, I want you to check carefully. Some points regarding your latest incarnation posted here:
re: "The next highest paying market is Trinidad to Belgium"
I think it's Trinidad to Australia @ 19500 (R10).

re: "It just so happens that Yemen to Belgium is the next highest priced market"
I think it's Yemen to Australia @ 19000 (U10)

So you may have mixed your rows up a bit.

While looking through the code I noticed the line, which I hadn't tested:
DemandRemaining(i).MaxAmountAllowed = Int(DemandRemaining(i).Quantity * 0.8) which was to accommodate a max percentage of a country's demand to be supplied by a given supplier, 80% in this case.

For testing purposes I changed it to:
DemandRemaining(i).MaxAmountAllowed = DemandRemaining(i).Quantitywhich takes out 2 aspects, the percentage and the Int part - which I'm not sure why it's there (was it some part of the original requirement?).

Later, when it comes to restoring this line I would leave the Int part out:
DemandRemaining(i).MaxAmountAllowed = DemandRemaining(i).Quantity * 0.8
When I make adjustments in the table to try and reflect what you intended in the price table, along with that line change in the code it looks like it will give what you're after, but I leave it to you to check.

rossmiddleto
09-27-2011, 06:52 AM
Oops, sorry for my typos. I have made the changes that you have advised and it works like a charm so thank you very much.

Donation has been made, have a good evening!