Results 1 to 4 of 4

Thread: Help! Multi-year projections for time change parts

  1. #1

    Question Help! Multi-year projections for time change parts



    Register for a FREE account, and/
    or Log in to avoid these ads!

    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
    Attached Files Attached Files

  2. #2
    Super Moderator p45cal's Avatar
    Join Date
    Dec 2012
    Posts
    1,481
    Articles
    0
    Excel Version
    365
    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.
    Attached Files Attached Files

  3. #3
    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?

  4. #4
    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
    Attached Files Attached Files

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •