Closed Thread
Results 1 to 58 of 58

Help automatic an excel calculation

  1. #1
    Registered User
    Join Date
    10-11-2018
    Location
    Netherlands
    MS-Off Ver
    Office 2016
    Posts
    36

    Help automatic an excel calculation

    Hello All,

    I am trying to calculate the selling price of a product using an excel template.
    I will try to explain first the template, but the cell I need automating would be H2 (Selling Price)

    The first 3 red columns are expenses,
    A: the price I pay for the product at the distributor
    B: The fee I pay to the selling platform, which is 15% of the selling price
    C: A fixed 21% tax I have to pay to the tax office based on the selling price

    E: is the Profit % which should always be 10%
    F: is the tax on the purchasing price A, which I can claim fully back from the tax office
    H: is the manual field I type to calculate the selling price. I keep modifying this until E=10%

    I tried myself but I get errors because of every time H change B also changes.

    If I need to add a column or two, it's no problem, my ultimate goal is that I should only enter the buying price in A and H will need to be calculated automatically.

    Any help would be much appreciated.

    Regards
    Nic
    Attached Files Attached Files
    Last edited by 737ngx; 08-05-2019 at 09:46 AM. Reason: Spelling mistakes

  2. #2
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,369

    Re: Help automatic an excel calculation

    Do you have the SOLVER add-in enabled?
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.

    Forum Rules (updated August 2023): please read them here.

  3. #3
    Registered User
    Join Date
    10-11-2018
    Location
    Netherlands
    MS-Off Ver
    Office 2016
    Posts
    36

    Re: Help automatic an excel calculation

    Hi Ali, I am sorry but I do not know what that is, do I need to download it and install it?
    Last edited by AliGW; 08-05-2019 at 10:02 AM. Reason: Please don't quote unnecessarily!

  4. #4
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,369

    Re: Help automatic an excel calculation

    No, it's in by default, but you have to go to Options and Add-Ins and enable it for it to appear on the Data ribbon.

  5. #5
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: Help automatic an excel calculation

    You don't need Solver:

    A
    B
    C
    D
    1
    Check
    2
    Direct Cost
    100.00
    3
    Seller
    15%
    27.78
    4
    Tax
    21%
    38.89
    5
    Profit
    10%
    18.52
    6
    Sell
    185.19
    B6: =B2 / (1 - SUM(B3:B5))
    Entia non sunt multiplicanda sine necessitate

  6. #6
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,369

    Re: Help automatic an excel calculation

    LOL!!! Doh ...

    I was certainly over-complicating this in my head ...

  7. #7
    Registered User
    Join Date
    10-11-2018
    Location
    Netherlands
    MS-Off Ver
    Office 2016
    Posts
    36

    Re: Help automatic an excel calculation

    Quote Originally Posted by shg View Post
    You don't need Solver:

    A
    B
    C
    D
    1
    Check
    2
    Direct Cost
    100.00
    3
    Seller
    15%
    27.78
    4
    Tax
    21%
    38.89
    5
    Profit
    10%
    18.52
    6
    Sell
    185.19
    B6: =B2 / (1 - SUM(B3:B5))

    I am not sure this is the right outcome.
    If you check the attachment in the original post, my numbers are different, my selling price should be 140.86
    maybe becasue you need to take into account two different taxes, the sales and the buyng taxes.
    Last edited by 737ngx; 08-06-2019 at 03:17 AM.

  8. #8
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: Help automatic an excel calculation

    B2 + C3:C5 totals the sell price; do you agree with those numbers?

  9. #9
    Registered User
    Join Date
    10-11-2018
    Location
    Netherlands
    MS-Off Ver
    Office 2016
    Posts
    36

    Re: Help automatic an excel calculation

    Quote Originally Posted by shg View Post
    B2 + C3:C5 totals the sell price; do you agree with those numbers?
    No sorry.
    Try to open my attachment and try to automate H2 within my file.
    You will see that every time you manually change H2 you see that many other fields also change because they are linked.
    In your formula above you forgot one of the taxes again. Remember there are two taxes to take into account, the selling taxes which for me is a loss of profit and the buying taxes which I can claim back fully and should be considered in the profit. I address this in my file in the original post.

    Thanks

  10. #10
    Registered User
    Join Date
    06-21-2017
    Location
    Missouri, USA
    MS-Off Ver
    2007 through 2019
    Posts
    71

    Re: Help automatic an excel calculation

    I think if you adjust the formulas to reference A2 instead it would work out. So B2 would be .2213*a2, c2 would be .2415*a2, d2 would be .1164*a2. E2 & g2 are fine as is, F2 would be .1736*a2 and H2 would be a formula to sum a2:d2 less f2. I tried a few numbers in A with this and it seemed to work.

  11. #11
    Registered User
    Join Date
    10-11-2018
    Location
    Netherlands
    MS-Off Ver
    Office 2016
    Posts
    36

    Re: Help automatic an excel calculation

    I think I am missing something in your explanation, the numbers you write are formulas outcomes, I cannot really type them myself.
    For example, your first one for B2, .2213*a2.... 22,13 for me is the 21% tax of H2 which changes every time H2 changes.
    Are you able to re-upload my file so I see how you typed it?

    Thanks
    Last edited by AliGW; 08-06-2019 at 06:28 AM. Reason: Please don't quote unnecessarily!

  12. #12
    Registered User
    Join Date
    06-21-2017
    Location
    Missouri, USA
    MS-Off Ver
    2007 through 2019
    Posts
    71

    Re: Help automatic an excel calculation

    I just typed =.2213*a2
    Changing the percentages to be based off the wholesale price instead of the sale price means all you end up manually entering is the wholesale price. The outcome will still be 21% of h2, but you're getting there by finding 22.13% of a2. It's just some manipulation of the algebra to get to the same outcome.

  13. #13
    Registered User
    Join Date
    08-05-2019
    Location
    Offaly, Ireland
    MS-Off Ver
    365
    Posts
    20

    Re: Help automatic an excel calculation

    I think this will work for you in the way you have requested, please take a look and let me know if the figures are as expected, Once you confirm their all good i will make some small modifications and resend the finished spreadsheet to you.

    Martin
    Attached Files Attached Files

  14. #14
    Registered User
    Join Date
    06-21-2017
    Location
    Missouri, USA
    MS-Off Ver
    2007 through 2019
    Posts
    71
    I think E is meant to be dynamic instead of manually entered as 10%

  15. #15
    Registered User
    Join Date
    08-05-2019
    Location
    Offaly, Ireland
    MS-Off Ver
    365
    Posts
    20

    Re: Help automatic an excel calculation

    I think your right, but i'm allowing for future increase of profit margins if desired, in addition the sheet is not finished as of yet as i want the guy to confirm he is happy with the figures. Hopefully this is what he needs.

  16. #16
    Registered User
    Join Date
    10-11-2018
    Location
    Netherlands
    MS-Off Ver
    Office 2016
    Posts
    36

    Re: Help automatic an excel calculation

    Quote Originally Posted by SjMaxwell View Post
    I just typed =.2213*a2
    Changing the percentages to be based off the wholesale price instead of the sale price means all you end up manually entering is the wholesale price. The outcome will still be 21% of h2, but you're getting there by finding 22.13% of a2. It's just some manipulation of the algebra to get to the same outcome.
    I still don't get how I need to write it in the file.

  17. #17
    Registered User
    Join Date
    10-11-2018
    Location
    Netherlands
    MS-Off Ver
    Office 2016
    Posts
    36

    Re: Help automatic an excel calculation

    Quote Originally Posted by martinoconnor View Post
    I think this will work for you in the way you have requested, please take a look and let me know if the figures are as expected, Once you confirm their all good i will make some small modifications and resend the finished spreadsheet to you.

    Martin
    In your example, when I increase the buy price in A3 the Platform fee should also go up because the selling price goes up, this does not happen in your file.

  18. #18
    Registered User
    Join Date
    06-21-2017
    Location
    Missouri, USA
    MS-Off Ver
    2007 through 2019
    Posts
    71

    Re: Help automatic an excel calculation

    Let me know if this works for you.
    Attached Files Attached Files

  19. #19
    Registered User
    Join Date
    10-11-2018
    Location
    Netherlands
    MS-Off Ver
    Office 2016
    Posts
    36

    Re: Help automatic an excel calculation

    Quote Originally Posted by SjMaxwell View Post
    Let me know if this works for you.
    It looks extremely close, it seems that your formulas always aim to 10.83% profit,
    if you plug one of your prices in my formulas, and manually type the selling price, you see that my formula gives 10.83% profit,
    it could be that I don't know how to calculate profit... :-)

  20. #20
    Registered User
    Join Date
    10-11-2018
    Location
    Netherlands
    MS-Off Ver
    Office 2016
    Posts
    36

    Re: Help automatic an excel calculation

    Could you explain how did you derive the numbers you typed, like .2213 and .2445 and .1164 and .1736

    Thanks a lot

  21. #21
    Registered User
    Join Date
    06-21-2017
    Location
    Missouri, USA
    MS-Off Ver
    2007 through 2019
    Posts
    71
    Quote Originally Posted by 737ngx View Post
    Could you explain how did you derive the numbers you typed, like .2213 and .2445 and .1164 and .1736

    Thanks a lot
    Your example used 100 as the price and output 22.13 and so on for each of your values that were calculated off of H. 22.13 is 22.13% of 100 So I used .2213 as the multiplier. I left your 10% formula the same, still based off of H and I tried different values to make sure I met the 10% each time. If you provide a few more examples from your regular calculations I'll take a look.

  22. #22
    Registered User
    Join Date
    10-11-2018
    Location
    Netherlands
    MS-Off Ver
    Office 2016
    Posts
    36

    Re: Help automatic an excel calculation

    I just tested on a new price, see attachment line 2 and 4 for 23,99
    In line 2 which is my line, the 10% gives 6,26 platform fee and a selling price of 35,08
    but line 4 is different.
    Attached Files Attached Files

  23. #23
    Registered User
    Join Date
    06-21-2017
    Location
    Missouri, USA
    MS-Off Ver
    2007 through 2019
    Posts
    71

    Re: Help automatic an excel calculation

    I'm still trying to figure this out, but as I'm going through I noticed your formulas don't match up to your descriptions. You said B is 15% of your selling price, but your formula has you paying 15% plus a dollar. You said C is 21% of the selling price, but your formula has you paying 17.36%. I'm still stumped on automating H, but if you're currently working off this model I thought it might be important to point out.

  24. #24
    Registered User
    Join Date
    10-11-2018
    Location
    Netherlands
    MS-Off Ver
    Office 2016
    Posts
    36

    Re: Help automatic an excel calculation

    Quote Originally Posted by SjMaxwell View Post
    I'm still trying to figure this out, but as I'm going through I noticed your formulas don't match up to your descriptions. You said B is 15% of your selling price, but your formula has you paying 15% plus a dollar. You said C is 21% of the selling price, but your formula has you paying 17.36%. I'm still stumped on automating H, but if you're currently working off this model I thought it might be important to point out.
    Thanks for keep looking at this.
    You are correct, for B the platform takes a fixed dollar for every sales independently on the selling price plus 15% of the selling price.
    For C, I checked on the internet how to calculate product taxes, where I live we have a 21% VAT, the mathematical formula to calculate that is to divide the full price by 1. and the tax percentage which in my case is 21, the result gives you the net, than from the full price you take the net away and you are left with the tax amount. Besically you cannot just take the full price and multiply it by 0.21 to get the 21%, that would be incorrect.

    Thanks again

  25. #25
    Registered User
    Join Date
    08-05-2019
    Location
    Offaly, Ireland
    MS-Off Ver
    365
    Posts
    20

    Re: Help automatic an excel calculation

    Hi Guys

    My earlier solution is certainly not going to work out, i jumped the gun on a number of issue's. With that said I wonder if there is a way to increase or decrease cell H2 which ever is necessary, automatically in conjunction with cell E2 until cell E2 = 10. Then H2 should reveal the correct selling price. Just a thought.

  26. #26
    Registered User
    Join Date
    06-21-2017
    Location
    Missouri, USA
    MS-Off Ver
    2007 through 2019
    Posts
    71
    Quote Originally Posted by martinoconnor View Post
    Hi Guys

    My earlier solution is certainly not going to work out, i jumped the gun on a number of issue's. With that said I wonder if there is a way to increase or decrease cell H2 which ever is necessary, automatically in conjunction with cell E2 until cell E2 = 10. Then H2 should reveal the correct selling price. Just a thought.
    I keep ending up with circular formulas...maybe with some helper columns or a sort of additional rate sheet...

  27. #27
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: Help automatic an excel calculation

    The $100 price from the distributor includes VAT?

    Seems to me ...

    A
    B
    C
    1
    NL VAT
    21%
    B1: Input
    2
    3
    Distributor Price (incl VAT)
    $ 100.00
    B3: Input
    4
    Recovered VAT
    $ (17.36)
    B4: =-B3 * B1 / (1 + B1)
    5
    Seller Fixed
    $ 1.00
    B5: Input
    6
    Total Landed Cost
    $ 83.64
    B6: =SUM(B3:B5)
    7
    8
    Seller Variable
    15%
    B8: Input
    9
    VAT
    17.36%
    B9: =B1 / (1 + B1)
    10
    Profit
    10%
    B10: Input
    11
    Sell (incl VAT)
    $ 145.10
    B11: =B6 / (1 - SUM(B8:B10))
    Last edited by shg; 08-07-2019 at 11:24 AM.

  28. #28
    Registered User
    Join Date
    10-11-2018
    Location
    Netherlands
    MS-Off Ver
    Office 2016
    Posts
    36

    Re: Help automatic an excel calculation

    Quote Originally Posted by shg View Post
    The $100 price from the distributor includes VAT?

    Seems to me ...

    A
    B
    C
    1
    NL VAT
    21%
    B1: Input
    2
    3
    Distributor Price (incl VAT)
    $ 100.00
    B3: Input
    4
    Recovered VAT
    $ (17.36)
    B4: =-B3 * B1 / (1 + B1)
    5
    Seller Fixed
    $ 1.00
    B5: Input
    6
    Total Landed Cost
    $ 83.64
    B6: =SUM(B3:B5)
    7
    8
    Seller Variable
    15%
    B8: Input
    9
    VAT
    17.36%
    B9: =B1 / (1 + B1)
    10
    Profit
    10%
    B10: Input
    11
    Sell (incl VAT)
    $ 145.10
    B11: =B6 / (1 - SUM(B8:B10))
    Yes the 100 dollars include VAT, but I need to calculate that because I can claim it back which impacts my profit... in a positive way.

  29. #29
    Registered User
    Join Date
    08-05-2019
    Location
    Offaly, Ireland
    MS-Off Ver
    365
    Posts
    20

    Re: Help automatic an excel calculation

    If the purchase price is €100 and the Tax is 21%, the Tax amount should be €21 and NOT €17.36, In addition to that, the 10% profit is actually 9.9985%. So unless we work to 4 decimal places to get the actual rounded figures i don't see that it's possible to calculate the figures which are submitted here. That's my understanding of this problem.

  30. #30
    Registered User
    Join Date
    10-11-2018
    Location
    Netherlands
    MS-Off Ver
    Office 2016
    Posts
    36

    Re: Help automatic an excel calculation

    Quote Originally Posted by 737ngx View Post
    Yes the 100 dollars include VAT, but I need to calculate that because I can claim it back which impacts my profit... in a positive way.
    Are we sure that 145.10 is 10% profit?
    My manual formula tells me that I can sell the product at 140,87

  31. #31
    Registered User
    Join Date
    08-05-2019
    Location
    Offaly, Ireland
    MS-Off Ver
    365
    Posts
    20

    Re: Help automatic an excel calculation

    I am getting a sell price of €146.34

  32. #32
    Registered User
    Join Date
    10-11-2018
    Location
    Netherlands
    MS-Off Ver
    Office 2016
    Posts
    36

    Re: Help automatic an excel calculation

    Quote Originally Posted by martinoconnor View Post
    If the purchase price is €100 and the Tax is 21%, the Tax amount should be €21 and NOT €17.36, In addition to that, the 10% profit is actually 9.9985%. So unless we work to 4 decimal places to get the actual rounded figures i don't see that it's possible to calculate the figures which are submitted here. That's my understanding of this problem.
    No the 21% vat on 100 is not 21, check the calculator at http://www.vatcalculator.co.uk/ make sure you click on remove vat.

  33. #33
    Registered User
    Join Date
    08-05-2019
    Location
    Offaly, Ireland
    MS-Off Ver
    365
    Posts
    20

    Re: Help automatic an excel calculation

    Your correct, sorry

  34. #34
    Registered User
    Join Date
    08-05-2019
    Location
    Offaly, Ireland
    MS-Off Ver
    365
    Posts
    20

    Re: Help automatic an excel calculation

    I have been working away on this and finally i have a solution which works, not exactly what you want but it will work in a few clicks of a mouse button until someone else comes up with a formulae.
    Attached Files Attached Files

  35. #35
    Registered User
    Join Date
    10-11-2018
    Location
    Netherlands
    MS-Off Ver
    Office 2016
    Posts
    36

    Re: Help automatic an excel calculation

    Are you sure you attached the right version?
    The only difference I see is that now there are 4 digits after the comma, but when I change A nothing happens to H

  36. #36
    Registered User
    Join Date
    10-11-2018
    Location
    Netherlands
    MS-Off Ver
    Office 2016
    Posts
    36

    Re: Help automatic an excel calculation

    Quote Originally Posted by 737ngx View Post
    Are you sure you attached the right version?
    The only difference I see is that now there are 4 digits after the comma, but when I change A nothing happens to H
    Sorry, I see the buttons with the macro now, for some reason, my home computer was not showing them to me.
    I do find it better than mine since clicking is faster than keep typing various possible outcomes in column H.
    So if a better solution comes along good otherwise I really appreciate for all the time you invested in this.

    Thanks a lot
    Nic

  37. #37
    Registered User
    Join Date
    08-05-2019
    Location
    Offaly, Ireland
    MS-Off Ver
    365
    Posts
    20

    Re: Help automatic an excel calculation

    Hi Nic,

    I can add other amounts such as $50, $20 etc, I'm happy to be able to provide some assistance on this as time is plentiful for me.

    Martin

  38. #38
    Registered User
    Join Date
    08-05-2019
    Location
    Offaly, Ireland
    MS-Off Ver
    365
    Posts
    20

    Re: Help automatic an excel calculation

    P.S I'm sure someone will provide a good solution

  39. #39
    Registered User
    Join Date
    10-11-2018
    Location
    Netherlands
    MS-Off Ver
    Office 2016
    Posts
    36

    Re: Help automatic an excel calculation

    Quote Originally Posted by martinoconnor View Post
    Hi Nic,

    I can add other amounts such as $50, $20 etc, I'm happy to be able to provide some assistance on this as time is plentiful for me.

    Martin
    Thanks, Martin,
    but I tested it and the amounts you have are plenty.

    But thanks for offering.

    Regards
    Nic

  40. #40
    Valued Forum Contributor Hydraulics's Avatar
    Join Date
    07-15-2018
    Location
    Udine - Italy
    MS-Off Ver
    Office 365
    Posts
    369

    Re: Help automatic an excel calculation

    After rearranging, your Selling Price can be written as

    Please Login or Register  to view this content.
    I have tested the formula for some distributor price and seems to work.
    Attached Files Attached Files
    Aim high or don't even try.
    ---------------------------------
    If your question has been answered, don't forget to mark the thread as SOLVED.
    If you find an answer helpful, click on the star icon at the bottom of the post.

  41. #41
    Registered User
    Join Date
    08-05-2019
    Location
    Offaly, Ireland
    MS-Off Ver
    365
    Posts
    20

    Re: Help automatic an excel calculation

    Cheers to Hydraulics, I have also just conquered the beast, my spreadsheet will also work but you need to enable the Iterative function for formulae.

    In EXCEL
    Click file
    Scroll DOWN to OPTIONS
    Click Formulae
    Click Iterative

    CLOSE all open Windows until your back to your Spread Sheet and enjoy

  42. #42
    Registered User
    Join Date
    08-05-2019
    Location
    Offaly, Ireland
    MS-Off Ver
    365
    Posts
    20

    Re: Help automatic an excel calculation

    In my excitement i forgot to attach the spread sheet oooops
    Attached Files Attached Files

  43. #43
    Valued Forum Contributor Hydraulics's Avatar
    Join Date
    07-15-2018
    Location
    Udine - Italy
    MS-Off Ver
    Office 365
    Posts
    369

    Re: Help automatic an excel calculation

    And I forgot to add that 0.1 is the profit (10%), therefore the selling price can be found directly knowing the tax on sale, the fee and the profit.
    Attached Files Attached Files

  44. #44
    Registered User
    Join Date
    10-11-2018
    Location
    Netherlands
    MS-Off Ver
    Office 2016
    Posts
    36

    Re: Help automatic an excel calculation

    now that was an interesting one :-)
    Thank You both so much for finding the solution.

    Now give me some time to come up with a more challenging one :-)

    But seriously, this is really helpful and it will same me a lot of time in the long term.

  45. #45
    Registered User
    Join Date
    08-05-2019
    Location
    Offaly, Ireland
    MS-Off Ver
    365
    Posts
    20

    Re: Help automatic an excel calculation

    No problem and looking forward to the next one

  46. #46
    Valued Forum Contributor Hydraulics's Avatar
    Join Date
    07-15-2018
    Location
    Udine - Italy
    MS-Off Ver
    Office 365
    Posts
    369

    Re: Help automatic an excel calculation

    Quote Originally Posted by 737ngx View Post
    Now give me some time to come up with a more challenging one :-)
    Definitely waiting for it!

    Glad I could help.

  47. #47
    Registered User
    Join Date
    10-11-2018
    Location
    Netherlands
    MS-Off Ver
    Office 2016
    Posts
    36

    Re: Help automatic an excel calculation

    Hello,

    I need to re-open this thread because I am having a doubt in the calculation of my net profit %
    In order to get the net profit % would I be correct saying that I take the profit amount ("sale price before tax" minus "purchase price before tax") and divide it by the "purchase price minus tax"?

    Thanks
    Nic

  48. #48
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,369

    Re: Help automatic an excel calculation


  49. #49
    Registered User
    Join Date
    10-11-2018
    Location
    Netherlands
    MS-Off Ver
    Office 2016
    Posts
    36

    Re: Help automatic an excel calculation

    Quote Originally Posted by AliGW View Post
    I still don't know if I add taxes to the calculation or not.
    If you check the calculator in this link: http://https://www.omnicalculator.co...-profit-margin
    it asks for Net profit, so this is without taxes, but then it asks for Total revenue, if we think this in a simple transaction of me buying a pen at a price and selling it at a higher price, would my revenue be
    the selling price including tax or excluding?

    Thanks
    Nic

  50. #50
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,369

    Re: Help automatic an excel calculation

    Total expenditure means just that - TOTAL expenditure (i.e. ALL outgoings).

    Total income means just that - TOTAL income (i.e. ALL income).

    Once you have calculated this, take expenditure away from income to leave you with your net profit.

    Are you running a business?
    Last edited by AliGW; 02-21-2020 at 09:03 AM.

  51. #51
    Registered User
    Join Date
    10-11-2018
    Location
    Netherlands
    MS-Off Ver
    Office 2016
    Posts
    36

    Re: Help automatic an excel calculation

    Thanks, I am trying to open a small webshop without any employees.
    Can I give you an example and could you tell me what is my profit percentage? and show me the calculation behind it?

    Goods purchase price with tax = 9.72
    Goods selling price with Tax = 15.00

    Goods purchase price without tax = 8.03
    Goods selling price without tax = 12.40

    My ultimate goal is to know what is the % profit goes clean in my pocket, knowing that taxes on sales will go to the tax office, and taxes on purchases can be 100% claimed back.


    Thanks
    N

  52. #52
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,369

    Re: Help automatic an excel calculation

    1. You buy goods at £10. Your supplier charges you VAT (@ 20 pc in the UK this would mean a price of £12), which you claim back from the government annually via your accountant, so the cost to you remains at £10 (the base cost).
    2. You sell the goods for £20 with VAT on top (£4 - you keep this to one side to pay your tax bill at the end of the year), so your profit is £20-£10 which is £10.
    3. From of the £10 profit, you can draw a dividend and claim business expenses and, if you wish, pay yourself a salary.
    4. At the end of the financial year, you will pay corporation tax on whatever is left after dividend, expenses and salary (no idea what that would be in the Netherlands).

    It will be more complicated than this if you are selling items to other countries.

    This is a conversation you should be having with your ACCOUNTANT.

    I am marking this as SOLVED, as this is above and beyond the scope of the forum, and my comments are personal opinion, not professional advice.
    Last edited by AliGW; 02-21-2020 at 09:14 AM.

  53. #53
    Registered User
    Join Date
    11-30-2023
    Location
    pakistan
    MS-Off Ver
    2
    Posts
    1

    Re: Help automatic an excel calculation

    I was struggling to calculate the VAT of different countries. After some research, I found a website vatcalcfree.com, that can calculate the vat of different countries separately. I make the VAT calculation so easy and fast. I will recommend that you all use this VAT calculator.
    Last edited by anatolymaster; 12-01-2023 at 02:43 AM.

  54. #54
    Registered User
    Join Date
    03-16-2024
    Location
    United States
    MS-Off Ver
    Microsoft Office 2021
    Posts
    4

    Re: Help automatic an excel calculation

    It appears that there are discrepancies in the calculations provided in the paragraph. Specifically, the tax amount and profit percentage calculations seem to be incorrect.
    Tax Calculation: If the purchase price is €100 and the tax rate is 21%, the correct tax amount should be €21, not €17.36. This discrepancy suggests that there might have been an error in the initial calculation or misunderstanding of the tax calculation process.

    Profit Percentage: Initially calculated as 10%, upon closer examination, it's determined to be 9.9985% when rounded to four decimal places. This indicates the need for precision in calculations, especially when dealing with percentages that may impact financial decisions or transactions.

    To resolve these issues, it's essential to ensure accuracy in calculations by using the correct formulas and paying attention to rounding precision. By double-checking calculations and potentially working with more decimal places, more accurate results can be achieved, avoiding discrepancies in the final figures.

  55. #55
    Administrator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    MS365 Family 64-bit
    Posts
    24,532

    Re: Help automatic an excel calculation

    Quote Originally Posted by Ashlee98 View Post
    It appears that there are discrepancies
    You are responding to a SOLVED thread that is 4 years old.
    Jeff
    | | |·| |·| |·| |·| | |:| | |·| |·|
    Read the rules
    Use code tags to [code]enclose your code![/code]

  56. #56
    Administrator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    MS365 Family 64-bit
    Posts
    24,532

    Re: Help automatic an excel calculation

    Quote Originally Posted by anatolymaster View Post
    I was struggling to calculate the VAT of different countries. After some research, I found a website vatcalcfree.com
    You are responding to a SOLVED thread that is 4 years old. The website you refer to has no active URL resolution, although the domain name is registered. Further posts like this will be treated as spam.

  57. #57
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,090

    Re: Help automatic an excel calculation

    For what it's worth, there's this: https://vatcalculator.eu/

    Googling finds lots of VAT calculators.

    Probably worth locking this now?
    Trevor Shuttleworth - Retired Excel/VBA Consultant

    I dream of a better world where chickens can cross the road without having their motives questioned

    'Being unapologetic means never having to say you're sorry' John Cooper Clarke


  58. #58
    Administrator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    MS365 Family 64-bit
    Posts
    24,532

    Re: Help automatic an excel calculation

    The OP did not ask about how to calculate VAT when the thread was started and subsequently solved over four years ago. Thread closed.

Closed Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 6
    Last Post: 12-06-2015, 10:05 AM
  2. Excel Formula-Automatic Calculation
    By ljgriffith12 in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 08-27-2014, 06:11 PM
  3. Excel turns of Automatic Calculation automatically
    By Plaus in forum Excel General
    Replies: 24
    Last Post: 06-06-2013, 10:28 AM
  4. [SOLVED] Excel 2010 - Function not updating (#value) in automatic calculation or otherwise
    By bassplr19 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 05-15-2013, 04:36 PM
  5. Automatic Calculation:open excel.
    By pkbravo in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 04-04-2006, 10:12 AM
  6. Replies: 0
    Last Post: 11-02-2005, 06:17 PM
  7. Replies: 4
    Last Post: 09-13-2005, 11:05 PM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1