This is the basic layout of the spreadsheet. It will consist of ten main columns: Holiday ID, Price, Destination, Hotel, For how long, Travel operator, Insurance, 5% profit, VAT and Total price. There will be a column along the bottom of the spreadsheet for a VLookup formula. All of the prices on the spreadsheet are based upon and calculated by the current VAT, and this can be varied in accordance with the official VAT percentage.
This is the finished spreadsheet. It has all of the features specified above.
My spreadsheet uses a variety of formulae to calculate the VAT, profit, insurance and final price. Here is a screenshot showing the formulae used. For calculating profit, the formula used multiplies the price cell it corresponds to by 0.05, which gives 5% of the price. The insurance is manually selected, so no formula is needed for it. For the VAT, the formula multiplies the corresponding price cell by whatever number is in cell E1. In this case the VAT is set the same as real-world VAT, at 17.5&, which is displayed as 0.175. The total price is worked out by a formula that adds the original price, the insurance, the 5% profit and the VAT together and displays the final price.
VLookup is a formula that is used to display a certain part of a database in a different location. VLookup works by looking at letters and digits, cell sw02 in my database, typed into a specified cell, and then searching for those letters or digits in a specified column or row, which in my spreadsheet is column A. The data from that column, and all of the information for that holiday, is then displayed along the bottom row of the spreadsheet by more VLookup formulae that look at all of the other columns.
In my spreadsheet I have used absolute cell referencing for applying different VAT percentages. The formulae under the ‘VAT’ column all look at cell G1, which contains the number ‘0.175’. This means that all of the formulae under the VAT column multiply the original price of their assigned holiday by whatever number is in cell G1.
The VAT percentage in the spreadsheet is variable to fit in with the current real-world VAT percentage. Here is an example of a change in VAT down to 15%. This is displayed on the spreadsheet as 0.15. I predict that this increase in VAT percentage will not make very much difference to holiday prices.
My prediction was correct, as there was no large price increase. In the screenshot below I have changed the VAT percentage to 50% to show what a drastic impact a VAT percentage rise like this would have on holiday prices. This would display as 0.5 on the spreadsheet. I predict that a VAT percentage change this big will have a large impact on the final prices of the holidays.
My prediction was slightly wrong, as the VAT percentage change only had a large affect on the more expensive holidays.
One of the original spreadsheet requirements was to incorporate 5% profit into the final price to make the company money. The profit percentage can be changed, but this has to be done manually as this feature does not use absolute cell referencing. In the screenshot display below I have shown what would happen if the company wanted to change the profit. I have chosen to increase the profit to 10%, as this is a relatively small increase. The affects of the profit change can be seen in the screenshot below. I predict that the profit percentage increase will have a small, but noticeable impact on the holiday prices.
My prediction was correct, as there wasn’t a large increase in the holiday prices. I decided to try a larger profit percentage increase. I chose to increase the profit level to 25%, and as can be seen in the screenshot below a profit increase that large had drastic effects on the total prices of the holidays. I predict that a large profit percentage increase will increase holiday prices by quite a lot.
My prediction was not quite correct, as the profit increase wasn’t all that large.
I have reviewed my spreadsheet and decided to make a few adjustments. Firstly, I have decided to use absolute cell referencing on the ‘5% Profit’ column, as to make it easier to adjust the profit percentage. To do this, I had to create a new reference cell underneath the VAT reference cell. All of the formulae in the ‘Profit’ column now multiply the original price by whatever is in the reference cell, which in this screenshot is G2. The final modification to the column was to rename it from ‘5% profit’ to ‘Profit’, as the profit percentage is now controlled by the reference cell.
Another small modification I have chosen to implement is to rename the column header ‘For how long’ to ‘Duration (Days)’, as this is shorter, and is a more relevant phrase.
The screenshot below shows my final completed spreadsheet. The total time it took to complete, including this report, was about four months. Originally, this spreadsheet had a user interface, comprising of a customer interface, a booking sheet and a customer invoice, all of which were linked by macros, but half way through the chapter we had a teacher change and the user interface was deemed unnecessary.
If I could do this project again, I wouldn’t have made a user interface, as this wasted a lot of time. I would have done more research before hand, as I didn’t do very much research.
