2023-24 EXAMINATIONS

PART II (Second, Third and Final Year)

MANAGEMENT SCIENCE

MSCI242L & 242M Spreadsheet Modelling for Management

This exam contains 3 sections. You must answer all of the questions in each section.

Section A contains 25 questions, and is worth 50 marks.

Section B contains 10 questions, and is worth 30 marks.

Section C contains 1 question, and is worth 20 marks

Submission Rules

Deliverable is an Excel 2021 workbook addressing all the tasks

The workbook will be assessed on a University-spec. PC-version of Excel 2021

The workbook must not use functions that don’t exist in Excel 2021, such as LAMBDA and VSTACK

All answers should be presented in the correct cells on the user sheet

The user sheet should include yourID number

All answers must be formula or function-based – not simple/static values, not generated by simply sorting or filtering the data, not macro-based, and not found manually by the user

Failure to comply with these rules may result in a zero mark for that task

Only correct results will receive the marks. No partial marks will be awarded

The model will be tested to check the formulas are dynamic and can handle new data

The workbook should not exceed 4MB in size                                                      (5 marks deducted)

The number of sheets in the workbook should not exceed 5                                (5 marks deducted)

The workbook should not have any hidden sheets                                      (5 marks deducted)

The workbook should be free from any circular references                            (5 marks deducted)

All sheets should have a dedicated name, i.e. not Sheet1 or Sheet2         (5 marks deducted)

Using the incorrect dataset (10 marks deducted)

Access to the Data

All Excel datasets are held in a folder on the 242 exam Moodle called ‘Exam Datasets’

The filenames are 3-digit code numbers, for example 123.xlsx

The file you must use is the one with the 3-digit code that corresponds to the LAST THREE DIGITS of your student ID number. For example, if yourID number is 12345678, then you must use 678.xlsx All the datasets are different, sousing the incorrect dataset will generate incorrect results

Background

This task is based on a modifiedversion of workshop 9 – the Tellend Logging Flow Model.

Previous modelling analysed the activity of the Ibis Forest sawmill, operating for 196 days a season with an opening schedule of seven 4-week blocks.

However, Glenn operates another forest nearby called Dragon Forest, which also has a sawmill and storage pond. Glenn has provided you with the logging and cutting data for last year for the Dragon Forest sawmill in the accompanying dataset. Like the Ibis Forest sawmill, the Dragon Forest sawmill operates for 196 days a season. However, it is different in the following ways:

•    it is a much bigger sawmill, and so processes more logs

•    the Dragon Forest loggers work 6 days a week throughout the season (Monday to Saturday)

•    the sawmill opening schedule is organised into 14 2-week blocks

The Dragon Forest sawmill opening schedule used last year was [2,2,3,3,4,4,4,4,5,5,6,6,5,5] days per week, for the 14 blocks. This schedule sums to 58 days. However, Glenn acknowledged that this was far from ideal, as the log pond size was quite ‘volatile’ and too many logs were wasted at the end of the season.

Section A – Analysis of Last Season [50 marks]

Analyse the Dragon Forest sawmill data from last season to produce the following results:

1)    the total number of logs added to the pond during the season

2)    the total number of logs cut at the sawmill during the season

3)    the maximum log pond size last season

4)    the day of the season on which the maximum long pond occurred

5)    the block number in which the maximum long pond occurred

6)    the day of the week on which the maximum long pond occurred  (Monday, Tuesday etc.)

7)    how many days of the season experienced a log pond size greater than 500 logs?

8)    the average number of logs cut per day at the sawmill (rounded to 2 decimal places)

9)    the average number of logs cut on Fridays at the sawmill (rounded to 2 decimal places)

10) the total number of logs cut at the sawmill on Fridays

11) the total number of logs added to the pond on Fridays and Saturdays

12)  over the full season, which day of the week (Monday, Tuesday etc.) saw the fewest total logs added to the pond (ignoring Sunday, obviously)?

13)  over the full season, which day of the week (Monday, Tuesday etc.) saw the most total logs cut in the sawmill?

14) what was the single biggest increase in pond size, from one day to the next?

15) which day of the season saw the biggest increase in pond size?

16) which day of the season saw the fewest logs cut in the sawmill?

17) which day of the season saw the most logs added to the pond?

18)  how many days of the season saw more than 200 logs cut at the sawmill?

19) which week (1 to 28) saw the most total logs cut in the sawmill?

20) which week (1 to 28) saw the biggest log pond size1?

21) which week (1 to 28) saw the highest single-day number of logs cut in the sawmill?

22) which block saw the biggest change  in pond size, from the start of the block compared to the end?

23) which block saw the highest number of total logs cut at the sawmill?

24) which block saw the 2nd  highest number of total logs added to the pond?

25) which block has the highest median number of logs added to the pond?

Section B – An Alternative Sawmill Opening Schedule [30 marks]

The original opening schedule at Dragon Forest sawmill last year was [2,2,3,3,4,4,4,4,5,5,6,6,5,5] days per week, for the 14 blocks.

In order to better manage the log pond and avoid wasting logs at the end of the season, Glenn has suggested using the following alternative schedule of [3,3,3,4,4,4,4,5,5,6,5,6,5,5].

This schedule sums to 62 days.

Based on a deterministic number of logs cut in the sawmill of 190 logs per day, adapt your model for this alternative schedule to produce the following results. The logs added to the pond should be the same numbers as last year.

26) the maximum log pond size

27) the final log pond size at the end of the season

28)  how many logs is the biggest single-day decrease in long pond size?

29) which block number sees the biggest single-day decrease in long pond size?

30) which week (1 to 28) sees the most total logs added to the pond?

31) which week (1 to 28) sees the biggest log pond size1?

32) what is the average daily difference  in log pond size, compared to last year’sactivity? (rounded to 2 decimal places3)

33)  how many days of the season experience a log pond between 100 and 300 logs (inclusive)?

34)  how many days of the season experience a log pond size greater than the equivalent day last year?

35)  how many days is the longest run of consecutive days of log pond size decreases?

Section C – Finding a Better Schedule [20 marks]

Using the alternative schedule as the starting point [3,3,3,4,4,4,4,5,5,6,5,6,5,5] investigate new

schedules whereby one of the 14 blocks in increased by 1 day and another block is decreased by 1 day, thereby maintaining the total of 62 days.

36) For all possible schedules, generate the maximum log pond size. What is the smallest maximum log pond size observed across all the schedules?