# % Markup Issue

1. ## % 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  Register To Reply

2. ## Re: % Markup Issue

Post a sample workbook showing the issue.  Register To Reply

3. ## 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.  Register To Reply

4. ## 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%)  Register To Reply

5. ## Re: % Markup Issue

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

6. ## 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.  Register To Reply

7. ## Re: % Markup Issue Originally Posted by Tagliaboo 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%.  Register To Reply

8. ## Re: % Markup Issue

Thanks it works great!!  Register To Reply

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