Solver Samples

PaseoPaseo por Microsoft Excel SolverMesT1T2T3T4TotalTemporada0.91.10.81.2Unidades vendidas3,5924,3903,1924,78915,962Ingresos por ventas143,662 $175,587 $127,700 $191,549 $638,498 $Cdigos de colorCosto de las ventas89,789109,74279,812119,718399,061Margen bruto53,87365,84547,88771,831239,437Celda objetivoPersonal ventas8,0008,0009,0009,00034,000Celdas a cambiarPublicidad10,00010,00010,00010,00040,000Costos fijos21,54926,33819,15528,73295,775RestriccionesCosto total39,54944,33838,15547,732169,775Beneficios14,324 $21,507 $9,732 $24,099 $69,662 $Margen de beneficio10%12%8%13%11%Precio del producto40.00 $Costo del producto25.00 $En los ejemplos siguientes se muestra la forma de trabajar con este modelo para resolver uno o variosvalores para maximizar o minimizar otro valor, escribir y cambiar restricciones y guardar un problema modelo.FilaContieneExplicacin3Valores fijosFactor de temporada: las ventas son mayores el los trimestres 2 y 4,y menores en los trimestres 1 y 3.5=35*B3*(B11+3000)^0,5Prediccin de las unidades vendidas cada trimestre: la fila 3contiene el factor de temporada; la fila 11 contiene el costo depublicidad.6=B5*$B$18Ingresos por las ventas: prediccin de las unidades vendidas (fila 5)por el precio (celda B18).7=B5*$B$19Costo de las ventas: prediccin de las unidades vendidas (fila 5)por el costo del producto (celda B19).8=B6-B7Margen bruto: ingresos por las ventas (fila 6) menos el costo delas ventas (fila 7).10Valores fijosGastos del personal de ventas.11Valores fijosPresupuesto de publicidad (aprox. 6,3% de las ventas).12=0.15*B6Gastos fijos corporativos: ingresos por las ventas (fila 6) por el 15%.13=SUMA(B10:B12)Costo total: gastos del personal de ventas (fila 10) mspublicidad (fila 11) ms gastos fijos (fila 12).15=B8-B13Beneficios: margen bruto (fila 8) menos el costo total(fila 13).16=B15/B6Margen de beneficio: beneficio (fila 15) dividido por los ingresospor las ventas (fila 6).18Valores fijosPrecio por producto.19Valores fijosCosto por producto.ste es un modelo tpico de mercadotecnia que muestra el crecimiento de las ventas a partir de una cifra base(quizs debido al personal de ventas) adems del incremento en publicidad, pero con una cada constanteen el flujo de caja. Por ejemplo, los primeros 5.000 $ de publicidad en el T1 producen aproximadamenteun incremento de 1.092 unidades vendidas, pero los 5.000 $ siguientes producen cerca de 775 unidadesadicionales.Puede utilizar Solver para averiguar si el presupuesto publicitario es escaso y si la publicidad debe orientarsede otra manera durante algn tiempo para sacar provecho del factor de cambio de temporada.Resolver un valor para maximizar otroPuede utilizar Solver para determinar el valor mximo de una celda cambiando el valor de otra. Las dos celdasdeben estar relacionadas por medio de las frmulas de las hoja de clculo. Si no es as, al cambiar el valorde una celda no cambiar el valor de la otra celda.Por ejemplo, en la hoja de clculo de muestra se desea saber cunto es necesario gastar en publicidad paragenerar el mximo beneficio en el primer trimestre. El objetivo es maximizar el beneficio cambiando los gastosen publicidad.nEn el men Herramientas, haga clic en Solver. En el cuadro Celda objetivo,escriba b15 o seleccione la celda B15 (beneficios del primer trimestre) en la hoja de clculo.Seleccione la opcin Mximo. En el cuadro Cambiando las celdas, escriba b11 oseleccione la celda B11 (publicidad del primer trimestre) en la hoja de clculo.Haga clic en Resolver.Aparecern mensajes en la barra de estado mientras se configura el problema y Solver empezar a funcionar.Despus de un momento, aparecer un mensaje advirtiendo que Solver ha encontrado una solucin.El resultado es que la publicidad del T1 de 17.093 $ produce un beneficio mximo de 15.093 $.nDespus de examinar los resultados, seleccione Restaurar valores originales y hagaclic en Aceptar para desestimar los resultados y devolver la celda B11 a su estado original.Restablecer las opciones de SolverSi desea restablecer las opciones del cuadro de dilogo Parmetros de Solver a su estado original demanera que pueda iniciar un problema nuevo, puede hacer clic en Restablecer todo.Resolver un valor cambiando varios valoresTambin puede utilizar Solver para resolver varios valores a la vez para maximizar o minimizar otro valor. Porejemplo, puede averiguar cul es el presupuesto publicitario de cada trimestre que produce el mayor beneficiodurante el ao. Debido a que el factor de temporada en la fila 3 se tiene en cuenta en el clculo de la unidad deventas en la fila 5 como multiplicador, parece lgico que se gaste ms del presupuesto publicitario en el trimestre T4cuando la respuesta a las ventas es mayor, y menos en el T3 cuando la respuesta a las ventas es menor. UtiliceSolver para determinar la mejor dotacin trimestral.nEn el men Herramientas, haga clic en Solver. En el cuadro Celda objetivo,escriba f15 o seleccione la celda F15 (beneficios totales del ao) en la hoja de clculo.Asegrese de que la opcin Mximo est seleccionada. En el cuadroCambiando las celdas, escriba b11:e11 o seleccione las celdas B11:E11(el presupuesto publicitario de cada uno de los cuatro trimestres) en la hoja de clculo.Haga clic en Resolver.nDespus de examinar los resultados, haga clic en Restaurar valores originales y hagaclic en Aceptar para desestimar los resultados y devolver a las celdas sus valores originales.Acaba de solicitar a Solver que resuelva un problema de optimizacin no lineal moderadamente complejo, es decir,debe encontrar los valores para las incgnitas en las celdas de B11 a E11 que maximizan los beneficios. Se tratade un problema no lineal debido a los exponentes utilizados en las frmulas de la fila 5. El resultado de estaoptimizacin sin restricciones muestra que se pueden aumentar los beneficios durante el ao a 79.706 $ si se gastan89.706 $ en publicidad durante el ao.Sin embargo, problemas con un modelo ms realista tienen factores de restriccin que es necesario aplicar aciertos valores. Estas restricciones se pueden aplicar a la celda objetivo, a las celdas que se van a cambiar o acualquier otro valor que est relacionado con las frmulas de estas celdas.Agregar una restriccinHasta ahora, el presupuesto recupera el costo publicitario y genera beneficios adicionales, pero se est alcanzadoun estado de disminucin de flujo de caja. Debido a que nunca es seguro que el modelo de ventas ypublicidad vaya a ser vlido para el prximo ao (de forma especial a niveles de gasto mayores), no parece prudentedotar a la publicidad de un gasto no restringido.Supongamos que desea mantener el presupuesto original de publicidad en 40.000 $. Agregue el problemade restriccin que limita la cantidad en publicidad durante los cuatro trimestres a 40.000 $.nEn el men Herramientas, haga clic en Solver y despus en Agregar.Aparecer el cuadro de dilogo Agregar restriccin. En el cuadro Referencia decelda, escriba f11 o seleccione la celda F11 (total en publicidad) en la hoja de clculo.La celda F11 debe ser menor o igual a 40.000 $. La relacin en el cuadro Restriccin es