Hello all, I am relatively new at PowerPivot and am trying to convert my excel 2007 spreadsheet based financial model to a more user friendly model in PowerPivot. The model is for a company that has multiple departments and is to be a 20-year forecast. The costs can change annually based on the following user inputted changes:
- Inflationary Changes (broken down by account, eg. 4% for salaries and wages, 3% for fuel, 2% for materials, etc.)
- Volume Changes (also broken down by account, changes that arise from a growing company)
- Capital Induced Changes (changes arising from acquiring/disposing of capital)
- Fixed Dollar Changes (changes to costs that are not percentage based)
I have brought in my account structure, department structure, prior year actuals and current budget but I am having a hard time figuring out what should be calculated in the source data/spreadsheet, as calculated columns and/or as a calculated field.
It may be best to start with the inflationary changes and then I can attempt the rest once I have these costs modeled. I previously had this calculating as such:
1. Table 1 - identifies the Inflation Driver and the appropriate % change for each year. (note: the years are the column headers)
<tbody>
</tbody>
2. Table 2 - assigns an inflation driver to each account in each department (because items like the wage increases may be different for each department, for example sake I have used Admin and Management for the departments)
<tbody>
</tbody>
3. Table 3 - using index/match I would multiply the previous years amount by the appropriate driver for the current year.
So I'm wondering if anyone has any tips on how to best set this up in Power Pivot? It seems like there should be a relatively easy way to set this up and avoid having to do the calculations in the spreadsheet. I apologize if the question is broad but I am trying to keep it as a simple example right now.
Thanks!
- Inflationary Changes (broken down by account, eg. 4% for salaries and wages, 3% for fuel, 2% for materials, etc.)
- Volume Changes (also broken down by account, changes that arise from a growing company)
- Capital Induced Changes (changes arising from acquiring/disposing of capital)
- Fixed Dollar Changes (changes to costs that are not percentage based)
I have brought in my account structure, department structure, prior year actuals and current budget but I am having a hard time figuring out what should be calculated in the source data/spreadsheet, as calculated columns and/or as a calculated field.
It may be best to start with the inflationary changes and then I can attempt the rest once I have these costs modeled. I previously had this calculating as such:
1. Table 1 - identifies the Inflation Driver and the appropriate % change for each year. (note: the years are the column headers)
Inflation Driver | 2015 | 2016 | 2017 |
Salaries - Admin | 1.02 | 1.02 | 1.02 |
Salaries - Management | 1.04 | 1.04 | 1.03 |
Fuel | 1.03 | 1.03 | 1.02 |
<tbody>
</tbody>
2. Table 2 - assigns an inflation driver to each account in each department (because items like the wage increases may be different for each department, for example sake I have used Admin and Management for the departments)
Account | Admin | Management |
Salaries and Wages | Salaries - Admin | Salaries - Management |
Fuel | Fuel | Fuel |
<tbody>
</tbody>
3. Table 3 - using index/match I would multiply the previous years amount by the appropriate driver for the current year.
So I'm wondering if anyone has any tips on how to best set this up in Power Pivot? It seems like there should be a relatively easy way to set this up and avoid having to do the calculations in the spreadsheet. I apologize if the question is broad but I am trying to keep it as a simple example right now.
Thanks!