Instructions to Students:
Answer THREE questions.
The use of a translation dictionary between English or Welsh and another language, provided
that it bears an appropriate departmental stamp, is permitted in this examination.

1. The ABC company has two stand-alone stores located in East and West, which sell a
selection of Binder, Pen, Pen Set, Pencil and Desk. Excel file Q1.xlsx contains
databases of the company’s sales report for the first half of this year. The worksheet
“Sales Representatives” details names of all sales representatives, their corresponding
ID and the region of the store at which they work. The worksheet “Sales Record
Quarter 1” records product descriptions and number of units sold in the first quarter
for each of the sales representatives. The worksheet “Sales Record Quarter 2” records
the same information for the second quarter. The worksheet “Unit Price” lists the unit
price of each product.

(a) Discuss the benefits and costs of a flat file database and a relational database,
giving examples of instances when one would be preferable to the other.
[5 marks]
(b) Describe and discuss the process of normalizing a database.
[5 marks]
(c) Use all tables provided to recreate a flat file database containing, for each sales
representative, their ID, their name, the region of the store at which they operate,
the names of the products, the number of units of each product sold by the
representative, and the total sales revenue generated by each sales representative.
Explain your actions.
[6 marks]
(d) Use pivot tables to summarise the total sales revenue for each product for each
sales representative working in West Stores.
[4 marks]
(e) Suppose commissions are paid to sales representatives per quarter as an incentive.
For each product, a sales representative receives a 10% commission on their total
sales revenue over £1,000, a 5% commission on their total sales revenue between
£300 and £1,000, and a 1% commission on their total sales revenue below £300.
Using appropriate logical functions and the Pivot table tool in Excel, calculate the
total commissions received by each sales representative.
[6 marks]
(f) Use pivot charts to create appropriate charts by which to compare the total
commission received from selling product ‘Pen Set’ by sales representative
‘Anjolie Hicks’ with the total commission received individually by each of the
other sales representatives selling the same product.
[4 marks]

2. The Rainbow food company produces four types of product (Chocolate, Fruit Sweets,
Crisps, and Shortbread) every month, each of which requires three sequential
operations (Production, Packaging and Delivery). The financial cost for each type of
operation for each product is shown in the table below:
Product Production Packaging Delivery
Chocolate £11 £5 £6
Fruit Sweets £8 £6 £5
Crisps £7 £8 £7
Shortbread £12 £4 £8
While the revenue per unit sold is:
Chocolate Fruit Sweets Crisps Shortbread
Unit Price £34 £30 £32 £36
The firm’s total monthly cost for producing, packaging and delivery must not exceed
£1,000, £650 and £700, respectively. The owner of the company requires that at least
50% of the revenue should come from Chocolate and Shortbread. On the other hand,
the company needs to produce at least 20 units of shortbread per month. As the
manager, you need to determine the quantity of each product to produce each month
in order to maximize the company’s profit.

(a) Formulate this as a Linear Programming problem. Clearly describe in detail the
Objective Function and the constraints.
[6 marks]
(b) Solve the Linear Programming problem and describe the results (the answer can
be rounded to the nearest integer).
[6 marks]
(c) How sensitive is the model to changes in the unit price of Chocolate?
[4 marks]
(d) Explain the ‘reduced cost’ in this question.
[5 marks]
(e) What is the interpretation of a negative shadow price?
[5 marks]
(f) If someone suggests spending an extra £200 on producing products, indicate
whether or not you will accept this suggestion. Explain your answer.
[4 marks]

3. Excel file Q3.xlsx contains a quarterly series of UK Business Investment data from
2000Q1 to 2018Q3, which is not seasonally adjusted.
(a) Explain each component of an observed time series. Comment on when it is
appropriate to work with the log of a series rather than the actual values.
[4 marks]
(b) Discuss the causes and effects of seasonal variation in data.
[4 marks]
(c) Deseasonalise the series given in Q3.xlsx using a 6-point moving average
method and plot the seasonally adjusted series against the original series.
Explain your steps.
[10 marks]
(d) Regress the seasonally adjusted series on a constant and a linear trend.
[4 marks]
(e) Using the estimated constant and trend, predict the seasonally unadjusted
series for 2018Q4 – 2019Q4.
[8 marks]
4. (a) Suppose a £1,000 face-value Bond has a coupon rate of 7% paid semiannually. The bond matures in 10 years from now with the annual interest rate
expected to be fixed at 4.8%. What is the price of the bond?
[8 marks]
(b) What is the price of a perpetual bond (i.e. a bond which has no maturity date
and pays coupons annually in perpetuity) with a £40 coupon paid annually and
an annual discount rate set to 3.3%?
[6 marks]
You want to buy a new car at £20,000. To finance buying the car, the car dealer offers
you a hire-purchase contract. The annual interest rate (APR) for the hire-purchase
contract is set at 8.2%. You are requried to pay 10% of the value of the car as a deposit
now. The rest of the amount owing will then be paid off in fixed monthly repayments
over a period of 4 years. The first repayment will be due at the end of next month from
now, and the last payment reduces the sum owed to zero.
(c) Using Excel and Solver, calculate the constant monthly repayment. Explain
your steps.
[10 marks]
(d) Explain the meaning of Internal Rate of Return. How would you calculate it in
Excel if the IRR function was not available?
[6 marks]

5. Excel File Q5.xlsx contains a series depicting the quarterly average effective exchange
rate index for sterling from 2000Q1 to 2018Q4.
(a) Explain the meaning of stationarity, and why stationarity is a desireable
property for predicting future values of a time series.
[3 marks]
(b) Carry out a Dickey-Fuller test to investigate whether this series is stationary
or not. If not, convert the series so that it is stationary.
[5 marks]
(c) Explain what an ARIMA (p,d,q) model is, and the mearnings of each
component and parameter.
[3 marks]
(d) Using Excel Solver, estimate the series as an ARIMA (p,d,q) model, with p
and q equal to 2 and an appropriate value for d. (Set the starting value of all
changing variables to 0.)
[10 marks]
(e) For the period 2000Q1 to 2018Q4, calculate the Root Mean Square Error
(RMSE) between the actual and estimated values.
[5 marks]
(f) Use your model to forecast the next 4 values of this series.
[4 marks]