Sales Aggregation & Commission Payments

[davidudow]

Hi,
I'm having some challenges and hoping someone in the community can help. I realize it's a big ask and I'm prepared to pay for help at a reasonable rate.

See the "Sales Test" workbook attached.

There are 2 sheets where data is ‘input’.
1. SalesData - this comes via CSV files that I download from a third party extranet every morning. My intention is to copy the rows from each day and add them to the bottom of the SalesData sheet. Note that any given day could have any number of rows. The SalesData feed is available daily. As each day passes, there will be an increasing number of rows.
2. ShowSchedule - This is a sheet that I have created and therefore it’s the only one of the source sheets that can be manipulated at will.

Summary of the business:
We run a number of roadshows at different warehouses. Shows are typically 14 days long, but not always. At these shows we sell a number of items (such as Apples, Juice, Candy). The shows are staffed by 1-3 commissioned demonstrators who can vary each day.

The SalesData sheet shows the date, warehouse, items & pricing
The ShowSchedule sheet shows the show start & end dates, creates a unique show ID, lists the names of the demonstrators working on any given day and the commission model under which they’re to be paid.

How I’d like to use the data:
Using filters, pivot tables, etc. I want to be able to view:
Sales $ by WarehouseID (See SalesWhse or SalesDate)
Sales Units by WarehouseID (See SalesUnits)
Sales by date, showing totals per warehouse and total units

I can do most of the above by WarehouseID, but not yet sure how by ShowID.

The hardest pieces for me, which I’m yet to be able to figure out are:

1. Commissions - I need to total sales by date by warehouse and correspond this to the list of demonstrators working on that given date, then multiply by the commission rate. This will give me total commission per demonstrator per day. At the end of each show period, I need to total the commissions earned by each demonstrator so I can pay them. In some cases, I make partial payments part way through a show, so a running total at any given time interval would be great too.

2. Linking ShowID’s to the totals. Right now I can get totals by warehouse just by using the data in the SalesData sheet. If however we do more than one show at a warehouse, the amounts will be summed together and I want to be able to see them separately.


Any help would be most appreciated.
Thanks,
David

[gak67]

Please Login or Register  to view this content.

in F9 and below will give the sales by day by warehouse.

[gak67]

Given you do not record Show ID on your sales data the only way to do it is by date, with something like this:

Please Login or Register  to view this content.

[davidudow]

Thanks SO much. Worked like a charm. Next up... show totals.

[gak67]

See post #3 above for show totals. Not sure where you want them to appear.

[davidudow]

Hi,
I've created a small new workbook to help with another topic, totals by person.
As you can see in the attached "Demo Totals Test", there can be any number of Demo names (david, bill, mary, sue, etc.) and the names can be in any of the demo columns (david is in both column A & B on different rows).
I need to get the totals for each demo name, but ideally without having to input their name as a criteria.

I know there's a SUMIF in there, maybe a SUMIFS, but I'm not sure how to populate the names without entering them.

Thanks!
David

[arlu1201]

Thread posted in Commercial Services forum, hence closed.