Help me develop complicated spreadsheet

February 23, 2016 7:06 AM

Note: NOT Homework. Real world application here. I have to come up with a spreadsheet based on a formula that allocates dollars to people based upon role and participation. I'm good at Excel but this one is boggling my mind. Details and example inside

I need to come up with a spreadsheet for profit sharing among a team. The team has several people, and many fill multiple roles. So to use an example of a garage sale:

The garage sale happens weekly, so four times a month. Different people staff the garage sale.

There are multiple roles: Some people set-up the sale, others are cashiers, and then some do price research. Then there's the managers who oversee it.

The payout is by role. So of the total Garage Sale income IN THE MONTH (not per sale), the manager(s) get 15%, the set-up people get 20%, the cashiers get 35%, and the price researchers get 30%.

Some people work multiple roles (a cashier may also do set-up, etc). And some people may work all 4 garage sales that month, some just 1.

So the formula per role is:

Managers: (Total Income * .15) = Manager Role's total amount to divide among the managers.

But then if one manager worked all 4 shows, another manager assisted with 2 shows, and a third manager worked just 1. So there were 7 manager "shifts" total. That breaks down to about 14% per "shift".

So the first manager would get 57%, the second 29%, and the third 14%.

But the next month perhaps only 1 manager would work each show, so 4 shifts total, and that manager would get 100% of the manager's 15% take

Finally complicating things, the manager could also work set-up or as a cashier. So the manager would get their portion of the 15%, but also a portion of each of the other roles.

Then each person only gets paid if their total amount is over $50. If it's under $50 the amount rolls over to the next month until they get $50. So I need to store a running total of how much they earned, and how much was paid out to them.

I figure there must be a way to do this in a spreadsheet, but I'm at a loss on how to store this data. I'd like the spreadsheet to store a year's worth of data.

I am imaginging rows for each person, columns for each month, but then...columns for each role as well? And how do we count how many shows were done in that month, and store that month's specific income?

Anyone able to suss this out?

posted by arniec to Computers & Internet (9 answers total)

Well, I think you start by doing the calculations such that you have one line per sale and record the numbers of managers, cashiers, etc assuming they are all different people. 3 managers and 3 cashiers at this sale. 2 managers and 9 cashiers at the other sale. So for this month you have 5 managers and 10 cashiers, which means each manager gets $10 and each cashier gets $4.

THEN you have another sheet that references this one (or another set of columns) where you note that, in February, Abel worked 2 manager and 1 cashier shift (so he gets $24) while Bethany worked 1 manager and 4 cashier shifts (so she gets $26).

In summary: First you calculate the pay per month for each shift type, then you calculate how much each person gets paid by adding together the shifts they worked.

You could create a third summary sheet if you liked, referencing the other sheets, that just lists how much each person got paid each month.
posted by telepanda at 7:23 AM on February 23, 2016

In broad outline:

Make a row for each event with columns for Person, Month, Role, Week. You can just keep dumping data into this table as it is generated.

Then use SUMIFS() and generate all the various numerators and denominators you need.
posted by paper chromatographologist at 7:26 AM on February 23, 2016

Standard disclaimer: What you're trying to do here is in many ways a database task. It might be easier to do it in a database. But it might not, so let's give it a whirl with Excel

(Post ramble note. I just sort of built this in my head stream of conciousness style, so sorry for the rambling)

So, we want to record a bunch of different data and then we want to do things with that data.

So probably sheet one, is your events.
Give each event a number in column A. Make this automatically increment.
The columns along are going to be things like, event date, event name, money earnt etc.

