Increase Percentage / Margin by 5% - Logic

[leeandoona]

Quote taken from this thread: https://www.mrexcel.com/forum/excel-questions/889707-increase-percentage-margin-5-logic.html

Not sure if this is what you're looking for or need.

Assuming:
A1 = Current Cost
B1 = Current Margin
C1 = Expected Cost (or Target Cost)
D1 = 5% (or Target Margin)
E1 = Your Sale Price (SP), which is fixed and known.

in C1, use:
=IF(E1/A1<1.05,(E1/1.05),A1)

in D1, use:
=((E1-C1)/C1)*100

Ignore the *100 in the D1 formula if you have the Cell formatted as percentage.

Let us know if this is what you're looking for.

I have a similar conundrum to the one above but I can't make the solution above work for me because my challenge is slightly different. I wonder if you know how I would do the following:

A=Cost
B=Markup
C=Retail Price
D=Fees
E=Other Deductions
F=Gross
G=Net (as a percentage of F/C)

So in the example above (A) is 1.00 and I mark it up by (B) 2.48 (A*B)+2.99. This gives me (C) which is the retail price including shipping (2.99). (D) contains the fee percentage, in this case 15% and (E) contains a monetary value which represents (E) (C*D). This leaves me a Gross amount in (F) which is calculated as C-E-A-2.99. Finally I can see what my actual % of profit is in (G) Net as a percentage which is calcualted F/C.

The conundrum is. I want to always get that same percentage at the end and I want excel to automatically work out the markup amount to ensure the resulting percentage is 12% (or very close to it and never below it). If I try to do this, excel can't because the sum relies on a circular calculation. I need to just run down a list of cost prices and apply a desired markup figure to always get 12% at the end. Try as I might, I can't figure this out. Any help would be much appreciated? I have posted this seperately but didn't get any luck.

Thanks.

 

[Manuel_Spreadsheet]

Look up the Goal Seek function in Excel. You can use it to have Excel set your net percentage (G) to a value of 12% by changing the markup (B).

 

[leeandoona]

Look up the Goal Seek function in Excel. You can use it to have Excel set your net percentage (G) to a value of 12% by changing the markup (B).

Thanks for the suggestion. Unfortunately it's not working. It attempts to do it but the result only seems to work for the first few rows and then the percentage starts to creep up?

Awsome suggestion though!

 

[leeandoona]

Look up the Goal Seek function in Excel. You can use it to have Excel set your net percentage (G) to a value of 12% by changing the markup (B).

How do you repeat the Goal Seek function for multiple rows? I have thousands of rows. It's worked on the first row only although it appeared to try and work on the others as I coukld see it apparently calculating iterations for other rows but they didn't change beyond the first few rows.

 

[Manuel_Spreadsheet]

Can you share a copy of your spreadsheet?

 

[leeandoona]

Can you share a copy of your spreadsheet?

I've resolved this with the 'GOAL SEEK' function. Although as I have such vast amounts of data to do this with I've (thanks to this forum) got sub routine for VBA that repeats the goal seek function which is below:

Sub BULK_GOAL_SEEK ()
Dim iRow As Long

For iRow = 2 To 10001
Cells (iRow, "N") .GoalSeek Goal:=0.05, ChangingCell:=Cells(iRow, "E")
Next iRow
End Sub

This works just dandy but as you can see over 10,000+ rows this takes many hours to run. That said, it saves many weeks! Ideally I'd have a bit of code that speeds this repeat/loop up a bit but I don't see how I could as for the most part this is pretty much reliant on the Goal Seek to do it's thing in each row and given the criteria its working with in my example, whereby it is taking a whole bunch of other calculations and attempting to reach the very specific goal of 0.5% it just takes a few seconds each row. I'll post a condensed version of the data I'm working with to this post also though just in case there is a better way, So far though, the Bulk Goal Seek is working, albeit a bit slow when running over more than a few hundred rows.

 

[Gerald Higgins]

Hi. I'm not sure but I think this is just a maths / algebra question which we should be able to resolve without goalseek.

But I'm struggling to follow your example.

So in the example above (A) is 1.00 and I mark it up by (B) 2.48 (A*B)+2.99. This gives me (C) which is the retail price including shipping (2.99)

What exactly does this mean ?

If A = 1 and B = 2.48, I can't see how you get 2.99.

A x B (or A*B) = 2.48.
A + B = 3.48.

 

[leeandoona]

