Assessment Tasks

The assessment for this unit is based upon (a) a copy of your attempts at the tutorial exercises during the first half of the Semester and (b) a mini 'project' that you undertake during the second half, these contributing 10% and 90%, respectively, towards the ov 525b110f erall mark.

For (a), I require INDIVIDUALS to hand in their disks, labelled with their name, by the end of teaching week 7 - i.e. FRIDAY NOVEMBER 10, 2000.

The remainder of this document contains details of suggested project tasks for (b), guidelines for their completion and the assessment criteria which will be used.

Guidelines / Extra Information

You should work (where possible) in groups of TWO. If this is not possible for any reason, please let me know. If appropriate, you will be given specific individual modifications to the list of requirements and criteria.

Each group should choose a different task. You are welcome to suggest an alternative task to those offered, but you must discuss your idea with me first to assess its suitability. I would then require a project definition, along the lines of those supplied in this document.

The information given on the following pages is for initial information. It is unlikely that this will be sufficient in all respects and I expect to have to discuss the details with you individually.

Not all tasks are self-contained - for some it will be necessary to acquire data or extra information.

You should submit the software on a 3.5" floppy diskette. The submission should comprise an EXCEL XLS file together with a word-processed report, of between 1000 and 2000 words (4 to 8 pages), describing how you have carried out the task. It should include your methods, describe the use of your software, and include a listing of your code (although this listing does not count towards the 1000 - 2000 words mentioned above!). Include any screenshots you deem appropriate.

Please ensure that the disk is clearly labelled with your names.

The deadline for submission of the work is Friday January 12^th 2001. Please hand work in to the school office before 1pm on this date - remember, it is school policy not to accept late work!

Keep backup copies of ALL your work. Special allowance will not be made for work which misses the deadline through disk corruption, viruses or similar problems. In the event of problems, make sure you submit something by the deadline, and include an extenuating circumstances form if necessary.

If you have any questions or problems, please get in touch, either during one of the scheduled classes, by calling in, or by ringing 0114 225 3279 (int 3279). Copies of this document, and other details of this unit, are available from this web page:

https://maths.sci.shu.ac.uk/units/ioa/

Good luck!

Assessment Criteria for the Project (90% of the unit mark)

Your work will be assessed against the following criteria:

It is particularly important to take advantage of any opportunity that exists to use your own initiative in providing additional features or making imaginative use of existing ones!

TASKS

CALENDAR Create an EXCEL macro which will generate a month-to-a-page calendar for a month chosen by the user. The calendar should indicate days of general note, such as Bank Holidays, Mother's Day etc., and should also provide the facility to add personal lists of dates which should be indicated on the calendar in some suitable fashion. These lists could include birthdays and anniversaries that an individual user wants to appear, and could be stored either in ordinary text files, or in another spreadsheet.

DATA ANALYSIS Write an EXCEL macro to read in data (either from disk or from the worksheet), displaying them on the worksheet if necessary, carry out some simple statistical analyses and to generate a plot of the data. The various options should be offered to the user in a dialog box. The statistical analyses that can be carried out might include determining the mean, median, standard deviation and range, and calculating a best-fit straight line. You might use some real data collected by you in a laboratory class - you could include specialised analysis which you may have had to carry out in that class.

NUMERICAL ANALYSIS This might appeal to those of you who wish to carry out a task with a slightly more mathematical flavour. It is often necessary to carry out numerical analysis of data - to evaluate the integral or derivative of a function whose analytic expression may be unknown, but for which you have numerical data, for example. Typical methods here would include Simpson's rule for numerical integration, and difference formulae to evaluate numerical derivatives. Numerical methods are sometimes also necessary in order to solve an equation for which there is no 'direct' analytic solution, such as x = sin(x), say. Write an EXCEL macro which will carry out a range of such tasks - you should determine which of these numerical methods you wish to implement.

ADDRESS-LABELS Write a macro to create a sheet or sheets of address labels. The addresses could be stored in a disk file in one of two ways: an address per line, with each field being separated by a delimiting-character (such as a comma) or by using a fixed number of lines per address, with each field on a different line. You could enhance this macro by offering the additional option of printing repeated single address labels, or disk labels, where each line may have different attributes. Your macro could examine the data file chosen by the user to automatically detect which type of data it contains, allowing the user to review and possibly modify the choice it comes up with.

CASSETTE/VIDEO/ZIP DRIVE LABELS. Create a mini-application that can correctly format labels for the above purposes. You should find out the exact dimensions required, use this to create a suitable 'template' and then get your code to obtain the necessary details from the user in some way. This information should then be added to the label in an appropriate place to create the finished product.

ADDRESS LIST Write a macro to keep, maintain and update as required a database of addresses and telephone numbers, printing out lists as necessary. You might also add the facility to include a 'personal details' section, and a search facility. You could either store the data in one or more text files on disk or create a database file in one of the 'standard' formats.

LEAGUE TABLE This one might appeal to sports enthusiasts. Create an EXCEL worksheet to hold the results of the matches between a number of teams, home and away. You should have the names of the teams appearing at the left of each row and at the top of each column, with the rows representing the 'home' teams and the columns representing the 'away' teams. The actual results would be contained in the appropriate grid cells. You should disable the cells down the main diagonal in some way (since teams cannot play themselves!) Write code to read in the current set of results at any stage and to calculate and display the current league table, with details of the number of games played, the number of wins, draws and defeats, the number of points and the 'goals' for and against. The number of points allocated for a win and a draw can be a user-input. I have described this for soccer, but you are welcome to adapt the idea for any other sport.

