Working with Sliders and Ratios

[adam.sherratt]

Ayup!
I am currently working on a Capacity Planning project for my office,
and i'm interested to hear your thoughts on the best way to approach
something. here's the scenario;
The database is used to record individual workpackets, start dates,
users id's etc. Each work packet is classified by a complexity value,
1-5. These complexities are assigned a point value by the system to
allow us to conduct performance monitoring and planning. The points
equate to hours, so that any anyone time we know roughly how many hours

work we have in. The point value is constant per complexity, as per the

table below.
Complexity Points
1 1
2 3
3 12
4 36
5 72

From experience we know roughly how many of each complexity we will be

receiving in a month, and so how many points we get in.

Now, heres the situation. We are expecting an uplift in work load,
about 30-40 rise. We therefore need to predict the total ammount of
points that we will be receiving, and therefore how many more staff we
will need. Simple as that? Not quite! We are not sure whether the
increase in work load will be to the same ratios we have at the moment.

S for example, we need to understand what an increase in the numbers
of complexity 5's would mean to us. Equally, what would happen if
there was a large amounts of complexity 1 projects coming in, and a
rise in the numbers of 3's. Another idea is that the complexity 5's
might increase, but the 1's decrease as well, so the total number of
jobs we receive stay the same, but the points change significantly.
To try and help with this, I have set up a form, that has the number of

each job type received in the last year, the % of total amount, and
their point values. I have then set up 5 sliders, one for each
complexity, that are preset to the % ratio. What I want to do is be
able to drag a slider bar to simulate an increase in that work stream.
I can do this part quite well, i can get the form to msgbox me with the

new points total. This however is increasing the number of jobs. Any
suggestions on how I could get the system to adjust the other sliders
down when I increase one of the other sliders? I thought about just
displaying the total % and getting the user to balance the other
sliders until we hit 100%, but it would be great if they could lock
some of the bars, and the others adjust automatically.
i realise this is a long post, so i appreciate you reading it. i'd
appreciate it even more if you could help me out with your ideas. I
can probably do this, but I want to see others views, or whether anyone

else has done anything similar.

[Smartin]

ad***********@gmail.com wrote:

Ayup!
I am currently working on a Capacity Planning project for my office,
and i'm interested to hear your thoughts on the best way to approach
something. here's the scenario;
The database is used to record individual workpackets, start dates,
users id's etc. Each work packet is classified by a complexity value,
1-5. These complexities are assigned a point value by the system to
allow us to conduct performance monitoring and planning. The points
equate to hours, so that any anyone time we know roughly how many hours

work we have in. The point value is constant per complexity, as per the

table below.
Complexity Points
1 1
2 3
3 12
4 36
5 72

From experience we know roughly how many of each complexity we will be

receiving in a month, and so how many points we get in.

Now, heres the situation. We are expecting an uplift in work load,
about 30-40 rise. We therefore need to predict the total ammount of
points that we will be receiving, and therefore how many more staff we
will need. Simple as that? Not quite! We are not sure whether the
increase in work load will be to the same ratios we have at the moment.

S for example, we need to understand what an increase in the numbers
of complexity 5's would mean to us. Equally, what would happen if
there was a large amounts of complexity 1 projects coming in, and a
rise in the numbers of 3's. Another idea is that the complexity 5's
might increase, but the 1's decrease as well, so the total number of
jobs we receive stay the same, but the points change significantly.
To try and help with this, I have set up a form, that has the number of

each job type received in the last year, the % of total amount, and
their point values. I have then set up 5 sliders, one for each
complexity, that are preset to the % ratio. What I want to do is be
able to drag a slider bar to simulate an increase in that work stream.
I can do this part quite well, i can get the form to msgbox me with the

new points total. This however is increasing the number of jobs. Any
suggestions on how I could get the system to adjust the other sliders
down when I increase one of the other sliders? I thought about just
displaying the total % and getting the user to balance the other
sliders until we hit 100%, but it would be great if they could lock
some of the bars, and the others adjust automatically.
i realise this is a long post, so i appreciate you reading it. i'd
appreciate it even more if you could help me out with your ideas. I
can probably do this, but I want to see others views, or whether anyone

else has done anything similar.

