Power Pivot
City Financial Models • 1 November 2019
Powerful Excel feature that can save a lot of time.
The more recent versions of Excel have included Power Pivot as standard. It is an amazing feature and extremely powerful.
It is incredibly useful for models with a lot of data, such as KPI models and historical and budget models.
If you haven't seen how it works, then in short, it allows you to save data into memory through the power pivot data model. The data is saved in a table format and the tables can be linked in a similar way to a relational database.
Power pivot uses the DAX language to enable analysis across the tables. Additional columns can be added to the raw data and measures can also be calculated across the data.
The results can be shown in pivot table or can be accessed through the associated cube formulas, such as CUBEVALUE. These are very handy, as they report directly from the Data Model rather than the pivot table.
Power Pivot also provides the basis for data for very impressive dashboard reporting in Power BI.
It really can mean updating reports in minutes rather than hours if set up correctly.
Contact us
today to see how we can help your organisation develop robust financial models using Power Pivot.
This something we are starting to see as we work across a number of different versions of Excel. Recently, Microsoft rolled out something called dynamic array formulas in Excel 365, which is a substantial change from previous versions. Dynamic array formulas automatically spill in to neighbouring blank cells. They have also added some very powerful new functions such as FILTER, UNIQUE, XLOOKUP and XMATCH. Even better there is no need to enter array formulas with control + shift + enter (CSE) as in other versions. However, because of the way dynamic arrays work, there will be instances where models built in other versions will have formulas which unintentionally now try to spill or populate into neighbouring cells when opened in Excel 365. Hence the Spill error. To overcome this, the @ character is added automatically to stop formulas unintentionally spilling multiple results into neighbouring cells. Because dynamic arrays are only available in the subscription version at the moment and not in the other versions, these powerful new functions may cause some compatibility issues. In general, the @ character should overcome the compatibility issues with models build in other versions to work in Excel 365, however the dynamic array functions at present will not work in non-subscription versions so should be used with caution if you aren’t sure of your final audience.
Brexit Scenario Analysis
Sign conventions can cause confusion in the use and development of a model.
Goal Seek
TEXTJOIN - A new text function
Handy Excel navigation keyboard shortcuts, especially useful for laptops
SWITCH - An update to the CHOOSE function
NPV - Using it correctly
IFS - A new approach to nested IF functions
DATEDIF - A very useful Excel function to find the number of complete years, months or days between two dates.
