Please enable JavaScript.
Coggle requires JavaScript to display documents.
RO60 Data Manipulation Using Spreadsheets Westwood Car Rental - Coggle…
RO60 Data Manipulation Using Spreadsheets
Westwood Car Rental
Client Requirements
The spreadsheet must work out the relevant calculations.
The spreadsheet must store details about the customers who hired a car.
The spreadsheet must 'Follow the organisation house style/branding by reflecting the logo.
The spreadsheet must record the different types of cars in the system.
The spreadsheet should sort data accordingly and create appropriate cahrts.
The spreadsheet must have appropriate security to keep the information safe.
The spreadsheet must include data validation and verification to minimise errors in the spreadsheet.
User requirments
The spreadsheet should have an easy to use HCI to allow the employees to navigate the spreadsheet easily
The spreadsheet should work well with no faults and transition smoothly.
The spreadsheet should have input masks and data validation and verification so that it is easy to put data in and minimise errors to allow for accurate data and exclude as much erroneous data as possible.
House style
The logo is a greenish colour, with 2 circles around a main logo of Westwood car Hire, in the same colour. The little vehicle is ore of an orangy- yellow and beige colour.
My spreadsheet will be a greeny blue shade similar to the logo as the main colour, with the accent contrasting colour being the yellowy orange colour.
The same theme and style will be applied to each tab of the spreadsheet.
Data Presentation
My spreadsheet will have a range of graphs to present data visually, providing an extra insight. Types of graphs I will show are:
Pie chart for percentage of those with and without discount
bar chart to show popularity of car types
Line charts to show revenue in a financial period.
Bar or pie chart to show how many of each car type was hired
Sheet filters will be installed to allow employees and Jamal to cusomise the order of data shown
Variables
The type of car hired
If an addition item such as a car seat or additional driver is required
If the discount has been given of 25.00%
The time the car is hired for
Calculations
The spreadsheet should calculate the following:
-The total income generated from each car type hired
-The number of bookings made by each customer for each car type hired
-The total number of days each car type is hired
-The total number of bookings for customers who hold a UK driving licence
-The total number of bookings for customers who do not hold a UK driving licence
-The total number of booking for each car type that include one or more additional items with the car hire
-The total income generated in the month from car hire of the discount of 25% is given.
Security
The spreadsheet should have password locks on sensitive sheets to make sure no unauthorised people can access confidential files.
The password to the password protected sheets should be
WCH*25
Certain cells in sheets should be locked so that unnecessary tampering and changing is not permitted.
We can use the protect sheet and ranges function to do this.
target audience
The target audience is Jamal and his company Westwood Car Hire
The employees who work there and use the spreadsheet will also be part of the target audience.
Drop downs and input masks
Input masks will ensure certain cells will be in the correct format, such as numerical or currency cells only able to receive numbers. This reduces the risk of input error and mitigate any erroneous data that could be entered.
Data validation in the form of dropdowns will be useful for the spreadsheet in areas such as the car type hired, which will restrict employees to enter only the data required, which again minimises the risk of erroneous data and allows the spreadsheet to calculate the formulas for the prices accurately.
