Abstract
We devise a simple yet highly effective technique for solving general optimal control problems in Excel spreadsheets. The technique exploits Excel’s native nonlinear programming (NLP) Solver Command, in conjunction with two calculus worksheet functions, namely, an initial value problem solver and a discrete data integrator, in a direct solution paradigm adapted to the spreadsheet. The technique is tested on several highly nonlinear constrained multivariable control problems with remarkable results in terms of reliability, consistency with pseudo-spectral reported answers, and computing times in the order of seconds. The technique requires no more than defining a few analogous formulas to the problem mathematical equations using basic spreadsheet operations, and no programming skills are needed. It introduces an alternative, simpler tool for solving optimal control problems in social and natural science disciplines.
Highlights
Optimal control problems are commonly encountered in engineering and life sciences, as well as social studies such as economics and finance [1,2,3]
An optimal control problem is typically concerned with finding optimal control functions that achieve optimal trajectories for a set of controlled differential state variables
We present a systematic technique for solving optimal control problems in a spreadsheet, modeled on partial parametrization direct methods
Summary
Optimal control problems are commonly encountered in engineering and life sciences, as well as social studies such as economics and finance [1,2,3]. We present a systematic technique for solving optimal control problems in a spreadsheet, modeled on partial parametrization direct methods. Our devised direct spreadsheet method, on the other hand, differs fundamentally from prior work, in that the algorithmic implementation for solving the IVP, and integrating the cost index, has been decoupled from the spreadsheet grid and encapsulated in pure spreadsheet solver functions suitable for seamless integration with the NLP Solver. The design of the solver functions, described, permits utilization of fully implicit and adaptive algorithms which make the method applicable to a general class of nonlinear multivariable optimal control problems. The remainder of this paper is organized as follows: we describe the basic steps required to model and solve an optimal control problem using the adapted direct method technique. We start from a mathematical statement of a given problem and present a feasible solution obtained by the method with relevant comparisons to the reported result in [17]
Sign-in/Register to view highlights & summary Sign-in/Register to access full text options 
Free) 
Talk to us
Join us for a 30 min session where you can share your feedback and ask us any queries you have
Schedule a call
