Making a formula with lots of variables

[Jaymie005]

So I'm attempting to put together an excel sheet that allows users to input the amount of money they want to make on their paycheck for that week. Agents make minimum wage + commission. What I want to return is the amount of hours, sales, and Daily's (an extra bonus for hitting a high target of sales in one day) the employee needs to get their desired pay for that week. The problem I'm having is the amount of variables. We have two different classes of sales, let's call them Q and M. They have different payout rates from each other. We have full-timers (FT) and part-timers (PT) in both classes. In addition, the Daily's are different between the FT and PT employees. Now add weekends and overtime into the mix.

That's when I realized I was in over my head. Obviously I'll have to set up parameters because we can't have individuals working X amount of hours with no sales to achieve their desired pay. The Excel worksheet needs to have a limit on the hours allowed with the rest of the pay to be made up in sales and kickers. Overtime is allowed, but only if agents are making 1 sale every 2 hours (running a .5) in M, or 1 sale an hour for Q (running a 1.0). OT in base salary is, of course, time and a half pay.

I'm still fairly new with Excel, but I understand basic IF functions, V/HLookUp, Cell Referencing, nesting, etc. Just the basics. I put in a modified version of the same sheet I'm working on. I made a note on Sheet 2 so you can visually see the main issue I'm dealing with at the moment. Any feedback would be greatly appreciated.

 

[Luke M]

Hi Jaymie,

I'm afraid that with such a complicated formula to figure out, we'll need more labels/info in your example sheet. I know it's probably obvious when you know what the numbers are, but from a new person's perspective, I don't know what the different tables are, or how they interact with each other. Could you add (lots) of comments to Sheet2 explaining what each thing is?

Overall, process for the setup would go something like this.

1. What are the inputs? What all values does the user give us (desired time, pay rate, PT vs FT, etc.)
2. How many different outputs do we have, and what are they (2..3?)
3. What are built-in limits? E.g., you mentioned hours vs. sales. What is minimum amount of sales someone can make per hour?

If we can get these defined, it will help a lot in setting up an overall formula to solve.

 

[Jaymie005]

1. Ok, FT work 40 hours a week. Saturday typically only allows 4 hours. We don't typically run on Sunday. PT works 25-29 hours a week, depending on whether or not they're scheduled for the weekend. The input will be desired pay, FT or PT, and how many hours they are willing to work.
2. Outputs will be needed hours, sales, and daily bonuses.
3. Limits... It's hard to place a limit on hours. As long as an employee is getting at least 1 sale every 2 hours, they're pretty much allowed as much OT as they want. If they can't get 1 sale within 2 hours, they get sent home. Even this is complex though (ex. 2 sales in first hour, means they can still have a total of 4 hours OT). Limit on daily's is 6, but only because we don't typically run on Sunday's. Employee's do have to reach they're weekly goal to get commission FT Q (30 sales) FT M (18 sales) PT Q (20 sales) or (24 sales with scheduled Sat.) PT M (11 sales) or (13 sales with scheduled Sat.).
4. Additional note, sales can be very random. Ex. When I was on phones, I had 1 sale on Monday, 7 sales on Tuesday, and 1 sale on Wednesday. I did this on PT hours and only needed 2 more sales for goal by the start of Thursday shift.
5. I put in some of what I had worked out on my own, and inserted comments. Nothing I have set in the worksheet has to remain concrete (except $8.25 an hour, and the $ coordinating with sale), I'm flexible and definitely open to ideas. Need any more information, let me know. Sorry about the lateness in my reply, I only get about 4-5 hours of non-work related time a day.

 

[Luke M]

In the report, does User input whether they are a Q or M, or should report give numbers for both? If the latter, I'm picturing end product is a table like this

With formulas filling in all blanks, showing how many hours you need at specified job type in first column to meet goal, how many sales, etc. Am I on the right track?

No need to apologize, it sounds like a daunting task, and you did a good job of providing information. I simply want to make sure I understand so that we don't waste time going down rabbit holes, and so that you get something that works.

 

[Luke M]

Here's my first crack. Needs some extra logic on when daily bonuses kick in, but hopefully I'm on the right track.

 

[Luke M]

Made some improvements to the daily bonuses. I think the next challenge may be to see how hours can change. Currently, from tables/info you gave, I'm only able to change # of sales. How can hours change? Can a FT work < 40? Do we need to model a FT + Saturday? Can a PT work > 25? Is there a minimum a PT has to work?

 

[Jaymie005]

Wow Luke, I really am in over my head. I appreciate the hard work and effort on this. It's way better than the goofy repeated IF nestings I was using.

FT and PT can't work less 'scheduled' hours. They can add more, but only if they're meeting the "1 sale every 2 hours" quota for OT. Yes to the model for FT + Saturday's and I made some adjustments and comments (red words for new comments) to the spreadsheet.

