£2.95
Introducing 4 Row Pivot Table (Sorted) for Excel
This Software will demonstrate a 'Solution' you can use to build a sorted, rolling 4 Row Pivot Table viewing Products with Sales, Forecast, Variance and Tracking % (4 Key Figures), from Sales and Forecast data using formula and a single Macro to update the Pivot Table week Fields. It allows you to keep your source data in Columns instead of transposing the weeks into Rows (a standard way of using rolling weeks is to have all of the weeks down the Rows and then to add a single 'Weeks' field into the Columns section). This method does not use any Calculated Items or Fields in the Pivot Table and allows you to Slice any data as long as it is on a 1-2-1 basis with the Products
Each Product will have 4 Rows (Key Figures) in this order Forecast, Sales, Variance (the difference between the Forecast and the Sales) and the Tracking % (the consumption of the Sales against the Forecast as a percentage). The Conditional Formatting being measured for both +/- is Green (Okay) for within a 30% tolerance from 100%, Warning (Amber) for up to 70% tolerance from 100% and Red (Bad) for > 100% tolerance. Therefore, at a glance we can see that if a Product is all Green then the Sales are pretty much in line with the Forecast and if it is all Red then the Sales are most definitely not in line with the Forecast. The order of the 4 Key Figures can be changed along with the Formula for consumption ie. you could swap it around and do a consumption of the Forecast against the Sales
Prerequisites
32bit or 64bit Excel
Basic Excel skills
Windows PC · NOT a Mac!
No support is provided for customization of this Software
Online Documentation/Help/Features/Credits:
https://www.kubiszyn.co.uk/documentation/four-row-pivot-table.html
Please note: this Software is released under an MIT license and as such you must attribute the author, Mark Kubiszyn whenever distributing or remixing the work