# Excel Sheet eBay Calculator

We require 1 Excel Workbook with 2 worksheets the details of each worksheet are highlighted below. The objective of this excel sheet is to be able to input products and values to automatically calculate profit however we also need to be able to input a required profit and work backwards to show us the required list price to attain the inputted profit.

Example

If I place my cost price list price shipping packing material cost labor cost it should give me a final profit amount

Alternatively If I place a desired profit it should be able to reverse calculate and suggest a list price taking into consideration all the factors that are attributed in finding the profit. Ie. Cost, Cost vat, Shipping, eBay commission %, eBay Insertion Fee, Paypal Fees etc. and anything else.

In the second sheet we require the same however we would like to be able to have a global drop down box with a markup percentage that can be set and added to the cost so we can price our products based on desired markup. Example 5%-200%

When an amount in the drop down is selected that adds a percentage markup to the cost in order to reach a suggested list price.

1) Price Calculator

Columns Required

1) ProductName

This requires no formulas It’s just a field we enter a product name

2) Weight

This requires no formulas it is just a field we enter a value into

3) MSRP (Manufacturer Suggested Retail Price)

This requires no formulas it is just a field we enter a value into

4) Cost

This requires no formulas it is just a field we enter a value into

5) VAT RATE

This requires no formulas it is just a field we enter a number into

6) Cost VAT

VAT is value added tax, in the UK which is currently 20%

This field will need to automatically calculate the VAT payable on the cost price entered above taking into consideration the value in the VAT RATE COLOUMN.

That formula should be (Cost x VATRATE)

7) ListPrice

This is the field that contains the suggested list price based on the percentage mark up that calculated when a certain mark up is selected from the global drop down box

The formula Should be (Cost x Markup%) + Cost

8) Shipping

This requires no formulas it is just a field we enter a shipping value into

9) RRP

This Is a Field that checks if our ListPrice price has exceeded the MSRP price by showing one of 2 Values a) Higher b) Lower

The formula for this should be

If List price > MSRP show HIGHER in Red colour

If List price < MSRP show LOWER In Green Colour

10) Amazon %

This is the a field that displays the percentage amazon take from the list price . That is currently 15% commission.

The formula for this is ListPrice x 0.15

11) ShippingPrice %

This is a field that displays the percentage amazon take from the Shipping. That is currently 15% commission.

The formula for this should be Shipping x 0.15

12) Actual Postage Cost

This field will display the final postage cost for the sale this needs to be able to calculate in 2 ways

If Weight value is entered then it will check the following bands of postage:

If weight is 0-100g then the postage cost will equal £1.63.

If the weight is 101-250g then the postage cost will equal £1.63.

If the weight is 251-500g then the postage cost will equal £1.63.

If the weight is 501-750g then the postage cost will equal £1.63.

If the weight is 751-1000g then the postage cost will equal £2.08.

If the weight is 1001-1250g then the postage cost will equal £2.45.

If the weight is 1251-1500g then the postage cost will equal £2.66.

If the weight is 1501-1750g then the postage cost will equal £2.99.

If the weight is 1751-2000g then the postage cost will equal £3.31.

If no weight value is entered then postage cost will equal £1.63.

13) Shipping +/-

This is the final amount added or subtracted from the profit.

The formula for this should be (Shipping-Actual Postage costs-ShippingPrice%)

14) List VAT

This is a field that calculates the Amount of VAT within the List price

The formula for this should be: ListPrice - (ListPrice / 1.2)

15) Payable VAT

This field will display the final payable vat amount we have to pay to the government on the sale of this product.

The formula for this should be: List VAT – Cost VAT

16) Packing Material

This requires no formulas it is just a field we enter a value into

17) Labour

This requires no formulas it is just a field we enter a value into

18) Profit

This is the final profit value we are after and the formula to calculate this is

(List Price- List Vat)-(Cost)-(Amazon%)(PackingMaterial)-(Labour)+(Shipping +/-)

2) Product Pricing

Global Markup drop down box that adds a percentage between 5%-100% ascending by 5% at a time.

Columns Required

a. ProductName

b. This requires no formulas It’s just a field we enter a product name

2. Weight

a. This requires no formulas it is just a field we enter a value into

3. MSRP (Manufacturer Suggested Retail Price)

a. This requires no formulas it is just a field we enter a value into

4. Cost

a. This requires no formulas it is just a field we enter a value into

5. VAT RATE

a. This requires no formulas it is just a field we enter a number into

6. Cost VAT

a. VAT is value added tax, in the UK which is currently 20%

b. This field will need to automatically calculate the VAT payable on the cost price entered above taking into consideration the value in the VAT RATE COLOUMN.

c. That formula should be (Cost x VATRATE)

7. ListPrice

a. This is the field that contains the suggested list price based on the percentage mark up that calculated when a certain mark up is selected from the global drop down box

b. The formula Should be (Cost x Markup%) + Cost

8. Shipping

a. This requires no formulas it is just a field we enter a shipping value into