Also, was wondering if build string readout could be avoided for Q/M PT. Have it read the build string only if it meets the same criteria as FT? A PT would already know they're going to have to work more than their scheduled hours to achieve the pay they want. If it can be done, it would be better if they just saw how many extra hours, sales, and DP they'd need.

OT is going to be really awful. OT is time and a half, but it's time and a half + commission. This sentence isn't explaining it very well. Say I'm scheduled 25 hours this week at $8.25 per hour. Typically any OT would be around $12.38 (time and a half of $8.25) an hour. Here's the evil part. I'll use a M PT example

• $8.25*25 hours = $206.25.
• Our M PT got 5 DPs (so a total 20 sales $160 + DP bonus $50) = $210
• $206.25 + $160 = $366.25 (DPs are a constant and added on, but not a part of commission, that's why I left it out of the OT calculation)
  
• $366.25 / 25 = $14.65 and OT is based on this number, not the $12.38

Obviously this skews everything, because working OT with good commission is going to result in a lot of the pay. We really do reward the agents well. But the system makes things extremely tricky. For example OT is calculated at the end of the week. So if a PT who's not scheduled for the weekend, works Mon-Fri with the above example, decides to come in on Saturday for 4 hours and lands 2 sales (otherwise they'd get sent home). So now they get 25 hours base ($206.25) + 22 Sales ($170) / 25 hours = OT pay ($15.05) and OT pay * 4 hours = $60.20

So the agent gets $60.20 (OT) + $206.25 (base) + $170 (sales) + $50 (DP) = $486.45 for the week. To reverse calculate this in Excel gives me a headache just thinking about it.

 

[Luke M]

It's not that terrible to calculate. You did a good job explaining it and giving examples. Take a look at the attached. Added in OT hours as a new input. For FT, limited by input on Dashboard. For PT, limited by hard entry on calc table.

Does cause some interesting numbers...
New question.
With current setup, we run up to the maximum listed # of sales, AND THEN if goal not met, start adding OT. Not being in your business, I'm not sure if saying person has to 56 sales in 25 hours is reasonable...may need to set some sort of limit there as well.

But, one step at a time. Definitely got a little closer.

 

[Jaymie005]

XD I should show you the other idea I came up with for this. I was actually going to go through and make an entire sheet calculated for each section Q FT, M FT, Q PT, M PT, Q PTW, and M PTW. That way I'd just have to VLookUp for each according to coordinated calculations I'd done. It would take FOREVER, but it would be accurate, lol.

If an individual manages to land 56 sales in 25 hours in M, they would be outperforming to our wildest imaginations. Same with Q. Some struggle just to meet their weekly. Honestly, to give you a good idea. The best in M would run a sale an hour, or a 1.0 (one-dot-oh). The best in Q would run 1-2 sales an hour, or a 1.5 (one-dot-five). We rarely see anyone outperform those, although there have been cases (1.5 M, 2.0 Q).

We can reasonably expect to see M run .5, and a Q run 1.0. For example, I've been on phones (on and off) this week to help out. I run in M, not Q. In 7 hours, I got 8 sales. I worked on phones for 6 months before I was promoted, and I was recently promoted. So I know that these numbers are reasonable expectations, both from stats and personal experience. As long as they are taking their coaching, there's no reason for anyone to miss their .5 in M or their 1.0 in Q. If they can't, but are a good employee in all other respects, we send them out to a customer service department.

So after that monologue, and to finally answer your question, lol... a limit would be based on our most common stats from our best individuals. 1.0 for M, 1.5 for Q. Anything higher would be a rare case.

 

[Luke M]

Wonderful, I'll add that as another table. Max Sales will now use that number in it's formula.

Did some major rework on the macro, to take into affect two inputs. Favors adding more sales rather than more OT, which seems to make sense...if I was running a forecast, I'd rather the model predict me making another sale rather than just sitting at a desk. If you want, we could get fancy, and make this a toggle on dashboard..."Prefer Sales/Hours" and use that in the macro to decide which way to approach. Something to think about maybe...

Actually, thinking about it, as I write this, I've added an "estimated Sales" table to Dashboard. That way user can input how many sales they think they can do. If they put it lower than Max, calculations will use their estimate, but they can't exceed the max.

 

[Jaymie005]

I want to apologize for the delay. Employer had asked me to put this on temporary hold and gave me some other projects to work on. Those have now been completed. I had set aside this morning to work on it, but got sick last night and felt it best to get as much sleep as I could. If all goes well, I should have some time tomorrow to put towards this. Just didn't want you to think I'd lost interest.

 

[Luke M]

No worries Jaymie. Hope you are fully recovered. It's certainly an interesting/fun problem, I look forward to seeing it develop. I just hope it doesn't get quite as long/big as this project I'm working on:
http://chandoo.org/forum/threads/compile-data-and-produce-a-dashboard.15000/page-16

394 posts and counting...