This template enables users to calculate the gross profit percentage of any single product based on a specified sales mark-up percentage and to calculate the sales mark-up percentage of any single product based on a specified gross profit percentage. The template also includes two additional sheets which can be used to calculate the gross profit & sales mark-up percentages of multiple products.
The following sheets are included in this template:
Calculator - enter the sales tax percentages, sales amount, gross profit percentage and sales mark-up percentage. The sales mark-up calculator is based on the gross profit percentage and the gross profit calculator is based on the sales mark-up percentage.
GP - this sheet enables users to calculate the gross profit percentages of multiple products by specifying the product cost and sales mark-up percentage of each product.
Mark-up - this sheet enables users to calculate the sales mark-up percentages of multiple products by specifying the selling price and gross profit percentage of each product.
Calculators
The Calculators sheet contains two calculators - a sales mark-up calculator in columns B to F which is based on the sales tax percentages, sales amount and gross profit percentage specified at the top of the sheet and a gross profit percentage calculator in cells H to L which is based on the sales tax percentage, sales amount and sales mark-up percentage specified in the input cells at the top of the sheet.
Note: In our sample data, the sales mark-up and gross profit percentages are the same in both calculators but this will not always be the case because the two calculators function independently from each other.
These two calculators enable users to calculate any gross profit percentage based on any specified sales mark-up percentage and vice versa. These calculations are extremely useful if you want to determine what the sales mark-up percentage needs to be in order to realise a specific gross profit percentage or to determine what the gross profit percentage of a specified sales mark-up percentage will be.
Note: Only the cells with a yellow cell background require user input - the other cells on this sheet are automatically calculated based on the input in the user input cells.
Gross Profit Calculations
The calculations on the GP sheet can be used to determine the gross profit percentages of multiple products. Simply enter a product code in column A, a product description in column B, a product cost in column C, a sales mark-up percentage in column D and the calculations in column E to L are automatically updated. The gross profit percentages are reflected in column K.
Note: Gross profit is defined as the difference between the sales and cost of sales (product cost) amounts and the gross profit percentage is defined as the gross profit amount divided by the sales amount (exclusive of sales tax).
Note: The GP sheet only includes 10 products but you can add as many additional products as required by simply entering or copying the user input values into column A to D and copying the formulas in columns E to L into all the additional rows.
Sales Mark-up Calculations
The calculations on the Mark-up sheet can be used to determine the sales mark-up percentages of multiple products. Simply enter a product code in column A, a product description in column B, a selling price (exclusive of sales tax) in column C, gross profit percentage in column D and the calculations in column E to L are automatically updated. The sales mark-up percentages are reflected in column L.
Note: Sales mark-up is defined as the difference between the selling price and product cost in percentage terms and is calculated by dividing the difference between the selling price and product cost (gross profit) by the product cost. Note that this calculation differs from the gross profit percentage calculation in that the gross profit amount is divided by the product cost (cost of sales) instead of the selling price.
Note: The Mark-up sheet only includes 10 products but you can add as many additional products as required by simply entering or copying the user input values into column A to D and copying the formulas in columns E to L into all the additional rows.
