There is a variety of equipment that is used to detect and deter nuclear smuggling at airports, seaports, and at borders. Every piece of equipment has two types of maintenance activities, preventive and corrective, that need to be performed in order for the equipment to function accurately. A model of the lifecycle cost for the equipment’s preventive and corrective maintenance is represented using Microsoft Excel’s Solver, What-if Analysis, and Visual Basic and Applications (VBA) programming in order to maximize the maintenance activities under specific constraints. The global optimal solution of the maximum number of activities will be used to help country’s determine how many activities they can do based on a budget.

In the model, there are two preventive maintenance activities of functional check and physical inspect. There are two corrective maintenance activities of network repair and workshop repair. The preventive maintenance frequencies, 1, 2, 4, 6, and 12, will change if the budget of the activities falls below/exceeds the maintenance budget. The Microsoft Excel’s What-If Analysis tool is used to calculate the overall budget cost of all maintenance activities. Based on the possible solutions, those that equaled the maintenance budget, their frequencies were used in Solver to determine if it is the optimal solution. When the VBA programming was used, Solver was coded in multiple loops and programed to copy and paste all table charts on a spreadsheet. By using two different approaches, 16 out of 25 solutions were feasible. The solutions with the most maintenance activities would be chosen but only if it used the entire budget. However, there are many deciding variables to consider when deciding the optimal solution. The next step to this research would be to consult subject matter experts on the maintenance equipment in order to better understand each country’s maintenance needs.


Angela Waterworth

Lab site

Pacific Northwest National Laboratory (PNNL)

Funding Acknowledgement

*This material is based upon work supported by the National Science Foundation through the Robert Noyce Teacher Scholarship Program under grant# 1546150. Any opinions, finding, and conclusions or recommendations expressed in this material are those of the author(s) and do not necessarily reflect the views of the National Science Foundation. The research was made possible by the California State University STEM Teacher Researcher Program.



URL: https://digitalcommons.calpoly.edu/star/376


To view the content in your browser, please download Adobe Reader or, alternately,
you may Download the file to your hard drive.

NOTE: The latest versions of Adobe Reader do not support viewing PDF files within Firefox on Mac OS and if you are using a modern (Intel) Mac, there is no official plugin for viewing PDF files within the browser window.