Spreadsheet Modelling – Assignment 3

Testing the Data Collection Spreadsheet [P7]

Plan

In order to ensure that an Excel form works as intended, testing is typically required. So that the testing would be as effective as possible, and would reliably target all aspects of the form, I decided to create a test plan, shown below.

1. To begin, open the form workbook as it would be opened on a computer in the tourist information offices. In order that any users cannot tamper with any of the settings, such as the spreadsheet's password protection, most of the ribbon tabs should be hidden and remainder collapsed:
   1. Open the Excel settings window by clicking File > Options.
   2. In the window, click the Customise Ribbon button on the left, and then untick the all but the Home tab in the box on the right.
   3. Click OK before double clicking the Home tab label in the ribbon, which will make it collapse by default.

2. Confirm that Excel has loaded the correct worksheet, and that the tabs for the other two worksheets do not show at the bottom of the window.

3. Ensure than the cell selection is in the first input cell, E8. The date field above should already be filled in with the correct date using the TODAY() function.

4. Fill in the form cell by cell. Using the Tab key in the upper left of the keyboard or the Return key, the form can be moved through easily. Because the cells in between the input fields are locked even to selection, Excel should only place the cursor in the fields requiring input; those in column E on rows 8, 10, 12, 14, 16, 18, 20 and 22.

5. When typing a response into one of the form cells, Excel should ideally auto-complete using the lists of data provided by the reference worksheet. The is the case for the Country of Residence input cell but not the Comments cell, for instance. The auto-completion means that users can enter data that can be efficiently processed by the spreadsheet and used in statistics, while still entering the data in a perhaps more familiar way than the drop-down menu.

6. If incorrect data is entered into the form, such as a country name without a capital letter at the beginning, the spreadsheet should throw an error and display a window to the user informing them to use the drop-down menus available in most of the cells. For some cells, the option to not change the data is presented, accounting for particular cases that may not otherwise be covered by the options given. The Gender input cell is among these.

7. When the form is complete and the submit button pressed, there should be a short delay before a window appears confirming that the form's data has been successfully submitted to the database worksheet. If this does not appear, an error has occurred in the execution of the submission macro. This is an issue that must be attended to by a developer or possibly a member of staff, likely with the aid of the technical documentation.

In Practice

I tested the spreadsheet and followed the process outlined above. The only area in which the spreadsheet failed to pass was the auto-completion. I was under the impression that a cell with list-based Data Validation enabled would automatically have auto-completion enabled, but this seems not to be the case. There may be an option in the Data Validation window to enable this feature, otherwise it may just not be available. Was I to create the form and data submission system again, I would likely research into this in greater detail.

Spreadsheet Data Formats [P8]

So that the data processed in the original spreadsheet would be available and accessible to as many people and computers as possible, I exported both of the spreadsheets two common formats:

• The first, CSV, is a text-based format, with values separated by commas. Unlike Excel's *.xlsx format, CSV files are not specific to any particular software package, meaning both proprietary and free spreadsheet editing software can read the file. That said, the format is very basic, and does not offer as much functionality.

• The second format was HTML and CSS, also both test-based. These use a variety of different syntax elements to express a website in terms of code. This can be read by a web browser such as Google Chrome or Firefox, meaning it has very good compatibility across different operating system platforms. It can also be easily viewed on mobile devices.

The contents of the HTML export are also shown below.

Exporting to CSV

In order to export the data collected from visitors to the tourist information office each month, follow the steps below:

1. Firstly, the spreadsheet containing the data must be un-hidden. In order to allow worksheets to be hidden and un-hidden, navigate to the *Review* tab in the ribbon and click on *Protect Workbook*, which should already be highlighted. The password to allow this is simply `password`.

2. We must now un-hide the database sheet, by right-clicking on the *Form* worksheet tab in the bottom left, clicking *Unhide*, selecting the *Form Database* worksheet, and then clicking OK. The results should now be shown on screen.

3. To export the data, click *File* on the ribbon, and then *Save As*. In the dialogue box that appears, select *CSV (Comma delimited) (\*.csv)* in the *Save as type:* drop-down menu. Give the file a name and choose where to save it on your computer. Once this is complete, click the *Save* button in the bottom right.

4. Remove the protection on the database worksheet, by navigating to the Review tab once more and clicking *Unprotect Sheet* button. Again, the password is `password`. This allows us to remove the data in the spreadsheet.

5. Select the data columns by clicking and dragging the mouse right-wards from the column A label to the column I label. This selects all of the data provided by visitors, regardless of how far down the spreadsheet it it.

