Be it for maximizing profits, finding the best truck/aircraft routes, or deciding which bonds to invest in; businesses do optimization all the time. Whether they are aware of it or not, there often is one best solution that satisfies their needs and available resources. A piece of software can make these important decisions without human bias, while taking countless possibilities into account. It just so happens that almost every business user is quite familiar with Microsoft Excel and it is a great tool when it comes to solving optimization problems. Thanks to several add-ins Excel readily ships with, running what-if scenarios, playing around with parameters and discovering alternatives is something every Excel user can easily do.
The simplest optimization analysis tool is Excel’s Goal Seek. Assume that you have a very long formula in one cell and you want to see how one parameter behaves, while keeping the others fixed. It could take a really long time to separate that one variable, or may be impossible. With Goal Seek, users can find the value of one parameter by entering a desired value for another.
For more complex applications; namely, equation sets with several constraints and variables, other tools are needed. Excel’s Solver add-in comes to play here. Considering that literally everyone is familiar with Excel, it’s no surprise that Solver found wide use in many businesses. Plus, it’s relatively fast and easy to use. With Solver, problems that require several iterations with different inputs and outputs can be solved.
In management and financial decision making, there are several methods for determining the viability of a project. When it comes to feasibility for example, the net present worth (NPW) and net future worth (NPW) are commonly accepted as reliable indicators. Both are calculated based on investment frequency and amount.
Another very common use is in production. Product mix, capacity and machine allocation problems are often tied to one other. Resource limitations and logistics constraints add up and it gets more complicated to figure out production numbers. As a result, you end up facing an even more difficult decision.
One of the core concepts of operations research, shortest path problems, are another distinct example for Solver use. Your GPS solves this problem all the time; it points you the best path to your destination. Optimizing a transportation and warehousing chain can save a company considerable money.
Employee scheduling and workforce allocation can be a challenging tasks for businesses. Finding the best hours for employees with different hours and skills is typically determined by managers. However, to find the optimum schedule, you would have to solve a few equations and add some constraints; such as time availability and company resources. If you could run these calculations and let your colleagues know of their schedule with one click and on the go, without a doubt, overall work efficiency would increase substantially.
Smaller businesses usually can’t afford specialized software for managing their finances, operations, production or distribution. Thus, Excel becomes their workhorse. Correct allocation of their investments and resources can mean a solid jump. As mobile devices are taking over the market, every business needs to be able to meet the demand.
Why waste all that time and effort put into works done in Excel? With SpreadsheetWEB, you can transform an Excel document into a web application in no time. This way, your users can access these documents from everywhere, using any kind of device with a web browser. Since SpreadsheetWEB actually mimics Excel capabilities, compatibility and security issues are no longer a concern. Plus, your data will always be up to date.
Integrating your own code into the application, you can also run customized solvers. Through the API, applications can exchange data with the server and run iterative calculations in a server environment or on cloud.
Below is a quite simple application. This is a calculator for finding the interest rate when loan amount and monthly payments are entered. Using the PMT function of Excel, the desired rate is calculated with some recursions. Instead of a “trial and error” approach, Solver does iterations. The software then calculates partial derivatives of the inputs and tries to approach the desired solution by forming a vector and extrapolating the results.
Of course, this is merely a desktop application. Things work differently on a server based software; the data is called using the API and then sent to the server for the calculations to run. Then the results are populated on the web application. This mechanic saves your device the CPU strain and the process is faster, especially in more complicated applications. With a bit of customization, this calculator can become a mobile application fairly easily using SpreadsheetWEB.
Solver is a great addition to Excel. Even though it’s relatively fast and easy to use, it falls short on certain levels. First of all, it’s completely dependent on the PC it is running on. And second, Solver is an Excel add-in and this can cause issues on different machines and it can’t be installed on every OS.
SpreadsheetWEB can help businesses overcome these shortcomings and solve optimization problems with ease. Optimization problems are CPU intensive tasks. Therefore, it is a better idea to run these calculations on a server; rather than a desktop PC, let alone a mobile device. On top of that, moving these applications to a web environment means better security and accessibility from everywhere.
