A spreadsheet approach for incorporating actual motor carrier freight rates and external environmental costs in a newsvendor model

Abstract

The purpose of this paper is to present a model for maximizing a retailer’s total expected profit using actual motor carrier freight rates and estimates of environmental costs associated with the transportation of a seasonal product. It is assumed that a single seasonal product is sold at a fixed price and demand for that product is normally distributed. Prior to the selling season, the retailer must determine how many units of the seasonal product to purchase from the supplier—therefore, the newsvendor model is appropriate for analyzing this problem. It is assumed that the retailer arranges and pays for transportation, i.e., the product is shipped free on board (FOB) Origin, Freight Collect from the supplier. Items unsold at the end of the season can be sold at a reduced price (salvaged). Actual freight rates are incorporated in the model by considering less-than-truckload discounts, fuel surcharges for both less-than-truckload (LTL) and truckload (TL) shipments, and over-declaring of shipments. All-units purchase quantity discounts are assumed also. Due to the nonlinear nature of motor carrier freight rates, this problem does not have a closed-form solution. Therefore, we present an Excel-based model for solving this problem. As demonstrated in the model solution, when environmental costs are considered, the buyer’s optimal purchase quantity decreases.