is that the shops are closed early. So, he decided to move from 2 shifts a day to 3
shifts a day. Every shop requires staff in three departments, namely, Accounts,
Front Desk and Supplies and the staff are equally skilled in all departmental
activities. He calculated the minimum and maximum number of staff needed for
each shift and for each shop in order ensure efficient operation and this
information is provided in the following table.
Department\
Shift
Shift 1 Shift 2 Shift 3
Accounts (6,8) (11,12) (7,12)
Supplies (4,6) (11,12) (7,12)
Front Desk(2,4) (10,12) (5,7)
The CFO wants to create a schedule that effectively makes use of his staff. The
accounts, Supplies and Front desk department need at least 26, 24, and 19 staff
respectively and shifts 1, 2 and 3 cannot have more than 22, 32, and 25 staff
respectively. Help the CFO to formulate this as an optimisation problem and create
a spreadsheet model to determine a feasible schedule with the least number of
staff needed for a shop.
(20 marks)
Week 3 lecture
2. The Monty Hall problem is a game show, where there are three closed doors.
Behind one of these doors is a prize. You can win this prize, if you can correctly
pick the door. You are first asked to pick a random door. The host, Monty, will open
one of the other two doors at random and shows you that it does not have the
prize. Now you are given an option to either change or stick with your decision.
The recommended strategy is to switch your decision. Simulate this strategy for
the Monty hall problem using macros in excel to play the game 100 times and
determine the number of times you won the price.
(15 marks)
Week 4 lecture
3. The Australian Bureau of Statistics publish their monthly data on the number of
livestock slaughtered at
https://www.abs.gov.au/statistics/industry/agriculture/livestock-and-meat-
australia/latest-release
Create a spreadsheet model to implement simple exponential forecasting to
forecast the number of pigs slaughtered in the next four months. Find the optimal
values for the parameters of your model.
(20 marks)
Week 6 lecture
4. Write a VBA function to implement simple exponential smoothing that takes
the timeseries, smoothing parameter and initial level as input and returns the
forecast for the next observation in the series.
(15 marks)
Week 8 lecture
5. CASE STUDY: A small manufacturing company, called MAD Ltd, to build a
spreadsheet model for demand forecasting. The Operations Manager of the client
organization has prepared an outline requirements specification for the
spreadsheet model. This specification is provided below. Client Spreadsheet Model
Requirements Specification At MAD Ltd we have a portfolio of 600 products, which
are classed into five categories according to their value as measured by annual
demand and unit cost as shown in the table below.
Products Demand/year (each product) Cost (£) (per unit)
V 1550 40
W1-W10 650 5
X1-X12 120 50
Y1-Y02 30 5
Z1-Z493 15 3
Currently we use simple exponential smoothing to forecast demand on a quarterly
basis for all products. However our current forecasts are not as accurate as we
would like because forecast error is too large for some products. This is leading to
problems such as carrying too much inventory or failing to meet demand. We
would like a spreadsheet tool that has the functionality for generating adaptive
demand forecasts from past sales data. This will include the following capabilities.
We want to be able to forecast demand when there might be seasonal patterns
and/or trends as well as random variation in the sales data. We want to be able to
use the spreadsheet model for all our products and to update forecasts every
quarter as new sales data becomes available to generate updates. We also want
the spreadsheet model to provide sensible estimates of forecast error, so that we
can understand the accuracy and precision of forecasts in relation to actual sales
for products over meaningful time windows to support management reporting.
We want to be assured of the validity of the calculations being conducted in the
spreadsheet model, therefore we have made available sales data for product X1.
This data is shown in the table below.
Past Sales of Product X1
Quarter1234
2014 8 10 7 15
2015 15 17 14 28
2016 25 26 21 40
2017 31 34 28 57
We would like to be able to have a case study of using the spreadsheet model
with Product X1 data to make sure we understand the choices we need to make in
choosing model inputs and interpreting the output from the model. We want to be
able to check the credibility of analysis. We might also use this example to train
other colleagues in using the forecasts generated by the spreadsheet model. We
also want to have visibility of the internal calculations in your forecast modelling
as well as the code so that they can be verified. Finally, we would like guidance on
how operations staff should use the spreadsheet tool for our full product range
and advice on how our IT staff should maintain the spreadsheet model
In summary our required deliverables are:
1. A spreadsheet model that meets our requirements as explained in our
specification.
2. A written report (maximum 2 pages) that includes (a) guidance for using and
maintaining the spreadsheet model and its generalisability to forecast other
products (not just X1) (b) assumptions and rationale for your modelling choice and
(c) analysis/interpretation of product X1 data in a form that is understandable to
our managers.
(30 marks)
Week 10 lecture
