distortionwarrior
New Member
- Joined
- Jan 14, 2016
- Messages
- 2
Hello, I am a budget guy and work in excel basically all day every day, and I'm stumped on how to do something and need a bit of hand holding. Thanks in advance for reading the long read.
I've got a long list of annual budgets and a long list of annual reconciliation cost report data or "execution" data. They are formatted with the same column headers so line up pretty well, and each annual budget and cost report have a unique version number.
Each line item has an entry in the budget and the cost report, so you can pivot and make a nice budget to execution variance report. There's like 20 years of actual budgets and cost reports all in one long flat file. It's awesome. I build a lot of charts and reports from this, and it is all working fine.
Here's where I'm stumped on how to add a very useful feature. I'm supplied data table with a very clearly defined government inflation rates that covers from like 1970 until about 2035 that I have added to a new worksheet within the budget to execution workbook, and while the inflation rates may not be entirely accurate in reality, it is certified as an accurate authoritative standard for generating cost estimates, so we use it. The big budget to execution spreadsheet is not adjusted for inflation, it's just actual raw numbers from "then year" reports, and I need to be able to adjust/calculate several years (or all years) dollar values to match the inflation rate of whatever year from 1970 to 2035 to look at cost growth based on inflation. This is called "Constant Year Dollar", and line by line it's simple to do by hand, but I've got several hundred thousand lines and 65 years worth of inflation table values. and I want to use a slicer to select the "Constant Year".
Constant Year works like this: If I use Constant Year 2016 as the "base" year, and want to know how strong the 2016 dopllar was compared to another year's dollar, say 2010, I would look up the conversion rate from 2016 to 2010 in the table and multiply the 2016 to 2010 inflation rate by the 2016 budget or execution value, and viola! you just adjusted for inflation!
So, let's look at a really simple example so I can get the concept out there...
2010 project "x" budget = $1000
2010 project "x" execution = $1050
2011 project "x" budget = $1100
2011 project "x" execution = $1197
2012 project "x" budget = $1200
2012 project "x" execution = $1205
2013 project "x" budget = $1225
2013 project "x" execution = $1299
2014 project "x" budget = $1300
2014 project "x" execution = $1398
2015 project "x" budget =$1400
2015 project "x" execution = $1410
conversion rates can be:
[TABLE="width: 271"]
<colgroup><col><col span="5"></colgroup><tbody>[TABLE="width: 315"]
<colgroup><col><col span="6"></colgroup><tbody>[TR]
[TD]Year[/TD]
[TD="align: right"]2010
[/TD]
[TD="align: right"]2011
[/TD]
[TD="align: right"]2012
[/TD]
[TD="align: right"]2013
[/TD]
[TD="align: right"]2014
[/TD]
[TD="align: right"]2015
[/TD]
[/TR]
[TR]
[TD="align: right"]2010[/TD]
[TD="align: right"]1
[/TD]
[TD="align: right"]0.80[/TD]
[TD="align: right"]0.80[/TD]
[TD="align: right"]0.80[/TD]
[TD="align: right"]0.80[/TD]
[TD="align: right"]0.80[/TD]
[/TR]
[TR]
[TD="align: right"]2011[/TD]
[TD="align: right"]2.00[/TD]
[TD="align: right"]1
[/TD]
[TD="align: right"]2.00[/TD]
[TD="align: right"]2.00[/TD]
[TD="align: right"]2.00[/TD]
[TD="align: right"]2.00
[/TD]
[/TR]
[TR]
[TD="align: right"]2012[/TD]
[TD="align: right"]1.80[/TD]
[TD="align: right"]1.80
[/TD]
[TD="align: right"]1
[/TD]
[TD="align: right"]1.80[/TD]
[TD="align: right"]1.80[/TD]
[TD="align: right"]1.80
[/TD]
[/TR]
[TR]
[TD="align: right"]2013[/TD]
[TD="align: right"]1.50[/TD]
[TD="align: right"]1.50[/TD]
[TD="align: right"]1.50
[/TD]
[TD="align: right"]1
[/TD]
[TD="align: right"]1.50[/TD]
[TD="align: right"]1.50
[/TD]
[/TR]
[TR]
[TD="align: right"]2014[/TD]
[TD="align: right"]1.50[/TD]
[TD="align: right"]1.50[/TD]
[TD="align: right"]1.50[/TD]
[TD="align: right"]1.50[/TD]
[TD="align: right"]1
[/TD]
[TD="align: right"]1.50
[/TD]
[/TR]
[TR]
[TD="align: right"]2015[/TD]
[TD="align: right"]1.40[/TD]
[TD="align: right"]1.40[/TD]
[TD="align: right"]1.40[/TD]
[TD="align: right"]1.40[/TD]
[TD="align: right"]1.40[/TD]
[TD="align: right"]1
[/TD]
[/TR]
</tbody>[/TABLE]
</tbody>[/TABLE]
How can I automate this calculation and create a separate group of values that show the resulting inflation adjusted values based on which "constant year" you choose? I'm currently using a concatenated "key" that includes program number, document type (budget or cost report), and year, to do a lot of other total budget functions and cost per unit functions, but I need to adjust for inflation.
I want to say "inflation has gone up/down since year X, and without a proportional increase in budget, we will not meet our goals without a similarly sized cash injection." Since there are thousands of programs and each program has an entry into each year's budget and the resulting execution reports, I will have to automate the calculations.
I use excel 2010 and 2013.
The large budget and execution flat file worksheet is in a table format, as are the inflation rates.
I've hit a brick wall. What functions can I do???
I've got a long list of annual budgets and a long list of annual reconciliation cost report data or "execution" data. They are formatted with the same column headers so line up pretty well, and each annual budget and cost report have a unique version number.
Each line item has an entry in the budget and the cost report, so you can pivot and make a nice budget to execution variance report. There's like 20 years of actual budgets and cost reports all in one long flat file. It's awesome. I build a lot of charts and reports from this, and it is all working fine.
Here's where I'm stumped on how to add a very useful feature. I'm supplied data table with a very clearly defined government inflation rates that covers from like 1970 until about 2035 that I have added to a new worksheet within the budget to execution workbook, and while the inflation rates may not be entirely accurate in reality, it is certified as an accurate authoritative standard for generating cost estimates, so we use it. The big budget to execution spreadsheet is not adjusted for inflation, it's just actual raw numbers from "then year" reports, and I need to be able to adjust/calculate several years (or all years) dollar values to match the inflation rate of whatever year from 1970 to 2035 to look at cost growth based on inflation. This is called "Constant Year Dollar", and line by line it's simple to do by hand, but I've got several hundred thousand lines and 65 years worth of inflation table values. and I want to use a slicer to select the "Constant Year".
Constant Year works like this: If I use Constant Year 2016 as the "base" year, and want to know how strong the 2016 dopllar was compared to another year's dollar, say 2010, I would look up the conversion rate from 2016 to 2010 in the table and multiply the 2016 to 2010 inflation rate by the 2016 budget or execution value, and viola! you just adjusted for inflation!
So, let's look at a really simple example so I can get the concept out there...
2010 project "x" budget = $1000
2010 project "x" execution = $1050
2011 project "x" budget = $1100
2011 project "x" execution = $1197
2012 project "x" budget = $1200
2012 project "x" execution = $1205
2013 project "x" budget = $1225
2013 project "x" execution = $1299
2014 project "x" budget = $1300
2014 project "x" execution = $1398
2015 project "x" budget =$1400
2015 project "x" execution = $1410
conversion rates can be:
[TABLE="width: 271"]
<colgroup><col><col span="5"></colgroup><tbody>[TABLE="width: 315"]
<colgroup><col><col span="6"></colgroup><tbody>[TR]
[TD]Year[/TD]
[TD="align: right"]2010
[/TD]
[TD="align: right"]2011
[/TD]
[TD="align: right"]2012
[/TD]
[TD="align: right"]2013
[/TD]
[TD="align: right"]2014
[/TD]
[TD="align: right"]2015
[/TD]
[/TR]
[TR]
[TD="align: right"]2010[/TD]
[TD="align: right"]1
[/TD]
[TD="align: right"]0.80[/TD]
[TD="align: right"]0.80[/TD]
[TD="align: right"]0.80[/TD]
[TD="align: right"]0.80[/TD]
[TD="align: right"]0.80[/TD]
[/TR]
[TR]
[TD="align: right"]2011[/TD]
[TD="align: right"]2.00[/TD]
[TD="align: right"]1
[/TD]
[TD="align: right"]2.00[/TD]
[TD="align: right"]2.00[/TD]
[TD="align: right"]2.00[/TD]
[TD="align: right"]2.00
[/TD]
[/TR]
[TR]
[TD="align: right"]2012[/TD]
[TD="align: right"]1.80[/TD]
[TD="align: right"]1.80
[/TD]
[TD="align: right"]1
[/TD]
[TD="align: right"]1.80[/TD]
[TD="align: right"]1.80[/TD]
[TD="align: right"]1.80
[/TD]
[/TR]
[TR]
[TD="align: right"]2013[/TD]
[TD="align: right"]1.50[/TD]
[TD="align: right"]1.50[/TD]
[TD="align: right"]1.50
[/TD]
[TD="align: right"]1
[/TD]
[TD="align: right"]1.50[/TD]
[TD="align: right"]1.50
[/TD]
[/TR]
[TR]
[TD="align: right"]2014[/TD]
[TD="align: right"]1.50[/TD]
[TD="align: right"]1.50[/TD]
[TD="align: right"]1.50[/TD]
[TD="align: right"]1.50[/TD]
[TD="align: right"]1
[/TD]
[TD="align: right"]1.50
[/TD]
[/TR]
[TR]
[TD="align: right"]2015[/TD]
[TD="align: right"]1.40[/TD]
[TD="align: right"]1.40[/TD]
[TD="align: right"]1.40[/TD]
[TD="align: right"]1.40[/TD]
[TD="align: right"]1.40[/TD]
[TD="align: right"]1
[/TD]
[/TR]
</tbody>[/TABLE]
</tbody>[/TABLE]
How can I automate this calculation and create a separate group of values that show the resulting inflation adjusted values based on which "constant year" you choose? I'm currently using a concatenated "key" that includes program number, document type (budget or cost report), and year, to do a lot of other total budget functions and cost per unit functions, but I need to adjust for inflation.
I want to say "inflation has gone up/down since year X, and without a proportional increase in budget, we will not meet our goals without a similarly sized cash injection." Since there are thousands of programs and each program has an entry into each year's budget and the resulting execution reports, I will have to automate the calculations.
I use excel 2010 and 2013.
The large budget and execution flat file worksheet is in a table format, as are the inflation rates.
I've hit a brick wall. What functions can I do???