Sounds like a fun project (^:

Sorry if I have missed the point here, but it seems to me setting other
slider values is the trivial part of your problem: sldComplexity1.Value
= X or some such. The real problem is knowing what X should be, and you
haven't said exactly how increasing counts of one type should affect the
others. Here are my rambling thoughts about that.

You do know from experience what the ratios are like, but if your aim in
simulation is to keep the overall number of jobs the same then by
definition the ratios will change. The question becomes how do you want
to do the offset? If for example increasing one complexity type is
balanced only by a decrease of one other type it is simple to compute
how much the other type should change, you only need to know which one
to change. If however you need to offset an increase of one by a
decrease of two others, you have two questions to answer. Which two to
choose, and how much should they change? Should they split the
difference, or do something more complicated?

If the simulation will keep the ratios the same, then increasing one
type will increase all the others, as well as the total workload. If 5's
increase 10% for instance, then all others should increase 10% as well
to keep the ratios the same.

So there are some questions only you can answer about the business rules
for your simulation. Do you expect ratios to remain the same? Or does
more 3's drive down 1's but marginally increase 4's? Once you define
these relationships then you can work out the formulas. On that note,
Excel might be a better analysis tool for all the number crunching.

Hope this helps
--
Smartin

[adam.sherratt]

thanks for taking the time to think this over:)

The problem with the simulation is that all of the points you raised, i
need to do! I want to see what happens if the number of points remains
the same, but the ratios change, I also want to see what happens if I
change the ratios. What I thought i would do was have a set of "lock"
buttons. Then, if i set the total pot to 130% I could distribute that
to the "unlocked" sliders and thus recalcualte the points. for more
"random" changes, I will simply use the sliders, reporting on the
current total in a box. that way I can make more dynamic changes
within each complexity, and still ensure that I am within the 130%
point.

Sorry if that makes no sense, i'm still trying to get my head around it
all!

Thanks again

[PC Datasheet]

I suggest that you are using the wrong software. Your problem is a classic
problem for Excel. Check into Goal Seek under Tools. I can help you with
this if you need help. My fees are very reasonable.

--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications
re******@pcdatasheet.com
www.pcdatasheet.com

<ad***********@gmail.com> wrote in message
news:11**********************@o13g2000cwo.googlegr oups.com...

Ayup!
I am currently working on a Capacity Planning project for my office,
and i'm interested to hear your thoughts on the best way to approach
something. here's the scenario;
The database is used to record individual workpackets, start dates,
users id's etc. Each work packet is classified by a complexity value,
1-5. These complexities are assigned a point value by the system to
allow us to conduct performance monitoring and planning. The points
equate to hours, so that any anyone time we know roughly how many hours

work we have in. The point value is constant per complexity, as per the

table below.
Complexity Points
1 1
2 3
3 12
4 36
5 72

From experience we know roughly how many of each complexity we will be

receiving in a month, and so how many points we get in.

Now, heres the situation. We are expecting an uplift in work load,
about 30-40 rise. We therefore need to predict the total ammount of
points that we will be receiving, and therefore how many more staff we
will need. Simple as that? Not quite! We are not sure whether the
increase in work load will be to the same ratios we have at the moment.

S for example, we need to understand what an increase in the numbers
of complexity 5's would mean to us. Equally, what would happen if
there was a large amounts of complexity 1 projects coming in, and a
rise in the numbers of 3's. Another idea is that the complexity 5's
might increase, but the 1's decrease as well, so the total number of
jobs we receive stay the same, but the points change significantly.
To try and help with this, I have set up a form, that has the number of

each job type received in the last year, the % of total amount, and
their point values. I have then set up 5 sliders, one for each
complexity, that are preset to the % ratio. What I want to do is be
able to drag a slider bar to simulate an increase in that work stream.
I can do this part quite well, i can get the form to msgbox me with the

new points total. This however is increasing the number of jobs. Any
suggestions on how I could get the system to adjust the other sliders
down when I increase one of the other sliders? I thought about just
displaying the total % and getting the user to balance the other
sliders until we hit 100%, but it would be great if they could lock
some of the bars, and the others adjust automatically.
i realise this is a long post, so i appreciate you reading it. i'd
appreciate it even more if you could help me out with your ideas. I
can probably do this, but I want to see others views, or whether anyone

else has done anything similar.