An Excel based approach to optimise trade-offs between freight expediting and safety stock inventory costs

Abstract

In this paper, we present an Excel based methodology for the computation of two optimal policy quantities, order size and reorder point of a (Q, r) inventory system under uncertainty when backorder costs are independent of the amount of the backorder and dependent on whether there is a shortage. It is shown that the total cost curve for the backorder model is a concave-convex function. Solving the problem using a spreadsheet solver would not necessarily yield the optimal solution. It is first necessary to identify the convex and concave domains prior to the application of Excel Solver to determine the optimal solution of the model. This approach is flexible and capable of handling different lead time demand distributions as well as the more realistic scenario when both the demand and lead times are randomly distributed. The methodology is illustrated with a simple problem.