Exercise 1
Question
- In the first yellow block, use a VLOOKUP to determine the interest rate to use.
- In the second yellow block calculate the interest earned
Exercise 2
Question
- In the yellow cells create a calculation to determine the fixed costs associated with the relevant level of production.
- In the C19 row, determine the fixed costs EXCLUDING the impact on inflation based on the table in A12 to B16 and the level of production in row 18.
- In the C20 row, multiple the determined number by the correct inflation factor in row 7
Exercise 3
Question
- In this example we have a project that has a variable life as per cell C14.
- However, we know that depending on the stage of the project our Admin costs may vary. For example in the beginning we may have a skeleton staff
- Then perhaps we have some high setup fees initially. It is stable for a while and then towards the end of the project we scale down again
- In B21 to E24 you have been provided with the information of how long each stage lasts and what the admin cost is.
- Use a VLOOKUP to put the correct Admin cost in the correct month. Do you want an exact match or an estimate?
Exercise 4
Question
- HLOOKUP is just like VLOOKUP except it searches horizontally and returns details x rows down.
- In this example, we have entered the expected growth rate in house price on an annual basis (blue cells)
- In the yellow cells we want to pull through the appropriate growth rate and show it monthly (divide by 12). So if we have 6% for year 1, then each month should show 0.5% (6%/12).
- Use the HLOOKUP function and the year indicator row (row 12) to determine what % should be in each cell.
Exercise 5
Question
- In this example you need to determine how many buses are required in order to meet the requirements of the particular route.
- You do not have to use our columns and can use whatever method you see fit. In A5 column and B5 column we have shown the departure time of the bus and the number of people that need to be transported. In B1 and B2 we show the capacity of a single bus and the time taken for a round trip. In C2 we have converted the time into a format the Excel recognises as time.
- Cell F2 needs to tell us how many buses we need. Remember that a bus returns after 70 minutes in this case and this must be taken into account in the buses required.
To see the full course contents click here.