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.
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.
I used a range of different formulae throughout the two spreadsheets. These primarily consisted of basic mathematical operations and interaction with functions, however.
A variety of functions were used on the two spreadsheets to achieve different goals, and some of they are listed below.
VLOOKUP()
— One of the most powerful functions available in Excel, VLOOKUP enables much of the spreadsheet to function. The lookup fucntionality on the Old Presentation worksheet (hidden) allows the user to pick a country from the drop-down list and then see the amount of money spent by those visiting from said country below the age of 45, and that for those 45 or older. VLOOKUP also allows the conditional to the right of this lookup; Higher <45 spending?. VLOOKUP is also used on the By residency worksheet in order to find the geographical region and language statistics for the list of countries. The function, in this case, looks up the country name found in column A in the Ref.Curr worksheet, using column B for region, C for currency and D for language.
IFERROR()
— This is used for catching errors on the Gender worksheet, as some of the original data is missing. IFERROR is used in columns K–N and P–S, and used to detect failed division as a result of blank cells. The blanks in the original data have been replaced with "n/a", which causes the error to be thrown. This is caught and replaced with another instance of "n/a", which is then picked up by conditional formatting and made a darker shade of red. This makes it more noticeable, and brings attention to missing data.
COUNTIF()
— This function is used to count cells if they meet a particular set of criteria. This is used in our case in the By Residency worksheet, in which the number of Ys and Ns is counted in the upper right. The function can be used simply to count the cell matching a particular sting, but also using more advanced conditionals involving greater-than and less-than operators.
AVERAGE()
— Calculating a mean average is easiest and most accurate with the AVERAGE function. This adds all of the specified cells' values together, and divides the result by the number of input cells. This was used on the By Residency worksheet, among others. This was used to find the average total annual spending in the UK of all the countries on the list.
SUM()
— Also acting as a simpler alternative to a more explicit but tedious method of combining several cell's values together, the SUM function returns the result of adding all of the values given together. This is useful in creating more minimalist and efficient formulae. In this case it was used to find a total amount of spending and total number of visits per age group to the UK per year.
LEN()
— This function returns the number of characters present in a given cell. Although not required, I used this on the form spreadsheet to count the number of characters entered by the user in the last form field. By not letting this exceed 280 characters, I was limiting the digital space that any given message could occupy. This also somewhat minimises the spreadsheet's vulnerability to malicious attack.
IF()
— One of the building blocks of simple programming and application building in Excel is the IF function. This is used across the primary spreadsheet, such as on the By Residency worksheet in column Q, where it is in fact used in conjunction with VLOOKUP. The IF function will return one of two values, dependent on the evaluation of the first argument. Once the first argument (a conditional) has been evaluated, the function will return the second argument (if true) or the third argument (if false). This is used to check whether or not each country uses the Euro and spends over £500 million, and return "Y" if so.
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.
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.
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.
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.