Instructions
1. Answer all questions
2. Your submission must have 5 excel files and one word document
3. Make sure you save your macro enabled excels as .xlsm
1. A CFO of a certain retail chain realised that one of the reasons for failing profits
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.
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.
3. The Australian Bureau of Statistics publish their monthly data on the number of
livestock slaughtered at
https://www.abs.gov.au/statistics/industry/agricul…
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.
4. Write a VBA function to implement simple exponential smoothing that takes
the time series, smoothing parameter and initial level as input and returns the
forecast for the next observation in the series.
