Advanced Spreadsheet Modelling

MSCI 342 Advanced Spreadsheet Modelling

The following is an experimental modelling task, called ‘Fill in the Blanks’.

The sequence of 6 numbers (orange cells) must be placed in the correct positions in the yellow cells, such that the subtraction result is mathematically correct, and the target value is achieved.
All six numbers must be used once.

For example,
the following sequence of 6 numbers: can be arranged as follows, to produce the result of 420:

[result is: 835 – 415 = 420]

Task 1a (40 marks)

Create an Excel model, with or without VBA support, that can solve the task for any given sequence of six numbers (inputted by the user) and any given target value (again, inputted by the user)
If a solution is mathematically impossible, then return the text “no solution possible”.

Use your model to find an arrangement to solve the each of the following sequences and target values:

i) ii)

iii) iv)

Task 1b (15 marks)

If the outcome for task 1a is “no solution possible” then present an arrangement of 6 numbers that produces the smallest difference between the calculated result and the target value.

For example, if the inputted sequence of 6 numbers is:

with a target value of 123

…then there is no mathematical solution to this problem.

In this case, the smallest difference would be 1, with the following arrangement :

difference is: target – calculated
123 – 122
= 1

Task 2 (20 marks)

For the given sequence of 6 numbers and target value inputted for task 1a, find an arrangement of the 6 numbers that generates the biggest difference2 between the calculated result and the target value.

For example, for the sequence and target value given above,

the biggest difference would be generated with the following arrangement 

difference is: target – calculated
123 – (-531)
= 624

How many unique arrangements are there which produce the biggest difference?

 
Bonus Task (10 marks)

For the following sequence, can you find a 3-digit target value where the smallest difference (task 1b) is the same value as the target value itself?

Model Design (25 marks)

High marks will go to a model which follows the design principles of the module, as laid out in the workshop documentation and described during the lectures.
The model should be intuitive and easy to use and not be more complicated than it needs to be, particularly in terms of speed, data management, sheet count and colour use. The model should also be able to handle input errors, and not crash or bug out if the user attempts to use the model inappropriately.

Deliverable

 Upload to moodle an Excel file that operates successfully using Excel 2019.
 The model will be assessed on a University-specification PC, running Excel 2019
 Any macros should be able to produce results in a reasonable amount of time (<1 minute)  Only correct answers will receive full marks û The model should not be password protected, or contain external links, or have any hidden sheets