Budgeting Analysis and Pivot Tables

In the first lesson set I demonstrate a little trick that you can use to make an effective dashboard when working with multiple accounts. If you want to present alternative forecasting techniques including regression and time series analysis with many different accounts you may want to enter projected data directly from the dashboard. You may also want to include seasonal adjustments using different techniques. If you want to change the forecasting technique, the seasonal adjustment method or, most importantly, the amount of the budget, you can do this with spinner buttons. To do this requires some VBA attached to a spinner button. The VBA allows you to assign a number that depends on the account that you choose. You can do this with assigning a range name and then using the CELLS command.

The file below includes this spinner box technique and a number of other ideas that involve benchmarking to KPI’s, making presentations with bubble charts and with waterfall charts and hopefully some other ideas. Instead of using pivot tables which are very useful in budgeting, the LOOKUP function, the SUMIF function and the MATCH/INDEX/INDIRECT methods are demonstrated for budget analysis.

 

I keep forgetting how to use pivot tables.  They are obviously a very good tool for budget analysis and I think you have to mess around with them.  To remember how to work with pivot tables and make some graphs in budgets, I have included a few example files.

 

Excel File with Example of Conditional Formatting with Arrows that have Increments of Values

Excel File with Budget Database for Practice with Different Graphs and Alternative Analysis and Graphs

Excel File with Budget Database for Practice with Inserting and Creating Different Pivot Tables

Excel File with Simple Example of Creating Bubble Charts without Labels and Data Values

 

 

I have included a few videos that support the budget file below. The first video demonstrates how to construct the dashboard and add VBA to the spinner boxes as well as the other forms. The second set of videos walks through how to benchmark KPI’s using statistical analysis.