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:
Thanks for checking this out! Any help is greatly appreciated!
Wayne
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