How to calculate the sale price of a product or service in Microsoft Excel? step by step guide

A new venture or product launch to the market is always good news. Especially if it’s the first. However, it may happen that the price we put on iteither an object or a service, be inappropriate.

Of course there are professionals who can perform this job exceptionally. But if it’s not within our means, there’s nothing wrong with trying. calculate your own price.

This is why, in this post, you can help yourself simple tools to put a number as appropriate as possible. Especially if you’re just starting out.

What accounts must be drawn to obtain the sale price of a product?

It is important that pay attention to the whole production process. As all elements will be useful when calculating the factors of the equation.

Let’s see them:

Material cost

If what you are trying to sell is a physical item, you must not leave out any of the materials you use. That is, the raw material. Even if it is a minimum element, you have to include it. It doesn’t matter if it’s the tape you use for the packaging or an essential product for manufacturing.

Workforce

Take into account the salaries of your employeesrelate them to the number of items produced during your shift. In the event that it is a sole proprietorship, you should not exclude yourself from the equation. Since an upcoming addition should not change the price.

Bills

It includes within this factor, all those extra expenses that occur in the process. Transfers, taxes, stationery, taxes, rent, services and even your company’s telephone bill.

If you do the sales through virtual stores and online payments addwhat’s more, The commissions. To perform these calculations it is essential that keep all invoices and receiptsto maintain an orderly and transparent administration.

Margin of gain

It is the percentage you want to earn as a company. You should not include labor here, even if the owner and employee are the same person. This may be the most difficult factor to calculate and will depend on the number you want to get. Such They are usually above 30%.

Steps to calculate the sale price of a product or service in Microsoft Excel

We have already shown what are the factors that will define the price of our product. The following will be how to calculate each of them and add them to the final equation. For this it will be essential to have a tool like Microsoft Excel. Let’s see how to carry out this process in a simple and orderly manner.

Have all the receipts at hand and follow these steps:

• Opens Microsoft Excel
• create a new spreadsheet
• In the first row enter the headings “Cost of materials”, “Workforce”, “Bills”, “Total cost”, “single cost”, “Margin of gain” Y “Product price”.
• Under each of them you must enter the corresponding number, guiding you from the image.

To carry out the process in a more orderly manner, we recommend perform individual calculations within the document, but on a particular sheet. bringing the content to cell through the special paste.

For example:

• Click on the sign “+” at the bottom of the document to create a new tab.
• Next, name it the same way as the corresponding header. To do this, press the right mouse button and choose “Rename”.

• In this new tab, enter all material costs in one columnwhile, in another, its description.
• You should leave a space to enter the formula (following the example in the image) “=SUM(B:B)” this will sum all the values ​​found in the column B..

• When you are done placing the values copy the cell that corresponds to the sum. In this case it is the A2.
• go back to the main tab and do the special glue. To do this, select the corresponding box and press the secondary button about her.

• Then hover over the menu “Special Paste” and press “Special Paste” in the dropdown. This will open a small window.
• In it click on “Paste Links”. This will cause any changes made on the second tab to be reflected in this value.
• Repeat the operation with all the headers you think necessary.
• The only thing we must have decided is the profit margin that we want to obtain.
• In “Total cost” we will enter the sum of the total expenditure items through the function “=A3+B3+C3”.
• In the cell corresponding to “single cost” we must divide the total cost by the number of items produced in the segmented time. For example “=D3/500”.
• Since we have defined the profit margin arbitrarilyyou will not need to perform any calculations in this box.

• As for the final price, we must carry out the following formula. Total individual cost divided by (1- the profit margin in %). In the example it would be applied in the form “=E3/(1-F3%)”.

This formula can also be important if you already have a defined market price, either based on the competition or on other parameters. The same could help you get a target cost or production goal.