Spreadsheet report
- describe what the user needs
The purpose of my spreadsheet is to model the cost of a Limo hiring service. The limos can be hired on a daily basis for a minimum of a day and a maximum of two days there will be a discount for a hiring the limo for more than £50 the discount will be 5% and if the client has hired more than £100 than they will get 10% discount. This means that when it is finished you will be able to see when a limo is being hired, on what date and how long for.
Describe the data needed
For my spreadsheet to be successful it needs to have the following data:
- Hiring costs
- Details of limos
- Details of customers
- Prices
- Discounts
3) Describe the worksheets, formulas and functions you plan to use
I will collect data from the Internet due to the range of sources and data available. I will collect data from theses websites:
Worksheet 1- Limo Details
Worksheet 2- Hirer Details
Worksheet 3- Commissions
The worksheets talk to each other and they talk to the graphs. If I change a figure in a cell which is in the “Hirer Details” sheet then the corresponding cell in the “Commissions” Average of per customer sheet changes as well as the information in the graphs. If I change the names of the limos on the “Limo Details” sheet than in the “Hirer Details” sheets the names of the limos changes, if I change the prices, registration number e.t.c then automatically on the other sheets all the details would ...
This is a preview of the whole essay
Worksheet 3- Commissions
The worksheets talk to each other and they talk to the graphs. If I change a figure in a cell which is in the “Hirer Details” sheet then the corresponding cell in the “Commissions” Average of per customer sheet changes as well as the information in the graphs. If I change the names of the limos on the “Limo Details” sheet than in the “Hirer Details” sheets the names of the limos changes, if I change the prices, registration number e.t.c then automatically on the other sheets all the details would change.
Here is a screen shot of me changing the information and seeing the other sheet from which the details have changed
I think it is a good idea to draw out a rough plan of the spreadsheet by hand before, this is because once I have profitably planed what I am going to do the it will be easier to maintain with my proper spreadsheet. I will know the quantity of columns and rows to use, the type of formulas that I will use, what my graphs will be showing, etc. By having my spreadsheet planed out I will save time in the long period while producing my spreadsheet.
4) Describe how you put your plans from worksheets into excel
1) Open Microsoft Excel by double clicking on this icon
Excel automatically opens a blank workbook with worksheet.
2) I re-named sheet one first “Limo Information” by:
-Right clicking on the sheet
-Select re-name
-Type in Limo Information
3) I set the second worksheet in exactly the same way.
-Right clicking on the sheet
-Select re-name
-Type in “Hiring”
4) I set the third worksheet in precisely the same way.
-Right clicking on the sheet
-Select re-name
-Type in “Commissions”
- Now that everything was in order I decided to save my work by clicking on the floppy disk which was on the tool bar.
A window appeared asking me where I wished to save my work. I selected my folder, renamed my file name and clicked save.
You can also save your work by clicking on file save or save as.
I also changed the width and height of the row and column so it would be easier to understand and it would make it clearer.
- Click on format
- Go to column
- Click on width and
- Finally a window should appear, and then you can change the column width
- Once I had everything saved in my folder, I then began to insert data which was needed for the boat information.
- First I typed in headings for the worksheet; I then highlighted them all and formatted them to be bold. This was so that the titles stuck out from everything else.
- Then I began to insert data into the worksheet in the correct headings that it was needed for: Registration, Limo name, Cost per hour and Seats
7) I changed the format of the cells by.
- Right Clicking any Cell
- Clicking on “Format Cell”
- format Cell window should appear
- Then e.g. click on Number and change the format of cell to Time And
- Finally click on ok and the cell is formatted.
I also added border, patterns and changed the alignment. This is how I done it
Border:
- Highlight where you want a border
- Click on format
- Click on cell
- A window should appear
- Click on Border and
- Choose what kind of border u want
- Then press ok
Here is an example
I completed the same for patterns and alignment. Here is some screen shot
Here is an example of Patterns:colour
I did this so that all my cells would be correctly formatted for there place. Meaning that a cell containing the Limo Name should be in General format, it wouldn’t make sense for it to be in a date format.
An example of a format that I used is date; this was to see all the dates in short term.
The format of cell “B17” to “D17” has been changed to date as the cell is based on the Dates which the limos are booked on.
5 Describe the formulas and functions you entered into your worksheets
Here is an explanation of the formulas that I used
I entered the formula and pressed this button auto sum and clicked on the edge of a cell and dragged it across so it would calculate the formula for me.
Here is an explanation of the functions that I used for the “Hirer Details” Worksheet.
6 Describe the graphs you chose to generate and why you chose them
I produced 2 graphs to show how my spreadsheet can work
The first graph is called Total Price
It is appropriate for this task as it shows how much each hirer paid has their total amount
The second graph is called Ratings out of 10
It is appropriate for this task as it shows what the hirers thought of the service, if it was good, or if it was bad. I have labelled the graph appropriate because it matches with the information.
Each graph has a title and labels for each axis. An example of this is
7 Do some What if.... Queries
I decided to test out my spreadsheet by changing the cell “B14”, “C14”, “D14” this used the what if
The result of this was when a hirer spent over 50 they will have a 5% discount off their total price and it will show if the hirer got a discount and show the price that they will have to pay. If the hirer didn’t spend over £50 then they will have no discount and will have to pay the original total price.
This result is shown in the print off at the back of this report.
Also I decided to test out my spreadsheet by changing cell “B14”, “C14”, “D14”
The result of this was when a hirer spent over £100 they will have a 10% discount off their total price and it will show if they got a discount and what the total price is now. If the hirer didn’t spend over £100 then they won’t get a discount and they will have to pay the total price.
This result is also shown in the print off at the back of this report.
8 Sort at least one worksheet into a selected order
I decided to sort the data in order of my choice in one of my worksheet. This was the “Hirer Details” worksheet. Here is an example o f how I did this.
- Highlight the cells you want to sort
- Click on Data
- Click on sort
- A window should come up
- Choose how you want to sort the data: ascending / descending
Here is some screenshots how I done it.
Here is the Result
9 Describe how you ensured that your work was accurate and safe
I kept backups of my spreadsheet report on floppy, on lgfl, on my h-drive and on my computer at home.
Here is a screenshot of the file being sent to floppy
Here my work is on floppy.
Here is a screenshot of the file on my home computer.
Here my file is on lgfl
To make sure that I didn’t have any errors on my work I proof read my work and did a spell check.
I also did a spelling and grammar check.
I made my work accurate, I checked all the formulae I used and they were correct. I checked all my formulas my clicking on tools then on options, clicking on view and selecting/ticking formulas.