Page 1 of 2 12 LastLast
Results 1 to 10 of 12

Thread: Marketplace Profit Calculator - Calculating Variable Costs

  1. #1
    Board Regular
    Join Date
    Sep 2013
    Posts
    65
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Marketplace Profit Calculator - Calculating Variable Costs

    I'm looking for help regarding calculating profit margins when selling on marketplaces Amazon


    I'd like to know if it would be possible to set the profit margin to eg. 20% and work backwards from there; have excel
    calculate what my selling price should be to make the 20%?


    Fixed Expenses: Amazon fees 15% on Retail Price Set+VAT on Retail Price 20%+Post and Packing 2.30

    Any help would be most appreciated, thanks.

    Hiten

  2. #2
    MrExcel MVP shg's Avatar
    Join Date
    May 2008
    Location
    The Great State of Texas
    Posts
    21,635
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    14 Thread(s)

    Default Re: Marketplace Profit Calculator - Calculating Variable Costs

    A
    B
    C
    1
    Amazon fee
    15.00%
    B1: Input
    2
    VAT
    16.67%
    B2: Input
    3
    Margin
    20.00%
    B3: Input
    4
    Post & Packing
    $2.30
    B4: Input
    5
    Cost
    $10.00
    B5: Input
    6
    Sell
    $25.45
    B6: =(B5 + B4) / (1 - SUM(B1:B3))
    7
    8
    Check:
    9
    Amazon takes
    $3.82
    B9: =B1 * B6
    10
    Taxes take
    $4.24
    B10: =B2 * B6
    11
    Post & package
    $2.30
    B11: =B4
    12
    You get
    $15.09
    B12: =B6 - SUM(B9:B11)
    13
    Profit
    $5.09
    B13: =B12 - B5
    14
    Margin
    20.00%
    B14: =B13 / B6

  3. #3
    Board Regular
    Join Date
    Sep 2013
    Posts
    65
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Marketplace Profit Calculator - Calculating Variable Costs

    Great, Many thanks shg

    Would you have the same for eBay?

    Regards,
    Hiten

  4. #4
    MrExcel MVP shg's Avatar
    Join Date
    May 2008
    Location
    The Great State of Texas
    Posts
    21,635
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    14 Thread(s)

    Default Re: Marketplace Profit Calculator - Calculating Variable Costs

    I don't have any information for eBay. I didn't have any information for Amazon until you posted it.

  5. #5
    Board Regular
    Join Date
    Sep 2013
    Posts
    65
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Marketplace Profit Calculator - Calculating Variable Costs

    Thank you for your reply shg,

    I will try to input the eBay data and see if that works else I will let you know the requirements.

    Once again, many thanks,

    Regards,
    Hiten

  6. #6
    MrExcel MVP shg's Avatar
    Join Date
    May 2008
    Location
    The Great State of Texas
    Posts
    21,635
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    14 Thread(s)

    Default Re: Marketplace Profit Calculator - Calculating Variable Costs

    You're welcome.

  7. #7
    Board Regular
    Join Date
    Sep 2013
    Posts
    65
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Marketplace Profit Calculator - Calculating Variable Costs

    Hi shg,

    There is one more bit on eBay, if you could help!

    eg. eBay fees - 10% of selling price, PayPal fees - 0.05% of selling price, VAT - 20%, Margin 20%, Post & Packing - 2.30

    How to find selling price, as I was unable to add & calculate the PayPal fees on the spreadsheet

    Regards,
    Hiten

  8. #8
    Board Regular
    Join Date
    Sep 2013
    Posts
    65
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Marketplace Profit Calculator - Calculating Variable Costs

    Hi shg,

    Apolgoies!

    Please disregard previous post.

    There is one more bit on eBay, if you could help!

    eg. eBay fees - 10% of selling price,
    eg. when selling price is £11.65, PayPay charges is 0.42 - when I try to convert in percentage and then do a check it does not give me the right figure.
    VAT - 20%,
    Margin 20%,
    Post & Packing - £2.30


    How to find selling price, as I was unable to add & calculate the PayPal fees on the spreadsheet

    Regards,
    Hiten

  9. #9
    MrExcel MVP shg's Avatar
    Join Date
    May 2008
    Location
    The Great State of Texas
    Posts
    21,635
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    14 Thread(s)

    Default Re: Marketplace Profit Calculator - Calculating Variable Costs

    eg. eBay fees - 10% of selling price,
    eg. when selling price is £11.65, PayPay charges is 0.42
    I get the 10% eBay fee.

    In the US, PayPal charges a percentage plus a fixed fee (2.9% + $0.30). What is it for you?

  10. #10
    MrExcel MVP shg's Avatar
    Join Date
    May 2008
    Location
    The Great State of Texas
    Posts
    21,635
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    14 Thread(s)

    Default Re: Marketplace Profit Calculator - Calculating Variable Costs

    I'll let you plug in the numbers:

    A
    B
    C
    1
    Sell-Based Percentages
    2
    eBay
    15.00%
    B2: Input
    3
    VAT
    16.67%
    B3: Input
    4
    PayPal
    2.90%
    B4: Input
    5
    Margin
    20.00%
    B5: Input
    6
    Fixed Costs
    7
    Post & Packing
    $2.30
    B7: Input
    8
    PayPal
    $0.30
    B8: Input
    9
    Cost
    10
    Direct Cost
    $10.00
    B10: Input
    11
    12
    Sell Price
    $27.73
    B12: =(B10 + SUM(B7:B8)) / (1 - SUM(B2:B5))
    13
    14
    Check:
    15
    eBay
    $4.16
    B15: =B2 * B12
    16
    VAT
    $4.62
    B16: =B3 * B12
    17
    PayPal (variable)
    $0.80
    B17: =B4 * B12
    18
    Post & Packing
    $2.30
    B18: =B7
    19
    PayPal (fixed)
    $0.30
    B19: =B8
    20
    Your Net
    $15.55
    B20: =B12 - SUM(B15:B19)
    21
    Profit
    $5.55
    B21: =B20 - B10
    22
    Margin
    20.00%
    B22: =B21 / B12

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •