Job shop scheduling with Excel and SolverStudio
This workbook features a job shop model to find the optimal production plan. The objective is to minimize the makespan, i.e., the duration until all jobs are completed. The spreadsheet is populated with exemplary data to be replaced. The resulting schedule is visualized in a simple Gantt chart that is created with conditional formatting. To inspect the schedules for each job, sort first by start time and then by job, or, to inspect the schedules for each machine, sort first by start time and then by machine.
Facility location optimization with Excel and SolverStudio
This workbook features a model to choose an optimal footprint of sites (e.g., warehouses) to serve customer demand. The objective is to minimize the total costs comprising (i) fixed costs for selecting sites, (ii) site-dependent production costs, and (iii) shipping costs that depend on the distance as well as the shipping quantity. The shipping distances from sites to customers are approximated using the haversine formula that gives the distance between two points on a sphere. The optimization results are shown in various formats including Excel’s 3D Map visualization tool.