6. Right click anywhere in the selected area, and then click *Clear Contents* to remove all user-submitted information. Place the selection in cell A1 before proceeding.

7. Re-protect the spreadsheet by clicking the same button used to un-protect it previously, now labelled *Protect Sheet*, and un-tick the two selected tick boxes. Enter `password` in the password entry box, and click OK. At this point you will be prompted to repeat the password you entered.

8. Rename the worksheet to "Form Database" by double clicking the name in the lower left.

9. Right-click on the database spreadsheet label in the bottom left, and click *Hide*, and then click *Protect Workbook* in the *Review* tab. Enter `password` once more and confirm it when prompted.

Save the spreadsheet and exit. Send the CSV file generated by Excel to the database manager.

Alternatively, you may instead only proceed to step three, then close Excel without saving changes. Delete the Excel spreadsheet, and then re-download it from the file-server it was originally obtained from. This will give you a fresh copy, with workbook structure and password protection already configured as it should be.

User Documentation [P9]

Introduction

The following document details the process of submitting information about yourself and your visit to the United Kingdom using the form on the appropriate computers in our tourist information offices. This form is created using Microsoft Excel, and uses a macro to submit the data to a separate table. We can then collect the information people have submitted from around the country, and generate a wide variety of statistical information about tourism in the UK. The information you submit is anonymous, and we respect your right to leave any of the form fields blank if you feel the information is too personal.

Frequently Asked Questions

• How is the information stored? The information you submit is stored in a secure Excel spreadsheet, and is collected daily by staff after the office closes. This means that the information is kept on the public computer for no more than a day, and is password-protected.

• Is the form submission anonymous? We only collect the information that you enter in the form, including the date that you submitted the form. This means that we don't know know your name, address or specific personal details. If you feel that any of the fields are too personal, you may decide to not enter any information, although we do encourage you to do so.

• Why is the information being collected? We collect the information in order to gain knowledge of the tourists who use our offices. This means that we can create more helpful resources such as guides and maps focused towards those most likely to visit us. By filling out the form, you are helping us to decide what material to create, and if you choose to visit in the future, you're helping yourself.

Guide for Form Completion

The form has been designed to be as simple to use as possible. A screenshot is shown below of the form as it should appear before being filled out.

The form without any data entered.

Image by myself; see the license.

Data Submission

The first field of the form, the Date field, should already be filled in with the current date. If this is not the case, please tell a member of staff. This cell does not require any input from you.

The second field requires you to enter a home country. The easiest way to do this without causing trouble is by clicking the downward arrow to the right of the cell when it is selected. This will show a list of nations available for selection. If your own is not shown, please select Rest of World, and tell a member of staff.

Continue the process of selecting the most appropriate option from the lists provided in each field. The last two fields, Cost of Visit (£) and Comments require you to instead type an answer. The images below may help to illustrate the process.

The process of selecting an age group from the list.

Image by myself; see the license.

Once you have completed the form with your information and are content with the data, press the submit button. The page may flicker for a short period of time, but a window will appear afterwards confirming that the data has been submitted if everything has worked. If this window does not appear, please tell a member of staff in the tourist office.

The message shown once the form has been successfully submitted, and the information recorded.

Image by myself; see the license.

If the window has appeared, as shown above, you may click OK. The form has been submitted, and you may leave the computer for the next person.

Thank-you!

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.

• 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

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.

Evaluation and User Feedback [D2]

In order to gauge how successful the model and feedback form spreadsheets were, suggestions and analysis was gathered from a classmate.

Spreadsheet Model

• Styling and Themes — The spreadsheet uses consistent styling, using company colours defined throughout the spreadsheet modelling project. The typography is also consistent, but unfortunately not all monetary data is in the correct currency format.

• Layout and UX — Navigation is present in the Assignment 1 spreadsheet, and conditional formatting has been used to make certain pieces of information more noticeable. Despite this, the colouring used for Conditional Formatting means that the effect is not as effective as it could be. The information is well laid-out, however, and can be easily understood.

• User Requirements — The user requirements outlined in the brief have mostly been met.

• Menu Page — The most up-to-date spreadsheet does not have a visible menu worksheet.

• External Data Connections — These have been used to gather up-to-date information about currency exchange rates.

• Formulae — The formulae on the spreadsheet perform necessary mathematical functions.

• Functions — The following functions have all been used on the spreadsheet: AVERAGE(), IF(), IFERROR(), IF(), AND(), VLOOKUP(), SUM(), COUNTIF().

