Simulation in Spreadsheet - News Vendor Problem

Due on Tuesday:
Simulating with Spreadsheets:
Newsvendor Problem – Setup

•          Newsvendor sells newspapers on the street

§  Buys for c = $0.55 each, sells for r = $1.00 each

•          Each morning, buys q copies

§  q is a fixed number, same every day

•          Demand during a day:  D = max (ëXù, 0)

§  X ~ normal (m = 135.7, s = 27.1), from historical data

§  ëXù rounds X  to nearest integer

•          If D £ q, satisfy all demand, and q – D ³ 0 left over, sell for scrap at s = $0.03 each

•          If D > q, sells out (sells all q copies), no scrap

§  But missed out on D – q > 0 sales

•          What should q be?

Newsvendor Problem – Formulation

•          Choose q to maximize expected profit per day

§  q too small – sell out, miss $0.45 profit per paper

§  q too big – have left over, scrap at a loss of $0.52 per paper

•          Classic operations-research problem

§  Many versions, variants, extensions, applications

§  Much research on exact solution in certain cases

§  But easy to simulate, even in a spreadsheet

•          Profit in a day, as a function of q:

W(q) = r min (D, q) + s max (q – D, 0) – cq

§  W(q) is a random variable – profit varies from day to day

•          Maximize E(W(q)) over nonnegative integers q

Newsvendor Problem – Simulation

•          Set trial value of q, generate demand D, compute profit for that day

§  Then repeat this for many days independently, average to estimate E(W(q))

–        Also get confidence interval, estimate of P(loss), histogram of W(q)

§  Try for a range of values of q

•          Need to generate demand D = max (ëXù, 0)

§  So need to generate X ~ normal (m = 135.7, s = 27.1)

§  (Much) ahead – Sec. 12.2, generating random variates

§  In this case, generate X = F_m,s(U)

U is a random number distributed uniformly on [0, 1] (Sec. 12.1)

F_m,s is cumulative distribution function of normal (m, s) distribtuion

Newsvendor Problem – Excel

•          File Newsvendor.xls

•          Input parameters in cells B4 – B8 (blue)

•          Trial values for q in row 2 (pink)

•          Day number (1, 2, ..., 30) in column D

•          Demands in column E for each day:

                 

•          For each q:

§  “Sold” column: number of papers sold that day

§  “Scrap” column: number of papers scrapped that day

§  “Profit” column: profit (+, –, 0) that day

§  Placement of “$” in formulas to facilitate copying

•          At bottom of “Profit” columns (green):

§  Average profit over 30 days

§  Half-width of 95% confidence interval on E(W(q))

–        Value 2.045 is upper 0.975 critical point of t distribution with 29 d.f.

–        Plot confidence intervals as “I-beams” on left edge

§  Estimate of P(W(q) < 0)

–        Uses COUNTIF function

•          Histograms of W(q) at bottom

Vertical red line at 0, separates profits, losses

Sample Output on Spreadsheet: