I am trying to create an Excel formula that will allocation daily sales
results for technicians. Each technician has an individual daily sales goal.
More than 1 technician can work a job, and a job can take more than one day.
For example, I have a primary tech that has a $1,000 day sales goal and a
helper with a $500 day sales goal. They both work 8 hours on day one, and on
day 2, the primary works 8 hours and the helper works 4. I want to base their
daily sales on their proportion of each's daily sales goals and the number of
hours worked.
For example, if the total job were $6,000, on day one, the primary tech
would be credited with $1,500 in sales and the helper $1,000. They would both
be over by 50%. Day 2 is where I have problems. The helper only works 50% of
the time, so his Daily Sales Goal for that job would be 50% of his daily
total. (Assume a maximum of 8 hours/day). How do I create a worksheet in
Excel with Mon - Sun days, where I can list all the techs, the total hours
spent each day and calculate their appropriate sales for each day. Assume
only one job per calculation.
Hope someone can help!
|
Similar Threads
