Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Commodity Trading? Solver maybe....
Hello all,
I have a spreadsheet to help me work out what commodities I should buy to sell on at a profit. In the system / world I'm dealing with you can order batches of products to be made, and then sell them off individually, e.g. a pallet of bricks at =A3100 contains 500 bricks, which you could sell at =A31 each, resulting in a profit of =A3400 - as long as the market can take 500 bricks... All well and good. I tell my spreadsheet there is a market for 1000 bricks, and it tells me to buy 2 batches and I'll make a profit of =A3800 - thank you spread sheet :-) Now.... there are not only bricks, there are all kinds of commodities available, some with more profit available than others, with different market saturation points (is that the phrase?), different batch sizes and so on and so forth, which brings me to my question. Is there a way, possibly using 'solver' to say to my spreadsheet 'I have =A310,000 here - what should I buy with that to make the most profit, and what would that profit be?'?? Ideally I would like to add another column to my sheet, put in my budget at the top and it tells me how many batches of each item I should buy (e.g. 2 bricks, 1 carpet tiles, 7 lots of doors). I'm not really sure what I should be searching for, so any comments at all would be helpful! Many thanks, Amy |
#2
|
|||
|
|||
bonjella wrote:
Hello all, I have a spreadsheet to help me work out what commodities I should buy to sell on at a profit. In the system / world I'm dealing with you can order batches of products to be made, and then sell them off individually, e.g. a pallet of bricks at £100 contains 500 bricks, which you could sell at £1 each, resulting in a profit of £400 - as long as the market can take 500 bricks... All well and good. I tell my spreadsheet there is a market for 1000 bricks, and it tells me to buy 2 batches and I'll make a profit of £800 - thank you spread sheet :-) Now.... there are not only bricks, there are all kinds of commodities available, some with more profit available than others, with different market saturation points (is that the phrase?), different batch sizes and so on and so forth, which brings me to my question. Is there a way, possibly using 'solver' to say to my spreadsheet 'I have £10,000 here - what should I buy with that to make the most profit, and what would that profit be?'?? Ideally I would like to add another column to my sheet, put in my budget at the top and it tells me how many batches of each item I should buy (e.g. 2 bricks, 1 carpet tiles, 7 lots of doors). I'm not really sure what I should be searching for, so any comments at all would be helpful! Many thanks, Amy ------------------------- You're getting into something a lot more complicated than just "Solver". To answer your question directly, you need to create a formula that is an "evaluation function". Something that allows you to have many input variables (how many of what to buy) and a single output (your profit). Then Solver will be happy to maximize that evaluation function by manipulating the variables -- probably. Some big hairy trading problems don't work well in Solver and you need to resort to random Monte Carlo methods instead which you can also do in Excel. That said, you still need to be careful. I'm not sure what it is you're trying to do with commodities, but... It's rather easy to come up with a stock trading algorithm that makes tons of money on past data, but which performs miserably in the future. It's a problem of over optimizing (or suboptimizing) your algorithm. You also need to look at how your algorithm would have performed in the past with some variation of parameters and other things to make sure it isn't overly sensitive to random variations. You've simply got to remember that if it were as simple as plugging some data about bricks into solver all the MBAs on Wall Street (or brick yard employees) would have been doing it years ago. Good luck... Bill |
#3
|
|||
|
|||
Your problem sounds somewhat like the classic "product mix" problem taught in
basic linear programming courses. Solver can do it. There's brief tutorial he http://www.solver.com/ You should also have an Excel file with Solver example on your PC called: SolvSamp.xls I'm using WinXP / Office 2003 and I found it he C:\Program Files\Microsoft Office\OFFICE11\SAMPLES I'd also see what's availlable from Microsoft on-line. For example: http://office.microsoft.com/en-us/as...245951033.aspx HTH Jason Atlanta, GA "bonjella" wrote: Hello all, I have a spreadsheet to help me work out what commodities I should buy to sell on at a profit. In the system / world I'm dealing with you can order batches of products to be made, and then sell them off individually, e.g. a pallet of bricks at £100 contains 500 bricks, which you could sell at £1 each, resulting in a profit of £400 - as long as the market can take 500 bricks... All well and good. I tell my spreadsheet there is a market for 1000 bricks, and it tells me to buy 2 batches and I'll make a profit of £800 - thank you spread sheet :-) Now.... there are not only bricks, there are all kinds of commodities available, some with more profit available than others, with different market saturation points (is that the phrase?), different batch sizes and so on and so forth, which brings me to my question. Is there a way, possibly using 'solver' to say to my spreadsheet 'I have £10,000 here - what should I buy with that to make the most profit, and what would that profit be?'?? Ideally I would like to add another column to my sheet, put in my budget at the top and it tells me how many batches of each item I should buy (e.g. 2 bricks, 1 carpet tiles, 7 lots of doors). I'm not really sure what I should be searching for, so any comments at all would be helpful! Many thanks, Amy |
#4
|
|||
|
|||
Oo, thanks chaps - that's certainly send me in the right direction.
I know that real life is not ordered enough to be modled on a spreadsheet, but it gives me a place to start. Right, off to get my teeth into the Solver, and then investigate evaluation functions.... what an exciting evening I have planned ;-) Amy - who know's she'll love ever minute of it |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Solver and dynamic ranges | Excel Worksheet Functions | |||
Solver Problems | Excel Worksheet Functions | |||
Excel: Solver | Excel Worksheet Functions | |||
Excel Solver background in forecasting modeling | Excel Worksheet Functions | |||
solver constraint | Excel Worksheet Functions |