Filetype Excel XLSX | Posted on 15 Aug 2022 | 3 years ago
Authentication
digital resources
230x Filetype XLSX File size 0.07 MB Source: www.excel-skills.com
Sheet 1: About
| Excel Skills | Gross Profit & Sales Mark-up Calculations | |
| Instructions | |
| www.excel-skills.com | |
| 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. | |
| Help & Customization | |
| If you experience any difficulty while using this template and you are not able to find the appropriate guidance in these instructions, please e-mail us at support@excel-skills.com for assistance. This template has been designed with flexibility in mind to ensure that it can be used in most business environments. If however you need an Excel based template that is customized specifically for your business requirements, please e-mail our Support function and provide a brief explanation of your requirements. | |
| © Copyright | |
| This template remains the intellectual property of www.excel-skills.com and is protected by international copyright laws. Any publication or distribution of this template outside the scope of the permitted use of the template is expressly prohibited. In terms of the permitted use of this template, only the distribution of the template to persons within the same organisation as the registered user or persons outside the organisation who can reasonably be expected to require access to the template as a direct result of the use of the template by the registered user is allowed. Subsequent distribution of the template by parties outside of the organisation is however expressly prohibited and represents an infringement of international copyright laws. |
| Gross Profit & Sales Mark-up Calculators | ||||||||||||||||||
| © www.excel-skills.com | ||||||||||||||||||
| Sales Tax 1 % | 15.0% | Gross Profit % | 20.0% | |||||||||||||||
| Sales Tax 2 % | 0.0% | |||||||||||||||||
| Sales Amount (excl. tax) | 100,000.00 | Sales Mark-up % | 25.0% | |||||||||||||||
| Sales Mark-up Calculator | Gross Profit % Calculator | |||||||||||||||||
| Sales markup at gross profit of 20.0% | Gross profit % at sales markup of 25.0% | |||||||||||||||||
| Sales | 115,000.00 | Sales | 115,000.00 | |||||||||||||||
| Sales Tax 1 | 15,000.00 | Sales Tax 1 | 15,000.00 | |||||||||||||||
| Sales Tax 2 | - | Sales Tax 2 | - | |||||||||||||||
| Sales (excl. tax) | 100,000.00 | Sales (excl. tax) | 100,000.00 | |||||||||||||||
| Cost of Sales | 80,000.00 | Cost of Sales | 80,000.00 | |||||||||||||||
| Gross Profit | 20,000.00 | Gross Profit | 20,000.00 | |||||||||||||||
| Gross Profit % | 20.0% | Gross Profit % | 20.0% | |||||||||||||||
| Sales Mark-up % | 25.0% | Sales Mark-up % | 25.0% | |||||||||||||||
no reviews yet
Please Login to review.
