Structure of Financial Forecast Model in PowerPivot

[pwc86]

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)

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!

 

[scottsen]

I have spent <strike>some time</strike> a lot of time solving a very similiar problem. There is no two ways around it: This is a hard problem for Power Pivot.

There is just no way to write a dax formula which is "[Value from Last Year] * [Inflation for Current Year]" to get the current year values. You have to "recalculate" from the base year through all the intermediate years to get the current year in 1 crazy formula.

It's all doable... but even the lack of a PRODUCTX function means you end up doing that with some freaky SUMX/LOG/POWER (see Running Product in DAX: Calculating Portfolio Returns | Javier Guillén) to calculate the running inflation.

I don't want to discourage you; and it is doable... but I think you are going to struggle as a beginner, and honestly, I'm really good at this stuff... and had to reach out to Rob at powerpivotpro to help me through this last time.

 

[pwc86]

Thanks for responding to me Scott, it's much appreciated.

Based on your answer I am going abandon attempting to complete this task using DAX formulas.

However, I would still like to improve the reporting aspect of my model and still think structuring the model to bring all forecast results into PowerPivot would be beneficial. Do you have any thoughts on how I should do this? I currently do the following:
1. Summarize the budget to a set list of “Account Categories” and “Departments” with each “Department” having its own spreadsheet.
2. The forecasts are then calculated for each department by referring to the Driver tables for inflation and volume and then the amounts identified for capital induced and fixed are brought in. There is a section on each department tab for inflation, volume, capital induced and fixed changed and of course a total.
- In other words: Corporate Forecast = sum (department forecasts) = (Previous year total * inflation * volume) + (capital induced + fixed)

I want to be able to report on the following:
· Department summaries which can be summarized by Account Category and Type of Change (ie. inflation)
· Corporate wide summaries which can be summarized by department, Account Category and Type of Change
· Percent changes over time for the above

Do you know of any sample forecasting models I can look at? Google isn’t giving me much.

 

[Tianbas]

I had a problem some time ago that is not exactly the same but goes in the same direction. Just have a look maybe it helps to get started.

PowerPivot Forum • View topic - Measure vs Calculated column

 

[scottsen]

If it's me... and somebody is asking me to do forecasting, I am probably doing it outside of powerpivot, then bringing the data back into Power Pivot for further analysis and reporting. Though, it certainly *is* solvable in Power Pivot, I have grown gun-shy.

I mean... say, interest is 3%, you can do that via math... base*1.03^years. The important part here is that there is one calc you can use to directly calculate the interest in year 5. It is NOT based on Year 4.

But if the principle changes each year, or the interest rate changes each year... you STILL have to do (direct) math. Which is still doable, just... harder.