Calculating a mileage forecast

[Cumbrianet]

Just purchased a new car whose credit agreement is based around monthly miles use. The agreement is over 4 years, with a penalty for over mileage use. Bit stuck as to were to begin other than entering a monthly mileage use. Any ideas excel 365?
Trevor

 

[candybg]

Are you wanting to compare your monthly miles vs 1/48th of your contract or something else?

 

[Cumbrianet]

Hi thanks for your reply. I have an allowance of 10,000 miles per calendar year. So I want to both track my actual milage and based on that forecast my mileage for the year. E.g If since purchase to date I've travelled 950 miles what would be estimated mileage for 12 months mileage. Do i calculate an average daily mileage and multiply by 365. Could I create a table Column A date, B Mileage to-date, C average daily mileage to date, D Estimated mileage for year .

Seems I'm missing something?

Trevor

 

[candybg]

Based on your stated goal to track mileage and project annual mileage, I would set up a spreadsheet with the following column headers:
Month, miles traveled, YTD miles, Projected Annual Mileage
The first two are straight data. YTD mileage formula in C2 would be
=SUM(C$2:C2)
I can picture several methods to estimate the projected mileage. You could, as you said, calculate a daily mileage and multiply by 365. My thought is that this data isn't very likely to be consistent or steady - one month you may take a vacation or during the summer you travel more on weekends. I think for projection purposes, you could take an AVERAGE of your monthly data and multiply that by 12 to project it out. Therefore, every month the projected mileage will change so I would use Col D to AVERAGE the Col B data and multiply by 12.

Hope this helps! Let us know if you would like a formula for Col D or can you figure it out yourself.

 

[Cumbrianet]

Thanks I'll give that a go over weekend