I was involved in a small project where we converted a spreadsheet model using Solver to GAMS/CONOPT. I was unable to reproduce the spreadsheet results, and it turns out the spreadsheet had some errors in the formula’s. I have converted quite a number of Excel models, and almost all had some problems that were uncovered by moving the model to a modeling language environment. See table 1 in http://panko.shidler.hawaii.edu/SSR/Mypapers/whatknow.htm for more evidence of how difficult it is to create error-free spreadsheet models.
PS: after fixing the spreadsheet errors we were able to get similar results. This is actually not a bad approach to verify and repair spreadsheet models.
Erwin,
ReplyDeleteWhat was your approach to the conversion? Did you "build" the model based on the spreadsheet formulas or is there an automated way of doing it?
Can the Solver build a MPS file for export of linear models?
Thank you,
Joao
This was a relatively small nonlinear problem so I did it largely by hand (used some tools to analyze the spreadsheet and some tools to extract the data in GAMS format). I am involved in another project where we translate a very large spreadsheet with a partial equilibrium model (system of nonlinear equations). This is done with tools where we parse and analyze the formulas and generate GAMS code automatically.
ReplyDeleteI believe Frontline systems provides tools to export an MPS file.
In my case I wanted to have a clean, maintainable GAMS model so an MPS file was not appropriate (besides that the model was very nonlinear).