• Conditional Formatting — This has been used, but not clearly in all cases.

• PivotTables — A PivotTable has been used, and is helpful.

• Charts — These have been used effectively, and use clear colours. They follow the colour scheme used by the rest of the spreadsheet.

• Error Handling — IFERROR has been used to detect Excel errors, and the function has been used to clearly indicate these in the worksheet.

• Security — All of the worksheets have been password protected, and the workbook protected in addition to this (preventing the (un-)hiding of worksheets). The workbook has then been shared, meaning that it cannot be un-protected until un-shared.

Feedback Form

• Styling and Themes — The colouring, text weight and text size are all consistent, and follow the company theme.

• Layout and UX — The fields are clearly laid out, and can be navigated through using the either the Tab or Enter keys. Using the Shift key as well allows the user to navigate backwards through the form.

• User Requirements — The form sheet meets the brief's specification; it's easy to use and shows error message. That said, there are no comments shown when selecting cells, which would ease the process of data entry for inexperienced users.

• Formulae — As before, simple mathematical operators have been used.

• Functions — Few, but some, functions have been used; CONCATENATE(),LENGTH(). They're used for the "x characters remaining" system for the last input field.

• Automation — A macro has been created using the Excel macro recorder, and then changed in the VBA editor. This macro moves the data in the user form to the database spreadsheet.

• Lists, Input and Error Messages — Although no alerts are shown when selecting input cells, informative errors are thrown when entering invalid or disallowed data and list drop-down menus are provided in the appropriate cells.

• Data Validation — The data entered into the form is checked either by list, range or length.

• Security — The worksheet has been password-protected, and the workbook protected additionally. This prevents users from tampering with the spreadsheet formulae and layout.

• Extra Comments — The form is not as user-friendly as it should be, considering that users of a wide range of technical ability will be using the form.

Response

I am happy with the theme that I developed throughout spreadsheet creation, and I feel it is effective. The typeface, Segoe UI, is clear and easily read at all sizes of text. It also has a dedicated bold version, which enables certain pieces text to stand out. The colouring used also means that the user can easily differentiate between different level headings. The colours I picked complement each other, and allow for a range of complementary colours to be used in graphs and charts.

I believe that the form in particular could use more clear labelling and information presented to the user when selecting each field of the form. This would mean that the less technology-familiar users visiting the tourist information office would still be able to comfortably complete the form. In it's current state, the form may discourage some from entering information, which would mean the form results would be skewed and would inaccurately represent the opinions of visitors.

I think that with minor adjustments, the spreadsheet could be used in a production environment, although I wouldn't recommend the use of Excel as a form-based data collection platform. Despite this, I do believe that the spreadsheets met the requirements outlined in the brief, and the model spreadsheet in particular.

The menu page, although visible on the Assignment 1 model spreadsheet, was not shown on the newer version of the spreadsheet due to my misunderstanding. If I were to create another model or develop the current version more, I would create a dedicated menu page, and include the functionality of the original one (such as VLOOKUP utility). By adding a clear menu page, the spreadsheet can be made a lot easier to navigate, and hyperlinks to other worksheets or cell references are all that are required to achieve this.

I believe the use of External Data Connections was successful, although I do feel data offered by MSN could be more concise. For instance, I would assume one could use a more capable finance API that would return only the values requested, which would be specified in the request address. This would reduce the time required to update the data, would make the process of using the data easier, and would greatly reduce the need for separate spreadsheet dedicated to the external connection. Despite it's possible inefficiency, however, the External Data Connection allowed for the accurate conversion between Euros and Pound Sterling.

I think that the functions and formulae I used were sufficient, and met their requirements. I feel like the use of VLOOKUP was also successful in extending upon the data already provided, which wasn't a documented requirement.

Conditional Formatting could have been used to greater effect had I picked more contrasting and saturated colours, which I decided against initially as they appeared to be excessively attention-grabbing. Other than the choice of colours, I believe the range of different conditional formatting rules available in Excel was used effectively.

Personally I feel that PivotTables have limited use, but that the use of them in the spreadsheet model showed one of their most useful features, asides from the flexibility in creation; the expand and collapse functionality on groups of data. This allows a great amount of data to be available on a spreadsheet, and without consuming a large number of cells initially. Without collapsing or hiding rows or columns, which can be cumbersome to reveal again, the PivotTable allows information to be consolidated and summarised by category.

Overall, I believe the spreadsheets were successful, but both could benefit from further development.