Windows 7 - Help decipher this code PLEASE!!!

Asked By Talon213 on 09-Aug-12 01:05 PM
Hi All,
So I am new to this forum, but it looks really helpfull. I am pretty good
at excel, not awesome but I get by. However i am working with a sheet
that someone eles has set up and i'm having issues figuring out
something. So I am hoping and crossing my fingers that someone can
explain this formula to me in layman's terms, I need to adjust it, but
have to know what it means first lol. HELP PLEASE!!!


=(C6/(C3*9)) * 1.05

this is supposed to help calculate how many employees we need to meet
our work load based on utilization and effenciency. Laborers Needed  @
90% Utilization (includes 6% PTO).




--
Talon213




joeu2004 replied to Talon213 on 09-Aug-12 07:38 PM
Well, you do not make it easy by not telling us what is in C6 and C3, and
what those values represent.

Presumably, C6/C3 is the number of laborers at 100% utilization and no PTO.

Then, C6/C3/0.9 -- which can be written C6/(C3*0.9) -- would be the number
of laborers at 90% utilization.

I can only assume that was written effectively C6/C3/9 (9 instead 0.9)
because C6 is scaled by 10 or C3 is scaled by 1/10.  That is, C6/C3 is 10
times the actual number of laborers at 100% for some reason(!).

(Alternatively, either the original formula is correct, or you miswrote it
here and "*9" is really "*0.9" or "*90%".0

Then, C6/C3/9 * 1.05 -- which can be written (C6/(C3*9))*1.05 -- would
account for the PTO.  However, I quibble with the number.  I wonder if 1.05
is rounded and based on PTO at 4.31% to 5.21%.

For 6%, the PTO factor would be 1/(1-6%), which is about 1.063830.
(Rounding intermediate values is a bad idea.  It introduces quantization
errors.)

In summary, this is how I would write the formula, assuming A1 is the
%utilization (written 0.9 or 90%) and A2 is the %PTO (written 0.06 or 6%):

=ROUND(C6/C3/10/A1/(1-A2),0)

Rounding of some form is prudent.  After all, you cannot hire 12.3 people
;-).

It might be prudent use ROUNDUP instead of ROUND.  That's a judgment call,
balancing potentially increased labor costs (due to ROUNDUP) v.
understaffing (due to ROUND down 50% of the time).

Again, I throw "/10" into this because it appears that C6/C3 is 10 times the
number of laborers.  I would prefer not to need that; that is, I would
prefer that C6/C3 is the actual number of laborers.

Does that help?

If you have any doubts, please include the data that you neglected to
mention in your next posting.  That is:  what is in C6; what is in C3; what
do those values represent; and copy-and-paste the formula into your posting
instead of retyping it to avoid any misleading typos.
Stan Brown replied to Talon213 on 10-Aug-12 02:33 AM
I do not think you quoted the actual formula, but what you write means
1.05".


--
Stan Brown, Oak Road Systems, Tompkins County, New York, USA
http://OakRoadSystems.com
Shikata ga nai...