Hi. I'm not sure but I think this is just a maths / algebra question which we should be able to resolve without goalseek.

But I'm struggling to follow your example.


What exactly does this mean ?

If A = 1 and B = 2.48, I can't see how you get 2.99.

A x B (or A*B) = 2.48.
A + B = 3.48.

It is indeed a maths/algebra problem but one that becomes annoyingly messy very quickly thanks to tax and tax on commission. Here's an exact breakdown of the process:

A= 1.00 (Pretaxcost)
B= 1.10 (Posttaxcost)
C= 0.65 (Pretax Shipping Cost)
D=0.78 (Posttax Shipping Cost)
E=0.99 (Shipping Charge/Sale inclusive of Tax)
F=0.12 (Pretax Materials Cost)
G=0.14 (Posttax Materials Cost)
H= 1.55 (Markup)
I= 5.00 (Example Retail Price inclusive of Shipping Charge)
H=15% (Commission, so that's 15% of 5.00)
I= VARIABLE (Tax on Commission, so tax on 15% of 5.00)
J=1.5% (Referral Fee, so that's 1.5% on 5.00)
K= VARIABLE (Tax on Referral Fee, so that's Tax on 1.5% of 5.00)
L=0.30 (Fixed Per transaction fee, not taxable)
M=3.00% (Banking Fee per transaction, so that's 3% of RP 5.00 and is not taxable)
N=COST OF SALE VARIABLEE (Posttax Cost+PostTax Shipping Cost+Posttax Materials Cost+Commission+Referral+Tax of Commission+Tax of Referral+Banking+Banking2)
O=GROSS (Retail Price-Cost of Sale)
P=NET (Retail Price - Tax - Cost of Sale)
Q=NET% (NET/PRETAX COST%)
R=20% (Tax)

So the cost of sale maths looks like this =(B2+D2+G2+H2+I2+J2+K2+L2+M2)
GROSS maths is =(I2-N2)
NET maths is =(I2-(I2/120*20)-N2)
NET% maths is =(Q2/A2)

The rest is worked out by multiplying the pretax cost by R2 being the tax. Post sale tax is worked out using the accounting method to reflect tax on the sale element (we also usually deduct from this what we spent out to reflect what is actually due in tax on this sale from the retailer perspective but that's excluded from this example as its not relevant.)

So in this example the Goal Seek will change to reference the Goal (Q2) by changing (H2).

There is added complication(not done in this example) in that Shipping is inclusive of retail price but is Taxable at point of sale but is 'lost' in the retail price. If the retail item is not taxable then this becomes more complicated as the item itself is not taxable but the services all were, as was the shipping. Therefore there is still a Tax element deductible at point of sale on non taxable goods that must be deducted from tax paid out on those items. The situation becomes further complicated in that some Commission is taxable, and others do not charge tax on it but I've excluded that from this example. Hence this is why commission, Banking and referral is all broken out separately rather than just one combined percentage. With regards the previous example (from a different and less complicated format of the same issue), the 2.99 shipping is arrived at as a fixed charge the customer pays that is added to the retail price to become the retail price inclusive of shipping (it is the difference between 2.48 and 2.99 that is profit on shipping in other words). This example is using an assumption that 0.99 of the retail price is being charged in shipping to the customer but included in the retail price. Hence the tax deductible issue on the profit of the shipping which is always taxable, even on non taxable goods.

The aim of the game is to obtain a % profit margin that reflects the pretax cost of a product. This margin is worked out by multiplying the pretax cost (A) by the Markup (H) which will ultimately give a % that is equal to a net profit amount of that cost price at the end, after all those other deductions were worked out in the middle. This is why GOAL SET works to make adjustments faster than I can, as I'm always left manually adjusting the markup calculation to give the desired result of Net profit at the end. The Bulk Goal Seek makes this all possible without thinking...anything which involves me not thinking is desirable!

The challenge is that there are variables in Cost, Shipping, Tax and desired net Margin. So far I've managed this by using Switch and If functions to change the Variables and set the desired net. This all gets clunky and ssssllllloooooowwww!

 

[Gerald Higgins]

OK I'm losing this one . . .

What is the calculation that you actually want to perform ?

 

[shg]

SellPrice = (Cost + Shipping) / (1 - Margin)

e.g., for a $10 cost, $3 shipping, and 12% margin,

SellPrice = (10 + 3)/(1 - 12%) = $14.77