Help! Multi-year projections for time change parts

dubyayoung

New member
Joined
Oct 5, 2012
Messages
11
Reaction score
0
Points
0
I manage the maintenance on several aircraft, and I need to do budget projections once in a while to determine future expected costs. There are several parts that are required to be replaced or overhauled at certain intervals, so I need the information as to what parts are going to be due during the coming year, or years, along with their projected costs. I track all of the critical time change parts are in an Excel workbook. I've done my projections by hand up until now, but I have been asked for several variations lately, so I would really like to automate the process!

I have attached a simplified sample sheet to give you an idea of what I am trying to do. I want the sheet to allow for a variety of years in the projection, and allow me to vary the time expected to be flown each year.

I need the sheet to not only project when the part will be due, but also project when it will be due again after that. What was getting hard for me was that I also need those projected numbers to show up in the correct year.

For instance in my sample sheet I am starting with an aircraft time of 4,100 hours. Part 3 will be due in 700 hours, or at 4,800 hours aircraft time, but it will be due again 1,200 hours after that, then again 1,200 hours after that… (such as 4,800 in year 1, 6,000 in year 3, 7,200 in year 5), so I not only need to figure each time the part will be due, but also in what year of my projection each of those numbers would occur.

I started trying to do this with IF statements, but it started getting pretty crazy for me trying to figure the subsequent replacement times, while correctly figuring what years they would actually come due. What was making it harder is that I have to show the part due at the first time, then show it again at the second time, and so on.

I think I can do the costs using the VLOOKUP function, but I need a number for the correct year to associate with the column the VLOOKUP function will look for.

I have the costs in a separate sheet, with an inflationary figure added for each year, so I also need to have the correct cost associated with the correct part in the correct year:


Year 1

Year 2

Year 3

Year 4

Year 5
Part 3

$550

$572

$595

$619

$643


Thanks for checking this out! Any help is greatly appreciated!

Wayne
 

Attachments

  • Sample sheet.xlsx
    11.4 KB · Views: 28
Check out the attached.
Play with adding/deleting part numbers, periods. Try changing part life values, flying hours per year.
Costs haven't been incorporated yet.
 

Attachments

  • Sample sheetExcelGuru2064.xlsm
    35.1 KB · Views: 16
p45cal, Sorry for not getting back sooner, I was off for a nice long weekend! Thank you so much for the effort you put into this. I played with it a little bit, and I can see what the macro is doing, and it looks great. Understanding how it does it is completely different! I hope to get back on this later in the week and hope I can come close to figuring out what you did, because frankly it is way over my head, but that's how we learn, right?
 
Good afternoon,

This could probably be cleaned up, but hopefully it's of some help. It's done only with formulas. I also added a couple little formulas in your "aircraft time" so you don't have to figure them every time.

Best of luck
 

Attachments

  • Sample sheet.xlsx
    13.6 KB · Views: 18
Back
Top