Exercise 1: The Net Present Value (NPV) Model
Shambles, a large toy retailer, are looking at bringing out a new range of soft toys. The range underconsideration is “Mythical Beasts.” The “Mythical Beasts” range will cost £50,000 to setup and has running costs of £12,000 per year after that for five years.
The predicted return from this range is:
Year 2012 2013 2014 2015 2016
£ 24,000 28,000 32,000 36,000 40,000
(a) Assume a discount rate of 10%. Use Excel to set up a discounted cash flow analysis of the above information, calculating the net present value (NPV) for the investment. Remember that the initial expenditure takes place in 2011 Your spreadsheet must be constructed in such a way that it can accommodate different values of the discount rate, and must be as efficient as possible. This means that formulae and fixed cellreferencing must be used where appropriate. Also, the entire analysis must be contained in a single table. Include a print-out of cell values as well as a print-out of cell formulae.
(i) From the information in the spreadsheet only, decide whether or not this is a worthwhile investment, giving your reasons.
(ii) Explain the effect of discounting in this case upon future inflows, outflows and the NPV.
(b) Calculate the NPV for discount rates of 20%, 30%, 40% and 50%, giving your answers to two decimal places.
(c) Using Excel and the data generated from part (b), plot NPV against the discount rate in the range 10% to 50%.
(Your graph must be labelled clearly.)
(d) Use your graph to estimate the discount rate that would give a NPV of zero. What is the significance of this discount rate? (Hint: What happens at discount rates lower and higher than this figure?)
Exercise 2: The Linear Programming (Optimisation) Model
Shambles have selected the “Mythical Beasts” range and decided to concentrate on “Pegasus” and “Phoenix.” They would now like to find the right mix of these two products in order to maximise profit. Each toy has to go through two processes during manufacture: sewing and stuffing. Pegasus takes 30 minutes to sew whilst Phoenix takes 20 minutes; there are 10 hours (600 minutes) available for sewing each day.
Pegasus takes 20 minutes to stuff whilst Phoenix takes 25 minutes; there are 10 hours (600 minutes) available for stuffing each day.
Pegasus generates £4 profit per toy whilst Phoenix generates £5 profit per toy.
What is the maximum possible daily profit, and how many of each product should be produced in order to achieve this?
(a) Show the solution by both mathematical calculation and the graphical representation.
(b) Use “Solver” in Microsoft Excel to solve this problem. Include:
a print-out of cell values [2 marks]
a print-out of cell formulae [3 marks]
a print-out of the Solver dialogue box with optimisation conditions and constraints an Answer Report from Solver
a statement of the answer in words
Exercise 3 – GPSS Simulation Model
You can download the free Windows version of GPSS (i.e. GPSS World student version) from
https://minutemansoftware.com/
The sales counter next to the soft toy display in Shambles receives a customer every 2-4 minutes.
Most of these customers (80%) are buying toys and are dealt with by the cashier in 3-5 minutes. The
remaining 20% of customers come to open accounts that require an account manager. These
customers wait for the account manager, who spends 10-20 minutes in serving them.
a) You are required to simulate a 10-hour day in this department using GPSS.
To begin with, construct a flowchart describing the above events using suitable GPSS blocks.
b) Now carry out the simulation in GPSS, giving your programme (code) and simulation report.
c) Describe the results of your simulation using the simulation report, giving as much detail as
possible.
Exercise 4 – Association Rule Mining
A simple TDB is given below. Let the minimum support be 2, please mine all the association rules.
Tid Items bought
10 a, b, c
20 a, c
30 a, b
Exercise 5 – Artificial Neural Networks
There are four training input/target pairs for a two-class problem:, , , . A two-input perceptron
With hard limit activation function is used to solve this classification problem. Write down the stepby-step Solution to train the classifier by the basic perceptron learning rule.
Exercise 6 – DSS Case Study
Design a DSS to help decision-makers run the 2012 London Olympics.
Your proposal (including diagrams and write-up) may use any combination of DSS types,
technology, architectures, frameworks, etc. that have been covered in the course. You may also apply them to any aspect(s) of the case study mentioned above.
Credit will be given for creativity, practicality and research.
MARKING SCHEME for Exercise 6
Credit will be given for the following areas:
Good overall design with appropriate choice of modules, functionality, information links and Flows, etc.
Model base: sensible choice of models with appropriate inputs and outputs. Database / Data warehouse: sensible choice of data useful for DSS.
Choice of appropriate DSS type(s) from the ones studied in the module.
Appropriate analysis of DSS in terms of technology, evaluation procedures, etc.
Research into existing/similar DSS in place already.
Creativity, practicality and innovative thinking