So you can chop this up as required (in a later sheet, apart from the event ID don't do any calculations here, or if you really need to keep it minimal, this is for input only) to get your profit per month.

There's a few ways of doing the next bit, depending on things like, total number of people, total number of roles they might fulfill per event, total number of people per event or whatever. But for now I'm going to work with the assumption that these are all potentially infinite and we'll see where we end up.

So we probably want a lookups sheet.
This has a table of people's names on it (give them an id in the same way as you gave events an ID), and a separate table of roles / role multipliers. (i.e. threeo columns, ColA = Role id, Col B = Role, Col C = value of role, i.e. Manager | 0.15)
This is just to keep your model consistent.

So, third sheet is a joiner sheet.
Here you have a table which has the columns:
Event|Person|Role
Use your ID numbers here.
(You don't need to use the IDs if you don't want, but I think you should. It can help with later lookups and avoid you mistyping things)

So you have one row for every role that each person worked.
i.e
Bake Sale 1 | Jeff | Set-Up (or the ids of those things)
Bake Sale 1 | Jeff | Manager
Bake Sale 1 | Kath| Cashier
Garage Sale | Steve | Set-Up
Garage Sale | Jeff | Manager
Garage Sale | Kath| Cashier

That should do it for Data in.
From here on in we're doing output.

So sheet 4
We have;
Month|Managers|Sales|Cashier (etc., one col per role. {I know I said I was assuming infinite roles above, but I take that back, I'm assuming under 200ish roles})

You can craft a lovely countif based on the shows in the month here and your Joiner table on sheet three, that gets you the total number of each role each month.

So under that, probably same sheet, same column layout we can calculate the percentage of the total profit which goes to managers etc, and put a dollar figure on that.

And then, under that, again same sheet, same column headings work out the dollar value per manager etc. per month (i.e. divide table one by table two)

On to Sheet 5!
Output sheet. here we bring it together.
A table of people down the rows and roles along the columns. Sum up roles per person per month, multiply by profit per role per month. Sum across the row and put it in a column at the end.
THEN next column over we have a month total. Add on the sum of all rows, add on the previous months total and if over 50 set to 0. If over $50 make a note on the next column that they got paid.
posted by Just this guy, y'know at 8:13 AM on February 23, 2016 [2 favorites]

(You might want to take a bit more space in sheet 5 and do a bit more explicit and therefore better carry over thing like scrittore suggested)
posted by Just this guy, y'know at 8:15 AM on February 23, 2016

That would be a clean and extensible and nicely normalized way to do this if it were in fact a database. I would also be a nightmare to maintain as a spreadsheet.

Working on the assumption that the number of team members is relatively small and that the team is relatively stable, I'd do it all in one big sheet, with a set of repeating columns for each team member. Adding a new team member would then just be a matter of copying an existing team member's columns and doing a paste-special of formats and formulas, but not data, to the right of the last existing team member's columns.

So an event is a row, and has columns for name, date, total takings and whatnot. It also has, for each team member, a set of columns labelled S for setup, C for cashier, P for price research and M for manager. Having two header rows and merging the cells above the S C P M subheads to make an overarching header cell for the name would keep things tidy.

If you're going to go for simplicity, the intersection of a row and a team member's S, C, P or M column has a 1 in it if that team member worked that role for that event; blank otherwise. If you want to get fancy, you could put the number of hours worked in that role for that event instead.

After every month's worth of events comes a couple of rows for accumulated earnings and money paid out to each person that month. Break it up with rows of blank cells to keep it navigable.
posted by flabdablet at 9:36 AM on February 23, 2016 [1 favorite]

Yeah, I definitely approached as a data modeller, it's a little over-engineered and you can see my database bias at work. :)
I would argue that it's not necessarily that hard to maintain, but you do end up with two very active input sheets, so I can see why you might want to not do it that way.

That said, if you have a small number of roles and a small, relatively stable team I would unquestionably go with flabdablet's approach.
That said, I'd still want to have the big input sheet, a calculations sheet and an output sheet (and a check totals sheet). But now I'm getting a bit philosophical...
posted by Just this guy, y'know at 9:50 AM on February 23, 2016

Best answer: Here's a solution using Google Spreadsheet. I like spreadsheets and I cannot lie.

It's a bit slow, because as others have said, this is really a job for a database, but as long as you don't mind waiting 5 seconds for the thing to update after adding new data, it should work ok.
posted by Salvor Hardin at 12:14 PM on February 23, 2016 [1 favorite]

Somehow my eye skipped over the "roll over if amount is under $50" requirement. If you plan to actually use that spreadsheet, I could add that in.
posted by Salvor Hardin at 12:19 PM on February 23, 2016

Response by poster: Salvor, that is awesome! I had started to pursue the database route but having this in Google Docs is exactly what I wanted for global accessibility. So thank you again! If you have time to add in the "roll over" feature, that would be awesome. If not, you've gotten me 80% of the way there.
posted by arniec at 12:33 PM on February 23, 2016