Speed Up VBA for Bulk Goal Seek

leeandoona

New Member
Joined
Oct 13, 2016
Messages
45
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
 

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.
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?
 
Upvote 0
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.
 
Upvote 0
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?
 
Upvote 0
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?!
 
Upvote 0
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.
 
Upvote 0
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;

SERVICEWeightsEXVAT (inc fuel surcharge) COSTINCVAT COST
RM 1 L0 - 100g0.680.82
RM 1 LL100g-250g0.951.14
RM 1 PCKT250g-1kg3.053.66
RM 2 L0 - 100g0.460.55
RM 2 LL100g-250g0.730.88
RM 2 PCKT0 - 100g2.452.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?
 
Upvote 0
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?
 
Upvote 0
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!)
 
Upvote 0
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
ABCD
1ChannelAmazon(eBay or Amazon)
2VAT20%(0% of 20%)
3Cost Price£50.00incl VAT
4Shipping Cost£8.00incl VAT
5Markup10.0%on Cost price ex VAT
6Round to2decimal places
7
8FeeVATincl VAT
9Channel Fees15.0%3.0%18.0%
10PayPal0.0%0.0%
11£0.00£0.00
12Third Party3.0%0.6%3.6%
13Packaging£0.15£0.03£0.18
14Total Channel Fees18.0%3.6%21.6%
15£0.15£0.03£0.18
16
17Cost Price£41.67£8.33£50.00
18Shipping Cost£6.67£1.33£8.00
19
20VAT claim£9.69
21plus:3.0%of TSV
22
23TSV:£80.60
24less:
25Cost Price ex VAT claimable£41.67
26Shipping cost ex VAT claimable£6.67
27Channel fees ex VAT claimable£14.66
28VAT payable on TSV£13.43
29£76.43
30Margin£4.17
31cf: Required margin£4.17
Sheet1
Cell Formulas
RangeFormula
C9, C12:C13C9=VAT*B9
B10B10=IF(Channel="eBay",2%,0)
B11B11=IF(Channel="eBay",0.2,0)
D9:D11, D12:D13D9=SUM(B9:C9)
B14:D14B14=B9+B10+B12
B15:D15B15=B13+B11
B17:B18B17=ROUND(D17/(1+VAT),N)
C17:C18C17=D17-B17
D17:D18D17=B3
C20C20=C13+C17+C18
C21C21=C9
D23D23=ROUND((SUM(B15:B18)+B5*B17)/(1-(B14+VAT/(1+VAT))),N)
D25:D26D25=B17
D27D27=ROUND(B14*D23,N)+B15
D28D28=ROUND(D23*VAT/(1+VAT),N)
D29D29=SUM(D25:D28)
D30D30=D23-D29
D31D31=B5*B17
Cells with Data Validation
CellAllowCriteria
B1ListeBay,Amazon
B2List0%,20%
 
Upvote 0

Forum statistics

Threads
1,223,911
Messages
6,175,324
Members
452,635
Latest member
laura12345

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top