I have a spreadsheet which contains pricing information which I refer to when creating estimates. When I enter the pricing information, I record the year I entered the amount. I'd like the spreadsheet to show both the escalated rate and also the escalated rate. Unfortunately, escalation does not occur at the same rate each year. I track the escalation percentages by year on a separate tab.
Any suggestions on a formula that would update the unit cost automatically?
The data is stored as follows:
Sheet Name = Unit Prices
Original price stored in column D.
Year original price entered in column E.
Escalated price stored in column F.
D E F
$5,000 2016 $5,408
formula would be $5,000 X 1.03 X 1.05 X 1.06 = $5,408
Sheet Name - Escalation Amounts
Year stored in column A
Escalation amount in column B
Ie:
2015 0%
2016 3%
2017 3%
2018 5%
2019 6%
I don't need to keep this on a separate sheet...can move to same sheet as cost information if easier.
Any suggestions?
Thanks
Any suggestions on a formula that would update the unit cost automatically?
The data is stored as follows:
Sheet Name = Unit Prices
Original price stored in column D.
Year original price entered in column E.
Escalated price stored in column F.
D E F
$5,000 2016 $5,408
formula would be $5,000 X 1.03 X 1.05 X 1.06 = $5,408
Sheet Name - Escalation Amounts
Year stored in column A
Escalation amount in column B
Ie:
2015 0%
2016 3%
2017 3%
2018 5%
2019 6%
I don't need to keep this on a separate sheet...can move to same sheet as cost information if easier.
Any suggestions?
Thanks