9. RRP

a. This Is a Field that checks if our ListPrice price has exceeded the MSRP price by showing one of 2 Values a) Higher b) Lower

b. The formula for this should be

c. If List price > MSRP show HIGHER in Red colour

d. If List price < MSRP show LOWER In Green Colour

10. eBay Insertion Fees

This needs to be a variable because it can vary depending on the type of account we have. Please leave this as an input field.

11. eBay commission %

This % needs to be a variable because it can vary based on our volumes so please allow this to be an input field

a. The formula for this is ListPrice * X/100 where x is the variable commission example : 10%

12. Paypal Fees

a. This field needs to be a variable as well because the paypal fee will change depending on the volume of products we sell. The way paypal calculates is (ListPrice+Shipping) * X + £0.20 where x is the variable commission.

Paypal is always a % + £0.20 so when you create this it always has to add £0.20

13. Actual Postage Cost

This field will display the final postage cost for the sale this needs to be able to calculate in 2 ways

If Weight value is entered then it will check the following bands of postage:

If weight is 0-100g then the postage cost will equal £1.63.

If the weight is 101-250g then the postage cost will equal £1.63.

If the weight is 251-500g then the postage cost will equal £1.63.

If the weight is 501-750g then the postage cost will equal £1.63.

If the weight is 751-1000g then the postage cost will equal £2.08.

If the weight is 1001-1250g then the postage cost will equal £2.45.

If the weight is 1251-1500g then the postage cost will equal £2.66.

If the weight is 1501-1750g then the postage cost will equal £2.99.

If the weight is 1751-2000g then the postage cost will equal £3.31.

a. If no weight value is entered then postage cost will equal £1.63.

14. Shipping +/-

a. This is the final amount added or subtracted from the profit.

b. The formula for this should be (Shipping-Actual Postage costs-ShippingPrice%)

15. List VAT

a. This is a field that calculates the Amount of VAT within the List price

b. The formula for this should be: ListPrice - (ListPrice / 1.2)

16. Payable VAT

a. This field will display the final payable vat amount we have to pay to the government on the sale of this product.

b. The formula for this should be: List VAT – Cost VAT

17. Packing Material

a. This requires no formulas it is just a field we enter a value into

18. Labour

a. This requires no formulas it is just a field we enter a value into

19. Profit

This is the final profit value we are after and the formula to calculate this is

(List Price- List Vat)-(Cost)-(Ebay insertion fee)-(Ebay Commision %)-(Paypal Fees)-(PackingMaterial)-(Labour)+(Shipping +/-)

Compétences : Excel

Concernant l'employeur :
( 13 commentaires ) Swansea, United Kingdom

N° du projet : #1617039

## Décerné à :

nagybatonyi

Hi, I'll deliver the project in a good quality as usual. Regards

40 £ GBP en 2 jours
(39 Commentaires)
5.4

## 15 freelance ont fait une offre moyenne de 102 £ pour ce travail

bharanisrihari

Hello, Excel VBA expert here. Please check PM for details. Thanks!

150 £ GBP en 3 jours
(57 Commentaires)
6.3
danielgpratidya

Hi, please check my sample before you award anybody

50 £ GBP en 1 jour
(64 Commentaires)
5.4
Teloquence

Consider it done. Check your PM for details.

100 £ GBP en 0 jours
(49 Commentaires)
5.2
binaryromel

##### Excel expert here! Please check PM. #####

120 £ GBP en 3 jours
(34 Commentaires)
4.9
Nhcuong

Hi, I can do it! will send demo soon!

35 £ GBP en 5 jours
(19 Commentaires)
4.8

This is very EASY JOB for us. Please check PMB for details.

50 £ GBP en 2 jours
(8 Commentaires)
3.5
ahmedmayeen

Hi, Please refer to attached private message of mine for bidding details. Thank you, Mayeen

90 £ GBP en 2 jours
(4 Commentaires)
3.4
rohitpithisaria

Hi I would like to apply for the job posted by you. Thanks Rohit Pithisaria

140 £ GBP en 5 jours
(4 Commentaires)
3.0

Excel Expert here interested to work with you, i will provide you quality work with accuracy.

175 £ GBP en 7 jours
(1 Commentaire)
2.4
shaarawi

Hi I can help

100 £ GBP en 3 jours
(3 Commentaires)
2.2
vidhyagp

Hi, Ready to start immediately. Regards, Vidhya G

200 £ GBP en 10 jours
(0 Commentaires)
0.0
rahulc13

Excel Expert...the project shall be delivered within 30-48 hours.

45 £ GBP en 2 jours
(0 Commentaires)
0.0
knizami

Greetings, I am an associate member of CPA Australia and has successfully passed four professional level and result awaiting for fifth module.I also have a BS in Accountancy degree from Arizona State University, USA Plus

150 £ GBP en 5 jours
(0 Commentaires)
0.0
flysep88

same work as my last job.

80 £ GBP en 2 jours
(0 Commentaires)
0.0