7.2: Hojas de cálculo para estimar costos
- Page ID
- 154323
\( \newcommand{\vecs}[1]{\overset { \scriptstyle \rightharpoonup} {\mathbf{#1}} } \)
\( \newcommand{\vecd}[1]{\overset{-\!-\!\rightharpoonup}{\vphantom{a}\smash {#1}}} \)
\( \newcommand{\id}{\mathrm{id}}\) \( \newcommand{\Span}{\mathrm{span}}\)
( \newcommand{\kernel}{\mathrm{null}\,}\) \( \newcommand{\range}{\mathrm{range}\,}\)
\( \newcommand{\RealPart}{\mathrm{Re}}\) \( \newcommand{\ImaginaryPart}{\mathrm{Im}}\)
\( \newcommand{\Argument}{\mathrm{Arg}}\) \( \newcommand{\norm}[1]{\| #1 \|}\)
\( \newcommand{\inner}[2]{\langle #1, #2 \rangle}\)
\( \newcommand{\Span}{\mathrm{span}}\)
\( \newcommand{\id}{\mathrm{id}}\)
\( \newcommand{\Span}{\mathrm{span}}\)
\( \newcommand{\kernel}{\mathrm{null}\,}\)
\( \newcommand{\range}{\mathrm{range}\,}\)
\( \newcommand{\RealPart}{\mathrm{Re}}\)
\( \newcommand{\ImaginaryPart}{\mathrm{Im}}\)
\( \newcommand{\Argument}{\mathrm{Arg}}\)
\( \newcommand{\norm}[1]{\| #1 \|}\)
\( \newcommand{\inner}[2]{\langle #1, #2 \rangle}\)
\( \newcommand{\Span}{\mathrm{span}}\) \( \newcommand{\AA}{\unicode[.8,0]{x212B}}\)
\( \newcommand{\vectorA}[1]{\vec{#1}} % arrow\)
\( \newcommand{\vectorAt}[1]{\vec{\text{#1}}} % arrow\)
\( \newcommand{\vectorB}[1]{\overset { \scriptstyle \rightharpoonup} {\mathbf{#1}} } \)
\( \newcommand{\vectorC}[1]{\textbf{#1}} \)
\( \newcommand{\vectorD}[1]{\overrightarrow{#1}} \)
\( \newcommand{\vectorDt}[1]{\overrightarrow{\text{#1}}} \)
\( \newcommand{\vectE}[1]{\overset{-\!-\!\rightharpoonup}{\vphantom{a}\smash{\mathbf {#1}}}} \)
\( \newcommand{\vecs}[1]{\overset { \scriptstyle \rightharpoonup} {\mathbf{#1}} } \)
\( \newcommand{\vecd}[1]{\overset{-\!-\!\rightharpoonup}{\vphantom{a}\smash {#1}}} \)
\(\newcommand{\avec}{\mathbf a}\) \(\newcommand{\bvec}{\mathbf b}\) \(\newcommand{\cvec}{\mathbf c}\) \(\newcommand{\dvec}{\mathbf d}\) \(\newcommand{\dtil}{\widetilde{\mathbf d}}\) \(\newcommand{\evec}{\mathbf e}\) \(\newcommand{\fvec}{\mathbf f}\) \(\newcommand{\nvec}{\mathbf n}\) \(\newcommand{\pvec}{\mathbf p}\) \(\newcommand{\qvec}{\mathbf q}\) \(\newcommand{\svec}{\mathbf s}\) \(\newcommand{\tvec}{\mathbf t}\) \(\newcommand{\uvec}{\mathbf u}\) \(\newcommand{\vvec}{\mathbf v}\) \(\newcommand{\wvec}{\mathbf w}\) \(\newcommand{\xvec}{\mathbf x}\) \(\newcommand{\yvec}{\mathbf y}\) \(\newcommand{\zvec}{\mathbf z}\) \(\newcommand{\rvec}{\mathbf r}\) \(\newcommand{\mvec}{\mathbf m}\) \(\newcommand{\zerovec}{\mathbf 0}\) \(\newcommand{\onevec}{\mathbf 1}\) \(\newcommand{\real}{\mathbb R}\) \(\newcommand{\twovec}[2]{\left[\begin{array}{r}#1 \\ #2 \end{array}\right]}\) \(\newcommand{\ctwovec}[2]{\left[\begin{array}{c}#1 \\ #2 \end{array}\right]}\) \(\newcommand{\threevec}[3]{\left[\begin{array}{r}#1 \\ #2 \\ #3 \end{array}\right]}\) \(\newcommand{\cthreevec}[3]{\left[\begin{array}{c}#1 \\ #2 \\ #3 \end{array}\right]}\) \(\newcommand{\fourvec}[4]{\left[\begin{array}{r}#1 \\ #2 \\ #3 \\ #4 \end{array}\right]}\) \(\newcommand{\cfourvec}[4]{\left[\begin{array}{c}#1 \\ #2 \\ #3 \\ #4 \end{array}\right]}\) \(\newcommand{\fivevec}[5]{\left[\begin{array}{r}#1 \\ #2 \\ #3 \\ #4 \\ #5 \\ \end{array}\right]}\) \(\newcommand{\cfivevec}[5]{\left[\begin{array}{c}#1 \\ #2 \\ #3 \\ #4 \\ #5 \\ \end{array}\right]}\) \(\newcommand{\mattwo}[4]{\left[\begin{array}{rr}#1 \amp #2 \\ #3 \amp #4 \\ \end{array}\right]}\) \(\newcommand{\laspan}[1]{\text{Span}\{#1\}}\) \(\newcommand{\bcal}{\cal B}\) \(\newcommand{\ccal}{\cal C}\) \(\newcommand{\scal}{\cal S}\) \(\newcommand{\wcal}{\cal W}\) \(\newcommand{\ecal}{\cal E}\) \(\newcommand{\coords}[2]{\left\{#1\right\}_{#2}}\) \(\newcommand{\gray}[1]{\color{gray}{#1}}\) \(\newcommand{\lgray}[1]{\color{lightgray}{#1}}\) \(\newcommand{\rank}{\operatorname{rank}}\) \(\newcommand{\row}{\text{Row}}\) \(\newcommand{\col}{\text{Col}}\) \(\renewcommand{\row}{\text{Row}}\) \(\newcommand{\nul}{\text{Nul}}\) \(\newcommand{\var}{\text{Var}}\) \(\newcommand{\corr}{\text{corr}}\) \(\newcommand{\len}[1]{\left|#1\right|}\) \(\newcommand{\bbar}{\overline{\bvec}}\) \(\newcommand{\bhat}{\widehat{\bvec}}\) \(\newcommand{\bperp}{\bvec^\perp}\) \(\newcommand{\xhat}{\widehat{\xvec}}\) \(\newcommand{\vhat}{\widehat{\vvec}}\) \(\newcommand{\uhat}{\widehat{\uvec}}\) \(\newcommand{\what}{\widehat{\wvec}}\) \(\newcommand{\Sighat}{\widehat{\Sigma}}\) \(\newcommand{\lt}{<}\) \(\newcommand{\gt}{>}\) \(\newcommand{\amp}{&}\) \(\definecolor{fillinmathshade}{gray}{0.9}\)Objetivos de aprendizaje
- Dar formato a una hoja de cálculo para que sea más legible y profesional en apariencia.
- Crear un área de suposiciones
- Nombrar celdas en una hoja de cálculo
- Usar nombres de hojas de cálculo en fórmulas
- Inserte filas y columnas en una hoja de cálculo.
- Realizar un análisis de sensibilidad
- Aplicar principios de diseño gráfico para crear hojas de cálculo de aspecto profesional.
Aplicar los Principios C.R.A.P.
Observe las hojas de cálculo en la siguiente página. La hoja de cálculo del “antes” es a la vez descuidada y difícil de leer. La hoja de cálculo “después” utiliza el contraste en las fuentes y el tamaño para centrar la atención en la información importante, y utiliza la alineación para crear fuerza y claridad. Los números y sus rubros están justificados a la derecha, y se deja justificada la categoría “Medida” y su encabezamiento. La proximidad y la repetición de fuentes crean un aspecto acabado profesional.
La hoja de cálculo “después” también incluye tres nuevas columnas: Años, Hombres Mayores/ (Menos) y Ganancias femeninas como% de las ganancias masculinas. Estas nuevas columnas son datos o información procesados. La información resume o describe los datos de manera significativa. Esta información muestra esa disparidad entre los ingresos masculinos y femeninos tanto como valor absoluto como como porcentaje. Crear información suele implicar una fórmula. Las fórmulas son escritas por el usuario para realizar cálculos sobre los datos. Las fórmulas de negocio tienden a ser simples, generalmente implican suma, resta, multiplicación o división. Por ejemplo Male Over/ (Under) resta las ganancias femeninas de las ganancias masculinas para mostrar la diferencia entre las dos. De igual manera, las ganancias femeninas como porcentaje de las ganancias masculinas divide las ganancias femeninas entre las ganancias masculinas. Tenga en cuenta que la información revelada por estas columnas nos dice que hay una diferencia; no nos dice por qué. Y la mayor parte de la información es muy parecida a esta. Necesita ser interpretada para tomar decisiones empresariales, o en este caso decisiones de política pública.
El área total de una hoja de cálculo a menudo excede el ancho normal del papel de impresión. Abordar este problema imprimiendo en orientación horizontal y escalando la página al ancho del papel.
Dar formato a una hoja de cálculo marca una enorme diferencia en cuanto a su profesionalidad. El formato adecuado también hace que los datos sean mucho más legibles.
Colocar estimaciones en un área de suposiciones
Mencionamos anteriormente que todo pronóstico implica hacer suposiciones. Estas son conjeturas educadas basadas en la investigación. Sin embargo, las suposiciones pueden cambiar con el tiempo. Idealmente, le gustaría poder actualizar su hoja de cálculo fácilmente a medida que cambian las suposiciones. La forma más fácil y libre de errores para realizar las actualizaciones es usar un área de suposiciones.
Un área de suposiciones contiene todas las variables que entran en tus cálculos. Normalmente se encuentra en la parte superior de la hoja de cálculo. En una hoja de cálculo perfectamente diseñada, el área de suposiciones es el único lugar en el que realmente se escribe un número. Cada otra celda de la hoja de cálculo es una fórmula que se refiere directa o indirectamente al área de suposiciones.
Considera la Comisión Apple ubicada en la celda B7. Necesitamos conocer la comisión para poder calcular el margen de contribución, cuánto ganamos en cada venta. Esperaríamos que la fórmula del margen de contribución en B8 haga referencia a B7. Entonces la fórmula en B8 podría verse así:
=B6* (1-B7)
Sin embargo, esa fórmula no es particularmente informativa. El problema es que Excel no conoce el significado de los números en B7 y B8. Tampoco examina las etiquetas a la izquierda. Si le dimos nombres significativos a las celdas B6 y B7, podríamos calcular el margen de contribución como:
=PriceperUnit* (1-AppleCommission)
o equivalentemente
=PrecioPor Unidad- (PrecioporUnidad*AppleCommission)
Observe cómo estos nombres son muy similares a las etiquetas. Decimos muy similar porque los nombres de celdas no pueden contener espacios, mientras que las etiquetas sí. Los nombres de las celdas se ocultarán a la vista pero se pueden usar en los cálculos como se muestra arriba.
El lugar donde se crean los nombres se encuentra en el cuadro de nombre en la barra de herramientas justo encima de la celda A1. Simplemente escriba el nombre y presione Enter. Debes presionar Enter o el nombre no se pegará.
Recuerde que los nombres de celda no pueden contener espacios. Entonces “Apple_Commission” está bien y también “AppleCommission”. No obstante, “Apple Commission” como dos palabras con un espacio intermedio no va a funcionar. Por cierto, poner en mayúscula la primera letra de cada palabra y eliminar los espacios entre las palabras (por ejemplo, PriceperUnit) se llama notación CamelBack por razones obvias.
En una hoja de cálculo bien diseñada el único lugar en el que deben actualizarse los números es en el área de supuestos. Cada otra celda hace referencia al área de suposiciones usando fórmulas.
Las fórmulas se utilizan para generar información
Nuestro último ejemplo mostró una fórmula en el área de supuestos. Es raro tener fórmulas en el área de supuestos. La mayoría de fórmulas aparecen en la hoja de cálculo propiamente dicha debajo del área de supuestos.
Supongamos que nombramos a B4 como UnitsYear1. Entonces la fórmula en B13 es =UnitsYear1. Cada fórmula comienza con un signo igual.
B14 es un ejemplo mucho más interesante, y uno que muestra el poder de Excel. Ahora estamos en el año 2 y necesitamos representar el incremento proyectado en las ventas. Representar cualquier incremento implica tomar el valor previo (B13) y sumarle el incremento. Calcular el incremento multiplicando el valor previo por el incremento porcentual. Entonces obtenemos:
=B13+B13*Incremento anual (700+700* 100%)
El primer B12 es el valor anterior; el segundo B12*añoIncremento es el monto del incremento. Lo bueno de esta fórmula es que se puede copiar hasta el resto de las celdas de la columna B. Simplemente haga doble clic o arrastre la esquina inferior derecha de B13 y el resto de la columna se llenará de la siguiente manera:
B15: =B14+B14*Incremento anual (1400+1400* 100%)
B16: =B15+B15*Incremento anual (2800+2800* 100%)
B17: =B16+B16*Incremento anual (5600+5600* 100%)
¡Excel repite correctamente el patrón sin ningún esfuerzo adicional de tu parte! Sin embargo, tenga en cuenta cuidadosamente que esto solo fue posible porque antes habíamos nombrado B5 como YearlyIncrease. Si no nombramos B5 entonces el patrón se vería así:
B14: =B13+B13*B5 CORRECTO (700+700* 100%)
B15: =B14+B14*B6 MAL (1400+1400*$2.00)
B16: =B15+B15*B7 INCORRECTO (3200+3200* 30%)
B17: =B16+B16*B8 INCORRECTO (5440+5440*$1.40)
Excel repite incorrectamente el patrón moviendo cada celda hacia abajo un espacio cada vez. Eso es un problema para B5 en el área de supuestos porque las siguientes celdas a la baja son Precio Por Unidad (B6=$2.00), Comisión Apple (B7= 30%) y Margen de Contribución por Unidad (B8=$1.40). Estos tres últimos no tienen nada que ver con unidades vendidas y obtenemos galimatías en lugar de información valiosa. Lo que es aún más aterrador es que los números no se ven obviamente equivocados.
Por lo que además de ser más legibles, nombrar celdas en el área de supuestos también nos protege contra errores al copiar fórmulas.
Por encima de cada celda en el área de suposiciones obtiene un nombre único. Por ejemplo, B7 se llama AppleCommission (sin espacios permitidos en el nombre). Debajo de las fórmulas en la sección de cálculo valores de referencia en el área de supuestos por nombre. Por ejemplo, la fórmula en B13 hace referencia al campo YearlyIncrease. Dichas fórmulas pueden copiarse abajo de la columna sin temor a que la fórmula cambie indebidamente cuando se copien.
Realice un análisis de sensibilidad para cubrir sus apuestas
Cada vez que haces suposiciones, tienes que permitir la posibilidad de que te equivoques. Ya que estás tratando con dinero real, algunos de los cuales probablemente sean tuyos o de un familiar, necesitas saber el alcance de tu exposición en caso de que las cosas no funcionen según lo planeado.
Por lo tanto, es una buena idea variar los supuestos y ver qué resultados ocurren. Este sofisticado análisis permite tener una discusión inteligente sobre posibles escenarios futuros.
Una buena manera de realizar un análisis de sensibilidad es usar una tabla de datos de Excel. Una tabla puede representar tres variables: encabezados de columna, encabezados de fila y los resultados dentro de la tabla. Se varían los datos en los encabezados de fila y columna para observar los cambios dentro de la tabla. Por ejemplo, supongamos que cada celda dentro de la tabla contiene el beneficio después del Año 5. Podemos variar la tasa de crecimiento y el precio por unidad para ver el efecto en las ganancias.
Para realizar estos cálculos sin la tabla de datos requeriría cambiar manualmente la tasa de crecimiento y los supuestos de precio por unidad VEINTE veces y registrar los valores después del año 5—esto es un trabajo ocupado. Por el contrario, Excel tiene la capacidad incorporada de crear una tabla de datos.
La parte más compleja de crear una tabla de datos se muestra mediante las celdas resaltadas en la hoja de cálculo. Debes repetir el valor de tus cálculos sobre los que deseas realizar el análisis. Y no sólo hay que repetirlo, sino que hay que repetirlo usando una fórmula sencilla. Entonces la fórmula en B23 es:
B23: =E18
Observe cómo esta celda debe formar la esquina superior izquierda de la tabla de datos. Ahora solo rellena los encabezados de fila y columna. Ponemos varios valores para tasa de crecimiento en los encabezamientos de las columnas y diversos valores para precio por unidad en las partidas de fila. A continuación, resalte toda la tabla incluyendo los encabezados de fila y columna y elija Análisis Qué pasaría si > Tabla de datos. El último paso es identificar para Excel las primeras celdas donde aparecen tasa de crecimiento y precio por unidad en el área de suposiciones. Entonces Excel sustituirá los valores de los encabezados de fila y columna en el área de suposiciones, rehará TODOS los cálculos en la hoja de cálculo y luego capturará los resultados en la tabla de datos, ¡todo detrás de escena!
Ahora es tu trabajo argumentar por los que crees que son más favorables y realistas.
Principales conclusiones
- Las hojas de cálculo bien diseñadas agrupan variables de entrada en un área de suposiciones. Idealmente, el área de suposiciones es el único lugar donde se escriben los números.
- Las fórmulas pueden ser copiadas. Sin embargo, las celdas que no deben cambiar en una fórmula deben ser nombradas.
- Muchos proyectos de sistemas de información fracasan. Su exposición se puede analizar en un análisis de sensibilidad utilizando una tabla de datos.
Preguntas y ejercicios
- Sin usar la función de tabla de datos incorporada, ¿cómo podría construir una tabla que realizaría un análisis de sensibilidad?
Asegúrese de que la esquina superior izquierda de la tabla de datos sea una fórmula simple que copie el valor que desea ver variado en la tabla de datos.
Técnicas
Las siguientes técnicas, que se encuentran en la sección de PowerPoint de la referencia del software, pueden ser útiles para completar las asignaciones de este capítulo: Resumen Mapa de interfaz • Nombre de Celda-Crear • Nombre de Celda-Eliminar • Número de Formato • Formula-Copiar • Formula-Crear • Modo Fórmula • Insertar Columna • Insertar fila • Mover punto decimal • Análisis de sensibilidad
Asignación L1: Pronóstico de ingresos/gastos
Los profesionales son capaces de representar lo que saben de manera intuitiva en una hoja de cálculo correctamente formateada con un área de suposiciones.
Configuración
Inicia Excel y titula correctamente tu hoja de cálculo.
Contenido y estilo
- Crear un área de suposiciones que contenga todas las variables.
- Nombra cada número en el área de supuestos.
- Usa esos nombres en los cálculos de la hoja de cálculo que aparece a continuación. A excepción de la columna Año, CADA número fuera del área de Supuestos debe calcularse con fórmulas.
- Siga las mejores prácticas de las técnicas de diseño en este capítulo.
- Sólo el primer número de cada columna obtiene el formato de MONEDA (No formatear como CONTABILIDAD.) Actualiza el formato usando la Técnica de Formato de Números. Todos los demás números mayores a 1,000 deben estar en estilo Comma.
- Incluye un símbolo de copyright con tu nombre en la parte inferior.
- Las líneas de cuadrícula de la hoja de trabajo no aparecerán en la copia impresa.
Entregables
Envío electrónico: Enviar el libro de trabajo electrónicamente.
Presentación de trabajos:
- Imprime tanto los resultados como las fórmulas. La impresión de fórmulas muestra las fórmulas en cada columna. Revela las fórmulas escribiendo CTRL+ ~ (presione las teclas CTRL y ~ al mismo tiempo). Ajuste los anchos de columna para recortar de cerca las fórmulas arrastrando el separador entre cada columna en el área de encabezado gris.
- Ambas impresiones deben usar orientación horizontal, que encontrará en Diseño de página > Configurar página > Orientación > Horizontal. Cada impresión debe caber en una página. Elija Diseño de página > Escalar para ajustar > Alto: 1 página; Ancho: 1 página.
Por favor, guarde la hoja de cálculo de ingresos/costos del pronóstico; la actualizará más tarde.
Asignación L2: Más gastos y análisis de sensibilidad
Para que el problema sea más realista, traemos costos fijos adicionales. Para ayudar en la toma de decisiones, realizamos un análisis de sensibilidad.
Configuración
Vuelva a guardar el archivo de su libro de trabajo desde la L1 con un nuevo nombre y luego modifíquelo para que se vea como se muestra Este debe ser un nuevo libro de trabajo, no simplemente cree una nueva hoja en el mismo libro de trabajo o algunos de sus nombres entrarán en conflicto y estropearán la tabla de datos.
Contenido y estilo
- Incorpore nuevos costos incluyendo Incorporación ($199), Costo de Desarrollador de Apple ($99/año), Publicidad ($2,400/año), Costo de mantenimiento del desarrollador ($1,000/año). Crear un área de suposiciones que contenga todas las variables.
- Nombra cada número en el área de supuestos.
- Usa esos nombres en los cálculos de la hoja de cálculo que aparece a continuación. A excepción de la columna Año, CADA número fuera del área de supuestos debe calcularse con fórmulas.
- Siga las mejores prácticas de las técnicas de diseño en este capítulo.
- Sólo el primer número de cada columna obtiene el formato de MONEDA. (No formatear como CONTABILIDAD.) Actualiza el formato usando la Técnica de Formato de Números. Todos los demás números mayores a 1,000 deben estar en estilo Comma.
- Incluye un símbolo de copyright con tu nombre en la parte inferior.
- Las líneas de cuadrícula de la hoja de trabajo no aparecerán en la copia impresa.
- Producir una tabla de análisis de sensibilidad de ganancia total/ (pérdida) en función de la tasa de crecimiento y precio por unidad.
Entregables
Envío electrónico: Enviar el libro de trabajo electrónicamente.
Presentación de trabajos:
- Imprime tanto los resultados como las fórmulas. La impresión de fórmulas muestra las fórmulas en cada columna. Revela las fórmulas escribiendo CTRL+ ~. Ajuste los anchos de columna para recortar de cerca las fórmulas arrastrando el separador entre cada columna en el área de encabezado gris.
- Ambas impresiones deben usar orientación horizontal, que encontrará en Diseño de página > Configurar página > Orientación > Horizontal. Cada impresión debe caber en una página. Elija Diseño de página > Escalar para ajustar > Alto: 1 página; Ancho: 1 página.
Agregue supuestos y columnas según sea necesario para acomodar costos adicionales de mantenimiento, incorporación y publicidad.