Speed Up VBA for Bulk Goal Seek

[leeandoona]

I'm running a bulk Goal Seek/Set using the following code over 10,000 rows. It works great but it takes about a day to run the macro! Is there any advice on how I can make it work Faster?

Sub LOWEST_BULK_GOAL_SET()
Dim iRow As Long

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

 

[StephenCrump]

Can you please provide a bit more detail?

What's the nature of the calculations linking columns N and F?

Why do you need 10,000 iterations?

 

[leeandoona]

Can you please provide a bit more detail?

What's the nature of the calculations linking columns N and F?

Why do you need 10,000 iterations?

The calculations in the row perform a set of 'SWITCH IF' calculations. They look at a couple of user selected drop-down selections being made and then carry out a calculation based on those selections. The calculations themselves are basic enough but they do rely on some vlookups to bring back the appropriate numbers with those IF choices made. Here's an example of what is happening in each row:

IF 'Channel' = 'eBay' AND IF 'Vatrate' = '1.20' AND IF 'Shipping' = 'Packet' THEN (excel then carries out the calculations required to show a Retail Price which is equal to a defaulted profit margin percentage).

During the calculation, excel uses the GOAL SEEK function to change a 'Mark-up' amount (column N) which gives a 'Net Margin' percentage in column N.

The reason it performs this over 10,000 rows is because it has to work this out for every 1-pence increment of cost price between £0.01p through £100.00. The file is then used to carry out Vlookups on cost price and give the corresponding sale price for any given cost price whislt also working out the profit margin %. I've never been able to figure out a way to get excel to do this without having it repeat the calculation downwards over the rows. I guess, with a bit more expertise and time, a Macro could be written that does all that in the background and is called up to run next to the cost prices in a worksheet and just put in the the correct retail prices but its probably beyond my current expertise to write this.

Thanks for trying to figure this one! I know I can possibly do something that switches off excel screen updates and switches it back on again but I've tried that a few times and it didn't seem to make any difference to the time lag. Currently it takes many hours to run this macro.

 

[StephenCrump]

Can you please provide some examples to illustrate the calculation of retail price for a give cost price?

On the face of it, it should be possible to convert cost price --> retail price using a formula approach, i.e. no need for GoalSeek, or creating a table with all results from £0.01 through £100.00.

You also quote one possible combination of Channel, Vatrate and Shipping. How does the calculation vary for other combinations, and does this mean you are calculating 10,000 prices for each possible combination of Channel, Vatrate and Shipping?

 

[leeandoona]

Can you please provide some examples to illustrate the calculation of retail price for a give cost price?

On the face of it, it should be possible to convert cost price --> retail price using a formula approach, i.e. no need for GoalSeek, or creating a table with all results from £0.01 through £100.00.

You also quote one possible combination of Channel, Vatrate and Shipping. How does the calculation vary for other combinations, and does this mean you are calculating 10,000 prices for each possible combination of Channel, Vatrate and Shipping?

I'll try but although the maths is simple enough, the multitude of combinations can make it quite difficult to decipher! Excel Colummns then;

