Foro Excel

Teniendo un total de Unidades anual mas una tabla porcentual por mes. Ahora tengo que efectuar la distribución mensual del total de unidades tomando en cuenta el porcentaje mensual de la tabla.

Las premisas para la distribución son:

1.- Aunque obvio pero la suma mensual debe de ser igual al presupuesto anual.
2.- Las Unidades no pueden contener decimales.
3.- No pueden existir unidades negativas.
4.- No puede existir un mes que claramente tenga un alto % del Total anual
5.- Se prefiere tener una distribución simétrica en lugar de los puntos 3 y 4
6.- El mes que se utilice para cuadrar la suma de los meses con el presupuesto anual deberá de ser el mes con el % más alto en la tabla

Nota: La tabla de porcentajes varí­a de acuerdo a cada sucursal.

Sin mas agradezco cualquier ayuda para eficientar la formula que se encuentra en las celdas con fondo amarillo.

Saludos

Los retoques que incorporé son insignificantes, pero bueno por ahora no se me ocurre nada mejor....
Obviamente, no logro entender acabadamente el asunto, aun presiento que la explicación que aportas deberia alcanzarme.

Por ejemplo: ¿qué significa el 24 que usas en el primer criterio de la formula?

GALI

Me parece que los retoques que mencionas tienen lógica y ayudaran en algo al tiempo de cálculo.

La condición del 24 es porque he notado que tratar de distribuir menos de esa cantidad en un periodo de doce meses en base a porcentajes, siempre resulta con picos muy altos en algún mes o números negativos en otro mes cuando intentas ajustar el presupuesto anual. Por esa razón prefiero hacer la distribución en base al ranking del porcentaje de cada mes (segunda parte de la formula).

Sin embargo cuando el total es 24 o mayor si conviene utilizar la distribución mensual en base a los porcentajes mensuales de la tabla. Y solamente se tiene que tener cuidado en que mes se efectúa el ajuste para cuadrar el presupuesto anual.

Lo de la selección del mes para hacer el ajuste pudiera parecer no importar pero este proceso se utiliza para distribuir el total de servicios anuales de 140 sucursales de 10 diferentes tipos de servicios. Por lo cual si se tuviera que ajustar un servicio por cada sucursal y cada tipo de servicio y escogiéramos un mes en particular para todos estarí­amos incrementando el presupuesto de ese mes en aproximadamente 700 servicios 140 * 10 * 50% (no darle mucha importancia al 50% es cuestión de probabilidad).

Tomando en cuenta ese impacto y pensando que muchos empleados obtienen bonos en base al alcance del presupuesto mensual y trimestral creo que es una obligación realizar la distribución lo más cuidadosamente posible.

Se agradecen las aportaciones.

Saludos

Hola saile , no estoy muy de acuerdo con el sistema planteado a la hora de hacer los repartos , no me parece equitativo redondear la cantidad beneficiando siempre al valor mas alto , sino que hay que redondear con el que se queda mas cerca de conseguir el coste de cada punto , en el archivo lo intento explicar paso por paso, si te parece logico , intento desarrollar una matricial a ver si consigo hacerlo todo en una sola celda.
Ahora voy a sacar a paseo a la familia luego me pongo con ello.

Un saludo

Hola chicos,

Seguramente se me escaopa algo, pero por si las moscas...

cambiando el ultimo parametro de JERARQUIA se consigue un resultado muy parecido al posteado por Sailepaty pero con menor DESVEST.

Hola

El planteamiento de JBF99 me parece bastante interesante ya que en cuestión de distribución me parece lo mas equitativo en base a los porcentajes de la tabla.

Por otro lado la opción de KL funciona bastante bien para todos los valores menores de 24 aunque habrí­a que efectuarle algunos ajustes para valores mayores.

De antemano les agradezco la ayuda y la puerta sigue abierta para todo lo que se les ocurra, en cuanto me den un break en casa me pongo a compilar un archivo con las diferentes propuestas.

Saludos

Hola salepaty,

Se puede saber en que consiste el fallo para los valores superiores a 24? En el archivo adjunto no consigo ver nada.

Tengo que reconocer que no tengo internalizada la cuestión, pero sin embargo me gustarí­a opinar.
En la primera formulacion de Saile yo entendi que los valores de distribucion mensual no solo eran funcion de "el orden del porcentual de distribucion" JERARQUIA(F4:Q4), sino que ademas intervení­a su "masa", es decir con Porcentajes medianamente "Planchados" la diferencia es imperceptible, pero si las masas fueran sensiblemente oscilantes con "picos y valles", el orden jerarquico de los porcentajes puede mantenerse inalterable, pero no asi las masas. Esto obviamente estaria medido por la Desviacion Estandard de la serie de 12 elementos, pero no veo, o tal vez no me doy cuenta donde haces jugar KL, las masas, o tal vez la desviacion estandard de la serie, en la asignacion de unidades:
a) 1/12 +0; (1/12)+0.01; (1/12)+0.02.............; (1/12)-0.02; (1/12)-0.01
b) 0.01; 0.02; 0,03: 0.04...........................0.11; 1-Suma de anteriores
Con la asignacion en BASE A JERARQUIA me da la impresion que tanto con una como con la otra se obtendrán identicos resultados.

¿Es esto lo esperado?


GALI

Perdon las series que propongo como "extremos de analisis", no son series de unidades quise decir series de porcentajes.

GALI

Tienes razon GALI - se me ha olvidado asignar los pesos. Ya me parecia demasiado facil la solucion - me he pasado de listo esta vez

Manana le dare otra vuelta - hoy ya toca ir a la cama.

Maldito insomnio!!!

A ver esto

Es el tipico reparto q se usa en las votaciones con los escaños .
Aqui va una posible solucion .

La explicacion basica es mas o menos esta.

Hola jbf99,

Las 4 hojas del libro adjunto muestran algunas de las situaciones en las que tu formula falla