Spreadsheet report

1) describe what the user needsThe 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 neededFor my spreadsheet to be successful it needs to have the following data:* Hiring costs* Details of limos* Details of customers* Prices* Discounts3) Describe the worksheets, formulas and functions you plan to useI will collect data from the Internet due to the range of sources and data available.

I will collect data from theses websites:* www.limo.org* www.limos.com* www.

limospros.comWorksheet 1- Limo DetailsColumn NameDescriptionFormat1RegistrationRegistration Number of LimoGeneral2Limo nameName of LimoGeneral3Cost per hourPrice per hourAccounting4SeatsHow many seatsNumber5BookedHow many times been bookedGeneralWorksheet 2- Hirer DetailsColumn NameDescriptionFormat1Hirer NameName of HirerText2Address line 1Address of HirerGeneral3CityAddress of HirerGeneral4PostcodeAddress of HirerGeneral5Number of seats desiredHow many seats desiredNumber6RegistrationRegistration Number of LimoGeneral7Limo nameName of LimoGeneral8Duration neededHow long forNumber9Price per hourCostAccounting10Total PriceTotal costAccounting11Discounted price by 5% if over �50DiscountAccounting12Discounted price by 10% if over �100DiscountAccounting13Date booked forDate booked onDate14Rating out of tenRatingNumberColumn NameDescriptionFormat1RegistrationRegistration Number of LimoGeneral2NameName of LimoGeneral3Number of commissionsHow many times been usedGeneral4Average per customerAverage CostAccountingWorksheet 3- CommissionsThe 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 changedI 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 excel1) Open Microsoft Excel by double clicking on this iconExcel 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 Information3) 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”5) 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 width6) 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 Seats7) 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 itBorder:* 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 okHere is an exampleI completed the same for patterns and alignment. Here is some screen shotHere is an example of Patterns:colourI 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 worksheetsHere is an explanation of the formulas that I usedI 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.CellFormula usedReason1=’Limo details’!B4Link the sheets together2=AVERAGE(‘Hirer details’!B14:D14)See the average cost3=IF(B14;100,IF(B14;50,B14*0.95,”-“),”-“)If they paid over �50 then they will get a 5% discount on their total price4=COUNTIF(‘Hirer details’!$B$10:$D$10,’Limo details’!B4)5=IF(D14;=100,D14*0.

9,”-“)If they have spent over �100 then they will get a 10% discount on their total priceHere is an explanation of the functions that I used for the “Hirer Details” Worksheet.CellFunction usedReason1H5=AVERAGE(‘Hirer details’!B14:D14)See the average cost2B14=B12*B13Work out the total3B15=IF(B14;100,IF(B14;50,B14*0.95,”-“),”-“)If they paid over �50 then they will get a 5% discount on their total price4D16=IF(D14;=100,D14*0.9,”-“)If they have spent over �100 then they will get a 10% discount on their total price6 Describe the graphs you chose to generate and why you chose themI produced 2 graphs to show how my spreadsheet can workThe first graph is called Total PriceIt is appropriate for this task as it shows how much each hirer paid has their total amountThe second graph is called Ratings out of 10It 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 is7 Do some What if…. QueriesI decided to test out my spreadsheet by changing the cell “B14”, “C14”, “D14” this used the what ifThe 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 orderI 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 / descendingHere is some screenshots how I done it.Here is the Result9 Describe how you ensured that your work was accurate and safeI 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 floppyHere my work is on floppy.Here is a screenshot of the file on my home computer.

Here my file is on lgflTo 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.