Solver, the optimization software, has myriad uses in industries from manufacturing to distribution and logistics, marketing and sales, finance and investment, and human resources. Now it is even stronger and easier to use.
By Dan Fylstra, CEO, Frontline Systems
Solver – the basic optimization tool in Excel that Frontline Systems developed for Microsoft, probably the longest lived (27 years) and most widely used (with over a billion Excel users) advanced analytics software ever – is getting a “facelift” in its user interface, and new “muscle” to solve larger scale, more challenging optimization problems. Though it solves the same kinds of problems, Solver has been re-imagined for an era of touch-sensitive screens, mobile devices, and cloud computing. All you need to get it—for free—is a web browser, an Office 365 account, and a visit to the Microsoft Office Store.
If you’re reading this magazine, there’s an excellent chance that you’ve used Solver in desktop Excel, and a decent chance that you’ve used Solver in Excel Online (which has well over 100,000 users). But if you’re new, Solver provides an easy way to turn a spreadsheet model – with input parameters and various calculated results – into an optimization model, that automatically finds the best possible values for chosen input parameters (called decision variables), to maximize or minimize some calculated value (called the objective), while not exceeding bounds on other calculated values (called constraints).
Solver has myriad uses, from manufacturing to distribution and logistics, to marketing and sales, finance and investment, and human resources, in almost every industry. A brief list of examples included with Frontline’s enhanced Solvers in shown in Figure 1.
How Solver Got Started
Turn back the calendar to 1989: It was a very different world. IBM-compatible personal computers were widely used in business, but iPhones and iPads didn’t exist yet. Most users were running MS-DOS on character-mode (non-graphical) displays. Lotus 1-2-3 was “king of the hill” in spreadsheets; Borland Quattro Pro was its challenger; Microsoft Excel was popular on early Macintosh computers, but only perhaps 10 percent of PC users were running it, under an early “Windows 2.0” add-on to MS-DOS. IBM was promoting OS/2 as the new, better alternative to MS-DOS, and Lotus was supporting OS/2. Frontline Systems had just been formed, and had developed a Lotus 1-2-3 add-in called “What-If Solver” that could solve optimization problems. At meetings with Lotus, we found their engineers were very interested in what we were doing with What-If Solver, but they never explained why.
In the summer of 1989, Lotus introduced 1-2-3/G, their first graphical spreadsheet, to great fanfare. Besides slick graphics, 1-2-3/G included a new, internally developed Solver tool that Lotus emphasized as a big advance over other spreadsheets. And 1-2-3/G ran only on OS/2 – not on Windows or MS-DOS. This move by the market leader prompted Microsoft (and Borland) to hunt for a quick way to respond to the challenge of 1-2-3/G and its Solver. And at Frontline Systems, we had an answer.
After evaluating other developers from around the world, Microsoft chose to work with Frontline. Pete Higgins, a Microsoft executive reporting directly to Bill Gates, made the deal with Frontline – a few years later he called it a “home run for both parties” – and product manager Tim McGuire shepherded the effort at Microsoft. We worked night and day throughout 1990 to create Solver for both Windows and Macintosh. Figure 2 is a brief excerpt from our development project spec and schedule.
Solver made its debut as part of Excel 3.0, along with Windows 3.0 (a major advance from Windows 2.0) on May 22, 1990. This was a watershed moment: Thereafter, Microsoft Excel steadily gained market share in spreadsheets – exceeding Lotus 1-2-3’s market share by late 1992 or early 1993. We continued working with Microsoft, adapting Solver for each new version of Excel – twelve in all, through Excel 2016. In 1991, Borland signed an OEM agreement with Frontline Systems to develop a Solver for Quattro Pro, and in 1996 Lotus signed an agreement with Frontline to create a new Solver for 1-2-3.
Stay the Same and Keep Changing
A major reason for Solver’s longevity is that it has been re-invented, in terms of technology, several times, while maintaining its simple user interface over many years, and maintaining compatibility for Solver models across new versions and new platforms. It’s still possible to open an Excel 3.0 Solver model in Excel 2016, click the Solve button, and get an optimal solution. Figure 3 shows an example Product Mix model in Excel 2016, with the standard Solver modal dialog box open.
The 1990 version of Solver used a 16-bit computational engine, a user interface written in the Excel 3.0 macro language, DDE (Dynamic Data exchange) to communicate with Excel for Windows, and AppleScript to communicate with Excel for Macintosh. Over time, Solver’s computational engine was upgraded to 32-bit and 64-bit versions, the optimization algorithms were improved, the user interface was rewritten using VBA (Visual Basic for Applications), and XLL and COM interfaces were used to communicate with Excel.
Solver has always been quite popular with Excel users – to the extent that, when Excel 2008 for Mac was released without VBA or Solver, an outcry from users led Microsoft to work with Frontline to quickly “bring back Solver” for Excel 2008, in a form that didn’t depend on VBA. Twenty years after its first release, the INFORMS professional society cited the “the overwhelming success and impact of Solver” when awarding Frontline the 2010 INFORMS Impact Prize.
Solver for Excel Online
The new Solver, which works in both Excel 2016 and Excel Online, is fundamentally different from the standard Solver that’s included with desktop Excel: It is “built for the cloud,” and requires a live Internet connection. (The main reason to use the standard Excel Solver today is that it solves the problem on your own PC and doesn’t require an Internet connection.) The new Solver doesn’t have to be “installed,” just selected from the Office Store – its user interface is always “served” from a Frontline website, much like a web page. When you click the Solve button, your Excel model is sent (securely) to Frontline’s RASON® server cluster on Microsoft Azure, where the optimization is run; results are then returned to you in Excel 2016 or Excel Online. You’re using “Software as a Service” (SaaS), and Frontline is paying for the compute resources needed to solve the problem – for small models, Frontline absorbs this cost as a marketing expense.
The new Solver is in its third generation – and over 100,000 users have used earlier versions. Since Solver is “served from the web” each time it is opened, these users get the latest version automatically.
Touch-Friendly User Interface
The standard Solver dialog was designed long before touch screens and mobile devices became popular. In that version, it is still possible, but not easy, to make selections by touch. The new Solver UI, however, is designed to be easy to use with either a mouse or a fingertip. It also follows UI conventions common on mobile devices. Figure 4 shows the same Product Mix model, still in Excel 2016, but with the new Solver’s “modeless Task Pane” open.
Automatic Model Diagnosis and Solver Engine Selection
The standard Solver in Excel handles optimization models of every type – linear programming and nonlinear optimization, and “arbitrary” models solved via genetic and evolutionary algorithms. But – as many readers have probably experienced – you must determine your problem type yourself, and select the right “solving method.” If you’re a bit rusty on algebra, or if you first built a model for other purposes in Excel and then decided to apply Solver to it, you might not be sure whether your model is linear, nonlinear, or non-smooth.
The new Solver determines the problem type for you: It does its own algebraic “diagnosis” of the Excel formulas you’ve used to relate the objective and constraint cells to the decision variable cells, to decide the problem type; then it selects the best “solving method” (better called a “Solver Engine”) to handle your problem type – and size.
More Powerful Solver Engines
The new Solver for Excel Online and Excel 2016, like the standard Solver included in Excel 2016, handles models of limited size for free; for years, Frontline has offered compatible Solver upgrade products to scale from hundreds to millions of decision variables and constraints. But the new Solver makes it much easier to “scale up” your model: If you’re using the free version and you try to solve a model that’s too large, Solver will solve it anyway (subject to some CPU time limits on Frontline’s cloud servers), report the solution status and final objective, and offer an easy upgrade to get full solution results. Once you have a license for one of Frontline’s upgrade products, you can simply login with your Solver.com credentials, and immediately use your license to solve larger models – using the same new Solver in Excel 2016, Excel Online or Excel for iPad. Depending on your license, you can use some or all Solver Engines.
You can also use the same license in our Analytic Solver add-in for desktop Excel 2007 through 2016, and on our cloud platform, AnalyticSolver.com – with these products, you can create and run Solver models, Monte Carlo simulation and risk analysis models, decision tree models, and a whole array of forecasting, data mining, and machine learning models. Figure 5 shows the now-familiar Product Mix model with the Analytic Solver Ribbon and Task Pane open in Excel 2016.
What it Means for You
Earlier we explained that “When you click the Solve button, your Excel model is sent (securely) to Frontline’s RASON server cluster on Microsoft Azure, where the optimization is run; results are then returned to you in Excel 2016 or Excel Online.” What’s RASON? Indeed, there’s more to this story: RASON is key to getting more than you ever expected out of your Excel Solver model.
Without getting into all the technology details, RASON is a bridge between the world inside Excel, and the world outside – including corporate servers, web and cloud services, and mobile devices. RASON can be a “common language” between business analysts who work in Excel, and software developers who work in C++, C#, Java, R, or Python.
From Solver Models to Operational Analytics
In Figure 5 which depicts Analytic Solver in desktop Excel, you’ll notice a button “Create App” on the Ribbon. This button enables you to translate your Excel Solver model into RASON – and that means your model can run outside Excel on desktops, servers, or in the cloud. Among other things, it means you can build a model in Excel, then easily publish it for use by a wide range of “BI consumers.” These are people using tools like Tableau and Power BI, who may not have the skills to build an analytic model, but who can use your model to help make operational decisions.
Used in “point and click,” without any programming in Tableau or Power BI, your model in RASON can connect to a wide variety of data sources to update parameters, re-solve (independently from Excel), and deliver results in tabular or chart form on “BI dashboards.” Used with programming, your model can be easily embedded in desktop, server, web or mobile applications to do almost anything.
We believe this is key for organizations that want to realize business value from data and analytic models. And we believe that Solver, Analytic Solver, and RASON offer the easiest and fastest way to reach that goal of business value. If you want to know more, just visit www.solver.com or contact Frontline Systems at email@example.com.
Dan Fylstra is President and CEO of Frontline Systems, Incline Village, Nev.