Hi,
I am doing a school project and this issue has been messing with my brain over the last two days.
I'm trying to calculate when (the exact year) a cost will occur based on a frequency. To be more specific, I want to look at what year tires for a car needs to be replaced when worned out and then calculate the cost. The cost should be an average for a fleet of cars, where each car is driving different mileages per year (assumed take-rates).
Some assumptions:
Tire life expectancy: 40 000 km
Cost for a new set of tires: 800 EUR
Mileage categories and take-rate (% of customers who drives the distance / year):
40 000 km 5%
35 000 km 2%
30 000 km 5%
25 000 km 11%
20 000 km 24%
15 000 km 51%
The key part of the problem is that I want to highlight the costs in a table so that it becomes visualized exactly what year the cost occurs and what the cost is (example below), but I'm not able to come up with a good idea how to build a formula so that get this:
If anyone could help, I would be VERY happy!
Thanks in advance.
I am doing a school project and this issue has been messing with my brain over the last two days.
I'm trying to calculate when (the exact year) a cost will occur based on a frequency. To be more specific, I want to look at what year tires for a car needs to be replaced when worned out and then calculate the cost. The cost should be an average for a fleet of cars, where each car is driving different mileages per year (assumed take-rates).
Some assumptions:
Tire life expectancy: 40 000 km
Cost for a new set of tires: 800 EUR
Mileage categories and take-rate (% of customers who drives the distance / year):
40 000 km 5%
35 000 km 2%
30 000 km 5%
25 000 km 11%
20 000 km 24%
15 000 km 51%
The key part of the problem is that I want to highlight the costs in a table so that it becomes visualized exactly what year the cost occurs and what the cost is (example below), but I'm not able to come up with a good idea how to build a formula so that get this:
Year 1 | Y2 | Y3 | Y4 | Y5 | |
40000 | cost in EUR if cost should occur | etc. | |||
35000 | |||||
30000 | |||||
25000 | |||||
20000 | |||||
15000 |
If anyone could help, I would be VERY happy!
Thanks in advance.