A - Drop-down selection containing 2 options 'eBay' or 'Amazon'
B - Drop-down selection containing 2 options for VAT rate '1.20' or '0.00'
C - Discount Percentage on Cost Price in Column D. Example 0.00%
D - ExVat Cost Price - example 5.00
E - IncVat Cost Price =IF(B2=0,D2*(1-C2),(D2*(1-C2)*1.2))
F - Markup Calculation - example 1.46
G - Retail Price =SWITCH(B492,1.2,AR492,0,AT492)
H - Drop-down selection containing 2 options for shipping 'Packet' or 'Large Letter'
I - Total Sale Value (TSV) This cell looks up another worksheet TAB and is returning a match for the Shipping Choice which is equivalent to the corresponding shipping charge being added to the RP =G2+VLOOKUP(H2,'Key Facts'!A$2:C$3,3,FALSE)
J - COGS (cost of goods/services) - This cell is looking up the choices made in A and H and entering the corresponding value from another column in that worksheet which in turn is referencing another worksheet for the resulting number. So the calculation in column J is =IF(AND(A2="eBay",H2="Large Letter"),AV2,IF(AND(A2="eBay",H2="Packet"),AX2,IF(AND(A2="Amazon",H2="Large Letter"),AZ2,IF(AND(A2="Amazon",H2="Packet"),BB2,"FALSE")))) and that is referencing cells that contain similar calculations to this =((I2*'Key Facts'!G$2)*1.2)+'Key Facts'!M$2+E2+((I2*'Key Facts'!J$2)*1.2) Their job is to return a summary of other related costs which are sale price dependent.
K - Gross Profit Margin =I2-J2
L - VAT DUE. This cell has to perform a calculation to work out the EXACT amount of VAT due (if you are not familiar with UK VAT this might be a challenge for you - essentially the VAT due is the difference between the VAT paid out and the VAT received). The calculation is this =IF(B2=0,IF(AND(A2="eBay",H2="Large Letter"),AF2,IF(AND(A2="eBay",H2="Packet"),AL2,IF(AND(A2="Amazon",H2="Large Letter"),AI2,IF(AND(A2="Amazon",H2="Packet"),AO2,"FALSE")))),IF(AND(A2="eBay",H2="Large Letter"),AE2,IF(AND(A2="eBay",H2="Packet"),AK2,IF(AND(A2="Amazon",H2="Large Letter"),AH2,IF(AND(A2="Amazon",H2="Packet"),AN2,"FALSE"))))) Again this referencing a cell which in turn uses a calculation like this =(I3/120*20)-(E3-D3)-('Key Facts'!L$3-'Key Facts'!K$3)-('Key Facts'!F$3-('Key Facts'!B$3+'Key Facts'!E$3))-((I3*'Key Facts'!J$2*1.2)-I3*'Key Facts'!J$2)-AP3 which is referencing another worksheet Tab for the resulting number.
M - Net Profit Margin =K2-L2
N - Net Margin as a percentage =M2/D2

As you can see. It's not a straightforward calculation as there are too many IFs involved to make it easy.

The goal of this spreadsheet is to ask a series of questions and give accurate answers based on user selection. Essentially you are asking for an answer to this;

"If I sell a widget on eBay or Amazon that is Vatable or Non-Vatable and is a Large Letter or a Packet, how much do I need to mark it up for to get X profit margin."

The holy grail is the answer! Unfortunately it becomes very complicated by things such as;

1. Sales Channel Fees (plus vat)
2. Shipping Fees (plus vat) + Variable Fuel Surcharges (Plus Vat)
3. Packaging Costs (plus Vat)
4. Third Party Fees (plus Vat)
5. Banking fees (such as PayPal)
6. VAT due on Sales price

Although I could use an approximate percentage which is equal to all those things and an approximate monetary value of costs applicable per sale, because of the margins involved in retailing in an online environment it needs to be 100% accurate to the point of a penny otherwise you simply lose money or don't make a sale.
The reason then for such a complicated way of working out a simple answer is because of the many variables involved and the need to be able to ultimately carry out some API/Auto VLookups against live cost price data every 5 minutes to return the correct corresponding retail prices as markets change with price matching.

And you thought selling on eBay was easy huh?!

 

[StephenCrump]

Thanks. The point still is that you don't need to use Goal Seek for what is simple algebra, in essence:

Sale Price = (Buy Price * (1+% costs relating to Buy Price) + Other Costs) / (1 -% costs relating to Sale price)

Sure, the calculations for some components such as postage might be complex, and we'll need to include allowance for VAT paid on Buy price and other inputs, and VAT collected on sale price and other amounts charged such as postage. But the principle is the same.

Turning around the formula above, we can build up Sell Price as follows:

Buy Price
+ % costs relating to Buy Price
+ Other Costs
+ % Costs relating to Sell Price
= Sell Price

I'm not sure of all the nuances of your formula, but you should be able to provide a similar build up? If so, we can reverse it into a formula, hence no need for Goal Seek.

 

[leeandoona]

