Programming Solutions 1 (IB137) Resit Assessment 2015/2016 Deadlines: Part 1: Friday 5 August 2016 Part 2: Friday 2 Sept 2016 Financial Advisor Recruitment Introduction Most organisations state that their people are their most critical resource; however relatively few organisations actively plan the recruitment, retention, progression and departure of their staff, to ensure they have the best ongoing resource profile to meet the anticipated workload of the organisation. This issue can be important for financial advice companies, and one such firm has invited you to develop and present a prototype application that will help them to improve their thinking and planning. This particular firm works sells and services contracts to provide advice for the staff of other companies. This advice can be seen as an employment benefit offered by the customer companies to their staff. It normally includes pension planning advice in particular, but can also be broader in scope. The model must also be extremely easy to use, and 'foolproof', since the MD has expressed an interest in taking it home to 'play with'! A full specification follows later. Part 1 (25%) Consider the task, and the capabilities of Excel. Design a suitable spreadsheet-based application that addresses the requirements. The model must conform to the detailed requirements specified later in this document, must support the overall task and must provide the deliverables listed below. It must include both a significant number of nontrivial formulae, and also VBA macros to achieve the required functionality, control and usability. Deliverables Submit a report, clearly marked with your ID that includes: x a logic model that represents the entities and their logical inter-dependencies required to achieve the basic functionality, and a short review of how it informs your design (200 words approx.). x a diagram that shows of the layout of the spreadsheet model, with an explanation of the key design aspects, decisions and assumptions about the model, including choice and purpose of the sheets, and their broad layout. It should also visualise the relationships between the sheets, and should outline the user interaction and any special features or functionality that you wish to emphasise. You will get relatively few marks for merely describing your work – you must say why your design is appropriate, as well as assessing its strengths and limitations (refer to the literature and module notes). You should not need to specify detailed cell references, but might label different sections to help explain the working. You should explain what will function with formulae and what will use macros (800 words approx.). x a specification (in pseudo-code / English, not VBA) of a macro that will implement a key element of automation provided by the spreadsheet (150 words approx. in addition to the code itself). Do not just explain the pseudo code operation; say how it adds value to the application. x a sketch1 of an informative chart that could be generated from your data, with a brief justification of its purpose and its particular chart type (100 words approx.). x two original (i.e. not given below) ideas about additional functionality you might realistically include in your implementation (200 words approx.). Notes. 1. The main value of the separation of Part 1 and Part II is to emphasise the importance of the design as a pre-requisite to implementation, as well as to encourage progress and creative, collaborative initial design. 2. You may include support for one additional feature within your design, if you wish, as long as it does not compromise communication of the basic concept. 3. I will provide feedback and a mark for part 1 within 3 working days of the deadline if it is submitted by the deadline. 4. For part II, you may change and develop ideas about their individual model, and may use as well as critique the earlier ideas. 5. Part 1 does not require computer implementation, but appropriate computer tools may be used if you wish. The report should be word-processed, however. 6. Word limits are approximate guides only; however answers that are too verbose (e.g more than 20% over the guide) may incur penalties. Conciseness is generally a virtue in academic reports. Part 2 (75%) Implement the application in Excel. You may change the part 1 design. The implementation must run on an English language system – so avoid non-English characters embedded in field names or code etc, use English language variable names. It must include both a significant number of non-trivial formulae, and VBA macros to achieve the required functionality, control and usability. Deliverables x your spreadsheet that fulfils the functional requirements, in Microsoft Excel format, and no other unnecessary files. It should include the required charts. The name of the spreadsheet should be simply YourIDNumber.xlsm (or .xls). It must not be password-protected and must be virus-free. Check that you can load and run the application on a WBS network computer. x a report (YourIDNumber.pdf) including: 1) user operating instructions: concise but clear and appropriately structured. Before handing in, follow your own instructions to test them as well as your model! Good instructions should direct the user rather than just describe the system (250 words approx.). 2) descriptions, and optionally, printouts of the main areas of interest and functionality in your model with an explanation of the key design and assumptions of your application, including spreadsheet layout, user interaction and any special features. You will get relatively few marks for merely describing your work – say why your design is appropriate, or critique it. You will 1 I mean here to offer you the freedom to create the sketch any way you like. You can use Excel if you wish! Put some realistic data in, but it does not have to be the output of a working Excel model. also not gain credit for over-documenting minor parts of the model. This section may also indicate key differences from the plan previously submitted in Part 1 (1000 words approx.). 3) images of two informative charts, one of which may be the Part 1 chart suggestion, which can be critiqued if appropriate. Describe how the new chart illustrates some of the key information generated by the model (150 words approx.). 4) results of any specified calculations or optimisations (200 words approx.). 5) 2-3 selected illustrative VBA routines (not all of them – focus on those that best illustrate your work; show and discuss briefly what you've achieved with VBA and how it helps the application). (250 words approx.) 6) brief reflections on the particular strengths and weaknesses of the implementation you have created, including three further ideas about how you might significantly improve or further develop your system if you had the same time again, in terms of both the functionality, design and realism of the model. You gain little credit for merely listing the standard basic or additional functionality elements that you failed to implement, nor for repeating the group's suggestions for additional functionality in these three suggestions (400 words approx.). Evaluation The assessment will gain credit for design, functionality, usability, presentation, robustness and clarity, conformance to the specification, generalisability, maintainability, and the use of appropriate functions, programming constructs and the quality of the report. Better models will have innovative but relevant features and original additional functionality. Do not neglect the report, which contributes to the marks and should do full justice to your model and thinking. The marking scheme will be approximately as below. Marking scheme (approx.) Part 1 Logic model 4% Sketch Layout and design justification 12% Macro specification 3% Chart and interpretation 3% Additional functionality 3% Part II User instructions 3% Justification of design and features 4% Layout, structure, formulae and formatting 12% Charts and interpretation 3% Basic functionality 20% User-friendliness and control including validation and robustness 12% Additional functionality 7% General control of macros 10% Ideas for improvements and overall perspective 4% A viva may be also given to test your personal understanding of your submitted work. Detailed specification Contracts are sold as packages of work (sometimes a customer may buy more than one package, depending on its size). The firm's staff who generate and service these contracts can be represented by three key ranks; junior advisor, senior advisor and partner. Staff costs are £30k, £60k and £100k for juniors, seniors and partners respectively. All staff undertake productive work, but have different work profiles. A contract package lasts for just one year (so contracts have to be won again for the next year), and requires the time of 3 juniors, 0.6 seniors and 0.5 partners. Although contracts may be won, they can only be delivered and the profits earned if there are sufficient staff in place at each required level to resource the contracts over the year they are due to run; otherwise the work is lost to competitors. Partners and senior advisors spend the rest of their time after servicing contracts working on bids for new contracts; each partner-year is expected on average to win 0.5 new contracts, to be delivered in the following year2; each senior advisor-year is also expected on average to win 0.5 contracts3. Only whole numbers of fully-resourced contracts may be signed up. There is a limit to scalability however, and senior advisors' contract-winning capability is reduced by 1/150 for each senior advisor in employment, although partner capability is not affected by scale. Past records from personnel suggest that about 20% of staff of each rank leave at the end of each year for one reason or another. Likewise, 15% of juniors are promoted to seniors, and seniors to partner, each year.4 Additionally, new advisors may be recruited towards the end of each year, becoming available for work in the next year; however partners are not recruited, but are only promoted from within the firm. Assume that the firm wants a stable recruitment policy of the same numbers every year5. Your investigations have established that currently there are 8 juniors, 5 seniors, 3 partners, with 3 contracts already won for the current year, and that the average revenue per contract is £500k. You are required to use this information to formulate a model that will allow the HR planner to experiment with recruitment figures and contract-winning capability, and to examine the firm's overall performance for 10 years ahead. 2 So contracts are won in one year, but worked on in the following year (if there is capacity in the following year). Seniors and partners have to work current contracts, and only use spare capacity to win new contracts. These figures are referred to as their 'contract-winning capability'. 3 Senior advisors pool their efforts, and partners do the same, but each group works contracts independently of the other. 4 Both promotions and leavers are based on figures at the start of the year, independently of each other. 5 This also considerably simplifies the problem. The number can be different between juniors and seniors, but for each it must be the same each year. Basic functionality The application must: x Support calculation of ongoing resource numbers, the number of contracts won, contracts actually worked, resources needed, and the expected profit each year and overall. x Support user interactions that allow the planned number of recruits and contract-winning capability to be changed. x Demonstrate your model's capability to answer the following questions, supposing that promotion and leaving rates and partner contract-winning capability are fixed: a) Show that the profit for the first year is £160k, with 2 contracts won for the next year, both of which can be delivered. b) Suppose that 3 juniors and 2 senior advisors are recruited each year; show that there would be an expected profit over the 10 year period of £1250k. Use the model to explain why/how this profit is lower than optimal; how (in general terms) could it be improved? c) Find the maximum total profitability over the ten years that could be achieved with these assumptions, by changing recruitment plans for juniors and for seniors (still keeping the numbers the same for each year). What is the maximum profitability, and what recruitment levels will deliver it? Optional additional functionality: 1. Experiment with one parameter of the model to find how it drives profitability. Report on the sensitivity and the behaviour of the recruitment plans to this parameter, keeping other parameters at appropriate constant levels (and still keeping recruitment the same for each year). 2. Make other insightful observations about the behaviour of the model. General support and tips: (Individual techniques required will depend on each particular model; here are some suggestions) Part 1 1. Part 1 (design) is to be done in groups, and should not include implementation. 2. Be steady, systematic and consistent about the setup of your model; then the multiple different parameters will not confuse you or the user. Think through the logical dependencies; although there are several factors, it is essentially a relatively straightforward model. 3. It is likely to be helpful to plan your spreadsheet layout and functions on paper, in your own language, to express clearly what you need to happen, then to work out how to implement it in Excel. Of course you need to know Excel functionality in order to plan appropriately, but you only require a subset of what we have seen in the module. Split it into small stages and use the spreadsheet's power to link them together. 4. I am more interested in seeing functional, usable and maintainable models than ones that have oversophisticated presentational or mathematical features; however, neat and tidy presentation does help usability and is normally associated with good design. 5. You are not expected to formulate or implement a mathematical optimisation algorithm, but are encouraged to design macros that allow you to automate some of the heuristics you discover as you use the model manually. You are not expected attempt to design for exhaustive optimisation except as indicated in 'Additional functionality'. Although it is interesting to reflect on this possibility, you should know enough about combinatorial explosion to know that it would take rather a long time! 6. There is no single best model. 7. You need not repeat the assumptions given in the specification. 8. It is helpful in general not to rush to macros too early, in order to get an appropriate balance between statics and dynamics, but you should use the power of both macros and formulae, demonstrating competence in each. The layout of your spreadsheet will be influenced by the balance of macros and formulae, and vice versa. 9. Since we have not yet dealt fully with macros, you may have to anticipate elements of their role, and you will have freedom to enhance your designs for part 2. 10. The logic model need only represent the logic for the basic functionality. 11. The Part 1 report is to be directed to the assessor. Part 2 12. Part 2 (implementation) is to be done entirely as individuals, independently, and is subject to collusion and plagiarism regulations. 13. First set up a 'static model' that allows you to make some decisions manually, experimenting with different possibilities to get a feel for the impact of various parameters and decision variables. This will also give you a feel about what information (intermediate as well as output) you need to be displayed, and what controls you need. 14. Implement the basic features first, although there are significant marks for the additional functionality. However, try to think through the whole model before implementing. If you are having difficulty creating the functionality, make assumptions or simplifications, state them clearly, and use as few as possible. You need not repeat the assumptions given in the specification. 15. Ensure that the program is robust, so that is does not crash if inappropriate or unexpected responses are made by the user, and so that data entry is valid. 16. Ensure that your model implements the rules realistically. 17. It will be nice if you can easily vary some of the key elements of the model, since you know that in the medium term 'change is the only aspect of business that is constant'. 18. Macros should not take long to run! 19. Considering the warnings made in the lectures, and because solutions can be found without using Solver, the use of Solver it is discouraged for this piece of work. 20. A good application will include significant user interaction, and validation of that interaction. It will include a range of both formulae and macros (but see comment under Part 1). 21. Remember that the user is to be your client, who is familiar with Excel as a user but not as a developer, and who thinks macros are magic (as we do -). 22. Avoid variable names in VBA that have a reserved meaning – e.g. 'Day', 'Row', 'Column', 'History'. 23. Avoid parameters that are embedded into formulae or code. 24. You are encouraged to record parts of macros, then paste the VBA instructions into procedures, or to adapt examples (with appropriate referencing for significant sources). 25. There is no substitute for testing your own system to identify and solve bugs, clarify requirements and ensure usability. 26. Try to shape your code so that you do not need to repeat almost identical code instructions (or at least tidy up, where you find that you have repeated code). 27. File size should be <5Mb, probably much less; you will not get marks for size per se! 28. Sorting data (if you need to) is easiest if they are in spreadsheet rows; you could sort an array of data in VBA, but would need to code a sort algorithm. 29. Use named ranges, but not too many – they can get in the way of copying and pasting; use key reference points only. Excel "Error 400" usually means you are trying to use a range name that does not exist. Error 400 or Error 1004 may mean that your macro is attached to a sheet but is trying to do something in a different sheet. 30. Ensure you do not hide sheet tabs, row/col headings or scroll bars, unless you allow me a one-click override to restore these! 31. The Part 2 report is to be directed to the assessor, but the user instructions element should be directed to the user. Health Warnings 1. Maintain regular backups; computers are programmed to crash during critical times of assessment work 2. Strategies encouraged by this simulation are not guaranteed to return profits in the real world! Students may seek further clarification if any ambiguities become apparent. Plagiarism guidance Please ensure that any work submitted by you for assessment has been correctly referenced, as WBS expects all students to demonstrate the highest standards of academic integrity at all times and treats all cases of poor academic practice and suspected plagiarism very seriously. You can find information on these matters on my.wbs, in your student handbook and on the University's library web pages here. The University's Regulation 11 clarifies that "…'cheating' means an attempt to benefit oneself or another by deceit or fraud. This includes reproducing one's own work…" It is important to note that it is not permissible to reuse work which has already been submitted by you for credit either at WBS or at another institution (unless you have been explicitly told that you can do so). This is considered self-plagiarism and could result in significant mark reductions. Upon submission of assignments, students will be asked to agree to one of the following declarations: Individual work submissions: "I declare that this work is entirely my own in accordance with the University's Regulation 11 and the WBS guidelines on plagiarism and collusion. All external references and sources are clearly acknowledged and identified within the contents. No substantial part(s) of the work submitted here has also been submitted by me in other assessments for accredited courses of study, and I acknowledge that if this has been done it may result in me being reported for self-plagiarism and an appropriate reduction in marks may be made when marking this piece of work." Group Work submissions: "I declare that this work is being submitted on behalf of my group, in accordance with the University's Regulation 11 and the WBS guidelines on plagiarism and collusion. All external references and sources are clearly acknowledged and identified within the contents. No substantial part(s) of the work submitted here has also been submitted in other assessments for accredited courses of study and if this has been done it may result in us being reported for self-plagiarism and an appropriate reduction in marks may be made when marking this piece of work." By agreeing to these declarations you are acknowledging that you have understood the rules about plagiarism and self-plagiarism and have taken all possible steps to ensure that your work complies with the requirements of WBS and the University. You should only indicate your agreement with the relevant statement once you have satisfied yourself that you have fully understood its implications. If you are in any doubt, you must consult with the NIE of the relevant module, because once you have indicated your agreement it will not be possible to later claim that you were unaware of these requirements in the event that your work is subsequently found to be problematic in respect to suspected plagiarism or self-plagiarism. Andrew Martin, July 2016 [email protected]