Excel
Update: Name Manager
Updated: Flexfind
New Zealand GST Calculation with Excel [Free Template]
If you operate a business in New Zealand (NZ) like me, chances are you too need to calculate GST on purchases & sales. Today, let me share the excel formulas needed to calculate GST as per NZ laws. I have also attached a free GST calculator template to help you if you are in a hurry.
How to calculate NZ GST using Excel?Assuming you have the sale price in cell C5, the GST is calculated by the below formula.
=C5 * 0.15We multiply the “sale” or “service” price by 0.15 (or 15%) as the official GST rate in New Zealand is 15% [ref].
Excel formula for NZ GST from “total” priceLet’s say you want to figure out the GST from total price (GST inclusive price) of something. This is quite common in retail scenarios. You have an item for $140 on the shelves, but you need to figure out what the GST should be on this. In this case, you can use the below Excel formula.
Assuming your “total” price is in cell C15, the GST is calculated with this formula:
=C15 * 15/115 GST Reverse Calculation – What is the “sale price” if I know GST?Occasionally, we may have the reverse problem. We know how much the GST is, but just need to figure out the total. In this case, you can use the below formula.
Assuming your GST is in cell I5, the sale price can be calculated with below Excel formula
=I5*100/15and total price can be calculated with this formula
=I5*115/15 The 3 / 23rds and 3/20ths rulesHere is a handy shortcut to quickly figure out the GST from total or sale prices.
3/23rds rule – GST from totalIf your total amount is known, just multiply that with 3 and divide by 23 to get the GST.
For example, if your total is $230, then GST would be $30.
=230 x 3 / 23 =690 / 23 =30 3/20ths rule – GST from Sale PriceIf you know the “sale” price, just multiply it with 3 and divide by 20 get the GST.
For example, if your sale price is $140, then GST would be $21
=140 x 3 / 20 =420 / 20 =21 GST for hourly rates, servicesIf you work as a plumber / electrician / some other type of service provider and you charge by hour, then you can use below formulas for calculating GST.
Assuming your hourly rate is in cell I15 and hours worked in cell I16, the GST formula looks like this: =I15*I16*0.15 FREE NZ GST calculator workbookI have created a simple, plug-n-play GST calculator workbook for you. Please download it here, enter your price / total / hourly information and the file automatically calculates the GST for you. It also has the GST formulas / patterns that you can apply to your own data.
Click here to download the NZ GST Calculator.
Need Spreadsheet help?If you are an NZ business and need spreadsheet help or automation services, please get in touch with me. I am a Wellington based Excel / automation expert and I have been helping clients for the past 15 years in creating simple & easy automation and Excel solutions. Please email me on hello@chandoo.org to discuss more.
Other Excel Templates to help you- Free 2025 Calendar & To-do list template
- Free Project Tracker Template
- Free Household budget template
The post New Zealand GST Calculation with Excel [Free Template] appeared first on Chandoo.org - Learn Excel, Power BI & Charting Online.
Updated: Trusted Documents Manager
Make a Pivot from Another Pivot Table in Excel
Recently, a client shared data with me that is clearly a pivot table and wanted me to make another pivot from it using Excel. This is a common and annoying problem we all face when working with Excel. Today, let me share my approaches for creating a pivot from another pivot report using Excel.
Option 1: If you have access to “original” dataHa, I know, but we can dream eh? So, if you do have access to the original data from which the pivot is generated, just use that data and make the new pivot as you want.
If you need help creating a pivot report in the first place, learn the process here.
Option 2: Making a Pivot from Another Pivot when you don’t have access to original dataTbh, this is the real scenario for most of us. We have a pivot and don’t have access to the data that was used to make it. Now we need to make another pivot. In this case, follow the below steps.
Pivot from Another Pivot – FREE Excel TemplateI created a free Excel template to guide you thru the process with sample data. Download it here and use the sample data to understand the process better.
Step 1: Select and name your pivot range- Select the entire pivot table (including any headers) in Excel.
- Go to the name box (next to formula bar on the left)
- Type the name “pivot_range”
- Pro tip: If you have multiple pivots, you can use names like “pivot_range1”, “pivot_range2”
See this illustration for the step.
Step 2: Go to Data Ribbon and load up the “pivot” to Power Query- Keep the pivot table selected
- Go to Data Ribbon
- Click on “From Table/Range” option in the Get & Transform Data area
This will load the Power Query Editor with your Pivot Table Data.
Step 3: Let’s “unpivot” the Pivot Table with Power QueryNow that our “pivot table” is in Power Query, we can “unpivot” it and create a regular table. This can be used to make our new pivot table.
Here is a snapshot of how the Power Query editor looks with the pivot_range data.
[optional step] Promote headers if neededDepending on how your source Pivot is setup, you may need to adjust the column headings in Power Query. For example, in my case, I need to promote the headers. To do this, click on “Use First Row as Headers” button in the Home ribbon of Power Query editor.
See below illustration.
Step 4: Replace “null” with value from aboveIn my sample pivot, you can see that Rep name is not printed in all rows, just the first row. This shows up as null in the Power Query editor for rest of the rows. We just need to fill these down based on the top value.
- Select the column(s) with this problem
- Go to “Transform” ribbon in Power Query Editor
- Click on “Fill” and select Down to fill down all the nulls with the value from above
We don’t need totals or sub-totals any more. We will calculate them in the new pivot as needed. For now, let’s remove all the rows and columns that have totals.
- Select the first column that has “total” labels
- Click on “filter” button
- Uncheck any total labels.
- Repeat the steps for any other rows that need this clean-up step.
- Pro tip: Use Text Filters > Does not contain to filter out all rows with “total” word in them.
Let’s also remove any “grand total” columns and “sub-total” columns from our pivot report. Right click on the column with totals and select “remove” to take this column out.
Step 7: Unpivot the dataFinally, our pivot report is ready to be unpivoted.
- Select the column(s) with row labels. In the above example, I selected “representative” and “day of week” columns
- Pro Tip: Hold SHIFT or CTRL to select multiple columns in one go.
- Right click on the column headings of either column.
- Select “unpivot other columns”
- This should reshape the pivoted data to unpivoted format.
- See this quick demo (GIF):
Double click on the newly added “attribute” and “value column headers to rename them to appropriate labels. In my case, I named them – Gender & Calls.
Step 9: Load the data back to Excel so we can make the pivotOk. We are done. Just load the data back to Excel. To do this, go to “Home” ribbon and click on “Close & Load” button.
Step 10: Create the Pivot from the loaded dataOnce the data is in Excel, just select any cell in the data, go to Insert > Pivot Table (shortcut: ALT N V T) and set up the pivot as per your needs. In my case, I needed the pivot report with number of calls by Day of Week & Representative. So here is how I made it (see the quick video demo).
Things to keep in mind:The Power Query based approach to create pivot from another pivot is great, but you need to keep a few things in mind.
- Doesn’t work for averages: If your original pivot table has “averages” instead of “sums”, the new pivot will not be correct. This is because you will make the mistake of “averaging averages”. This technique works great for sums & counts only. Any other measures like average / median / min /max, you need “actual” data to make the new pivot.
- Power Query steps can get complicated: If your original pivot has a very complex, nested layout, then the PQ steps needed to “transform” data can be complex (but not impossible). I suggest learning how to use Power Query to solve such issues. Refer to this article or video to start your PQ journey.
- Needs refresh for data changes: If your original Pivot table changes (new values or new rows / columns), you need to update the “pivot_range” named range and refresh the power query data.
- To update the named range: Go to Formula ribbon in Excel and click on “Name Manager”. Select the name “pivot_range” and edit it. Adjust the cell references as per your newly updated pivot.
- To Refresh Power Query: Right click on the Power Query data you have loaded in Step 9. Select “Refresh’ to update the loaded data with new changes. Now go to the pivot you made (in step 10) and refresh that too (you guessed it right! Right click and Refresh).
Errors can happen either during the initial process (steps 3 to 9) or when you refresh the power query connection. Solving the error depends on your exact pivot table layout and what changes were made. But here are the most likely reasons for the error.
- Column names have changed: You will get error if your columns (in the original pivot) were changed between updates. Adjust the names in the original pivot or go to Power Query editor, locate the step where the error is happening and adjust the names there.
- Data type issues: If for some reason, your original pivot’s values are read by Power Query as “text”, it can create issues. Right click on the columns with numbers and explicitly convert them to numbers in PQ.
- Layout changes: If your pivot layout changes (say, instead of 2 columns, it now has 3 columns of row labels), then your refresh will fail. You need to select one more column before unpivoting (step 7).
- Other issues: Leave a comment with the issue / error you are facing so I can help.
I created a free Excel template to guide you thru the process with sample data. Download it here and understand the process better.
In conclusion:Power Query in Excel offers an elegant, simple and easy way to deal with the annoying issue of using “pivot tables” as data source. I had plenty of success with this method and I hope will too. If you do have any questions or face issues during the process, leave a comment.
The post Make a Pivot from Another Pivot Table in Excel appeared first on Chandoo.org - Learn Excel, Power BI & Charting Online.
Update: Updating an Excel Addin Through the Internet
Update: RefTreeAnalyser
Planificación de compras
La aplicación Excel siguiente permite confeccionar la lista y calcula en forma de porcentaje la importancia de cada artículo en relación con el presupuesto total. Además, informa del dinero gastado y disponible en cada momento.
"El hombre debe ordenar sus acciones de tal forma que sea máxima la suma de su goce de la vida" (Gossen)
Aplicación Excel: Planificación de compras
Plan de viabilidad para emprendedores
Hojas de entrada de datos:
- Detalle de las inversiones, vida útil de los activos y cálculo de amortizaciones.
- Presupuesto de financiación, con tipos de interés, plazos y cálculo de intereses a pagar.
- Presupuesto de ingresos por ventas o servicios prestados, y costes de los aprovisionamientos.
- Gastos de personal, alquileres y generales
- Cuenta analítica de resultados.
- Presupuesto de tesorería, calculado a partir de los resultados, al que se añaden los cobros y pagos por operaciones de explotación, inversión y financiación.
- Balance, que recoge el saldo de las cuentas al inicio de la actividad y al final de cada año transcurrido.
- El estudio se completa con análisis mediante ratios, del balance, el punto de equilibrio, las rentabilidades anuales, la rentabilidad del proyecto y el cálculo de riesgo operativo y financiero.
Descargar: Plan-de-viabilidad.xlsx
Amortización de préstamos mediante el sistema francés
Sin embargo, en determinados casos, por ejemplo en los préstamos hipotecarios que se formalizan a largo plazo, la cuota puede modificarse si se ha contratado a tipo de interés variable para distintos periodos, y/o cuando se realicen amortizaciones parciales anticipadas. Estos supuestos implican el cálculo de una nueva cuota en función del nuevo tipo de interés del préstamo.
La cuota constante se calcula mediante la siguiente fórmula, obtenida por equivalencia financiera:
a=C* i * (1+i)^n / ((1+i)^n-1)
Donde:
- a=cuota de amortización constante a final de cada periodo (pago)
- C=capital del préstamo (va)
- i=tipo de interés efectivo al tanto por uno (nominal anual dividido por el número de pagos anuales)
- n=número de periodos (nper)
- PAGO(tasa;nper;va;vf;tipo). Para calcular la cuota constante
- PAGOINT(tasa;período;nper;va;vf;tipo). Para calcular los intereses de un periodo determinado
- PAGOPRIN(tasa;período;nper;va;vf;tipo). Para calcular la amortización de periodo n
- TIR(valores;estimar). Para estimar la tasa interna de rentabilidad
- TAE= (1+TIR)^m-1 (Igual a la TIR anualizada, donde m=frecuencia de pagos en el año)
La explicación teórica y ejemplos prácticos de cómo confeccionar el cuadro de préstamos sistema francés se realiza en el siguiente libro, publicado en Amazon.
Matemáticas financieras en mapas mentales
Descargar: Simulacion-prestamos-sistema-frances.xlsx
Se recomienda descargar la aplicación y ejecutarla en Excel. No se garantiza el funcionamiento correcto en otros paquetes ofimáticos.
Estimación de la curva de aprendizaje
Esto se manifiesta por ejemplo en la producción de un bien, en el que el tiempo o los costes son menores a medida que se realizan más unidades o ciclos, y además la tasa de disminución del tiempo es cada vez menor. Se puede establecer, entonces, una relación entre el tiempo que se tarda en producir cada unidad y el número de unidades de producción consecutivas, que denominamos: curva de aprendizaje.
Su estimación y conocimiento es muy útil para la planificación de la cantidad de trabajo necesario, programar la producción, prever plazos de entrega, estimación de costes y presupuestos, etc.
Para obtener la curva de aprendizaje primero debemos elegir una función que se ajuste bien a los datos, y esta suele ser una función potencial de la forma tn=kn^r, (donde tn=tiempo de la unidad n; k=tiempo de la primera unidad; n=la unidad producida; y r=exponente negativo que le da forma decreciente a la función.)
Los coeficientes de esta función los podemos estimar por MCO (Mínimos Cuadrados Ordinarios) a partir de los datos observados en el proceso productivo, previa transformación de la función potencial en lineal mediante logaritmos Log(tn)=Log(k)+rLog(n).
Otra forma de cálculo es mediante la estimación de una tasa o porcentaje de aprendizaje constante (p) cuando se duplica la producción. Esta tasa se deduce de la función anterior mediante p=k2n^r/kn^r=2^r. O viceversa, conocida la tasa de aprendizaje se puede obtener el exponente de la función tn r=log(p)/log(2).
La aplicación siguiente, a partir de una serie de observaciones, calcula la curva de aprendizaje por MCO mediante funciones de regresión Excel y deduce la tasa o porcentaje de aprendizaje a partir de la función potencial. Además, se realiza la estimación a partir de un gráfico de puntos al que se agrega la tendencia potencial, que debe coincidir con la anterior.
Aplicación Excel: Curva de aprendizaje
Diagrama de Pareto
También se le conoce como regla del 80/20 basándonos en que el 80 % del resultado lo genera el 20 % de las causas, o como una ley de potencia por el tipo de relación matemática entre las variables efecto y causa. La aplicación práctica del análisis de Pareto se realiza mediante una comparación cuantitativa de las causas, ordenándolas en una tabla, de mayor a menor, según su porcentaje de contribución a un efecto determinado. Una vez ordenadas obtenemos los porcentajes acumulados con objeto de conocer el grado de concentración de las causas más importantes. La representación gráfica de la tabla indicada es el diagrama de Pareto que aporta una imagen intuitiva del análisis.
La aplicación Excel siguiente, a partir de la introducción de las causas o elementos con su frecuencia correspondiente, calcula la tabla y diagrama de Pareto.
El diagrama de Pareto descubre los aspectos prioritarios que hay que tratar para conseguir un objetivo o resolver un problema determinado, diferenciando los pocos factores vitales de los muchos factores útiles, que contribuyen al resultado. Es, por tanto, una herramienta útil tanto en las ciencias naturales como en las sociales, siendo algunos ejemplos de su aplicación el control de calidad, distribuciones de la renta, ventas, costes, producción, inventarios, etc.
Bajar Libro Excel: diagrama_pareto.xls
Calendario perpetuo para registro de datos contables con Excel
Este es un calendario anual con Excel que tiene la ventaja de que se puede convertir en perpetuo, simplemente modificando el año.
La aplicación ajusta automáticamente los días del año elegido a los de la semana que corresponda.
Con objeto de buscarle utilidad, al lado de cada día creamos una celda para registrar datos contables. Esto nos servirá para llevar un control de numerosos datos como pueden ser: ventas, gastos, kilómetros, cotizaciones de acciones, precios, etc.
Al final de cada mes calcula el total del mes, la media diaria y la desviación típica.
También se puede utilizar para llevar el mismo registro contable, pero para años distintos. Para ello simplemente cambiamos en distintas hojas el año que corresponda.
Recomiendo descargar la aplicación y ejecutar en Excel de escritorio.
Descargar libro Excel: Calendario-perpetuo-datos-contables.xlsx
Revisión de préstamos a tipo de interés variable
Aunque es habitual que la entidad prestamista comunique al prestatario el nuevo tipo de interés y la cuota resultante, es conveniente realizar su cálculo a modo de comprobación y de previsión de pagos. Esto se puede realizar con la siguiente aplicación Excel, que incluye dos hojas, una para la revisión anual y otra para la semestral (las más usuales en España).
Libro Excel: Revisión de préstamos a tipo de interés variable
En el libro siguiente se explica con mapas mentales la teoría y ejemplos prácticos de matemáticas financieras utilizando fórmulas, calculadora financiera y Excel.Echa un vistazo: Matemáticas financieras en mapas mentales
Tasa Anual Equivalente (TAE)
Cuando una operación tiene periodos de liquidación de interés inferiores al año, y bajo la hipótesis de que se reinvierte el importe de dichas liquidaciones al mismo tipo de interés y en régimen compuesto, se incrementará el coste o rendimiento (según se trate de un préstamo o un depósito, respectivamente), dando como resultado la TAE.
El cálculo de la tasa anual equivalente se realiza mediante la fórmula TAE=(1+i/n)^n-1, donde i=tipo de interés nominal anual en tanto por uno, n=número de periodos de liquidación en el año.
Con Excel se puede calcular utilizando las funciones INT.EFECTIVO(int.nominal; num_per_año). Para incluir las comisiones y gastos hay que calcular previamente el int.nominal a través de la función TASA(nper;pago;va;vf;tipo;estimar), en la que el va=(principal de la operación - comisiones y gastos).
La aplicación Excel, al final de esta entrada, calcula la TAE para operaciones de préstamo de cuota fija, sistema francés y depósitos bancarios. Además, se añade una hoja para calcular la equivalencia entre TIN (tipo de interés nominal) y TIE (tipo de interés efectivo), en donde solo intervienen tipos de interés, sin comisiones ni gastos.
En los capítulos 27 y 75 del libro siguiente se explican con detalle como calcular la TAE con fórmulas, calculados y Excel.
Matemáticas financieras en mapas mentales
Descargar: Calculo-de-tasa-anual-equivalente-TAE.xlsx
Se recomienda descargar la aplicación y ejecutar en Excel, no garantizando el funcionamiento correcto en otros paquetes ofimáticos.
Estimación de la tendencia lineal en ingresos y gastos
.
Free download: Estimación de tendencia en ingresos y gastos
Análisis DAFO aplicando técnicas cuantitativas
- Dividimos la matriz DAFO en dos submatrices, una relacionada con el ámbito interno FD (Fortalezas y Debilidades), y otra el externo AO (Amenazas y Oportunidades).
- En cada matriz FD y AO, ponderamos (de 0,0 a 1,0) los factores en función de su contribución al éxito de la empresa. La suma de los pesos en cada matriz será uno. Además, les asignamos una calificación: en el análisis interno FD, según la fortaleza de la estrategia actual de la empresa respecto al factor. (1-Mayor debilidad, 2-Menor debilidad, 3-Menor fuerza, 4-Mayor fuerza), y para el análisis externo AO, según el grado de respuesta de la estrategia actual al factor: (1-Baja, 2-Media, 3-Superior a la media, 4-Alta).
- Multiplicamos cada ponderación por la calificación correspondiente para obtener las calificaciones ponderadas de los factores.
- En cada matriz (FD y AO), sumamos las calificaciones ponderadas para obtener el total ponderado. El resultado estará entre un mínimo de 1 y un máximo de 4. El punto medio será 2.5
- Representamos en un gráfico de dos dimensiones la posición de la empresa. El eje de abscisas representa la competitividad y tomará el valor del resultado total de FD y las ordenadas representan la atractividad y tomará el valor del total de AO.
- Valor bajo de ambas variables: reestructuración, cambio estratégico y operativo,
- Competitividad alta y atractividad baja: nuevos productos, cambio de mercados, diversificación, etc.
- Competitividad baja y atractividad alta: rumbo correcto, pero debe mejorarse la organización, productividad, costes, diferenciación, etc.
- Ambas altas: expansión, desarrollar al máximo los productos y posición en el mercado
- Hoja 1: Situación interna
- Hoja 2: Situación externa
- Hoja 3: Gráfico de la posición de la empresa respecto de la competitividad y atractividad y estrategias a formular.
Simulación de préstamos con cuota creciente en progresión aritmética
- Introducir los datos del préstamo: importe, tipo de interés, plazo
- Introducir en la celda H6, el importe en que debe incrementarse cada cuota a partir de la primera.
- Ejecutar Herramientas / Buscar Objetivo
- Definir la celda: introducir la columna H, y la fila correspondiente al último periodo de amortización.
- Con el valor: introducir 0
- Para cambiar la celda: $G$10
- Aceptar
Free download: Préstamos cuotas crecientes en progresión aritmética
En el libro siguiente se explica con mapas mentales la teoría y ejemplos prácticos de matemáticas financieras utilizando fórmulas, calculadora financiera y Excel.Echa un vistazo: Matemáticas financieras en mapas mentales
Préstamo con amortizaciones crecientes
Existe varias modalidades de amortización de préstamos, siendo las más conocidas las de pago único, (o americano), las cuotas constantes de amortización-liquidación (método francés), y la amortización constante con cuota total decreciente (alemán). Pero existen otras modalidades como puede ser la amortización creciente en progresión aritmética. En este caso la amortización crece en un importe fijo respecto de la del periodo anterior. Como consecuencia de ello, la cuota de pago total puede crecer también en casos de tipo de interés bajo, o crecer al principio del periodo para decrecer al final, cuando el tipo de interés es alto. Las amortizaciones crecientes comentadas son el resultado de multiplicar el capital del préstamo por un factor en progresión aritmética obtenido al dividir el número de orden de la cuota entre la suma total de la serie número de cuotas. Este método facilita el pago del préstamo al deudor, teniendo en cuenta que las cuotas al principio son menores y que en el futuro aumenten sus ingresos reales o nominales (debido a la inflación). Una buena forma de entender este método es a través de una simulación con Excel. Se puede ver como se aplica el factor para calcular la amortización creciente, hallando además el interés de cada periodo y cuota total. La aplicación incluye un gráfico donde se puede ver el comportamiento simultáneo de dichos componentes de la cuota.
Free download: Préstamo con amortizaciones crecientes
En el libro siguiente se explica con mapas mentales la teoría y ejemplos prácticos de matemáticas financieras utilizando fórmulas, calculadora financiera y Excel.Echa un vistazo: Matemáticas financieras en mapas mentales
Páginas