Thanks. The point still is that you don't need to use Goal Seek for what is simple algebra, in essence:

Sale Price = (Buy Price * (1+% costs relating to Buy Price) + Other Costs) / (1 -% costs relating to Sale price)

Sure, the calculations for some components such as postage might be complex, and we'll need to include allowance for VAT paid on Buy price and other inputs, and VAT collected on sale price and other amounts charged such as postage. But the principle is the same.

Turning around the formula above, we can build up Sell Price as follows:

Buy Price
+ % costs relating to Buy Price
+ Other Costs
+ % Costs relating to Sell Price
= Sell Price

I'm not sure of all the nuances of your formula, but you should be able to provide a similar build up? If so, we can reverse it into a formula, hence no need for Goal Seek.

I did actually attempt something like that a while back and it failed miserably! Here's the basis for that formula and I've worked it backwards as it seems to make more sense this way. You'll see there are essentially 4 variable types of sale that have a further 6 sub variables. For example there will be 1 of 2 sales channels that are either VATable or NONVATable and they will be sent using 1 of 6 shipping services.

The retail price (RP) depends on the markup (MU) which in turn depends on the net profit % (NP%). The goal seek was doing this for me, by saying change MU (which is the exvat cost multiplied by a number) to make the NP% = 10%. The conundrum with arriving at that figure is that the RP dictates the fees paid to the sales channel. So BEFORE you know what RP/MU you need, you must first know what your NP% to make adjustments up or down to the MU to give the correct RP equal to the correct NP. That's probably why I find it easier to explain it backwards from the sales price like this;

Fees:
eBay
Channel Fees 15% (plus VAT) = 18%
PayPal 2% + 20p per sale
Third Party fees - 3% (plus VAT) = 3.6%
Packaging 15p (plus vat) = 18p
Total 23.6% + £0.38p

Amazon
Channel Fees 15% (plus VAT) =18%
Third Party Fees - 3% (plus VAT) = 3.6%
Packaging 15p (plus VAT) = 18p
Total 21.6% + 18p

Variables;

Vatable eBay Sale

TSV (Total Sales Value) = X
- Cost (incl VAT)
- Shipping Cost (incl VAT)
- Fees % (TSV*23.6%)
- Fees £ 0.38p
- VAT DUE (TSV/120*20)-(Cost*0.2)-(Shipping Cost/120*20)-(TSV*18%/120*20)-(Cost120*20)
= GROSS TSV-(Cost*1.20)-Shipping Cost-Fee%-Fee£-VAT DUE
= NET (Gross-(Gross*10%))

NON-Vatable eBay Sale

TSV (Total Sales Value) = X
- Cost (zero VAT)
- Shipping Cost (incl VAT)
- Fees % (TSV*23.6%)
- Fees £ 0.38p
- VAT DUE ((Shipping Cost/120*20)-(TSV*18%/120*20)-(Cost))
= GROSS TSV-(Cost)-Shipping Cost-Fee%-Fee£-VAT DUE
= NET (Gross-(Gross*10%))

Vatable Amazon Sale

TSV (Total Sales Value) = X
- Cost (incl VAT)
- Shipping Cost (incl VAT)
- Fees % (TSV*21.6%)
- Fees £ 0.18p
- VAT DUE (TSV/120*20)-(Cost*0.2)-(Shipping Cost/120*20)-(TSV*15%/120*20)-(Cost120*20)
= GROSS TSV-(Cost*1.20)-Shipping Cost-Fee%-Fee£-VAT DUE
= NET (Gross-(Gross*10%))

NON-Vatable Amazon Sale

TSV (Total Sales Value) = X
- Cost (zero VAT)
- Shipping Cost (incl VAT)
- Fees % (TSV*21.6%)
- Fees £ 0.38p
- VAT DUE ((Shipping Cost/120*20)-(TSV*15%/120*20)-(Cost))
= GROSS TSV-(Cost)-Shipping Cost-Fee%-Fee£-VAT DUE
= NET (Gross-(Gross*10%))

Sub Variables;

