Exercise 7
Joanne Reid is the Cycling Supervisor for Smarts Leisure Park. She wants details of the bike hire to be computerised. She wants to be able to enter bookings into a spreadsheet that calculates hire charges, insurance, and discount. She also wants to be able to check that enough profit is being made.
The table below gives the hire charges for bikes and equipment. It also shows the daily running cost for each item.
|
Item Name |
Daily Charge (£) |
Daily Cost (£) |
|
Boneshaker |
11.00 |
2.00 |
|
Dirt Thrasher |
10.00 |
2.75 |
|
Lazybones |
14.00 |
4.50 |
|
Speedstar |
12.00 |
2.50 |
|
Tiger |
8.00 |
3.00 |
|
Venom |
12.00 |
2.50 |
|
Maps (Copies) |
0.50 |
0.10 |
|
Body Armour |
2.00 |
0.75 |
|
Insurance per Person |
2.50 |
0.75 |
|
Group Insurance (8-12 people) |
26.00 |
12.00 |
|
Family Insurance (2 adults and 2 children) |
10.00 |
2.50 |
|
Helmet- Full Face |
1.50 |
0.60 |
|
Helmet- Open Face |
1.00 |
0.50 |
|
Goggles |
1.00 |
0.30 |
Work to Do
Enter the text and numbers from the table below into a spreadsheet booking form for customers. It should have a layout similar to Table 7.2 below.
Complete the booking form using information from Table 7.1. You will need extra rows.
Change the width of the columns so that the text fits into the column.
Table 7.2
Insert a formula to calculate the total for each item.
To figure out the formula you will need to multiply the number of bikes required by the daily charge.
In this case you will have to double click on the first cell under the "Total" heading.
Type an equals sign.
Type the cell reference of the first cell under the "Number Required" heading.
Type an "*" sign.
Type the cell reference of the first cell under the "Daily Charge" heading.
You have just typed in a formula which tells Excel to multiply the number of cycles required by the daily charge.
Copy this formula into all cells under the total heading.
Use the Autosum function to calculate the total charge for the booking.
Make sure that you include all of the cells under the total heading, but not including the cell right beside your total charge heading.
Format all cells under the "Daily Charge" and "Total" headings to display £ and pence.
Check to ensure that the spreadsheet is clearly presented and easy for the staff to use.
Type your Name and form on the document.
Open your My Document Folder.
Create a new folder and Name it Spreadsheet Activities.
Save this spreadsheet in your Spreadsheet Activities folder, giving it a new name BOOKING.
Save this spreadsheet onto your floppy disk.
Print a copy of the spreadsheet showing the function entered for total charge. Make sure it fits on one A4 sheet.
