Optimization using a spreadsheet program

Abstract

This project focuses on developing Excel spreadsheet with the aim to solve different optimization problems so that the users can understand the optimization problems better. First, the background of the airline industry is looked into so as to facilitate the development of the spreadsheet targeted at solving the seat allocation problem of an airplane. After which, with the typical dynamic programming problem that looks into how to determine the shortest route with the given limitations, the workings of Excel Solver is illustrated. It is shown that the Excel spreadsheet is an excellent tool to work with to illustrate the workings of mathematical modelling with linear programming and dynamic programming. The final product of this project is a general spreadsheet that shows the user how to determine the value of the objective function of the seat allocation problem. With the help of the Excel Solver, the user can determine the value of the objective function stage by stage. However, when relating to the actual seat allocation problems, there will be more than one fare class and hence more than one stage to the dynamic programming of the mathematical model. Therefore, it is recommended that future work can be done in developing an Excel spreadsheet that utilizes Visual Basic for Application (VBA) so that the spreadsheet can be easier to work with.