Hello,
The attached file mirrors a real world problem I am having with a budget file at work. I am using Excel 2016 on my pc. I do not understand how to prevent the budget sales number from multiplying by the number of actual sales transactions. I realize I need to group the data somehow, but cannot figure out how to do it. Please help.
Thanks,
Connie
Hi Connie,
You have two options:
1. Divide the total budget amount over the individual product items so the total of all items correctly adds up to your expected total budget amounts
2. Create a separate table containing the budget at the location level, if that's the lowest level you have the budget broken down by. Then use Power Pivot to summarise the data in a PivotTable. For this you need a dimension table to create the relationships between. If this means nothing to you, then option 1 is probably easier, otherwise you can learn Power Pivot here. In this course I cover this exact example.
Also note with option 2, that the lowest level you store the budget values is the lowest level you can analyse the actual vs budget at. e.g. if you only store budget at the location level of detail then you can't compare budget to actuals at the category, product or sales manager levels.
I hope that makes sense, but let me know if you have further questions.
Mynda
Thanks so much for your quick response!