An extremely numerate newsagent (with a spreadsheet program, as you will need) is trying to work out how many copies of a newspaper he should order. The cost to him per copy is 15 pence, which he then sells at 45 pence. Sales are distributed Normally with an average daily sale of 250 and variance 625. Unsold copies cannot be returned for credit or refund; he has to throw them away, losing 15p per copy.

(a) What do you think the seller’s objective should be?

(b) How many copies should he order?

(c) What happens to the variance of profit as he orders more copies?

(d) Calculate the probability of selling more than X copies. (Create an extra column in the spreadsheet for this.) What is the value of this probability at the optimum number of copies ordered?

(e) What would the price–cost ratio have to be to justify the seller ordering X copies?

(f) The wholesaler offers a sale or return deal, but the cost per copy is 16p. Should the seller take up this new offer?

(g) Are there other considerations which might influence the seller’s decision?

Set up your spreadsheet as follows:

col. A: (cells A10:A160) 175, 176, . . . up to 325 in unit increments (to represent sales levels).

col. B: (cells B10:B160) the probability of sales falling between 175 and 176, between 176 and 177, etc. up to 325–326. (Excel has the ‘=NORMDIST()’ function to do this – see the help facility.)

col. C: (cells C10:C160) total cost (= 0.15 × number ordered. Put the latter in cell F3 so you can reference it and change its value).

col. D: (cells D10:D160) total revenue (‘=MIN(sales, number ordered) × 0.45’).

col. E: profit (revenue – cost).

col. F: profit × probability (i.e. col. E × col. B).

cell F161: the sum of F10:F160 (this is the expected profit).

Now vary the number ordered (cell F3) to find the maximum value in F161. You can also calculate the variance of profit fairly simply, using an extra column.