SERVICE	Weights	EXVAT (inc fuel surcharge) COST	INCVAT COST
RM 1 L	0 - 100g	0.68	0.82
RM 1 LL	100g-250g	0.95	1.14
RM 1 PCKT	250g-1kg	3.05	3.66
RM 2 L	0 - 100g	0.46	0.55
RM 2 LL	100g-250g	0.73	0.88
RM 2 PCKT	0 - 100g	2.45	2.94

NOTE* When using the above to make a calculation you may notice that for some results, the VAT DUE element will return a minus amount. This is because if a cost price is low enough and or not vatable it is possible to be owed VAT by the tax man as I've paid out VAT on fees, VAT on Shipping, VAT on packaging and VAT on third party fees even if the product itself wasn't vatable, all the services around it were vatable.

Hope this makes sense?

 

[StephenCrump]

Thanks for the detail.

It may be a couple of days until I can look at this. But someone else may like to jump in?

 

[leeandoona]

Thanks for the detail.

It may be a couple of days until I can look at this. But someone else may like to jump in?

Thanks! I fully appreciate if its not possible to find a workable solution. It's simple but yet complicated all at the same time! (hence that Goal seek solution I came up with!)

 

[StephenCrump]

If your markup is expressed as a percentage of CostPriceExVAT, then I think you simply need:

TSV = [CostPriceExVAT(1+MarkUp%) + ShippingExVAT + ChannelFees$ExVAT] / [1-ChannelFees%ExVAT - VAT/(1+VAT)]

See if the calculations below do what you need? You'll need the rounding parameter in B6 set to 2, which may produce the occasional £0.01 rounding discrepancies in the margin.

File for download: Box

Book1
	A	B	C	D
1	Channel	Amazon	(eBay or Amazon)
2	VAT	20%	(0% of 20%)
3	Cost Price	£50.00	incl VAT
4	Shipping Cost	£8.00	incl VAT
5	Markup	10.0%	on Cost price ex VAT
6	Round to	2	decimal places
7
8		Fee	VAT	incl VAT
9	Channel Fees	15.0%	3.0%	18.0%
10	PayPal	0.0%		0.0%
11		£0.00		£0.00
12	Third Party	3.0%	0.6%	3.6%
13	Packaging	£0.15	£0.03	£0.18
14	Total Channel Fees	18.0%	3.6%	21.6%
15		£0.15	£0.03	£0.18
16
17	Cost Price	£41.67	£8.33	£50.00
18	Shipping Cost	£6.67	£1.33	£8.00
19
20	VAT claim		£9.69
21		plus:	3.0%	of TSV
22
23	TSV:			£80.60
24	less:
25	Cost Price ex VAT claimable			£41.67
26	Shipping cost ex VAT claimable			£6.67
27	Channel fees ex VAT claimable			£14.66
28	VAT payable on TSV			£13.43
29				£76.43
30	Margin			£4.17
31	cf: Required margin			£4.17
Sheet1

Cell Formulas
Range	Formula
C9, C12:C13	C9	=VAT*B9
B10	B10	=IF(Channel="eBay",2%,0)
B11	B11	=IF(Channel="eBay",0.2,0)
D9:D11, D12:D13	D9	=SUM(B9:C9)
B14:D14	B14	=B9+B10+B12
B15:D15	B15	=B13+B11
B17:B18	B17	=ROUND(D17/(1+VAT),N)
C17:C18	C17	=D17-B17
D17:D18	D17	=B3
C20	C20	=C13+C17+C18
C21	C21	=C9
D23	D23	=ROUND((SUM(B15:B18)+B5*B17)/(1-(B14+VAT/(1+VAT))),N)
D25:D26	D25	=B17
D27	D27	=ROUND(B14*D23,N)+B15
D28	D28	=ROUND(D23*VAT/(1+VAT),N)
D29	D29	=SUM(D25:D28)
D30	D30	=D23-D29
D31	D31	=B5*B17

Cells with Data Validation
Cell	Allow	Criteria
B1	List	eBay,Amazon
B2	List	0%,20%