maintenance problem

[andybandy]

I have a problem. In Excel I'm making a budget for a car. Every 15 000km the car needs a small maintenance. But every 30 000 km it need a big maintenance. how do I make a model that can solve this issue regardless of how many kilometres the car is driven pr year. The km pr year is always the same. The budget is for 6 years.

[Bryan Hessey]

I have a problem. In Excel I'm making a budget for a car. Every 15 000km the car needs a small maintenance. But every 30 000 km it need a big maintenance. how do I make a model that can solve this issue regardless of how many kilometres the car is driven pr year. The km pr year is always the same. The budget is for 6 years.

Hi,

you would need to take the kilometers cumulative to the year,
and take the integer of that divided by 15000,
budget for the mini,
then, on the integer of that (integer) divided by 2,
add the figure that is the difference between mini and big maintenance

deduct any budgeted in previous year.
and the budget for this year is what remains.

---

[andybandy]

I'm sorry, can you explain some more. I'm new to excel and besides my english is poore. I would really appreciate it.

[Bryan Hessey]

I'm sorry, can you explain some more. I'm new to excel and besides my english is poore. I would really appreciate it.

You have the number of kilometers that the vehicle will travel each year, or an initial estimate thereof (note, without a number of kilometers or a guess there is no budget)

Assuming that you need a budget per year,

at the end of each year, multiply the number of kilomteres by the number of years, to give the kilomters the vehicle will have expected to travel by the end of each year.

Take that total of kilomters and divide it by 15000, drop any fraction.

If the number is an odd number note +1 mini service costs.

Divide the number by 2, drop any fraction.

The answer is the number of 30,000 k, which means a Mini & a Big service

so multiply the number by the cost of a Mini + the cost of a Big service

add on the 'odd number' mini service if there was one

that gives the total cost of expected services up to this point, subtract that from what is budgeted in prior years.

To see this work, in A2 put a fugure (23,500)
in A3 put

=A2

in B2 put

=INT(SUM(A$2:A2)/15000)

in C2 put

=MOD(B2,2)

in D2 put the cost of a mini service
in E2 put the cost of a big service
and in D3 =D2
and in E3 =E2

in F2 put

=B2*(D2+E2)+IF(CV2>0,D2,0)

in G2 put

=F2-(SUM(F$1:F1))

and formula fill (three columns from row 3, remainder from row 2) down to row 7

You should come up with something like

Please Login or Register  to view this content.

where 75222 is the assumed K per year, 15 and 35 are the cost of the 2 services.

I know you can use A$2 D$2 and E$2 but this way permits increases at various years.

See how you go.
---

[andybandy]

Thank you ! But when the vehicle hits 30 000k there is just a big serice. Not both a big and a mini. Can u help me figure out how? Thanx a lot for helping me

[Bryan Hessey]

Thank you ! But when the vehicle hits 30 000k there is just a big serice. Not both a big and a mini. Can u help me figure out how? Thanx a lot for helping me

I know

When the vehicle hits 30,000 it has passed 15,000.

so one of each.

The calculation was correct.

The 'odd' 15,000 is to pickup the 15,000 that has not (yet) reached 30,000.

---

[andybandy]

If the km the first year was 75222km then the cost of services that year would be: 115 (mini=15 big=35)

[Bryan Hessey]

If the km the first year was 75222km then the cost of services that year would be: 115 (mini=15 big=35)

Yes,

I missed the stated "Divide the number by 2, drop any fraction."

the formula for F2 should be

=INT(B2/2)*(D2+E2)+IF(CV2>0,D2,0)

---