Hi everyone, I'm not too good with Excel and I would greatly appreciate your help with this project.
I receive monthly P&L reports from 4 brands . Within each brand, there are 2-3 stores. These P&L reports are individual excel files.
My aim is to consolidate all the P&L reports into a single pivot table so that I can look at any opportunities within the brands (such as driving more sales, reducing cost). Once I have created the pivot table, I would like to drill down into individual stores within the brand to see how I can drive sales/cost reduction.
Please help me with these two areas:
1) How can I automate data extraction from the individual P&L reports from the brands into my master spreadsheet? Sadly, I am a noob with VBA.
2) I would like to create clustered stacked pivot charts per brand, comparing 3 revenue categories stacked in 1 column with 4 cost categories stacked in another column. The difference between the stacked revenue and the stacked cost column would be the profit/loss of the brand. Each clustered-stacked pivot chart shows the brands YTD performance, and it can then be drilled down into its respective stores.
Thank you very much in advance for your help!
I receive monthly P&L reports from 4 brands . Within each brand, there are 2-3 stores. These P&L reports are individual excel files.
My aim is to consolidate all the P&L reports into a single pivot table so that I can look at any opportunities within the brands (such as driving more sales, reducing cost). Once I have created the pivot table, I would like to drill down into individual stores within the brand to see how I can drive sales/cost reduction.
Please help me with these two areas:
1) How can I automate data extraction from the individual P&L reports from the brands into my master spreadsheet? Sadly, I am a noob with VBA.
2) I would like to create clustered stacked pivot charts per brand, comparing 3 revenue categories stacked in 1 column with 4 cost categories stacked in another column. The difference between the stacked revenue and the stacked cost column would be the profit/loss of the brand. Each clustered-stacked pivot chart shows the brands YTD performance, and it can then be drilled down into its respective stores.
Thank you very much in advance for your help!
