+ Reply to Thread
Results 1 to 8 of 8

% Markup Issue

  1. #1
    Registered User
    Join Date
    10-27-2003
    Posts
    18

    Question % Markup Issue

    I have a proposal work sheet that has a list of dollar amounts. I would like to create a formula to at 11% markup. The issue is when I add the 11% markup it changes the total. I am currently calculating in manually on a calculator several times until the amount matches. See Example below.

    Markup
    Item 1 $6,849.00
    Item 2 $3,425.00
    Item 3 $4,000.00
    Total $14,274.00

    The first time I calculate 11% it is $1570 - Total $15844
    Next 11% is $1743 - Total $16017
    Next 11% is $1762 - Total $16036
    Next 11% is $1764 - Total $16038
    Next 11% is $1764 - Total $16038 I am done.

    The larger the number the more times it takes to get a matching number. Is there a formula to do this calculation automatically?

    Thanks

  2. #2
    Forum Expert (Lazarus)
    Join Date
    02-14-2009
    Location
    .
    MS-Off Ver
    .
    Posts
    2,594

    Re: % Markup Issue

    Post a sample workbook showing the issue.

  3. #3
    Registered User
    Join Date
    10-27-2003
    Posts
    18

    Re: % Markup Issue

    I listed the process I go through. You can't see on the workbook I use because it is done with a calculator.

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

    Re: % Markup Issue

    Welcome to the board.

    A
    B
    C
    2
    Item 1
    $ 6,849.00
    3
    Item 2
    $ 3,425.00
    4
    Item 3
    $ 4,000.00
    5
    Total
    $ 14,274.00
    B5: =SUM(B2:B4)
    6
    $ 16,038.20
    B6: =B5/(1-11%)
    Entia non sunt multiplicanda sine necessitate

  5. #5
    Registered User
    Join Date
    10-27-2003
    Posts
    18

    Re: % Markup Issue

    the issue occurs when you calculate the markup it changes the grand total creating a circular expression

  6. #6
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    12,644

    Re: % Markup Issue

    The way you are describing it, you are using a simple "successive approximations" or "simple iteration" approach. This can be set up in Excel using circular references and turning iteration on.

    However, shg's approach is using basic algebra. I know that we told our algebra teachers back in school that we would never need algebra because we would always have a calculator or computer to do the work for us. Little did we know that computers are good at arithmetic, but not really good at algebra.

    I am not a financials person, but the usual expression I see is something like:
    total=cost+markup
    markup=1.11*total
    total=cost+1.11*total
    This can easily be solved for total. I will assume you remember from school how to solve these kind of equations. If you need a refresher on how to solve equations where the unkown is on both sides of the equal sign, I will recommend this tutorial: http://www.purplemath.com/modules/solvelin3.htm note the 5th and later examples on that page.

    If you are using a dramatically different equation for markup, then explore that equation. I expect it will have a similar solution.
    Quote Originally Posted by shg
    Mathematics is the native language of the natural world. Just trying to become literate.

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

    Re: % Markup Issue

    Quote Originally Posted by Tagliaboo View Post
    the issue occurs when you calculate the markup it changes the grand total creating a circular expression
    You can change the 11% gross margin in B6 to any number other than 100%.

  8. #8
    Registered User
    Join Date
    10-27-2003
    Posts
    18

    Re: % Markup Issue

    Thanks it works great!!

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Sliding calculation of markup.
    By dougall in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 02-08-2013, 03:44 PM
  2. % markup to end in $.99
    By fubar2378 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 02-08-2011, 10:29 AM
  3. formula for calculating Markup and Vat
    By ianmb in forum Excel General
    Replies: 5
    Last Post: 03-23-2010, 01:34 PM
  4. markup formula
    By skyjoe in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 11-11-2009, 04:18 PM
  5. markup percentage
    By Winston in forum Excel - New Users/Basics
    Replies: 6
    Last Post: 10-08-2009, 07:05 AM
  6. Markup Problem
    By 3smees23 in forum Excel General
    Replies: 1
    Last Post: 05-18-2009, 11:14 AM
  7. Dividing a markup
    By gtapper in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 12-19-2006, 05:38 PM
  8. [SOLVED] Remove html markup
    By Stuart in forum Excel Formulas & Functions
    Replies: 19
    Last Post: 11-25-2005, 11:50 AM

Tags for this Thread

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