Spreadsheet Modelling – Assignment 3 – Excerpt

Technical Documentation [M4]

Hardware and Software Requirements

In order to open the file and modify its contents, there a number of requirements which must be met both in terms of hardware and software.

Firstly, a capable computer is essential to efficiently developing the model. I recommend a computer with at least a 1 GHz processor, and ideally one with multiple cores or a higher clock speed. I recommend at least 1 GB of RAM, and at least 5 GB of free space on your hard-drive, meaning that there will be plenty of space for a Microsoft Office installation (which is required) and for the spreadsheet model to expand as more information is added. A monitor of a reasonable size can help greatly in improving one's workflow, and I recommend a monitor of no less than 1366 × 768 pixels, and ideally one of 1920 × 1080 pixels.

In terms of software, Windows or Mac OS X is required in order to run Excel. Alternatively Wine can be used to emulate a Windows environment in which Excel can be executed if you're using Linux. In order to open the spreadsheet, I strongly recommend Excel 2010 or newer, in order that all functions and formulae work as intended. Asides from the OS and Excel, no other software is required to open the spreadsheet.

The spreadsheet is saved as 42.3.xlsm. The majority of spreadsheets made in excel are saved as *.xlsx files, which is the default extension for Excel files. In order to enable the use of macros or any other VBA scripting in a spreadsheet, it must be saved in a separate format which allows this. In order to minimise the chances of data loss or corruption of the spreadsheet, I recommend making backup of the spreadsheet before and after making significant changes. This can be done simply by opening Window Explorer, and then copying and pasting the spreadsheet file. It's recommended to rename the new copy including the current date and time, so that you know exactly when the file was backed up, even if the file is accidentally modified. Continue to modify the original as you work, and keep making backups. If you make a severe mistake, delete the main file, and then copy/paste the most recent backup, before renaming to the original file name. You could also utilise revision control software such as Git or Mercurial to ease this process.

Colours and Type

I used a custom-designed theme for the two spreadsheets, which primarily involved the use of Segoe UI for the font, and a selection of colours for various different pieces of text. The largest text shown should be of 14 pt, in bold and in black. Secondary level headings should be of 11pt in company blue (RGB 33 62 92), and all lower-level headings should be the same size but in company red (RGB 117 5 1). All headings should be set in bold.

Formulae

I used a range of different formulae throughout the two spreadsheets. These primarily consisted of basic mathematical operations and interaction with functions, however.

Functions

A variety of functions were used on the two spreadsheets to achieve different goals, and some of they are listed below.

PivotTables

PivotTables offer a flexible way to show information, that can be easily changed by dragging sets of data around a simply interface. The tables generated allow for categorisation of data, and automatically generate totals at the bottom of sections. A PivotTable was used on the By Residency worksheet, to the right of the data, in order to show the total visits from different regions of the world in an interactive manner. All countries are listed, but grouped by geographical region, which makes finding a particular entry a lot easier. Different geographical regions can be collapsed using a small icon at the left side of each cell.

External Data Connections

In order to keep an up-to-date list of currency rates, the spreadsheet uses an external data connection. Every time the spreadsheet is opened, or on an regular basis if configured to do so, Excel will fetch data from the MSN Money API about the exchange rates between the US Dollar a number of other currencies from around the world. This means that the information shown in GBP, having been converted from USD, is always as accurate as it could be.

Data Validation

In order to ensure that the information users enter into the form is organised, Data Validation is used. This is how the in-cell drop-down menu is created; by being linked to a list of allowed values on the Reference worksheet (hidden, 42.2-form.xlsm). Data Validation restricts what people can enter in the form's fields; by ranges, conditionals, matches and more. In the form spreadsheet, for example, the user callow enter a value below zero for the Cost of Visit (£) field.

Macros

A macro was created in Excel to copy the information from the form to a separate database worksheet, and then clear the form for the next user. The macro was created initially by recording the copying and pasting of information, but was then heavily modified in the Visual Basic for Applications (VBA) editor in Excel. The allowed for better performance and improved stability.

The macro used on the form spreadsheet to copy data in the form to a separate database spreadsheet is shown below, with comments.