YEAR PLANNER Write an EXCEL macro which will request the user to input a year and produce a year calendar on a single sheet of A4. A common use of a calendar such as this is as a year planner. To carry out this function, your macro needs to be supplied with extra data, depending upon the specific purpose the year planner will be put to. You should use some of your own ideas here, but possibilities might include (a) a staff holiday planner (individual staff names and holiday dates could be provided as input), (b) University term calendar, with an indication of the semesters, and which weeks were exam weeks, which revision weeks etc, (c) bookings (e.g. of a holiday cottage) or (d) project scheduling. If the information content is too great to fit onto A4, you can relax that constraint! Also, you could, for the purposes of this exercise, use colour (and assume a colour printer is available if a print-out of your planner should become necessary).

SUNRISE/SUNSET CALCULATOR It is possible to calculate the times at which the Sun will rise and set at any given latitude on the Earth's surface, and at any given time of year using a simple bit of spherical trigonometry (making some approximations about the Earth's orientation and orbit!). I won't go into details here, but I can explain it for anybody who is thinking of tackling this particular task. Your job, should you accept it, is to write an Excel macro which receives as input from the user the date and the latitude and calculates and presents the times of sunrise and sunset (in GMT and BST) to the user. You might choose to provide a shortcut button which will do this automatically for the current date.

LOAN/APR CALCULATOR Loan deals offered by companies need to be carefully assessed to see whether or not what they are offering competitive rates. Very often the figures quoted are not immediately comparable, and in any case it is useful to have a simple utility for calculating monthly repayments of a fixed loan, at a given APR, over a particular period of time. The idea of this task is to write a macro in Excel to request details of the APR, loan amount and loan period, and returns the monthly repayment required. A modification of this could calculate the APR for a given loan amount and period and repayment amount. You could enhance the macro by offering the option of including explicitly any deposits and/or discounts involved.

VIDEOTAPE CATALOGUE The object of this task is to design a set of macros to create a videotape catalogue. Such a system will store details of videotape recordings based upon a tape number, the numerical position of that recording on the tape (or equivalently its time from the start), the category of the recording from a pre-determined list, the date of the recording and an optional text field which could be used to describe its contents. The user should be able to enter details of new recordings, and be able to obtain listings of all the recordings currently in the catalogue in each category. You should provide the facility to modify the list of categories and preferably provide a search facility, so that users could look for any recordings containing a particular word or phrase in their title.

OTHER 'CATALOGUE' PROJECTS to that above could be generated in a number of areas. If this idea interests you, you might like to consider one of the following:

an audiotape catalogue

a recipe catalogue

a photo catalogue or

a software catalogue.

A set of requirements similar to those for the last project would apply. You can, of course, suggest other similar applications.

CAR EXPENSES For people who use their cars for business purposes, an accurate record of related expenses is necessary for their annual tax returns. Necessary information would include the initial cost, date of purchase and mileage (for depreciation), and expenditure on petrol, servicing, tyres, parts, repairs, car washes etc. You should create an Excel spreadsheet, probably with a worksheet for petrol expenditure, another for servicing, and another for general expenses. Write macros as necessary so that the user can specify a particular tax year and Excel will calculate the figures as described above for that period. You could also provide graphical summaries of the data if you have time.

DISK CATALOGUING UTILITY The idea of this task is to write a macro (or set of macros) which allow the user to catalogue a disk. The main idea is to keep a record of the files contained on a floppy disk, but it could also work for any drive that the computer recognises. The user could be asked to select which types of file to catalogue, and the details of these files together with the label of the disk on which they reside, could be stored in some convenient format on your hard disk. A search facility should also be provided to enable the user to subsequently find the location of a desired file.

EXCEL WORKSHEET - HTML CONVERTER This task is really only suitable for those of you who already know HTML (HyperText Markup Language - used for coding web pages), or are prepared to spend additional time learning the elements of HTML. The task is to write an EXCEL macro which will take data stored in an EXCEL worksheet and convert it to HTML, using suitable 'tags', so that it is viewable by a web browser. You could enhance the macro by including other information on the worksheet to include as part of the web page - e.g. title, date, author, a description of the data, URLs of relevant web pages etc. If this interests you, discuss possible specific applications with me.

EXCEL - HTML COURSE TIMETABLE CONVERTER The idea of this exercise is to create an EXCEL program which converts timetable information stored in outline form in a worksheet and converts it to HTML format for viewing over the web. The information which can be stored in the worksheet includes the course title, year and semester; for each day of the week information about classes can be included, such as the unit title, times, class type, location and staff involved. The output can be in any suitable HTML format, but the use of a table is recommended.

SOFTWARE REGISTRATION INDEX If you purchase software through the shareware mechanism, eventually you will have a number of registration codes to remember for each version of each application. The purpose of this exercise is to develop a spreadsheet system for the storage of this information. The system you develop should be able to store (at least) the name of the software, the date purchased, the cost, the version number and the registration details. You should provide a way of adding to the list, and of sorting the list according to each of the fields, as appropriate.

FILOFAX. This task is to develop a spreadsheet system for storing and presenting a variety of personal information, rather like a traditional filofax. It should not attempt to emulate a fully-featured personal organiser, but you could create a system for handling much of your most important personal information. One important feature you should provide, however, is some degree of security for this information!