A spreadsheet-based optimization model for the integrated problem of producing and distributing a major weekly newsmagazine

In this paper, we address the problem of producing and distributing the Brazilian newsmagazine Época, a major weekly publication with one of the 10 largest circulations in the world. This real-world problem had been puzzling magazine publishers in Brazil and remained unsolved for many years. We propose an innovative mixed-integer-linear programming model to determine the number and location of the industrial facilities that should produce the magazines, what destinations should be assigned to each selected facility; the production sequencing and the modes of transportation (air or truck). Our model aims to minimize the total cost while adhering to production capacity and time constraints. The model was implemented in an electronic spreadsheet environment and yielded a savings of 7.1% of the total costs. Given that despite their huge popularity, little has been written on the issues of implementing full-scale optimization models in spreadsheets; thus we also provide the details of the model’s implementation in Excel.