Excel Exercises
Using Formulas Proceed through these additional exercises, once you have worked through the introductory exercise in the Excel handbook. Open the Excel Exercises file and click the Using Formulas 1 tab.
1) Table 1 highlights the regional distribution of hotel accommodation in England by Tourist Board region and the size of establishment (number of bedrooms).
„
In column J, use the
tool to calculate the row total for the Cumbrian Tourist Board region.
What formula has been generated in cell J8 to calculate this total?
Use the fill down option to calculate the row totals for the remaining tourist board regions.
„
In row 19, use the
tool to calculate the column total for the 1 to 3 size category.
What formula has been generated in cell B19 to calculate this total?
Use the fill right option to calculate the column totals for the remaining Tourist Board regions.
2) Table 1 gives a detailed breakdown of the size structure of hotel accommodation in England. You have been asked to simplify this table to show the distribution of small, medium and large hotels. In this case small= 1 to 25 bedrooms, medium = 26 to 100 bedrooms and large = 101+ bedrooms. The unknown category is also to be included. The structure for your new table is provided in Table 2. p. 3
Excel Exercises
„
You can use the
tool to calculate the revised totals for the different size categories.
What formula has been generated in cell B29 to calculate this new total?
What formula has been generated in cell C29 to calculate this new total?
What formula has been generated in cell D29 to calculate this new total?
Use the fill down option to calculate the new values for the remaining tourist board regions.
„
What formula has been generated in cell B38 to calculate this total?
Notice how the formula has changed to reflect the different cell names and values. Refer to page 31 in your Excel handbook for additional information on how Excel automatically recalculates cell values.
„
In row E you could copy the values for Unknown from Table 1. Alternatively, what formula could you type in cell E29 that would give you the same value?
Once you have identified the correct formula, use the fill down option to complete the table. p. 4
Excel Exercises 3) The data provided so far has referred to the absolute number of hotels in a specific region. In Table 3, now try and calculate the actual percentage distribution of hotel accommodation. In this instance you need to illustrate the distribution within each Tourist Board region and therefore need to use the row total.
„
In cell N8, what formula including the absolute cell reference would yield the required percentage value. You need to refer back to Table 1 for cell co-ordinates.
„
How else could this formula be written to give the percentage value in cell N8? Does this formula work if you were to use the fill right option to complete the row? What has happened?
If necessary delete the contents of cell Q8 to V8, and from cell N8 use the fill right option to complete the row, using the formula containing the absolute reference. This should now give you the percentage size distribution of hotels in Cumbria. Because you have used the absolute reference you cannot simply use the fill down option to complete the table as this will give you the wrong figures as all subsequent calculations will use the total value in cell J8 (the absolute cell reference in Table 1, which refers only to the Cumbrian tourist board region). To illustrate, highlight the cells N8 to V8 and use the fill down option to calculate the values for the Northumbrian tourist board region. Quickly perform a manual calculation and compare your answers. You should find that the answers generated in Excel are wrong.
p. 5
Excel Exercises
Delete the contents of cells N9 to V9. In cell N9, what formula including the absolute cell reference would yield the required percentage value. Again, you need to refer back to Table 1 for cell co-ordinates.
Use the fill right option to complete the row. You will need to generate a new formula for each row. Do this and complete the table.
If your calculations are correct you should find the row totals in column V total 100 for all regions. The column totals in row 19, will give the percentage distribution at the national level.
4) In Table 4, repeat this exercise but instead of calculating hotel distribution by region, calculate the distribution of accommodation by size category. Therefore instead of using the row total you will need to use the column total. You will again need to refer back to Table 1 for cell coordinates.
To check you are on the right lines, in cell N29, what formula including the absolute cell reference would yield the required percentage value?
You can use the fill down option to complete the column. As before you will have to generate a new formula for each size category. Now complete the table. p. 6
Excel Exercises 5) Use your familiarity with formulas and the absolute cell reference to now complete Table 5. In this case calculate the size distribution by region by referring to the row totals in Table 2.
p. 7
Excel Exercises
Excel Exercises Exercise 1:
Click the Using Formulas 2 tab. Table 6 highlights the volume and value of tourism in English regions in 1997.
To do: 1a. Calculate the total number of trips, nights and expenditure for England. 1b. Insert new columns and calculate the percentage distribution of visitor trips, nights and expenditure across the English regions 1c. Calculate the average number of trips, nights and expenditure across the English regions. 1d. VisitBritain have admitted a mistake in their calculations, and all figures need to reflect a 22% increase. Insert a series of new columns or create a new table to reflect the true volume and value of tourism in England. 1e. The figures for tourism in England also include data for overseas visitors. VisitBritain estimate that overseas visitors account for 18% of all trips, 45% of all nights, and 42% of all tourism expenditure. Create a new table to reflect the volume and value of attributable to domestic and overseas visitors across the English regions.
p. 8
Excel Exercises
Excel Exercises Exercise 2:
Click the Using Formulas 3 tab. Table 7 shows Arrivals and Receipts for International Travel between 1970 and 1997.
To do: 2a. Insert additional columns and calculate: [a] the actual annual increase in arrivals and receipts [b] the annual percentage increase for arrivals and receipts. 2b. Calculate the total percentage increase for arrivals and receipts between 1970 and 1997.
p. 9
Excel Exercises
Excel Exercises Exercise 3:
Click the Using Formulas 4 tab. Table 8 highlights selected operational costs and earnings for the F1 Williams team.
To do: Using the information in Table 7 attempt to calculate the following race-based scenarios presented here and overleaf. Record your answers clearly on the worksheet and where possible use (and clearly record) formulas to calculate your answers.
1. The Race and Test Teams - Planning for the Season 1a. The total cost of each race car (excluding tyres). 1b. The total cost of five cars for the season (excluding tyres)(3 race and 2 test cars). 1c. The total cost to the team of running three cars throughout the session and attending 12 European-based races and 5 international events. For each event the team uses 8 sets of tyres. 1d. In addition, calculate the cost of running an additional 2 car test team that runs 4 European tests sessions per season. For each test event the team uses 12 sets of tyres.
p. 10
Excel Exercises 2. British Grand Prix Weekend For the British Grand Prix the team runs 3 cars. However, during qualifying Webber strays off the circuit and destroys his front wing. He reverts to the spare for qualifying, and a new nose cone is used for the race. The team have an extremely successful weekend. Webber wins his first British Grand Prix and Nico Rosberg is third (equivalent to 14 world championship points). Eight sets of tyres are used. Calculate: 2a. The total costs to the team of attending the British Grand Prix 2b. The total sponsorship money earned by the team. 2c. The total TV money earned from the race. 2d. The overall profit/loss margin for the weekend.
3. Brazilian Grand Prix Weekend For the Brazilian Grand Prix the team runs 3 cars. However, the weekend is a disaster. One race car is totally destroyed in qualifying and the spare is used in the race. In addition, both Webber and Rosberg receive penalties for speeding in the pit-lane, and the team has to use the private jet to fly out a number of spare parts for the race day. Eventually, the team finished the race third and fourth (7 championship points). Eight sets of tyres are used. Calculate: 3a. The total costs to the team of attending the Brazilian Grand Prix 3b. The total sponsorship money earned by the team. 3c. The total TV money earned from the race. 3d. The overall profit/loss margin for the weekend.
p. 11
Excel Exercises 4. Italian Grand Prix Weekend For the Italian Grand Prix the team plans to run 3 cars. However, in the week before the Grand Prix testing is allowed at the Monza circuit, so the test team also accompanies the race team to the circuit.
Testing proves successful and as a result the team scores a one-two finish (16
championship points). Eight sets of tyres are used into total for testing and the race. Calculate: 4a. The total costs to the team of attending pre-race testing and the race itself. 4b. The total sponsorship money earned by the team. 4c. The total TV money earned from the race. 4d. The overall profit/loss margin for the testing session and race weekend.
5. Season Overview Despite some strong performances, the team cannot beat Ferrari but finishes second in the championship standings with 72 points. Included in this total are three race wins, of which two were one-two finishes. Calculate: 5a. In question 1c you calculated the total cost to the team of running three cars throughout the session and attending 12 European-based races and 5 international events. Using this figure, include the additional cost of building two new race cars that were destroyed during the course of the season. 5b. The total sponsorship money earned by the team during the season. 5c. The total TV money earned during the cost of the season. 5d. The overall profit/loss margin for race team during the season.
p. 12