Individual Coursework Assignment 1

1)  Individual Coursework Assignment 1

This assignment is worth 25% of the overall module mark.

Coursework Brief

For this coursework you are required to solve a problem using Microsoft Excel. Your solution should be word-processed, including the computer output (pasted in), and

submitted electronically.

Your solution should include both the output itself and a concise account of the methods you have used. Where appropriate you should also explain the reasons you have

chosen those particular methods.

A new book is to be launched by a publishing firm to catch the Christmas market. The manager of the firm wants to know how many books to print for this time-limited

market. If she prints too few, she will miss the opportunity for profit. If she prints too many, she will have a lot of books left over in the New Year which will have

to be sold off at a loss. Suppose every book sold before Christmas makes a profit of £15 whilst the publishing firm loses £5 for every book left over after Christmas.

The pre-Christmas demand for the book has a probability distribution given in the table below.
(i)    Calculate (in units of 1000 books) the expected demand for the book, and the standard deviation in this demand.
(ii)    Calculate figures for a third column to the table, equalling the profit (in units of £1,000) for each amount sold if the print run is 23,000 books. What is the

return (expected profit) in this case?
(iii)    Calculate the print run size that will maximise the expected profit. What is the risk associated with this strategy? Support your answers by calculating

expected profit figures for each possible stock level in a table.
(iv)    Use your answers to parts (i) to (iii) to produce a short report advising the manager of the publishing firm on her strategy.

Number sold (1000’s)    Probability    Profit when print = 23(1000’s)
20    0.01
21    0.02
22    0.03
23    0.03
24    0.04
25    0.05
26    0.06
27    0.07
28    0.08
29    0.09
30    0.10
31    0.07
32    0.07
33    0.06
34    0.05
35    0.05
36    0.04
37    0.03
38    0.02
39    0.02
40    0.01

Key Marking criteria will include:

•    Accuracy of the numerical solutions
•    Appropriateness of the methodology
•    Clarity and conciseness of the descriptions of the methodology


find the cost of your paper